Execution Of SQL Batch Statements While Installation
Sep 7, 2001
Hi,
I want to know if I can execute a set of batch statements ( basically create statements) during the installation of our product. I have used Access with ADP connected to MS SQL Server 7.0 and I am trying to merge the database and table creations with the installation procedure. Please tell me how I can go ahead with this. (I have tried using the installshield and some other similar products with not of much use).
Thanks in advance,
Mangala
I have an interesting problem. A number of spids are being blocked by a single select statement. The select statement is the same as returned from sp_who2, sysprocesses, sp_whoisactive of dbcc inputbuffer. It is not waiting on anything and has status as sleeping.
Clearly it is not 'just' a sleeping select statement as I can see over a thousand locks held by the spid on 2 user databases and tempdb. I'm working on the theory there is a begin transaction with a bunch of statements and no closing commit. But I want to be able to prove that. How can I show what statements were previously executed as part of this transaction?
Additional Info: SQL 2012 Enterprise Edition. This is a test server but is a reproduction of a live issue. At this point the application team cannot isolate the code causing the problem, only the set of processes the code resides in.
I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.
When I run the above statement in SQL Plus or SQL Workbench, it works just fine.
In fact, it works in SQL Plus and SQL Workbench like this as well:
Code Snippet
TRUNCATE TABLE FactTable1
/ TRUNCATE TABLE FactTable2
/ TRUNCATE TABLE FactTable3
/ TRUNCATE TABLE FactTable4
/ TRUNCATE TABLE FactTable5
Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:
...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?
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
When I run the following T-SQL statement using SQLExecDirect with the SQL Server ODBC driver, it seems to indicate to me that the driver is somehow treating the SQL as a batch rather than a single statement.
Code Snippet IF (1=1) UPDATE MyTable SET [Guid] = 'A663EC5D-A3AF-4391-8C45-9EBE87C9FD6C', [Id] = 1 [FieldA] = 'Always run' WHERE ([ID] = 1) ELSE INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 2,'Never run')
Now the above statement is only a simplified statement to illustrate my problem, but I've also tested this stripped down version to ensure that I could replicate the problem with it.
Assuming I have the following table definition and data, I would expect that SQLExecDirect return SQL_ERROR due to the unique key constraint being violated on the [guid] column. However, I'm getting a SQL_SUCCESS_WITH_INFO error.
Code Snippet
CREATE TABLE MyTable ( [Guid] uniqueidentifier,
[Id] int,
[FieldA] varchar(100) )
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'A')
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'B')
According to a MS knowledge base article 195297:
In the version 3.6 SQL Server ODBC driver, SQLExecute, SQLExecDirect, or SQLParamData returns SQL_ERROR only if no other statements are executed after the first statement. If any other statements are executed after the first, even a simple RETURN statement with no return value, SQLExecute or SQLExecDirect returns SQL_SUCCESS_WITH_INFO
This makes me think that somehow the SQL Server driver is treating the T-SQL (with IF-ELSE) as a batch of statements (normally delimited with semi colon e.g. "statement1; statement2;").
I'm running MSDE, with SQL Server driver (SQLSRV32.DLL) v2000.85.1117.00 on Windows XP Professional SP2.
However, running the same SQL but changing the driver to the SQL Native Client (SQLNCLI.DLL) v2005.90.1399.00 returns SQL_ERROR as I expect.
Also the same SQL returns the desired error using both SQL Server and SQL Native Client drivers when connecting to SQL Server 2005.
Is the order of execution guaranteed to go from top to bottom in a transaction that has multiple statements like below?
BEGIN TRAN T1; UPDATE table1 ...; UPDATE table2 ...; SELECT * from table1; UPDATE table3 ...; COMMIT TRAN T1;
How about here?
BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 ; UPDATE table2 ...; SELECT * from table1; COMMIT TRAN M2; UPDATE table3 ...; COMMIT TRAN T1;
how can i guarantee that statements will be executed from top to bottom in a transaction batch like above? I am not interested in the errors in statements. I just want whole thing to either execute fully from top to bottom or none executes
I had created 2 packages... one is the parent package and contains a 50 iterations loop running a secondary package for each iteration... i had reached the following conclusion:
My package takes an average of 5 seconds from the time it ends executing one iteration and starts another... after about 30 iterations... my average time between end and start increases significantly to about 12 secs or even more...
All packages have delay validation set to false, and receive several variables from the parent package... Has for the logging, it is done to files based on a path coming from a variable in the parent package.
To execute the parent package i am using dtexecui.exe and i consider this behavior rather strange... had anyone experienced the same? Can anyone test this?
My environment is a 4 x64 processors with 8gb memory, so i guess its good enough to get 0 secs from end to start
We have a number of customers using the same database and ASP application. We need to run a script that modifies the database to the latest version. If the script runs twice it will cause problems so we need to build in a fail safe way of stopping it running a second time.
To do this we can update a version table at the end of the script. At the start of the script we check that the version is the previous one. If it isn't then we need to abort the enitre script. The problem is that the RETURN statement will only exit the current batch and execution of the script will continue from the next GO statement.
Is there any way to stop a multi-batch script running if a certain condition is met in one batch in such a way that the remaining batches do not run?
Batch execution is terminated because of debugger request. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Batch execution is terminated because of debugger request any solution or cause of this exception please let me know freindz
I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise.
When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters.
I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen.
The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms).
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
Hi All I'm Scripting various Upgrade scripts that set up roles / users / create databases etc and want to Exit the Script unconditionally if for example the user already exists etc. I can't Use RETURN because it just jumps to the Code following the next GO statement There is also some USE [Database] Statements Is there a way to do this or am I missing something fundamental here ? GW
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.
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
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.
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
The 6:th of march Sql server data tools for Visual Studio 2012 was released.
[URL]
I seem to be unable to install this using the link provided on the blog page. I'm getting a "Same architecture installation" error. Running on the machine is Visual Studio 2012 Premium & Sql Server 2012 (64bit).
I am trying to install SQL Server 2000 on a Win2K OS machine but Iget this error message:"A previous program installation created pending file operations onthe installation machine. You must restart the computer before runningsetup."I see others have had this problem but have fixed it using the solutionin:http://support.microsoft.com/defaul...kb;en-us;312995I have tried the solution but to no avail. The registry key:HKEY_LOCAL_MACHINESYSTEMCurrentControlSetContro lSessionManagerPendingFileRenameOperations keeps reappearing as soon as Iclose RegEdit.Any ideas?Thanks,lq
Please help,I am trying to install MSDE 2000 so that Backup exec will run on thisserver. I have gotten so far as to find an entry in my SQLSTP.TXT filethat says that c:mssql7 was detected so the install fails.Now, to start with there is no directory called C:mssql7 on thisserver. I have gone through all MS articles on manual removal of SQLserver and instances. I have searched the registry for mssql7 andremoved any keys that matched, as per MS article I have searched onSQLBOOT.dll and it is not present anywhere on my system and I still getthe same results.2:07:44 Begin Action: Detection12:07:44 SQL Server version already installed: 7.00.62312:07:44 Previous Install SQL Path: C:MSSQL712:07:44 Previous Install SQL Data Path: C:MSSQL712:07:44 End Action Detection12:07:44 Begin Action: ODBCLocked12:07:44 End Action ODBCLocked12:07:44 GetDefinition returned: 112:07:44 Previous SQL Server ProductType: MSDE [0x4]12:07:44 This combination of Package and Operating System allows a fullproduct install.can anyone think of anything here..what else could be causing the server to think there is a previousversion of SQL 7 on this box!!!?
I get an error when trying to install the SQL Native Client. It says it cannot find the installation package and to use a valid copy of 'sqlncli.msi'. I've downloaded the Express setup twice and get the same error from both of them.
I'm attempting this in a VMWare session setup for Windows 2K, Advanced Server. I am trying to test my installation and setup procedures. This is not a production setup, just install testing and documentation. I have allocated only 272MB to the VMWare session, which is why the system doesn't meet the recommended settings.
Any help would be greatly appreciated.
Below is the Summary.txt output file.
Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows 2000 Advanced Server Service Pack 4 (Build 2195)
Time : Wed May 03 09:54:23 2006
CPCWIN2KVM1 : The current system does not meet recommended hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online.
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server Setup Support Files (English)
Error : An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
Error String : An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
Here's my case, I have written a stored procedure which will perform the following: 1. Grab data from a table using cursor, 2. Process data, 3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
I'd like to install SQL SERVER 2005 Express Edition ,I begin to install it from DVD but during the installation it dosn't ask me about any instance and after installation there isn't SQLExpress service and I can't use sql server .(there are not MSSQL and Binn directory in sql server 2005) but when I install visual studio 2005 and after that I install sql server 2005 it ask me for name of instance and with this way every thing is ok and I use sql server completely.
I 'd like to know the complete installation of sql server 2005 is depend on installation of visual studio 2005 befor it ?if not how can I introduce any instance after installation of sel server 2005 .
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
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
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?
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
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.
