Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting som
e
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connectio
n
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:

>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certai
n
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting so
me
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connecti
on
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:

> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
>
>

Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting some
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connection
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:
>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certain
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting some
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connection
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:
> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
> >Is there a means to pass information to SQL server (on a single connection)
> >that is accessible from T-SQL or triggers. My scenario is that when certain
> >tables are updated/inserted/deleted, I am making entries in a "log" table,
> >via triggers, that contains the table name and the action performed on it.
> >What I also need to insert into the logging table is an integer denoting some
> >additional information about the update/insert/delete which needs to come
> >from the client. Also, this information needs to be scoped to the connection
> >so that it is not accesible from other connections.
>

Passing connection managers to subpackages.

Is there a recommended way to pass the connection managers from the parent package to the subpackages? I also noticed that the subpackages can't reference the same configuration file as I get error messages when I try to build the solution. After I changed the configuration file for each subpackage it worked just fine. Did I miss something or is this intended to work this way?
thanks

Hi,

when you say "passing the CM's from the parent package to the subpackages", you use the ExecutePackage task or a different method?

The error with using the same configuration file is a known issue and will be addresed in a future realease

Thanks,
Ovidiu Burlacu|||I guess I'm having a difficult time understanding and even testing connection managers and configuration files. Here's what I'm trying to accomplish and am failing...

I have a project "X" with a "main" package and a another package called "child".
I have defined a generic connection manager in each of the packages (main and child ) called "SOURCEDB".

I have then created a configuration file called "connMgr". In both packages I identify this configuration file (let's just use direct configuration for now) and specify that I want the connection string, username and password configurable at load/run time.

My understanding would be that before I run the package I edit the configuration with the appropriate connection string and username so I can run the main package against different databases based on current requirements.

However, when I run the program it says that the configuration file is the same (during build) and generates an error. What is the recommended way to get around this and am I going about this the wrong way?

I have also noticed with indirect configurations that once you have to make a modification to the configuration file the file location initially specified for the configuration file is lost. Is that a bug also?

thanks
John|||1. To avoid the build error r-click the project in Solution Explorer window and choose 'Properties'. In that dialog go to 'Deployment Utility' and Set CreateDeploymentUtility to False.

If you really need deployment utility then use the XML configuration file only for the parent package and in the child package use a Parent Package variable configuration. Then in the parent package you create a variable where you can put your connection string.

2. I suggest you configure only the ConnectionString, because that will take care of User and Password as well.

3. You said "I have also noticed with indirect configurations that once you have to make a modification to the configuration file the file location initially specified for the configuration file is lost. Is that a bug also?"
Can you be more detailed in the steps that I need to follow to repro this problem?

HTH,
Ovidiu Burlacu|||Thanks for the info. I'll try answer 1. I tried using only connection string but I have noticed that the password doesn't get stored in the configuration file. When I change the connection string the job will fail the next time as the password isn't valid. How do I get the password changed and where when using the configuration file?

3. When I create an indirect configuration file I start first by creating the direct configuration which I point to a filename and directory. Once I have that created I go back and specify the env variable to use for this configuration.

Let's say I then have to make a modification to the configuration file. If I go back and uncheck the "Configuration location is stored in an environment variable" to make configuration changes I have to check the other option but the file name and directory are no longer there. I then have to go search for the configuration file location which is not too much pain for me but for someone else it could be who is administering the packages... It would be nice to retain the filename and location in that field.

thanks|||

With respect to your answer 1 I assume that's just a current workaround, correct? I assume that this is a bug and will be fixed in the near future ...

|||Yes this is a workaround, and as I specified in my first reply the bug will be addressed in a future release.

Thanks,
Ovidiu|||Do you have any thoughts on the password problem I'm having? I'm not sure if you saw the post from this morning ...
thanks|||

I tried your scenario but when I went in the XML config file and added Password=<my_password> to the ConnectionString the package executed just fine.

HTH,
Ovidiu

|||I was under the assumption it would add the password automatically. There is one slight problem with this. If you add the password manually it gets overridden the next time you regenerate the configuration file. You'll have to remember to add the password manually every time!!!|||Adding the password automatically is a security violation. (disclosure of confidential information). The user must enter the password manually, this way he will make sure that the file will get the appropiate ACL's

Thanks,
Ovidiu|||I did notice that if you use the manifest to install your packages on the server that it will automatically add the password to the config file. That would seem to contradict the above statement somewhat.|||

Can you describe the issue in more detail?

What password is saved in the config file?

Thanks,
Ovidiu

|||

If you double-click on the manifest file and go through the package installation wizard you can add the values for password and username if that's what you have checked off in the config file. It will then go ahead and add the password='password' in the configuration file.

Someone else at our company didn't specify connection string but username and password and it seems to add the password in the configuration file. However, if you use connection string it won't.

|||

This falls into what I said: "The user must enter the password manually"

If the user enters the password value in the wizard I must save that value into the configuration file because this is what the user want. Is not that we automatically save the password in the config gile.

Ovidiu

Passing by reference an ADO Connection from Excel VBA to C++

I want to pass by reference an ADO Connection which has been already opened in Excel VBA to a C++ DLL, but I get the following error:

"Unhandled exception at 0x4dd5230f in EXCEL.EXE: 0xC0000005: Access violation writing location 0x1775238d."

What am I doing wrong?

The code I am using is:

- VBA:

Declare Function Retrieve_C Lib "xxx.dll" (ByRef conn As ADODB.Connection) As Double
Function Test() As Double
Dim c As ADODB.Connection
Set c = New ADODB.Connection
c.Open "Provider=MSDASQL; Data Source=xxx"
Test = Retrieve_C(c)
End Function

- C++:

#import "xxx\msado15.dll" rename("EOF","ADOEOF")
double __stdcall Retrieve_C(ADODB::_ConnectionPtr conn)
{
CoInitialize(NULL);
ADODB::_RecordsetPtr recordset(__uuidof(ADODB::Recordset));
recordset->Open("SELECT xxx",
conn.GetInterfacePtr(),
ADODB::adOpenForwardOnly,
ADODB::adLockReadOnly,
ADODB::adCmdText);
return recordset->Fields->GetItem("xxx")->GetValue();
recordset->Close();
}

I have moved this thread to the native data access forum. You are more likely to get a response there, since this is native ADO and not ADO.NET.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks,

Sarah

|||

Try using "xxx.dll" (ByVal conn As ADODB.Connection) instead.

(Passing conn ByVal instead of ByRef)

Also as a side note, you've placed call to recordset close method after the return statement...

---

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Passing "@" to SQL connection string

Hi,
I'm using c# to connect to a sql database.
My dev work is fine and it connects; when deployed to the live environment,
it needs to have special chacters (@. and !) in it. The ! goes through fine,
but @. is removed. If I hard code the @., it's fine, but my setting is held in
a variable.
Does anybody know how I can pass the @. sign through to a ConnectionString?
I've tried escaping it with a \, but it won't compile (\@. is not a valid
escape sequence).
Thanks,
Sandy
What SQL database? Why do you need a "@."? Is it part of a UserName or
Password?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Sandy Beach" <SandyBeach@.discussions.microsoft.com> wrote in message
news:4F3897C5-4A0D-4E47-AE35-2B856FB27A33@.microsoft.com...
> Hi,
> I'm using c# to connect to a sql database.
> My dev work is fine and it connects; when deployed to the live
> environment,
> it needs to have special chacters (@. and !) in it. The ! goes through
> fine,
> but @. is removed. If I hard code the @., it's fine, but my setting is held
> in
> a variable.
> Does anybody know how I can pass the @. sign through to a
> ConnectionString?
> I've tried escaping it with a \, but it won't compile (\@. is not a valid
> escape sequence).
> Thanks,
> Sandy
|||Thanks for the response.
I'm trying to connect to a SQL 2000 db and the "@." starts the password. The
DB administrator set it up for the user account I'm allowed to log in as.
Cheers,
Sandy
"William (Bill) Vaughn" wrote:

> What SQL database? Why do you need a "@."? Is it part of a UserName or
> Password?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Sandy Beach" <SandyBeach@.discussions.microsoft.com> wrote in message
> news:4F3897C5-4A0D-4E47-AE35-2B856FB27A33@.microsoft.com...
>
>
|||Thanks for helping, but I've managed to track down the problem and it had
nothing to do with the "@." in the password.
I'm encrypting/decrpyting the password, and the decrypt has a list of valid
characters. Guess what? "@." wasn't one of them!
So, again, thanks for you help and time.
Cheers,
Grahame
"Sandy Beach" wrote:
[vbcol=seagreen]
> Thanks for the response.
> I'm trying to connect to a SQL 2000 db and the "@." starts the password. The
> DB administrator set it up for the user account I'm allowed to log in as.
> Cheers,
> Sandy
> "William (Bill) Vaughn" wrote:

Passing "@" to SQL connection string

Hi,
I'm using c# to connect to a sql database.
My dev work is fine and it connects; when deployed to the live environment,
it needs to have special chacters (@. and !) in it. The ! goes through fine,
but @. is removed. If I hard code the @., it's fine, but my setting is held in
a variable.
Does anybody know how I can pass the @. sign through to a ConnectionString?
I've tried escaping it with a \, but it won't compile (\@. is not a valid
escape sequence).
Thanks,
SandyWhat SQL database? Why do you need a "@."? Is it part of a UserName or
Password?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Sandy Beach" <SandyBeach@.discussions.microsoft.com> wrote in message
news:4F3897C5-4A0D-4E47-AE35-2B856FB27A33@.microsoft.com...
> Hi,
> I'm using c# to connect to a sql database.
> My dev work is fine and it connects; when deployed to the live
> environment,
> it needs to have special chacters (@. and !) in it. The ! goes through
> fine,
> but @. is removed. If I hard code the @., it's fine, but my setting is held
> in
> a variable.
> Does anybody know how I can pass the @. sign through to a
> ConnectionString?
> I've tried escaping it with a \, but it won't compile (\@. is not a valid
> escape sequence).
> Thanks,
> Sandy|||Thanks for the response.
I'm trying to connect to a SQL 2000 db and the "@." starts the password. The
DB administrator set it up for the user account I'm allowed to log in as.
Cheers,
Sandy
"William (Bill) Vaughn" wrote:

> What SQL database? Why do you need a "@."? Is it part of a UserName or
> Password?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> "Sandy Beach" <SandyBeach@.discussions.microsoft.com> wrote in message
> news:4F3897C5-4A0D-4E47-AE35-2B856FB27A33@.microsoft.com...
>
>|||Thanks for helping, but I've managed to track down the problem and it had
nothing to do with the "@." in the password.
I'm encrypting/decrpyting the password, and the decrypt has a list of valid
characters. Guess what? "@." wasn't one of them!
So, again, thanks for you help and time.
Cheers,
Grahame
"Sandy Beach" wrote:
[vbcol=seagreen]
> Thanks for the response.
> I'm trying to connect to a SQL 2000 db and the "@." starts the password. Th
e
> DB administrator set it up for the user account I'm allowed to log in as.
> Cheers,
> Sandy
> "William (Bill) Vaughn" wrote:
>

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1