Executing Multiple Query Returned
Feb 24, 2008
Hi,
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?
Thanks,
Dipak.
View 1 Replies
ADVERTISEMENT
Feb 27, 2007
hi,
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?
thanz in advance..
divya
View 1 Replies
View Related
Oct 2, 2006
Hi,
I'm at a loss as to why i am getting the following error:
Retrieving the COM class factory for component CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E}failed due to the following error: 80040154
I am calling the package from a C# application which i have installed within a citrix environment. The application works without any trouble on my development machine but as soon as i run the code that executes the SSIS pacakge i get the error. Can anyone enlighten me as to what is causing this?
Many thanks in advance,
Grant
View 4 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Jan 23, 2008
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
View 2 Replies
View Related
Sep 2, 2006
The following statement gives the error that multiple records are returned. I am attempting to pick one weight out of a group of three, the three have the same ResidentID and different dates in the table variable @WeightReportRaw. If I hard code the ResidentID, ResidentID = 6, it works fine. The variable @intMonth3 passes in a month number ans dosen't seem to be a problem.
What am I missing, Thanks in advance for any help.
update tblResidentWeightsTemp set FirstWeight =
(select RWeight from @WeightReportRaw as W where ResidentID = W.ResidentID and month(W.RWeightDate) = @intMonth3)
View 6 Replies
View Related
Oct 9, 2014
I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.
First is that possible? Second if so how?
My data looks like this:
University X
University X - 1
University X - 2
University X - 3
College X
College X - 1
College X - 2
College X - 3
Cmty College X
Cmty College X - 1
Cmty College X - 2
etc.
How can I get it to return only:
University X
College X
Cmty College X
View 20 Replies
View Related
Mar 25, 2008
Hi,
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
Thanks and Regards
Salil
View 9 Replies
View Related
Jan 27, 2008
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
View 2 Replies
View Related
Oct 22, 2007
Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly.....
any suggestions....
thanks,
jonathan
View 5 Replies
View Related
Jan 12, 2004
Hi,
I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -
EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5
Thanks
View 4 Replies
View Related
Apr 24, 2008
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Can I use Execute package task for this purpose??
Thanks!!
View 4 Replies
View Related
Aug 27, 2007
I am probably missing something simple here but...
I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.
Case One
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL Query.
8. Execute Query via ExecuteReader and get SQLReader.
9. Read query result set.
10. Close QueryReader.
11. Set Command Text Property to another SQL Query.
12. Execute Query
This is where I get one of two exceptions.
1. An Exception stating that I cannot have parallel transactions.
or
2. An Exception that I have an open SqlReader that must be closed.
Case Two
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL INSERT command.
8. Execute Insert command.
9. Set Command Text Property to SQL Query command.
10. Execute Query Command via ExecuteReader.
At this point the stored procedure appears to hang.
Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?
Thanks in advance.
View 2 Replies
View Related
Aug 22, 2007
HI,
We are currently trying to import the Data from one DB to Multiple DBs.And these multiple DBs connection strings must be configurable. This I can do it by reading the connection strings from dtsconfig file and execute it for each DB.Is it possible to execute for all the DBs in parallel(Multithreaded).
Thanks,
Sabari.
View 6 Replies
View Related
Oct 15, 2007
Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.
View 1 Replies
View Related
Apr 24, 2008
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Then I did the following:
1. Right click on MSDB
2. Import package
Inside Import package window:
Package location: SQL Server
Server: servername
authentication: windows authen
Package path: \serverC$folder1SSIS Packagesfolder2
Import package as:
Package name: package.dtsx
Below is the error I am getting:
TITLE: Import Package
------------------------------
Cannot find folder "\serverC$folder1SSIS Packagesfolder2".
Can I use Execute package task for this purpose??
Thanks!!
View 12 Replies
View Related
Jan 3, 2005
Hi all,
Is there any way so that we can run/ execute multiple jobs or local packeges at once. Can we create any sql script which will execute multiple jobs/packeges at once. please advise
View 3 Replies
View Related
Jul 20, 2005
Hello!We have a set of individual .SQL scripts which we would like toexecute against a MS SQL Server 2000. Is there any way to have ISQLutility (or any other means) to execute all of them without having toestablish a separate database connection for each script:isql -Ux -Py -Ss -i script1.sqlisql -Ux -Py -Ss -i script2.sqlisql -Ux -Py -Ss -i script3.sql.....isql -Ux -Py -Ss -i scriptN.sqlAll scripts are in the same location, which could be made visible toby the SQL Server itself (so either a client-based or a server-basedinvokation is ok)TIA
View 6 Replies
View Related
Mar 3, 2008
Hi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
View 3 Replies
View Related
Jan 2, 2008
Happy new year everyone!!!
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:
1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:
SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
Help anyone? Please ...
View 5 Replies
View Related
Apr 10, 2007
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
View 14 Replies
View Related
Nov 9, 2007
Hi
I have written a stored procedure that returns 8 tables.
When I try to design a server report based on this stored procedure, reporting services only recognises the first table and not the other 7 tables.
I am using SQL Server 2005 and Visual Studio 2005.
Thank you in advance
Jav
View 4 Replies
View Related
Dec 28, 2007
Hi,
Can we execute multiple instances of the same SSIS package simultaneously??
If yes, how?
If no, what is the work-around to simulate such a functionality?
Thanks in advance.
Regards,
B@ns
View 9 Replies
View Related
May 13, 2008
Hello all -
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
thanks for all the fantastic support on this site
View 6 Replies
View Related
Jul 20, 2005
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies
View Related
Jan 30, 2015
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
View 0 Replies
View Related
Jan 24, 2012
I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491
Here is the SQL I'm using.
Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'
[Code] ....
View 5 Replies
View Related
May 26, 2006
I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query
View 3 Replies
View Related
Dec 26, 2006
Hi Folx,
I am new to
SQL Server. I am rolling summed values from table profile to two columns in table txn. When the value for one (not necessarily both...) column is null, can that value be translated to zero? Or must I write a procedure or use a cursor?
Versions:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
Microsoft SQL Server Management Studio
9.00.1399.00
For example, when version_count for a particular transaction_id is null in the profile table but profile_count has summable integers, how do I update the version_count to zero in the same pass that I update the profile_count?
update txn
set
version_count =
(
select
sum(a.version_count)
from
profile a
where
a.transaction_id=txn.transaction_id
and
a.extraction_date=txn.extraction_date
and
txn.version_count is null
),
profile_count =
(
select
count(*)
from
profile a
where
a.transaction_id=txn.transaction_id
and
a.extraction_date=txn.extraction_date
and
txn.profile_count is null
)
View 5 Replies
View Related
Oct 19, 2006
I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
View 3 Replies
View Related
Apr 29, 2004
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle
View 2 Replies
View Related
Jul 30, 2013
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
View 3 Replies
View Related
Aug 17, 2005
Hi there,
I¡¦ve got a table with the following as well as other info:
User ID
DirectoryTypeID (int)
Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9
If need these results to be:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9
Is this possible?
Many Thanks!! ļ
Grazi
View 1 Replies
View Related
