DTS Error (ActiveX)

Hello,

I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such:

set objConn = Server.CreateObject("ADODB.Connection")

I get this as a resolution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;201740

But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas?

Thanks.

Try this url for sample code. Hope this helps.

http://www.sqldts.com/default.aspx?272

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 2K and Hang with Ingres import

Hi All,

I have a problem with a DTS package to import data from one of our Ingres based Databases.

I've recently moved the DTS package from one server to another and recreated the Ingres ODBC Settings, which are all OK.

When I run the package through EM and the CMD line, it runs OK, but not as a scheduled task.

I've had a look at a KB Article Q318819 and followed the steps about package threads on 3rd party drivers, but this doesent help, nor does making sure DTSRun isnt in the current task list.

Could anyone help me out please?

TIA.

Hi,

Can anyone help me?

Thanks

|||

Steve,

This isn't a DTS forum. Check out the microsoft.public.sqlserver.dts newsgroup

-Jamie

Dts

I have 3 separate DTS to transfer tables.
They transfer tables from 3 different Access databases
into one SQL server database.
But I would like to make them into one dTS so that I can
just check on error log file.
How can I do this?
Thanks for any suggestions.Basic copy-pasting of the entire contents of your DTS packages into one would do the trick because a DTS can have many separate connections and many "tasks" even if they are not related.

Now, you might want to keep them separate because if one fails (because of data transformation for example), you could still run the other two. If you keep them separate, use 'dtsrun' with the SQL Server Agent to run all your packages in different steps (and setting the "On failure" option to "Go to the next step") hence -kind of- grouping them together.

Good luck,

SC|||You can enable DTS package logging as specified by BOL:
To enable package logging

Open the Data Transformation Services (DTS) package for which you want to create a log.

On the Package menu, click Properties to display the DTS Package Properties dialog box.

Do one of the following:
Save package logs to Microsoft SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.

Security Note When possible, use Windows Authentication.

Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
... which will help you to assess the package(s) information.|||Thanks for all your responses.

Dtabases over multiple instances

Hi all,
For performance reason, I want to devide all databases over multiple
instances.
Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
In this case, the application would need to know on which
VirtualServer/Instance the database resides.
If there is no way to tell the application, is there a solution within
SQL(2000) for it to do some kind of redirect to the rightServer/Instance
where the database does reside ?
I am looking for something similar like Exchange, where all servers in the
Organisation know where(which server) your mailbox resides.
So you could connect to any mail server and get directed to the one with
your mailbox.
Thx,
Erwin
Clustering doesn't work like that. Each instance owns a defined set of
databases. Each database can be owned by only one instance. The entire
instance fails as a unit to another host node should the first host node go
down. Clustering is not a scale-out technology.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> For performance reason, I want to devide all databases over multiple
> instances.
> Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
> In this case, the application would need to know on which
> VirtualServer/Instance the database resides.
> If there is no way to tell the application, is there a solution within
> SQL(2000) for it to do some kind of redirect to the rightServer/Instance
> where the database does reside ?
> I am looking for something similar like Exchange, where all servers in the
> Organisation know where(which server) your mailbox resides.
> So you could connect to any mail server and get directed to the one with
> your mailbox.
> Thx,
> Erwin
>
|||Thx,
I understand that.
I am looking for some kind of solution where you could connect to A database
without having to know which server(or instance) the database physically
resides(clustered or non-clustered).
As in the Exchange example, connecting to An Exhange server which will
redirect you to the server actually holding your mailbox.
It would be a good option for SQL to be able to do this too.
Thx,
Erwin
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> Clustering doesn't work like that. Each instance owns a defined set of
> databases. Each database can be owned by only one instance. The entire
> instance fails as a unit to another host node should the first host node
go[vbcol=seagreen]
> down. Clustering is not a scale-out technology.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
the
>
|||Hi Eppie,
You can puiblish the database to AD and let you're application search for
the database name in AD ; find the server/instance name and connect. This
solution however has nothing to do with clustering but counts for all SQL
sever installations.
The SQL server magazine had an article on this in their April number
providing explanation and code to get it working. (see link below)
http://www.winnetmag.com/Windows/Art...41/pg/1/1.html
(members only)
"Eppie" wrote:

> Thx,
> I understand that.
> I am looking for some kind of solution where you could connect to A database
> without having to know which server(or instance) the database physically
> resides(clustered or non-clustered).
> As in the Exchange example, connecting to An Exhange server which will
> redirect you to the server actually holding your mailbox.
> It would be a good option for SQL to be able to do this too.
> Thx,
> Erwin
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> go
> the
>
>

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.

dt_ procs and the public role

In a SQL 2k instance (latest SP) some of my user databases show the public role with execute on a variety of stored procs named dt_* (i.e. dt_addtosourcecontrol). However, not all the user databases do this, some do not grant the public role execute on these procs .

So, can someone explain what generates these permissions and is it acceptable to remove them? If I have a database that does not grant public access, should I be concerned? I don't see any reference to these procs in BOL.

TIA,

Moblex

This seems to be a Visual Studio generated procedure, so you should ask this question on a Visual Studio forum. Here's a related thread

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=562892&SiteID=17

that I found with the following search query:

http://search.live.com/results.aspx?q=dt_addtosourcecontrol&mkt=en-us&FORM=LVSP&go.x=10&go.y=13

Thanks
Laurentiu