Hi everybody,
I need a syntax in SQL Server 7.0. By using which I need to excecute a query from file. By using xp_cmdshell I am getting it. But whole results comes in a single column. I need the result set as it appears while executing the query. Can you help me in this. Any help will be appreciated.
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.
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
i have a .sql file, which i can run using query analyzer. is there any other way to execute this file, like using a .bat file or .exe file. user should be able to execute this file without using query analyzer or any other database tools. i cannot call this from my main programe. this should be a self executable file.
Hi all, I am executing a backup batch job scheduled daily. It runs successfully but havent backed up any of the databases. The message is "The name specified is not recognized as an internal or external command, operable program or batch file."
I am trying this for a full day and i gave up..please can anyone help me or suggest me what to do?..The same batch file is executed in all servers and is working fine..
I have a bunch of sql in a text file I would like to execute from my .net/c# program, (Mostly DDL and inserts I run via SSMSE when I create a new database) I would rather not go through the laborious process of opening the file, reading through line by line, concatenating strings, and sending it to the database. I would just like to say "here's a file of SQL, run it".
Someone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?
I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.
I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.
I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.
Any help would be greatly appreicated. Consider me an SSIS novice.
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.
osql -E declare @cmd nvarchar(1000) declare @cmd2 nvarchar(1000) declare @state1 varchar(100) declare @message varchar(100) set @message = '' -- Build command to determine state of SQLSERVERAGENT service on Master Server SET @CMD = 'create table #state (state varchar(2000))' + char(10) + 'declare @cmdx varchar(1000)' + char(10) + 'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' + + char(10) + 'select @state=state from #state' + char(10) + 'drop table #state' -- Build command to execute command that determines state of service being monitored set @cmd2 = 'declare @state varchar(100)' + char(10) + 'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' + 'N''@state varchar(100) out'',' + '@state out' + char(10) + 'set @state1 = @state' -- Execute command and return state of service being monitored exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out -- Is the service that was monitored not IF (UPPER(@state1) <> 'RUNNING.') --if @state1 <1 'Running.' begin -- Display message that primary monitor is down select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13) print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.' exec master.dbo.xp_smtp_sendmail
It works fine when I run it on the command line prompt. And I receive a mail , if the server agent is running.
But when I save it as bat file and try to run , it stops and doesnot even give an error.
Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex() method reads the script file from starting to end and returns script text. My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime, @prmEndDate datetime,@prmEvent char(1)ASBEGIN SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) as EventDate from dbo.Parishioner P left outer join dbo.Sex S on P.SexId = S.SexId where (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) between @prmStartDate and @prmEndDate END But when i executing script file having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm
where DATEPART(yyyy, ActivityDate) = DATEPART(yyyy, getdate()) and Month(ActivityDate) = 3
Group By Datepart(yy,Activitydate), Month(ActivityDate), DataCenter, Farm
) A
INNER JOIN
(SELECT *
from Monthly
where Year = DATEPART(yyyy, getdate()) ) B
ON
A.Year = B.Year and
A.Month = B.Month and
A.DataCenter = B.DataCenter and
A.Farm = B.Farm
Above query executes fine giving output as 3519; that too in 1sec. However when i try to apply criteria on Monthly table of Month=3, query just keeps executing for hours..
I have to execute a .bat file on a remote server (that is used to stop and start services of an appl). The remote server doesn't have SSIS,SSMS installed. I want to create a package on my desktop the uses Execute process task and execute the .bat file on the remote server and then schedule it using the SSMS.
When I execute this query, I get the error: 'The colum prefix 'ClassifiedAd does not match with a table'. But the table does exist. Does anyone know what's wrong?
select ca.ItemID, ca.ModuleID, 'CreatedByUser' = u1.FirstName + ' ' + u1.LastName, ClassifiedAd.CreatedDate, 'UpdatedByUser' = u2.FirstName + ' ' + u2.LastName, ca.UpdatedDate, ca.AdTitle, ca.AdText, ca.AdHasImage, ca.AdPOCName, ca.AdPOCEmail, ca.AdPOCPhone from ClassifiedAd ca left outer join Users u1 on ca.CreatedByUser = u1.UserID left outer join Users u2 on ca.UpdatedByUser = u2.UserID where ca.ItemID = 1 and ca.ModuleId = 1
if Region==Asia then compute percent of Amount if Region==US then compute percent of Amount if Region==Europe then compute percent of Amount if Region==Africa then compute percent of Amount
join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID
2)
but if I add following Join to above query then query does produce results and keep saying 'executing' for hours "join TaxRate tr on tr.CalcDataID = cd.CalcDataID
Select cd.*
from TaxByPackage p join TaxOverrideReason tor
on tor.TaxId = p.TaxId
and p.PackageId in (10, 11)
join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID
join TaxRate tr on tr.CalcDataID = cd.CalcDataID
3)
If I insert the data produce by first join 1) to a table and then join the new table data result to
Taxrate table then result come fine
insert into c1
select cd.*
from TaxByPackage p join TaxOverrideReason tor
on tor.TaxId = p.TaxId
and p.PackageId in (10, 11)
join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID
declare @AccountID numeric(3,0) set @AccountID = 101 select * from AccountTest WHERE AccountID IN (CASE WHEN @AccountID = 101 THEN (100,101) ELSE @AccountID END)
I m getting error like
Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ','.
Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San
i am making a n application which in between deletes the multiple tables from the sql database.
for that i have written the following code:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
in this way, many more tables are to be deleted.
is there any need to create the new SQLCommand object again and agin for each and every query. can iot be done like the given above or can there be some better method?
I am executing following script which return more than 1 query/row as there are more than 1 log backup : select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)
[Above script restores database with latest Differential backup file and latest log files. ] Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
I was having some errors from the webpage accessing the OLlinks table in the database. Error executing non query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at admin_admOLEditLinks.selectData(String strID) in e:wwwroothomeadminadmOLEditLinks.aspx.cs:line 101DateTime:5/23/2007 1:14:10 PMSource:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3ErrorMessage:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error: SQL Execution Error. Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks Error Source: .Net SqlClient Data Provider Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.
Open any other table works fine except this table only. Any help is much appreciated.
Hi all i am facing problems in executing stored procedures. Sometimes the connection to server fails. all the query, sp working fine few minutes back fails. the sp executes and executes and does not get results but hangs later a get an error.
Any ideas please let me know it is quite urgent. i am at the point of testing and only now facing such problems
I am really in a great trouble. My requirement is quite complex, as I feel, it may be quite simple for some of you. Here is my problem - Actually I am doing a project, where client has a specific requirement. i.e. he want's to build a query on runtime for selecting particular record he wants, so that we have provided a user interface where he can select any datasource e.g. SQL, Oracle, Excel etc. He also specifies the database name. He is displayed all the tables and columns under those tables. He selects columns from those table boxes and write the query he wants, with where clause, if required. I am saving these query in a table, storing column names in another table where we map those oringinal column names with columns of another table, wehre we want to store actual result set of the prepared query by the user. for examaple .. if user preapare query like - 'SELECT CUST_ID, CUST_NAME FROM CUSTOMER WHERE CUST_ID = 10' In case of above query I will store CUST_ID in column COL1 of table TAB1 and CUST_NAME in COL2 of table TAB1, like that we have such fifty columns in that table. Now question is here every thing is dynamic data provider, database, tables, columns and where clause, then how can get the result set out of those queries and store that query output in the that storage table with columns col1, col2 and so on, upto 50 columns. Please help me on this, as it is so urgent. I will be very much thankful to you people.
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
Hi, I have written a stored proc to bulk insert the data from a data file. I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc
The follwing statement works fine if i give the table name directly in the query
Code Snippet
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
INSERT INTO Category SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt', FORMATFILE = '\remotemachineFormatFile.fmt', FIRSTROW = 2) AS a
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below
Code Snippet
@Category - Will be passed as a parameter to the stored proc
Print @vsBulkSQL - This prints the folliwing statement
INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
Exec @vsBulkSQL - This statement gives the following error
The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same