Showing posts with label string. Show all posts
Showing posts with label string. Show all posts

Thursday, March 29, 2012

can't conect remotely to SQL Express

I am connecting remotely to SQL Express using my VB application. Here is the
connection string that I use.
cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrated
Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
"database=CopyRequest"
I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
connect. What is the cause of this error? I allow remote connections on SQL
Server.Hi
Have you checked the permissions for 'ILYA-TEST-PC\Guest' ?
John
"Ilya" wrote:

> I am connecting remotely to SQL Express using my VB application. Here is t
he
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrate
d
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on SQ
L
> Server.|||Guest is disabled by default. I assume you're not logged in as guest when
you try to make the connection so this error means you are connecting as a
Windows user who doesn't have permissions or can't be authenticated to the
local Windows machine. Get Windows security working and I think it will
work.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:6CAFF2A5-3273-4E28-AFEB-07060DFFE503@.microsoft.com...
>I am connecting remotely to SQL Express using my VB application. Here is
>the
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network
> Library=DBMSSOCN;Integrated
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on
> SQL
> Server.

can't conect remotely to SQL Express

I am connecting remotely to SQL Express using my VB application. Here is the
connection string that I use.
cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrated
Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,14 33;" & _
"database=CopyRequest"
I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
connect. What is the cause of this error? I allow remote connections on SQL
Server.
Hi
Have you checked the permissions for 'ILYA-TEST-PC\Guest' ?
John
"Ilya" wrote:

> I am connecting remotely to SQL Express using my VB application. Here is the
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrated
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,14 33;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on SQL
> Server.
|||Guest is disabled by default. I assume you're not logged in as guest when
you try to make the connection so this error means you are connecting as a
Windows user who doesn't have permissions or can't be authenticated to the
local Windows machine. Get Windows security working and I think it will
work.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:6CAFF2A5-3273-4E28-AFEB-07060DFFE503@.microsoft.com...
>I am connecting remotely to SQL Express using my VB application. Here is
>the
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network
> Library=DBMSSOCN;Integrated
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,14 33;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on
> SQL
> Server.

can't conect remotely to SQL Express

I am connecting remotely to SQL Express using my VB application. Here is the
connection string that I use.
cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrated
Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
"database=CopyRequest"
I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
connect. What is the cause of this error? I allow remote connections on SQL
Server.Hi
Have you checked the permissions for 'ILYA-TEST-PC\Guest' ?
John
"Ilya" wrote:
> I am connecting remotely to SQL Express using my VB application. Here is the
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network Library=DBMSSOCN;Integrated
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on SQL
> Server.|||Guest is disabled by default. I assume you're not logged in as guest when
you try to make the connection so this error means you are connecting as a
Windows user who doesn't have permissions or can't be authenticated to the
local Windows machine. Get Windows security working and I think it will
work.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:6CAFF2A5-3273-4E28-AFEB-07060DFFE503@.microsoft.com...
>I am connecting remotely to SQL Express using my VB application. Here is
>the
> connection string that I use.
> cn.ConnectionString = "Provider=SQLNCLI;Network
> Library=DBMSSOCN;Integrated
> Security=SSPI;"Server=192.168.10.243\SQLEXPRESS,1433;" & _
> "database=CopyRequest"
> I get "Login failed for user 'ILYA-TEST-PC\Guest'" error when I try to
> connect. What is the cause of this error? I allow remote connections on
> SQL
> Server.

Sunday, March 25, 2012

Can't Add Tables To A Diagram

SQL Server 2000 - Latest SP, etc...
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
Jerry
Hi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Can't Add Tables To A Diagram

SQL Server 2000 - Latest SP, etc...
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
JerryHi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Can't Add Tables To A Diagram

SQL Server 2000 - Latest SP, etc...
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
JerryHi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, March 22, 2012

cant access to remote MS SQL server!

Here is the connection string i used:

"Data Source=<IP>,1433;Network Library=DBMSSOCN;Initial Catalog=<dbname>;User ID=sa;Password=<pwd>;"

i use that to connect to the remote SQL server, which has status of port 1433 at "Stealth"... do i need to open the port complete, if yes how i do that ?

does the SQL Server must use SQL authentication ?

Your help is greatly appreciated.Try this:

server=<ip>;uid=sa;pwd=<pwd>;database=<dbname
Remove everything else from the connection string.|||thnx, btw, do i need to add the port # after the IP ?|||yes, if it is not default (not 1433)
if it is 1433, there is no need to specify it

Sunday, February 19, 2012

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce

this issue.

1. I could set the value of the "SourceConnection" property to

the name of the connection I want to use in my bulk insert task and it

works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.

|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce

this issue.

1. I could set the value of the "SourceConnection" property to

the name of the connection I want to use in my bulk insert task and it

works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.

|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot set Connection property of Backup database task if connection string is customized in con

I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.

Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.

Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.

Is this an intrinsic issue?

Not quite sure what this issue is, but I tried this-

Created a ADO.Net connection and set an expression on the ConnectionString property. Evaluated value is a valid conn string.

Add a Backup DB Task, and set an expression on the Connection property. Evaluated value is name of connection created above.

Works fine, executes fine. If the Tasks' Connection property, the literal value is wrong at design-time, but will be correct at run-time when the expression is evaluated overriding the literal value, you may want to set the DelayValidation property to True for the task.

|||Thanks, this one - setting the DelayValidation was useful. What I tried was to customize the connectionstring in expression property of ConnectionManager. My issue is now solved, Thanks a lot.

Cannot set Connection property of Backup database task if connection string is customized in

I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.

Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.

Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.

Is this an intrinsic issue?

Not quite sure what this issue is, but I tried this-

Created a ADO.Net connection and set an expression on the ConnectionString property. Evaluated value is a valid conn string.

Add a Backup DB Task, and set an expression on the Connection property. Evaluated value is name of connection created above.

Works fine, executes fine. If the Tasks' Connection property, the literal value is wrong at design-time, but will be correct at run-time when the expression is evaluated overriding the literal value, you may want to set the DelayValidation property to True for the task.

|||Thanks, this one - setting the DelayValidation was useful. What I tried was to customize the connectionstring in expression property of ConnectionManager. My issue is now solved, Thanks a lot.

Tuesday, February 14, 2012

Cannot save a long string

This is what I am trying to save and what is actually being saved:

All variables are STRINGS.

Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:

Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:

The codes:

Table scripted to a query:

USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored procedure is:


USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@.symbol varchar(50),
@.cDate varchar(50),
@.part varchar(50),
@.collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@.symbol,
@.cDate,
@.part,
@.collection)
END

C# procedure:

public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@.symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@.cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@.part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@.collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis

Table is defined with the last columns[3].Length = 500;

What happened to the rest of my strings?

I would appreciate any help.

Many thanks.

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

Thank you Jens.

I actually figured it out right after I posted the message but in the end I did a complete workaround even without SQL Server. Probably was very tired that day. Goofy!

Sunday, February 12, 2012

Cannot save a long string

This is what I am trying to save and what is actually being saved:

All variables are STRINGS.

Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:

Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:

The codes:

Table scripted to a query:

USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored procedure is:


USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@.symbol varchar(50),
@.cDate varchar(50),
@.part varchar(50),
@.collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@.symbol,
@.cDate,
@.part,
@.collection)
END

C# procedure:

public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@.symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@.cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@.part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@.collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis

Table is defined with the last columns[3].Length = 500;

What happened to the rest of my strings?

I would appreciate any help.

Many thanks.

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

Thank you Jens.

I actually figured it out right after I posted the message but in the end I did a complete workaround even without SQL Server. Probably was very tired that day. Goofy!