BCP utility help

I want to bcp out the record set in a flat file. I am unable to write the
correct script to do this.
What I am trying to do is build a text comma delimited file by running a
stored procedure say procTest. This bcp command will be executed in a nightl
y
job.
Please help.David (David@.discussions.microsoft.com) writes:
> I want to bcp out the record set in a flat file. I am unable to write the
> correct script to do this.
> What I am trying to do is build a text comma delimited file by running a
> stored procedure say procTest. This bcp command will be executed in a
> nightly job.
BCP db.dbo.tbl out tblout.bcp -T -c -t,
This is a command-line operation. To run it from a stored procedure,
you would have to call xp_cmdshell to spawn out to command-line level.
Now, when you say comma-delimited, do you in fact mean something like:
"value",2,"other value",98
then it gets trickier, particularly if the first column needs a quote.
If the first column needs a quote, you can use a formar file. If the
first column needs a quote, you will need to use the queryout option, or
define a view or possibly use a global temp table. Queryout appears to
give people headache, so I would stay away from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The line to tell BCP to import a comma separated file to SQL table is:
-t,
It's so tricky.
*** Sent via Developersdex http://www.examnotes.net ***

bcp operation within transaction

hello,
I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.

Salik.BCP operation is not affected by a transaction ...
This might help...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31640|||Well...

wouldn't you say that it's an implicit TRANS?

I mean if the bcp fails in the middle (in it's own spid) then that rolls back..

But as Enigma has said...if you do

BEGIN TRAN

master..xp_cmdshell 'bcp...

ROLLBACK TRAN

IF the bcp was successful, then you won't be able to roll it back...

EDIT:

And...

When I do that...SQL server hangs

I would say that's not true...do sp_lock

I would say your spids are blocking each other...

been there...done that....|||Well ... its a thing to be left alone ...
as you say ... been there .. done that (discussion)|||My main issue is that I am writing out data based on a 'events' table which tracks the changes users make to a product table. from the time I have built the strings and inserted them into a temporary table and then output the file, then removed the 'events' - a user could change a field in a product I am writing out - which would be missed by the system. Not good.

The only thing I can think of is to lock the appropriate rows in the product table for the duration. WHich I'm not aware of the syntax.

Any other strategies?

Salik.|||Data is fluid ny bature...what's the difference if it's updated during your export, or immediatley after...the data will still be changed...

And is your issue a matter of concurrency? Why build a temp table?

Also why not schedule a batch window? Grab the date from the system...use that as the window close date, grab all rows added or updated between the last time the window close and this windoes close...

So if they update it during your export it wouldn't be part of this batch...

How long is your transaction...and how many rows on average do you export?|||The output data is '*' delimited for another system and requires padding of numbers, calculation etc.. that's why I build the strings into a temporary table and then ship the temp table out using bcp.

We use triggers to track the events a user makes to a product. on certain fields a product event is created. and on others a price event. The price events are held util evening processing as they can only be sent out once a day. the product files are output every 15 minutes. There are four different files output at the same time, ecah containing slightly different data.

The events table prevents duplicates ie: we don't append. if a existing price event exists we don't add another row.

when I have finished the bcp the appropriate rows are removed from the events table.

sooo - between the time the row for the product has been read and the time I delete the events row for that product. and event could happen...no duplicate would be added..and the new data not written out and the system thinks it has.

I could snatch the rows from the events table into a temptable. delete them and if an error occurs add them back in to the events table...

Batch process for sending mail

Hello everyone,

I want to write a batch process that will run everyday and send emails.

The scenario is as follows:

There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).

I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.

Table structure:

Admin

AdminID

Email

AdminArea

AdminID

AreaCode

Area

AreaCode

AreaName

WebSummit

SummitId

RequestorName

DateOfRequest

RequestStatus

AreaCode

Regards,

Vidya.

Hi,

Kindly advise.

Regards,

Vidya

|||

You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.

Roughly, create a SQL Server Agent job to run at 8:00AM

and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.

|||

Hi,

Thanks for reading. Some code will be useful.

Regards,

Vidya

|||

Here's the basic template for the script to be executed.

You'll need to adjust the queries to be exactly what you need.

I just did a simple query to get the admin who had 5 or more requests present.

Code Snippet

createtable #Admin (AdminID int, Email varchar(50))

insertinto #Admin values(1,'admin1@.mycompany.com')

insertinto #Admin values(2,'admin2@.mycompany.com')

insertinto #Admin values(3,'admin3@.mycompany.com')

insertinto #Admin values(4,'admin4@.mycompany.com')

createtable #AdminArea (AdminID int, AreaCode int)

insertinto #AdminArea values(1, 100)

insertinto #AdminArea values(2, 200)

insertinto #AdminArea values(3, 300)

insertinto #AdminArea values(4, 400)

createtable #Area (AreaCode int, AreaName varchar(50))

insertinto #Area values(100,'Area 100')

insertinto #Area values(200,'Area 200')

insertinto #Area values(300,'Area 300')

insertinto #Area values(400,'Area 400')

createtable #WebSummit ( SummitId int, RequestorName varchar(50),

DateOfRequest datetime, RequestStatus int, AreaCode int)

insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )

insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )

droptable #to

select a.AdminID, a.Email

into #to

from #Admin a

where a.AdminID in

(

select a.AdminID

from #Admin a

innerjoin #AdminArea aa

on a.AdminID = aa.AdminID

innerjoin #Area b

on aa.AreaCode = b.AreaCode

innerjoin #WebSummit w

on b.AreaCode = w.AreaCode

groupby a.AdminID

havingcount(*)>4

)

declare elist cursorfor

select adminid, email from #to

declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)

open elist

if@.@.cursor_rows> 0

begin

fetch next from elist into @.id, @.email

while@.@.fetch_status=0

begin

set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))

exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,

@.subject='Summit Follow-up'

fetch next from elist into @.id, @.email

end

end

close elist

deallocate elist

|||

Thanks Dale.

That was very helpful.

Regards,

Vidya.

Batch Operations of SQL Command.

Hi,

Im trying to write a class that compiles a list of SQLCommands and then executes them all at once. Im trying to reduce the amount of calls to the database.

Im also trying just to update the fields which have changed so I cannot use Stored Procedures as that would mean writing way too many stored procedures for every permutation on every table in my database.

So Ive decided to build sql commands and then execute them all with one call to the database. When I print the commandtext property of the sqlcommand to the page whilst debugging It shows something like insert into XXX (f1,f2) values (@.v1,@.v2). Is there any way to see the final sql string, with the @.v1 variables replaced by the actual values in the parameters I have added to the sql command?

Im building the commands by creating a new sqlCommand. Then I set the commandtext to "insert into XXX (f1,f2) values (@.v1,@.v2)". Then I Add Parameters to the sqlCommand. My Parameters count is showing the correct value.

I want to be sure now that when I go to send these commands to the database as part of one long string that it will work.

Thanks,

C

Hi ,

I think you are going right. For seeing the result with the value don't use the parameters. What you can do is directly substitute the values in the SqlCommand lkike this

CommandText="Insert into XXX(f1,f2) Value('" + Field1.Value +"','" + Field2.Value + "')"

and then you will be able to see the value. I think you will be able to texecute the multiple queries. Special care needs to be taken if you are returning multiple result sets i.e Select statements

|||

Hi Satya,

Thanks for the response. My class is creating commands for itself, and then calling a Save() method in all its member classes each to return a List<sqlCommand> of Sql commands. So at the end Ive a list of sql commands to execute which Ive got in the correct order for execution.

However at this stage I want to be able to see exactly what is being sent to the database to test it before i put it to use. Here is an example from the function that builds the sqlCommand objects.

switch (scb.TransactionType) {/* CREATE SQL INSERT STATEMENT */case"Insert" : first =true; sqlString.CommandText ="INSERT INTO " + scb.DBTableName +" (";foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" " +value; } sqlString.CommandText +=" )"; sqlString.CommandText +=" VALUES ("; first =true;foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" @." +value; } sqlString.CommandText +=" )";foreach (SqlParameter sqlParamin scb.changedParamArray) { sqlString.Parameters.Add(sqlParam); }break;

So as you can see Im first building the commandText using @.field for the fieldnames and then adding the parameters using a loop. I assume this is the correct way to build a command? This function then returns this command. What I want to know is when I finally have all my commands in a list, how can I then check the final SQL strings that will be executed. I dont want to replace the @.field at this stage, because I dont need to, and Im not sure if Im still using the escaping and size properties of the parameters ive created if i do.

So how, when I've the final list of sqlCommand objects can i preview the final sql string that will be executed by the SQL Server?

Thanks Again,

C

|||

Hi,

Actually, if you want to execute the command, then adding the parameters using a loop is ok. But in this way, you can't preview the final sql string. I suggest you to put the following code after yours.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values (@.p_a,@.p_b)

int i=1;foreach (SqlParameter sqlParamin scb.changedParamArray){if(i==1){ sqlString=sqlString.Replace("@.p_a",sqlParam) }else if(i==2){ sqlString=sqlString.Replace("@.p_b",sqlParam) } .... i++;}

And then you can preview the final sql command by sqlString variable.

Meantime, there's another method to achieve this.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values ( {0},{1})

string[] para_array =new string[para_num]/// in this sample, para_num=2int i=0;foreach (SqlParameter sqlParamin scb.changedParamArray){ para_array[i]=sqlParam; i++;}string sqlString_preview =string.Format(sqlString,para_array[0],para_array[1]);

In this way, sqlString_Preview also stands for the preview of sql command.

Thanks.

batch file to send email

Hi,

I am a developer, but my client asked me to write a SQL batch file to send emails at the end of the day. This email should be sen to all the people who make changes to the database table name tblXYZ. I mean update the table for that particular day.
I am using SQLServer2000 database and .net framework.

I need the help really urgent.

Any help would be really appreciated.

Thanks :confused:priya0123,

Have you tried xp_cmdshell ?

This is an extended stored proc that allows you to shell out. You could write a batch file wrapper for a simple smtp client - something like bmail.

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Use osql utility.
Sample syntax:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan ''TEST'''"
>--Original Message--
>
>Hi there,
>I am trying to write a batch file that can run a
scheduled Job that is
>already written in SQL server. I know how to run a
package using DTSRun
>command from a dos prompt, but, is there any command to
fire a scheduled
>job. or run a stored procedure.
>Thanks in advance,
>Sree
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim