help with export to csv or note

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with export to csv or note

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-29 : 14:59:47

HI All,

Is there a way to include a msg the end of in your store proc to say something like "your enrollment is complete"?

I was using below but this is error mage, I don't want error msg.

IF @@ERROR = 0
RAISERROR ('Patient enrollment completed !!!', 16, 10)

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-29 : 15:07:42

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-29 : 15:13:17

Thanks TG I tried it but I don't see the msg popup?
Pasi

quote:
Originally posted by TG

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 15:42:24

quote:
Originally posted by Pasi

Thanks TG I tried it but I don't see the msg popup?
Pasi

quote:
Originally posted by TG

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

Your application should display that via application code based upon the outcome of the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-29 : 15:45:24

And if you're in a query window you would need to look at the Messages tab in the results pane. Or change to "results to text"

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-29 : 17:29:14

Use a severity of less than 11. From MSDN:

quote:
RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

RAISERROR ('Patient enrollment completed !!!', 10, 10) WITH NOWAIT

NOTE: If you are using management studio, you still need to go to the Messages tab to see this "message."

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-29 : 17:49:03

Thank you all!
Pasi

quote:
Originally posted by Lamprey

Use a severity of less than 11. From MSDN:
quote:
RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.
RAISERROR ('Patient enrollment completed !!!', 10, 10) WITH NOWAIT
NOTE: If you are using management studio, you still need to go to the Messages tab to see this "message."

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-29 : 18:28:41

Tara,

Is there a way to generate a document in store proc to print a .txt or html with person user name and password printed in that document?
Thanks.

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

Thanks TG I tried it but I don't see the msg popup?
Pasi

quote:
Originally posted by TG

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

Your application should display that via application code based upon the outcome of the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 18:51:50

quote:
Originally posted by Pasi

Tara,

Is there a way to generate a document in store proc to print a .txt or html with person user name and password printed in that document?
Thanks.

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

Thanks TG I tried it but I don't see the msg popup?
Pasi

quote:
Originally posted by TG

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

Your application should display that via application code based upon the outcome of the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Yes, but should that really be done in a stored procedure?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-30 : 18:40:20

HI Tara,

Wanted to ask you this,

I finally managed to finalized my SP. This sp enrolls the person and gives him/her a user name and password I want to grab this user name and pass word and export it to a document like notepad or word doc, is that possible to do at the end of this SP? I would be very grateful!

here is the code:
ALTER PROCEDURE [dbo].[AutoEnroll] @person_id varchar(36), @user_id int
AS
DECLARE @practice_id char(4)
DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
DECLARE @enc_id uniqueidentifier

SET NOCOUNT ON

SELECT TOP 1 @practice_id = pe.practice_id
FROM patient_encounter pe
INNER JOIN patient pt ON pt.person_id = pe.person_id AND pt.practice_id = pe.practice_id
INNER JOIN person p ON p.person_id = pe.person_id
WHERE pe.person_id = @person_id
AND p.email_address <> ''
ORDER BY pe.enc_timestamp DESC

-- Build the user name and password
SELECT @user_name = RTRIM(LTRIM(last_name)) + RTRIM(LTRIM(LEFT(first_name, 1))) + RTRIM(LTRIM(CONVERT(varchar, person_nbr)))
, @psswrd = date_of_birth
FROM person WHERE person_id = @person_id

begin
INSERT INTO ngweb_bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,modified_by,modified_timestamp)
Values (newid(),@person_id,'0001',@user_name, @psswrd,'brown','color','brown',@user_id,current_timestamp, @user_id,current_timestamp)

END

SET NOCOUNT off;

GO

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

Tara,

Is there a way to generate a document in store proc to print a .txt or html with person user name and password printed in that document?
Thanks.

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

Thanks TG I tried it but I don't see the msg popup?
Pasi

quote:
Originally posted by TG

here's one way:

print 'Patient enrollment completed !!!'

Be One with the Optimizer
TG

Your application should display that via application code based upon the outcome of the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Yes, but should that really be done in a stored procedure?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-30 : 19:06:21

Yes it's possible in a stored procedure, however it is not at all recommended. This does not sound like a good security practice.

Plus the exported file would be from the database server's perspective. Where do you want the file written to? And why do you want to do this from the stored procedure rather than from the application code?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-30 : 19:21:28

Yes I want to export it to my local drive C:\temp don't worry about the security, I can have it in my application.

I don't have a way to get the user name/password from application so It has to come from the table bulk_enrollment through SP if possible.

Thanks.

quote:
Originally posted by tkizer

Yes it's possible in a stored procedure, however it is not at all recommended. This does not sound like a good security practice.

Plus the exported file would be from the database server's perspective. Where do you want the file written to? And why do you want to do this from the stored procedure rather than from the application code?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-30 : 22:47:24

I also want to keep a log on user names and passwords.
Pasi.

quote:
Originally posted by tkizer

Yes it's possible in a stored procedure, however it is not at all recommended. This does not sound like a good security practice.

Plus the exported file would be from the database server's perspective. Where do you want the file written to? And why do you want to do this from the stored procedure rather than from the application code?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-06-02 : 16:46:09

I put this together to export the user name /password to a csv file to test my SP to output the user name/password. but I am getting errors below.
any help would be great! the below code is at the end of my SP.
Thanks.

DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)

SELECT @user_name ,@psswrd
FROM ngweb_bulk_enrollments
EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C:\test.csv -Sserver1 -T -t, -r\r\n -c'

but I am getting error SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].

NULL NULL
NULL NULL
NULL NULL
NULL NULL

SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].

quote:
Originally posted by tkizer

Yes it's possible in a stored procedure, however it is not at all recommended. This does not sound like a good security practice.

Plus the exported file would be from the database server's perspective. Where do you want the file written to? And why do you want to do this from the stored procedure rather than from the application code?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-02 : 17:19:45

You do not want to expose xp_cmdshell in a stored procedure that'll be called by an application. Xp_cmdshell should be disabled on production servers, IMO.

The issue is with -S.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-06-02 : 17:25:43

Ok, so how do I set this up then? can I just remove the -S?
Thanks.

quote:
Originally posted by tkizer

You do not want to expose xp_cmdshell in a stored procedure that'll be called by an application. Xp_cmdshell should be disabled on production servers, IMO.

The issue is with -S.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-06-02 : 17:33:33

I had the wrong server name, now it works and exports but I don't get the headers in my excel file, just data? How do I include the headers in
the excel file?
Thanks.

quote:
Originally posted by Pasi

Ok, so how do I set this up then? can I just remove the -S?
Thanks.
quote:
Originally posted by tkizer

You do not want to expose xp_cmdshell in a stored procedure that'll be called by an application. Xp_cmdshell should be disabled on production servers, IMO.

The issue is with -S.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-02 : 18:00:18

It gets complicated to add the headers to a file you are bcp'ing out. I don't have that kind of time to post it here, but to give you an idea you'll need to create a view that includes the headers. You really should not be exporting this data in a stored procedure. This should be done by the application or via other methods such as SSIS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-06-02 : 18:17:33

OK Thnx. When I execute the export I now get error with my SP saying: Erro source .Netsqlclinet data provider . Error message time out period elaspsed prior to completion of the operation or server is not responding?

Do you know how do I fix this?
Thanks.

quote:
Originally posted by tkizer

It gets complicated to add the headers to a file you are bcp'ing out. I don't have that kind of time to post it here, but to give you an idea you'll need to create a view that includes the headers. You really should not be exporting this data in a stored procedure. This should be done by the application or via other methods such as SSIS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-02 : 18:29:00

The stored procedure timed out. What is your application timeout setting? And how long does the stored procedure take to execute in Management Studio?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-06-02 : 18:38:56

Don't know the time out of apps but when I used to execute the SP from application was fast within a min or 2.

SP execute fast right away a milisec from management studio.

When I do select * from ngweb_bulk_enrollments which is the table in my SP now it takes long time and I don't see the table contetnt, and it still sitting there executing?

also when I go to delete the excel file form the folder, it wont let me delete saying the files is open in bcp.exe. How can I stop this?
Thanks.

quote:
Originally posted by tkizer

The stored procedure timed out. What is your application timeout setting? And how long does the stored procedure take to execute in Management Studio?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources