Dear friends
I want to be able to make a batch that adds a column to every table in one go
Find a table object
Attach table name to columnname called "Concurrency"
Add column called Concurrency timestamp
If present already, don’t do it
Anyway to do it or do I have to do it manually?
cm
The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @userIDs which has the following format:
101, 102, 103
I got the error message:
Syntax error converting the varchar value '101, 102, 103' to a column of data type int.
Obviously, UserID has datatype int. How can I write this SP?
CREATE PROCEDURE dbo.MySP
@userIDs varchar(100)
AS
SET NOCOUNT ON
BEGIN TRANSACTION
UPDATE Users SET IsActive = 1 WHERE UserID IN (@userIDs)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
Is there a way to run/call a batch file from a stored procedure?
Or, is there a way to run/call a batch file from a trigger?
I have a stored procedure that generates some data and dumps it into a table. I need to export data using bcp based on the data that this procedure creates. So I know how to use bcp but don't know how to execute the procedure and pass it the two variables that it needs. I googled it and sqlcmd looks promising but can get the syntax right. The two variables are the current year and school number ie. 1112, 0021.
View 3 Replies View RelatedWhat is the difference between Batch and Stored Procedure?
View 5 Replies View RelatedHi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
how to catch batch id from a running stored procedure. My intention is that when we run store procedures in batch we are running a lot of procedures and I would like to log each run and if the same procedure is running several times per day I need to separate the runs by a "batch id" for the specific run. I have created a logtable and a logprocedure that logs the start and end of a procedure run and also some values for the run. So I'm trying to find a way of fetching the "batch id" that the sp is running so I can separate the runs when analyzing the logtable. I have looked at metadata tables and also in the table sys.sysprocesses but I cannot find BATCH ID.
View 11 Replies View Relatedhi ,
i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...
The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.
thanx in advance
raj
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
Thanks,
Dave
Hi,
I want to schedule a daily job using sql server to update the info. in a sql server table. This is very new to me. Could you please forward me some helpful resources.
Thanks,
QuestionsI need to batch a set of update commands. Can that be done and if so what are the possible ramfications? Can one mix / match Deletes/Inserts and Updates into a SQL command via the semi-colon in a batch set? (Is this the appropriate forum?)
WHYI am unable to use the
dynamic sql building found in the dataset due to a multiple table
contstraints. I have built classes that will extract the differences
from two datasets and report on the updates/deletes and inserts
required. Since I have multiple SQL statements, it would be nice to combine them into one statement.
Platform.Net 2C#IIS6SQL Server 2000 is the eventual target database for the generated SQL
advTHANKSance
Hey guys, I periodically have to update maybe 300 records of the same column.. However, I've been udpating each record one by one.
Is there a way I can do something the following.ID Name1 john2 chris3 adam4 ben5 steveupdate table1 set name = name where id = ? but have it run through an entire set of these? Is there any way of automating that rather than having to update it manually? Do i need another table with just the id numbers and the column I want to change? or do i need a txt file?
hi,
I need some help in accomplishing this task.
I want to design a DTS task which will:
a)copy a certain given files from one directory to another
b)import the files into the tables
c)upon successful import delete the files from the original directory.
I done know much about scripting and need help in figuring out steps a) and c).
thanks
Zoey
Is there any way using to run already developed DTS pacalkes one after another.
Thanks
Do u know how to write a batch file
for example i will need to type the sql server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
Do u know how to write a batch file
for example i will need to type the server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
Can someone show me an example of the syntax required to execute multiple BCP commands within the same batch (*.bat) file?
Sorry if this is a bit of a basic question, but not being a programmer by profession, I need to plead ignorance. I've tried a few things, but I just can't seem to figure it out.
Thanks!
Rich
I have a problem running a batch file, now the problem is that when i run the batch file the command prompts the user for an input, but I have all the output of the bat file going into a log file. So when i run the bat file the process just sits there until i hit the 'y' key or unless i nput sumthin manually. This is a problem becuase this batch file is running on the UAT server as a job and there is no one there to input once the job is running. The commnd in the batch only requires an input once a month.
for eg if run: launch_scrt.bat and i want to put sum parameters such as 'y' or 'n' to avoid the manual input once the job is running. Any ideas?
Thanks in advance
Hi There,
I want to able to run a batch of sql statments (execute procedures) in SQl Server at a specific time. Just wondering if there is a method for that.
I like to schedule set of jobs in sql server 2005 to run in a particular sequence. How do I schedule batch jobs? Each job is a set of packages. Idea is to create a job with multiple steps and each step should be a one job but there is no option for the "sql server agent jobs" when mentioning each step.
Any idea how to create a set of jobs in sequence?
Thanks in advance,
Maria
What is the best way to run a scheduled task that fires off three scripts that need to be run sequentially? I could set up three different tasks but I don't know exactly how long each will take and they are interdependent.
View 2 Replies View RelatedHi all
I have a batchjob which runs a DTS package now it calls it ok
However it is running against 2 Databases on 2 servers and I get a login error even tho I am using the sa login
does anyone know of any issues relating to this Hopefully with a soloution that does not involve a lot of work
thanks
Michael Grieve
In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @ plus the path and file name. So, if I have a script called E:CreateTable.sql, I can simply enter @CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:CreateTable.sql file (there may be one or more commands).
Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?
Thanks,
hmscott
I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
Mark
Hi pals,
I need a small help from u all.
I need a Windows batch file script which does the ftp to remote machine and "puts" a file
say "data.xls" from a predefined dir say "c:uploads" and puts on the database server "d:dumps".
Regards,
Franky
I need to execute around 100 SQL server jobs in a batch. If I execute them in a batch will they get executed linearly or asynchronously?
------------------------
I think, therefore I am - Rene Descartes
Hai, i would like to do Bulk update to avoid the round trip to the database.
Means, In my UI im dsiplaying all the employee details who are related to one particular dept. Now i want to update the bonus to all the employees based on their category. UI changes are refelected in the Datatable(.NET). Finally the datatable changes i would like to update in the Database.
how can i do that..
sample code pls.
im very very new to sql
I am inserting records into a table - around 1 million records. I wantto do the insert 10000 records at a time using TRAN. My insertstatement is very simple:BEGIN TRAN T1Insert INTO TABLE A(ColA, ColB, ColC)Select B.TBLBCOLA, B.TBLBCOLB, B.TBLBCOLCFROM tbltable BWHERE blah balh...etc.COMMIT TRAN T1Any hep or link on the syntax will be helpful!ThanksSri
View 6 Replies View RelatedHi AllThis is a belter that my little brain can't handle.Basically I have 1 SQL table that contains the following fields:Stock CodeStock DescReferenceTransaction DateQtyCost PriceBasically this table stores all the transaction lines of when a userbooks stock items into stock so that they can look at a journal ofthis goods in as and when they please.My task is that the user wants a list of all the stock items with thelast cost price that they were booked in at.So I think I have to find the last transaction date used for eachstock code and then bring this back as 1 row per stock code with theabove fields of data.How the whats-its can I do this? Is it acutally possible?Any help you can give is much appreciated.RgdsLaphan
View 3 Replies View RelatedI have a windows batch file that executes a SQL Server bcp command. Iwould like to obtain a return code if the bcp command fails. However,I cannot seem to find the return code (if any) for bcp. For example,if the bcp command is improperly formatted, or has a bad password, Iwant the batch file to return an error. Right now, my batch filesimply executes and returns success, even when the bcp command fails.Has anyone run into this before?Thanks!
View 3 Replies View Related Hi Folks,
I am running batch jobs(Nearly 50 queries) on Production server.It is taking nearly 5 or 6 hours(some times 10 hours also) to get the results.The problem is If I loose VPN connection I am not able to save data.I need to save the data upto the query executed before I loose VPN connection Please help me