How to save for xml statement into a file

Hi

I have a T-SQL like :

Code Snippet

select * from customers for xml PATH , ROOT

and how to save the result to a xml file .

Refer to Books Online about using BCP.

Look in Books Online, Topic: 'BCP Utility'

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps

How to retrieve the T-SQL for a particular subscription?

In SQL 2005 NS, I have created a custom subscription management interface that allows for condition actions. I can currently create, update and delete subscriptions without a problem.

However, I would like to be able to retrieve the T-SQL that will be run when the event is fired to test it for correct syntax etc. I am hoping to notify the user of any possible issues as soon as the subscription is created as opposed to waiting for an error when the event fires. Since many matching fields are simply text entries, the user may enter some data values that cause the matching statement to fail and I would like to catch that error and notify the user at subscription creation.

To do this, I plan to create the subscription with a "disabled" status. The next step would be to retrieve and run the actual T-SQL that will be executed when an event is fired. If there are no errors then I will update the subscription to "enabled".

I cannot seem to find a class member or stored procedure that returns the resulting T-SQL for a single subscription.

Any help would be appreciated.

Thanks

Maybe, I didn't understand exactly what you are trying to accomplish, but as far as I can see, you do not need to retrieve T-SQL for a subscription creation. Instead, you can just do your validations in your SMI class. And you can maximize your GUI usage for validations.

For example, use as many drop-down menus or listboxes as possible instead of combo boxes, textboxes, etc. Let users choose the predefined values.

Also, say, for example, that there is a field in your subscription class which is a DateTime value. Don't let the user enter the date. Give him a Calendar or DateTimePicker controls.

If the user still has to enter some values manually (such as money values), do validations in GUI before calling a SMI method, such as

try {

double amount = Convert.ToDouble(textBox1.Text);

// invoke SMI method for creating or modifying the subscription

} catch (Exception ex) { // show error message to the user }

|||

Thank you for your response.

I am already performing these types of validations and using dropdowns/calendars where appropriate.

However, I also wanted to take it a step further in case something is missed in the validations. The bottom line is; you never know what a user is going to enter in a textbox.

I would like to retrieve the actual SQL that will be run for an individual subscription and test it.