DTS and stopred procedures

I have a sproc defined many databases. It has the same neme in every databases and does the same in every databases too.

I have a table that lists all the companies where the sproc exists. (BISYMAP)
The field that holds the database name is INTERID.

I need to run every instance of the sproc that exists on my SQL server. I did a cursor that returns the database name into a variable and I use an EXEC statement to run the sprocs. I build the name using the variable filled by the cursor. (@.TgtCoy)

Here it is.
__________________________________________________ __

/*List all companies from system table and put it in @.TgtCoy*/
DECLARE @.TgtCoy AS CHAR(5)

DECLARE Coys CURSOR FOR
SELECT DISTINCT RTRIM(INTERID)
FROM BISYMAP
WHERE INTERID <> ''

OPEN Coys

FETCH NEXT FROM Coys INTO @.TgtCoy

WHILE @.@.FETCH_STATUS = 0
BEGIN
/* Execute biCoMapGlAccount within each company*/
EXEC('EXEC ' + @.TgtCoy + '.dbo.biCoMapGlAccount')
FETCH NEXT FROM Coys INTO @.TgtCoy
END
CLOSE Coys
DEALLOCATE Coys

__________________________________________

This runs fine in SQL Query Analyzer. The issue is when I try to run this from an SQL Task in a DTS package. I can't even save the task. SQL tries to validate the query before saving and returns the following error: ... Could not find stored procedure '.dbo.biCoMapGlAccount'.

I also tried to put this into system stored procedure and call it from my DTS package but I get the same error.

Anybody ever had to deal with such a situation? If so, I would certainly appreciate a hint or two on how you didi it.

Thanks in advance

RomboltI found the answer to my problem... and I'm almost ashamed to tell.

I insisted on pressing the "Parse Query" button after filling the query in the DTS SQL Task, and that's when I would get the error... Well, guess what, all I had to do was NOT to press it. That way the expression is not evaluated and if it is syntaxly correct, it will work fine at runtime.

If you press "parse query", it looks like SQL evaluates the query line by line so, in my case, it failed.

That's all there was to it.

Thanks

Rombolt

Dts (ms Access To Ms Sql Server)

hi,
I have 10 tables in ms access.
currently what i am doing is ...
1) import data into ms sql server, hence creating 10 tables
2) i have 2 procedures which does some calculations (basically consolidation of data) based on 10 tables. And insert data or update data into 2 tables.

Can i automate this etl process..
take data from 10 ms access tables
consolidate and put the data into 2 ms sql server

I want this process to occur every weekends
Please guide if any body has done this kind of processNote: Ms Sql Server 2000|||Based on the title of the topic :), you've already found out that this kind of tasks can be easily done with DTS packages.
Please describe what problems do you have, as creating DTS packages for your case should be a pretty simple task: create package, add connection to MS Access, add one task to import data and another one to execute SPs, etc.|||1) I have a ms access 2000 database with tables a, b, c, d, e, f, g, h, i, j (10 tables)
2) I have 2 ms sql server 2000 procedure which retrieves data from all these tables, does some calculations, and insert or updates the rows in ms sql server table x and y
3) I have 2 ms sql server 2000 tables

The procedure works perfectly if these tables are ms sql server table and if I execute those procedures manually (i.e. execute pro_1...)
How do I automate it? (Using DTS)
(Note: I am new to SQL Server :-))|||1. Create required connections.
2. Create tasks to import 10 tables from MS Access to SQL Server (you will have to create 10 tables in MSSQL for storing imported data)
3. Create task(s) to execute stored procedures
4. If required - create task to cleanup the tables with imported data (you can do it from stored procedures).|||I dont want to import the data into MS SQL SERVER.
My MS Access tables are very big containing millions of records.

(Problem with importing: Table space will exceed the disk space!, so no question of importing)
Without importing the data into Sql Server.
How, my procedure can refer the 10 MS Access tables as source and put the resultant value into 2 MS Sql Server tables?
Or is there any alternative?|||You can add a linked server (see samples here ) or use one of the Rowset Functions in your stored procedures (consider using OpenQuery or OpenRowset Rowset Functions so that only aggregated data would be transmitted to SQL Server)|||Thanks...this sovled my problem.
What is the solution if the .mdb Access file resides in a unix server.|||This gives the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Even after registry entry.

What is the solution if the .mdb Access file resides in a unix server.|||FTP the mdb file to Windows at regular interval|||I think, the mdb file is access by some other application or one of the table is opened by you.|||Thanks,
Srinidhi Rao

dt_addtosourcecontrol

I would like to know this stored
procedures 'dt_addtosourcecontrol' description, funtions,
when, why and how it is used. Thanks.
I try to search the SQL 2000 book online and Microsoft SQL
web site, but can not find the correct information.Please debug the code :-) (It's not documented)
>--Original Message--
>I would like to know this stored
>procedures 'dt_addtosourcecontrol' description, funtions,
>when, why and how it is used. Thanks.
>I try to search the SQL 2000 book online and Microsoft
SQL
>web site, but can not find the correct information.
>.
>

dt_ stored procedures

I am trying to use microsoft access project file to access msde databases on local workstations. I have to set the users security to db_owner due to the application requirements.
The problem users are having is since they are owners they see the dt_?? stored procedures in the queries list which are created when access addes a table to the database.
I need to keep these "Version Control" stored procedures from being created. I have tried triggers on the sysobject table but this is not allowed. The users can not delete the triggers due to sql security.
I did find out that in access if the view system objects is not check the dt_ stored procedures will not show up. But I still want to stop these from being created.
Any ideas on how to stop them from being created.
dt_ procedures are created by default (I thought only when diagrams are
created). I don't believe you can prevent them from being created, however
you could have the app use a custom query from INFORMATION_SCHEMA views that
leaves out any objects that start with dt_
http://www.aspfaq.com/
(Reverse address to reply.)
"Eric Brasher" <Eric Brasher@.discussions.microsoft.com> wrote in message
news:FAB9C710-45A0-47D2-9914-CB94D55138F4@.microsoft.com...
> I am trying to use microsoft access project file to access msde databases
on local workstations. I have to set the users security to db_owner due to
the application requirements.
> The problem users are having is since they are owners they see the
dt_?? stored procedures in the queries list which are created when access
addes a table to the database.
> I need to keep these "Version Control" stored procedures from being
created. I have tried triggers on the sysobject table but this is not
allowed. The users can not delete the triggers due to sql security.
> I did find out that in access if the view system objects is not check the
dt_ stored procedures will not show up. But I still want to stop these from
being created.
> Any ideas on how to stop them from being created.