Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.