nvarchar to variable adding extra backslash

Question

nvarchar to variable adding extra backslash

slim.richard

SSC-Addicted

Points: 413
More actions
November 28, 2011 at 5:27 pm

#390971

I have a SQL Task that does a select from a couple of nvarchar columns one of which has a servername in it. The result set is added to a System.Object variable which is fed to a ForEach Loop Container where each column is mapped to a String variable.
The issue I have is after upgrading from 2008 to 2008R2 when a servername has an instance with it (servername\instance) the contents of the variable has an extra backslash (servername\\instance). It wasn't doing this before R2.
Anyone have any idea how to stop this behaviour?
Thanks in advance 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Sounds like it's forcing an escape character, ie: http://msdn.microsoft.com/en-us/library/ms141001.aspx

To confirm the events: Dataflow with OLEDB source of SELECT col,col,col FROM table. Immediate destination to a recordset destination, which drops to your variable. From that in the control flow the object is immediately dumped into the for each loop?

For an immediate workaround, drop a script task in as the first component inside the loop and adjust the variable there to remove replicated backslashes. A quick review of known bugs didn't turn up anything obvious for the above being the case.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

slim.richard SSC-Addicted Points: 413 More actions · Answer 2

Thanks for the reply 🙂

I saw that page too and figured it must be internally escaping the character but when it is used directly it is not reading it as escaped.

Your flow of events seems correct. This is a legacy package I am dealing with so didn't write it myself. I am unable to find the mechanism to which the variable (ConnectionString) is being applied to the connection. I have done it in the past using an expression on the connection but not sure how it is being done here. I will keep investigating as I can't try your fix until I identify how it is being used.

I will also try updating the instance to SP1 CU3 to see if any update fixes this behaviour.

slim.richard SSC-Addicted Points: 413 More actions · Answer 3

Ok found the mechanism that the connection is being defined.

1. There is a variable called @[User:RSServer] which is a Object.

2. A SQL Task performs a select from a table of ServerName (nvarchar) and connectionstring(nvarchar) (ServerName contains the name of the server and instance eg servername/instance and connectionstring holds the full connection string eg Data Source = servername/instance;Initial Catalog = Master;Persist security info =true;provider =SQLNCLI10.1;user ID =username;Password =password;) the sql task returns the result set into the object variable RSServer

3. The object is fed to a for each loop container which maps the first row index to a variable called ConnectionString and the second to ServerName (Both are string variables)

4. Inside the container there are a bunch of Data Flow tasks that use a connection called Source.

5. This connection has an expression that maps the ConnectionString value of the Connection to the @[User::ConnectionString] variable.

This works fine for servers without instances (no backslashes) but as I have a breakpoint on the first data flow task the connectionstring variable has two backslashes and get a connection failed instance not found error on any connectionstring with an instance.

If I do a script task to open a messagebox before the data flow task it shows me the connectionstring without double quotes. I tried this expression when mapping the connectionstring for the connection with no luck also:

REPLACE(@[User::ConnectionString], "\\\\", "\\" )

Any thoughts on what could be causing this? Surely someone has tried to do this before and run into this problem?

Cheers,

Richard

saul.c.robles SSC Journeyman Points: 99 More actions · Answer 4

how did you solve this? i have exactly the same issue 🙁

slim.richard SSC-Addicted Points: 413 More actions · Answer 5

This was a while ago but pretty sure I changed it from setting the full connection string to just set the servername property. I don't work at the place anymore so can't check for you but pretty sure that's what I did.