Duplicate Server Name When Creating ODBC

When creating a SQL Server ODBC connection via Data Source applet on a WXP
workstation, when clicking the drop down menu to select the server, two
instances of the same server appear. We recently migrated our SQL server to
new hardware. Turned off the old server and gave the new server the same name
and IP. Also renamed the instance of SQL on the server to the old name. I am
sure this is the cause but not sure how to remove the duplicate entry on the
workstation. We are only seeing this on one workstation? Thanks for your
feedback.
netwerktek
On this computer do the following:
Go to Start - Run
Type in cliconfg and click OK
This will start the Client Network Utility
Go to the Alias tab
There should be an alias defined for this server
Delete the alias. this the where the second entry is coming from.
(Before deleting note the properties. You may need to add it back if
connections fail after deleting it.)
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||That did it. Thank you very much!
"Rand Boyd [MSFT]" wrote:

> On this computer do the following:
> Go to Start - Run
> Type in cliconfg and click OK
> This will start the Client Network Utility
> Go to the Alias tab
> There should be an alias defined for this server
> Delete the alias. this the where the second entry is coming from.
> (Before deleting note the properties. You may need to add it back if
> connections fail after deleting it.)
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Duplicate Server Name When Creating ODBC

When creating a SQL Server ODBC connection via Data Source applet on a WXP
workstation, when clicking the drop down menu to select the server, two
instances of the same server appear. We recently migrated our SQL server to
new hardware. Turned off the old server and gave the new server the same nam
e
and IP. Also renamed the instance of SQL on the server to the old name. I am
sure this is the cause but not sure how to remove the duplicate entry on the
workstation. We are only seeing this on one workstation? Thanks for your
feedback.
--
netwerktekOn this computer do the following:
Go to Start - Run
Type in cliconfg and click OK
This will start the Client Network Utility
Go to the Alias tab
There should be an alias defined for this server
Delete the alias. this the where the second entry is coming from.
(Before deleting note the properties. You may need to add it back if
connections fail after deleting it.)
Rand
This posting is provided "as is" with no warranties and confers no rights.|||That did it. Thank you very much!
"Rand Boyd [MSFT]" wrote:

> On this computer do the following:
> Go to Start - Run
> Type in cliconfg and click OK
> This will start the Client Network Utility
> Go to the Alias tab
> There should be an alias defined for this server
> Delete the alias. this the where the second entry is coming from.
> (Before deleting note the properties. You may need to add it back if
> connections fail after deleting it.)
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Dumb question: How do I view a report?

I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
for my username and password even though I'm using Windows integrated
security for SQL Server. What do I need to do to create/access reports?Never mind. I'd typed in http://localhost/ReportServices$SQLSERVER2005 when
I was prompted for a username and password. I should've typed in
http://localhost/Reports$SQLSERVER2005/Pages/ReportViewer.aspx ...
As I said, it's a dumb question ... hahaha.
"AlBruAn" wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?|||On Apr 9, 1:22 pm, AlBruAn <albr...@.hotmail.com.(donotspam)> wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?
If you are using a browser other than IE, this may occur. If not, then
you will want to check the Directory Security (for the Reports and
ReportServer virtual directories). You do this via: Right-click My
Computer -> Select Manage -> Navigate to Services and Applications ->
Internet Information Services -> Web Sites -> Default Web Site. Then,
right-click Reports (or ReportServer) and select Properties -> select
the Directory Security tab -> select the Edit button just below
'Anonymous access and authentication control' and check to see if you
have either 'Anonymous access' selected or 'Integrated Windows
Authentication.' If anonymous is selected, most likely SSRS will not
allow you to avoid providing authentication (if it allows navigation
to the URL at all). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom
|||

Sam,

I still get an error. The package execution failed

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom|||

Sam,

I still get an error. The package execution failed

DTSrun utility security ?

Using SQL2000

I've created a DTS package and I now want to kick it off via a webpage interface. The package is realatively simple and either adds new rows to a table or updates them or both.

My question is this. If I create a batch file on the server that calls the DTS package and the Webpage is the client which executes the batch file what security context is the package going to run in.

The dtsrun batch file looks like this.
dtsrun /Sservername /Npackagename /E /Mpassword

The /E tells the package to use windows authentication.

As always, ThanksHi

A batch file will usually run in the security context of the user that kicked it off. Its worth making sure the user context has the required access to tables stored procedures etc ( read, update etc ) as well.

Be careful too if you are passing user crededtials from a web site on the internet to the SQL box - its an accident waiting to happen unless its an encrypted link ( people can "sniff" network traffic to capture the username & password). Alternatively, you could have a web page that updates a field in a table and have a job on the server that scans the table for a change in value, then kicks off a DTS package. This stops credentials being passed around the network.

Cheers,

SG.|||Great information and very unique way of getting around the username and password thing. I was expecting that I would have to encrypt but with your suggestion I'm thinking I'll try the table update you suggested.

I was fairly sure about the security context but I wanted to double check.

Thanks for the reply

Mike|||Howdy

Glad I could help.

Cheers

SG

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.com
Replace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com
|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script
.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/ ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A99
9760D84E91CD170332E5D7CB590B764064631A19
51589D6F310C930314F0F68864FCADAF0232
E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B
352C4319ED868A5232FAEDCA9031A0BB43FB
FF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> / ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A
999760D84E91CD170332E5D7CB590B764064631A
1951589D6F310C930314F0F68864FCADAF02
32E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE36
1B352C4319ED868A5232FAEDCA9031A0BB43
FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an I
D
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job vi
a
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
--
Message posted via http://www.sqlmonster.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?
Message posted via http://www.sqlmonster.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
SQLMonster.com" <u3288@.uwe> wrote:
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some
>> sort
>> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>> So what you are telling me is there the long string does not refer to an
>> ID
>[quoted text clipped - 14 lines]
>> sort
>> that I can compare?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1

DTSrun permissions

Hi

We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.

C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

We have also tried this with the /E option with the same results.

Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'

well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.

dts2000 migration, activex script and odbc connections help needed

Apologies if this has been covered before or if i am in the wrong place.

I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS package that connects to an AS400 to retrieve teh record count of a table for integrity checking. Under SQL server 2000 this worked fine but in SSIS it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server 05 on windows 2003. The odbc driver for the as400 has been installed also.

The activex script is as follows:

dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string

set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%

'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"

set rs1 = cn.execute (insertstr1)

'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function

I have since changed MSDASQL to SQLNCLI but (after errors re ad hoc distributed queries which i have enabled) i now get errors re linked servers:

ole db provider "sqlncli" for linked server "(null)" returned message "invalid connection string attrbute"

Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks

Chris

I am contacting the SSIS team but I suspect that you need to run the 32-bit version of SSIS since - as you pointed out - the ODBC driver is a 32-bit DLL, somehwat similar to the case discussed at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1.

|||

I confirmed: if there is only 32-bit ODBC driver, you need to run the package using 32-bit runtime.

DTS: Inserting the contents of a global variable into a table via a loop

Hello,

I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:

I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)

I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).

I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.

Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.

Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10

if Inputvalue < 10, Output Value = 20, else Output Value = 30.

If you are trying to make those types of changes, then you can do some simple Transforms.

With two data sources, you can add a Transform Data Task.

You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.

If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.

I'm impressed you could decipher what I was after!

Thanks for the help.

dts/ sql server 7.0

hai i am new

i am trying to execute DTS (dtsrun) via query analyser, it showing error as "
Execute permission denied on object 'XXX',Database 'YYY',Owner 'dbo' "You're probably not the owner of the dts.|||

Quote:

Originally Posted by space1000

You're probably not the owner of the dts.

without having the owner rights how to handle the same|||

Quote:

Originally Posted by raveekumarg

without having the owner rights how to handle the same

The system administrator should give the user enough rights on the ntfs system.

DTS, Excel, and ActiveX

Hi-
I have an excel spreadsheet that I want to poulate with data via DTS. What
I need is the previous data to be cleared from the spreadsheet starting
with the second row(The first row is going to be column headers). Can that
be accomplished via an ActiveX? Any help is appreciated.Hi
You could either start with a template file without any data and then
copy/create the final data file before you start see
http://www.sqldts.com/default.aspx?292 for help.
Alternatively this may help to delete the whole sheet
http://www.sqldts.com/default.aspx?245
If not then issue a SQL delete statement againsta range in your sheet.
John
"Jeff York" wrote:

> Hi-
> I have an excel spreadsheet that I want to poulate with data via DTS. Wha
t
> I need is the previous data to be cleared from the spreadsheet starting
> with the second row(The first row is going to be column headers). Can tha
t
> be accomplished via an ActiveX? Any help is appreciated.

dts works but run same dts via sql job failed

hi all,
i posted this somewhere else but i think here is the right group.

i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i really hope and appreciate if someone out there can help me out. thanks!

ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...

what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.

start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.

hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...

anymore ideas can help me out here? please please...

tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!

1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.

2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?

hope u all out there can help! thanks...really appreciate yr help.

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?