can this be done?

We have a large customer database in MSSQL 2000, and we are looking at scheduling software that uses MS-Access for the DB. is there an easy way to take the customer data from our AR_CUST table in SQL and link/update/insert as into Access? I've seen all kinds of stuff about going from Access to SQL, btu not the other way. HELP!
AndyI believe Access has a table import utility which allows this

You will find it in
Files --> Get External data --> Import|||yup,
I see that. in order to use it though, we would have to import every time we add a customer before we could schedule them. I was looking for more of an ODBC/dynamic type thing.|||Don't import in to Access!

Perform an Link to SQL Server from Access...using Code or File>GetExternalData>Link

If you do the later you'll need to set up an ODBC Connection (Code is better)

DONT USE sa as your login|||HEY BRETT,
I've done the link thing, and the SQL table appears in the DB for the new software. HOWEVER, the DB already has a table (Customers) and the SQL table is AR_CUST. The field names don't match, and I can't seem to find a way to get the data from SQL INTO the customer table!|||The "application" has data tables already?

Ohh I get it...

1st make a backup copy of the application, and put it away somewhere safe..

How big is the app btw?

This may be tedious..actually I wrote code to do this...

But create what I call Alias Views for the table...

Queries and tables in Access are referenced the same way...

Take your AR_CUST, and rename it with an "X_" in front...

The create abn access query called AR_CUST..use the sql server customer table as the data source

Now map the fields...make sure that every column in the AR_CUST table is there...even if you don't have that field in sql server..

AND make sure the names are identical to AR_CUST

for example if SQL Sever has LName and AR_CUST is Last_Name

Map it in the QBE Grid as Last_Name: LNAME

Do that for every column in the table.

Now do it for every table

This way, you've "Faked" the code out...it'll never know it's talking to sql server...

Now, this is hookey at best...AND if the code/forms are written poorly, you'll notice a performance hit...

For example, if they have a dozen drop downs on a form, and they all talk to the backend, it'll open a connection per...if they're upodateable, it'll be 2 per...

GOOD LUCK|||thanks Brett,
I'll give it go. our customer DB has about 6000 records, and i can't see re-typing all the time...|||Lettuce know how it turns out;-)

btw...6,000 isn't big...|||No,
6000 isn't very big. There is a large amount of admin related to this business, tho, and trying to keep the office staff updating the schedule software will never work. I'll let you no!|||Ok,
started on this, and I'm a little confused. In SQL the table is AR_CUST.
in Access the table is 'Customers'. Now, I need to rename the the Access table 'Customers' to something like 'Customersx' or 'XCustomers'; anything but 'Customers', right? Then in Access create a new Query using the AR_CUST table in SQL as the data source named 'Customers'. That is how far I've got. Now how do I map the query to work correctly? Do I need to map it to a new 'Customers' table? I can't seem to find an area in Access to define the mapping! Talk about feeling like a DooF!
Andy|||If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.

1. If the data structure of the Customers table is the same in both the current Access database and the SQL Server database then you can archive the current Access database. Create a new copy of the Access database and remove the Access Customers table, then create a linked table to your AR_CUST table in SQL Server and rename the linked table in Access to Customers

2. If the data structure of the Customers table in Access is different to the data structure in SQL Server then create a view in SQL Server renaming all the columns to the correct Access column names. Then create a linked table within Access to the view and rename the view to Customers.

Using the above methods means that you won't need to change any code or forms in Access, unless the Access database is using ADO to access a Customers table somewhere, but this seems unlikely as it appears that the Access database is a stand-alone database at the moment.

If you do use ADO you will either have to change the Access forms, queries and everything to use AR_CUST or change SQL Server to use a table called Customers.|||Originally posted by sjp
If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.


No. You don't want to store the data in Access.

1. You want the Query to be named AR_CUST
2. You want to pick the linked SQL Server Table Customer as the Datasource
3. You want to make sure you have every column that exists in the AR_CUST Table in the query, AND Named exactly as the way they are in AR_CUST
4. You need to establish a mapping for every column, like, for example the customers last name..

The SQL would look Like

If the Customer table has the field called LName and AR_CUST is defined as Last_Name

SELECT LName AS Last_Name FROM dbo_Customer

In the QBE Grid it would look like

Last_Name: LName

You need to make sure that you have every single column that AR_Cust has. If not you'll have problems...|||gotcha Brett,
I'll let you know how I make out...

Can stored procedure be called recursively?

Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?

what happens when you run this:

---

create table test (i int)
go
insert into test values(0)
go
create proc TryTest
as
declare @.i int
update test set i = i + 1
select @.i = i from test
print @.i
exec TryTest
go
exec TryTest

|||? Yes, it can. 32 levels. You can also use @.@.NESTLEVEL to determine the current level: CREATE PROCEDURE recurseASBEGIN PRINT @.@.NESTLEVEL EXEC recurseENDGO EXEC recurseGO DROP PROC recurseGO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <nonno@.discussions.microsoft.com> wrote in message news:452f0e66-9594-4818-9d92-6de799ad0dd5@.discussions.microsoft.com...Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?|||

Does @.@.NESTLEVEL has a transactional scope?

What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction?

Like:
UserA calls the procudure for the first time: NestLevel = 1
UserA calls the procedure again: NestLevel = 2

What will be the value of NestLevel for user B? 1 or 3?

TIA,

Isabel

|||? 1. It's the nesting level for the current scope; it has nothing to do with some other connection's scope. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Isabel_ve@.discussions..microsoft.com> wrote in message news:dd99b941-0788-4916-a1f8-19fe6117f330@.discussions.microsoft.com... Does @.@.NESTLEVEL has a transactional scope? What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction? Like:UserA calls the procudure for the first time: NestLevel = 1UserA calls the procedure again: NestLevel = 2 What will be the value of NestLevel for user B? 1 or 3? TIA, Isabel

Can stored procs run after handle is closed?

I have written a stored proceedure for MSSQL that needs to run for hours at
a time. I need to execute it from C++ code. The current code does:

nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)

followed shortly after by a

Free_Stmt_Handle(hstmt) //roughly

The stored proc currently dies with the statement handle, not fully
populating the table I need it to.

I need to either know when the proc finishes so I can close the handle after
that, or allow the proc to run independently on the server no matter what
the program is doing (is exited, etc), either of these is fine.

Please Help! Thanks in advance!
JosephI know nothing about C++, but if the proc runs for a very long time, it
might be better to implement it as a scheduled job. The client could
set a flag or insert a row into a 'queue' table, then you have a job
which runs every few minutes or whatever, and if the flag is set, it
then starts the stored proc.

Simon|||That is an interesting approach, ideally I would like to stay as far away
from the database as I can but it sounds like this could be the best way...
my stored procedure is running for the exact same number of instructions and
then dying, whereas if I run it via Query Analyzer it runs to completion.

I finally caved and just copy-pasted from Q.Analyzer into code to confirm
this. I will investigate a little further before taking that plunge.

Thanks
Joseph

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1112950699.762977.19700@.o13g2000cwo.googlegro ups.com...
>I know nothing about C++, but if the proc runs for a very long time, it
> might be better to implement it as a scheduled job. The client could
> set a flag or insert a row into a 'queue' table, then you have a job
> which runs every few minutes or whatever, and if the flag is set, it
> then starts the stored proc.
> Simon

Can not show all level in analysis manager MSSQL Server 2000

Hi,

I use MSsql server 2000 and analysis service for create cube.But when I drilldown many Dimension on Analysis manager it can not show all level.
If I drilldown some level it can show data but when I drilldown many level so it have many row (I test about 60,000) it can not show result.
How can I show all result in Analysis manager or other tool ?

Thank you : )I sometimes use MDX sample aplication i got with the installation of Analysys Services to run MDX queries on cube data.

If you simply want to browse some specific portion of the data, maybe it would help to write MDX queries specifying smaller subsets of the data?