Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
View 7 Replies
I have create a batch file, that creates an ODBC then updates the application datasource key in the registry to the new system dsn name.
The problem is that the new DSN doesn't work when i try and connect the application...but if i had manually created the odbc source the app connects as expected... i have checked the registry and there is no difference between the two dsns created...but the application throws IM002[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified after i have also changed the applications registry key to point to the new database.
Now the interesting things to note are that when i re-create the odbc to the old server and database (sql 2000) by batch file it works fine. The new ODBC is linking to a sql 2005 database, but it is still using the 2000 drivers and when i manually created it, it worked also.
Another interesting thing is that if i go into the odbc dsn and click configure, go through and test the connection it works fine... after i close this i then retry the application and it opens correctly...
I need this to be automated with no manual intervention, as this will be added to a large group of users login scripts!
Any help greatly appreciated.
My batch file code is as below:
ODBCCONF.exe CONFIGSYSDSN "SQL Server" "DSN=RMS Live 2005 | SERVER=d-db99sql2005| Trusted_Connection=Yes | Database=RMS-Livedb"
Hi
I have written stored procedures to create a database with db name as input parameter
I need to create a batch file to run the stored procedure with input value given in the command prompt along with the batch file will be my dbname, which will be used by the stored procedure to create the databse.
and also few sql statements to insert data into those tables after creating
Plz could any one help me out to create code to create a batch file
thanks in advance
Ok.. I thought I could do this but I can't get this to work.. I need to create a batch file to run an sql update statement.. This is simple but I can't get it to wortk.. How do I do it?
View 4 Replies View RelatedI have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
InsertToOrderTypes.sql
UpdateClientAddress.sql
DeleteDuplicateOrders.sql
InsertToEmailAddress.sql
ConsolidateBrokerData.sql
UpdateInventory.sql
EliminateInvalidOfficeLocations.sql
My log table in the database looks like this.
select * from sqlfileexecutionlog
FileNameRunTimeResult
---------------------
DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success
UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
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
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
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
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
View 6 Replies View Relatedis there any chance to create an exe file to update the sql server database by uisng windows schdule ??
for example this exe file will run to update my database, every night @ 12:00 AM.
this exe should be in vb.net
pllllzzzz help
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 wrote a number of SQL scripts to do a data migration, from MSSQL 2000 to 2008 R2. These scripts should be run each night. All error message should be captured, though execution may continue, so we have an overview of all errors in the morning. We know when one failing script will cause others scripts to fail, so that's not a real problem.
Multiple related SQL scripts are grouped in a file for ease of development. There a are multiple files, and their number is still growing.
How can I automate this? Can I leave multiple SQL scripts in one file, or do I have to limit the number of scripts per file to one?
I am thinking of using a scheduled job, but I have never created one and I don't know how to handle the error messages.
When clicking New Subscription (or Manage Shared Schedules) I get the following error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I can't find any information about this error. Does anyone know what is wrong?
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
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
hi all,
I have a function and batch witch consisted of same sql statement, and they will get the same result. but time they take is different, and produce a little different query plans, another significant difference is the estimated numbers. the function is always slower than the batch.
does anyone know why same sql would produce different query plans performance? how do i can to let the function as fast as the batch?
thanks!
There are 2 sql servers : A and C.
I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job.
I created linked server on server A and ran query select... It works.
I can create Update query.
What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this.
Appreciate any help.
Ann2
I have a collection of around 16000 records, and have been trying to find the best way to update the information in the DB. I have done alot of reading about both BulkCopy and Batch Update, but haven't come to any clear solutions as to which performs better. I am not doing any inserting, just getting a dataset from the DB, changing the values, them want to update the Db again. Thanks for any help. Mick
View 8 Replies View RelatedCan anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.
I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.
In a seperate table I have a charge per day for late returns. I read this into a variable @LateCharges
I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLate column*@Latecharges
Any help much appreciated.
Regards
Clive
I have an application that has a dropdown in which, I want a users most popular choices (from history) to be on top. This is easily done via a query however the query is too slow, after be optimized. I'd like to have create a table that contains each users most popular selection, and then just update it nightly. How can I schedule some SQL code to run nightly?
View 1 Replies View RelatedBefore the start of each season, the various clubs/teams compile an initial game schedule using Excel. Once the master schedule is complete, it is sent to me. The initial master schedule is loaded into the database [MatchSchedule] from the Excel spreadsheet using the SQL Server Import/Export Wizard. This works well enough. However, during the season there are frequent small changes to dates & times, venues, etc. These changes are sent to me in Excel spreadsheets.
I need an efficient & ERROR FREE method of getting the new game details into the master schedule. I thought perhaps the easiest way to accomplish updates to the table MatchSchedule t1 was to use a temporary table MatchSchedule_Updates_temp t2 to hold the new game details (obtained from Excel spreadsheets) and then use UPDATE to update MatchSchedule with those games that have changed. The primary key is Match_Id.
Unfortunately, I keep getting the following error messages which I have been unable to correct!
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 't1'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'WHERE'.
Here is my code:-
USE [database]
GO
UPDATE dbo.MatchSchedule t1
SET (
t1.MatchDate
,t1.MatchTime
,t1.Venue
[code]....
I have written the following code:
SET NOCOUNT ON
DECLARE @RowCount int; SET @RowCount = 0;
Begin Try
Begin Transaction
--------------------------------------------------------
-----Table Name: AlertsStaticRecord_Archive
-----Column Name: AlertID
--------------------------------------------------------
ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11
Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'.
Msg 1750, Level 16, State 0, Line 11
Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query
Statement stmt = null;
try
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
for ( String sql : sqlList )
{
stmt.addBatch( sql );
}
results = stmt.executeBatch();
stmt.clearBatch();
conn.commit(); //throws the exception
}
catch ( SQLException e )
{
try
{
conn.rollback();
}
catch ( SQLException e1 )
{
throw new DataSourceException( e1 );
}
throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() );
}
I am setting up a role to allow certain users to create and maintain Shared Schedules in SSRS 2005. The tasks I have authorized are:
View reports
View folders
Manage all subscriptions
View data sources
Consume reports
What else do I need to grant? Right now the user cannot see the Site Settings link in order to get the the manage schedules.
Thanks,
Phill
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
Can anyone help me understand what this means:
Code Snippet
java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:0. Index:41. End:83.TDS_DONEINPROC(-1) TDS_DONEPROC(-2) TDS_DONE(-3) TDS_COLMETADATA(-127)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
I'm trying to do a very simple batch update.
hello .
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.
the problem is that some of the table fields have to be unique
now imagine the next situation.
user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch
user B writes '1000' in the unique field
user B saves the recordset with updatebatch
now there will be two records with the field '1000' !
how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset
thanks !
I used "OLE DB Command"(row by row) for update but because i found that very very slow i decided to try batch update. I check what is for insert and what is for update and those things that are for update i save to temp table. Then i run this:
update [ODS].[dbo].[Adres]
SET
[MutatieDatumEinde] = stAdres.MutatieDatumEinde,
[StraatNaam] = stAdres.StraatNaam,
[HuisNummer] = stAdres.HuisNummer,
[HuisNummerToevoeging] = stAdres.HuisNummerToevoeging,
[Postkode] = stAdres.Postkode,
[Plaats] = stAdres.Plaats,
[Land] = stAdres.Land,
[IndicatiePTTAdresStandaard] = stAdres.IndicatiePTTAdresStandaard,
[KodeHerkomstAdres] = stAdres.KodeHerkomstAdres,
[VervalDatum] = stAdres.VervalDatum,
[BronODS] = stAdres.BronODS
from
[ODS].[dbo].[Adres] oAdres
inner join [M2O_Stage].[dbo].[ODS_Adres] stAdres
on oAdres.InstNr = stAdres.InstNr and
oAdres.TypeAdres = stAdres.TypeAdres and
oAdres.MutatieDatumAanvang = stAdres.MutatieDatumAanvang
where stAdres.IsInsert = '0'
I run this as an "execute sql task". Everything is running as it should and speed is incomparable, but.. i have problem to make this generic... what i mean is.. db names will change.
How can i make db names dynamic here ?
p.s. i just got idea .. i could probably make dynamic sql statement in script component and then run it there... but does anyone have a better idea ?
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
hi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.
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
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