.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

exec sql and transact

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
Hello guys. i've a problem, in a stored procedure i have a sql (SELECT * FROM TABLA -is only an example, actually, my sql statment is more complex) if i execute the sql statment the time for delay results is 6 seconds. but if i execute the stored procecure (EXEC spexample that has the same statmen) the results is in 2 minutes. is very slowly exec te store procedure, but the statmen is fast. excuse me for my english!!!

View Complete Post

More Related Resource Links

Transact-SQL Built-In Stored Procedures

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email


Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

Using equal operator in transact-SQL for ntext datatype column

Hi, I've a problem with using equal operator in transact-SQL for ntext datatype column. (SQL Server 2000) I'm using the following SQL command text. use NorthwindSelect * from Categorieswhere Description ='Seaweed and fish' If I use 'like' operator intead of '=' then the qurey retuns correct value. Any idea about this? Any help is appreciated. Regards, Julia

Using EXEC with a table variable?

I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )   exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2   ---------------------------------------------------------------------------------------------------------------------------------- I tried insert @a execute dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2    but got this error: Msg 197, Level 15, State 1, Line 6 EXECUTE cannot be used as a source when inserting into a table variable.   How can i do this.   Thanks.

How to pass the file path in EXEC of Stored Proc for getting IIS Logfile data

Hello,                  I need to get IIS Logfile data from Stored Proc. How to do this. I am using Bulk Insert Method in my stored Proc When I execute the stored proc I am getting an error that  Incorrect syntax near 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log'.     Below is the code.                 Create  PROCEDURE  spWebLog @path varchar(300) AS begin exec('BULK INSERT Weblog from''' + @path + ''')  end go Declare   @SQL Varchar(100 ) Set    @SQL= 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log' exec   spWebLog @SQL    

Backup tools fro granular restore - Symantec BAckup Exec Problem

Dear all, we are facing a problem here. Our sys admin set up everything to run the farm backups. He is using Symanteck Backup Exec 2010. In that farm we have 3 web applications which represents 3 content databases on a separate SQL back-end machine. When the backup is done, the report tells the backup succeed. It happens that when we try to recover from the backups, we can recover from an item level from 2 of those web applications, but not from 1. It created the whole site backup, but we cannot dig in to recover an item or a subsite. Did anyone had this problem before? (I gave farm admin levels to the account used by Backup Exec to double check the rights. That user could log on to the site and indeed he could go to all subsites and all item levels. So it is not access I believe). Any other expiriences with this tool? What about other tools? We are having a demo of AvePoint soon. Does anyone worked with that before? Thanks in advance for any help. Regards, GoncaloGonçalo

I have dual core machine i have hosted wcf service . At a time how many requests my server will exec

I have dual core machine i have hosted wcf service . At a time how many requests my server will execute how to find that count.

EXEC 17P_Comose will give incorrect syntax error. Because of numeric name

I am working with a sql server database with about 50 stored procedures. The database and stored procedureswere not created by me. The stored procedures all begin with a number in their name. Example: 17P_Comsetter The problem I am having is that if I try and execute the stored procedure at the command line in Query Analyzer,the query analyzer appears not to like that the stored procedure begins with a number. For example: If I try an run the stored procedure as follows: EXEC 17P_Comsetter Then I get the following error message:Msg 102, ..... Incorrect Syntax near '17' If I rename the stored procedure so that it does not begin with a number, then I do not get the error. The problemis that the company I am working at has a policy in place that stored procs must begin with a number.I am using SQL server 2005. Does anyone know why I am getting this error message?

extract date from text string - Transact-SQL

Hello, I have a field in my database with an archived date... (Giampaoli  Live Oak, Almonds Archive 09/16/10) I need to be able to extract the date from this, then perform a datepart function on it... How do I extract the date into it's own value using just SQL? Thanks, Wes

How to check if ADO recordset object populated by SQL Exec task


Hey all,


Is there a function I can use in the precedence constraint editor to check if a Exec SQL task populated an ADO recordset object?


Basically I have a select a,b,c from metadata_table where key = param and enabled = 1 and the output gets saved as an recordset object

I want the constraint to check that the object was populated before continuing and error out if not (the package should never execute with 0 rows being returned)

i've tried using 

!isnull(User::ListOfFiles) and Len(User::ListOfFiles)>0 but both return true even when 0 rows are returned

I'd rather not add a @@rowcount output parameter to the Exec SQL task

Does the object have any methods I can use in the expression bit of the constraint editor that'll check if it has any rows? Or are there any Count-like functions that'll work on it? I'm not much of a .net coder so can't think of anything else


edit: nvm I just realised I need to use a script task to set the error message anyway so I can load the object there and do a Rows.Count

(INSERT EXEC statement cannot be nested.) and (Cannot use the ROLLBACK statement within an INSERT-EX


hi all
i have a very important issue,

read this scenario please

i have three Stored Procedures Sp1,Sp2 and Sp3 .

the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.

if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

How to exec Code behind function in javascript


I had a custom confirm box Using Jquery....In that It crates two buttons Yes and No.

I need to execute my code in Yes click.....how?????

Backup Exec 2010 -MOSS 07 GRT Restore troubles

I was just curious if anyone else had trouble restoring documents from Backup Exec 2010 SP1 ?  I'm at a client and whenever they try to restore a document library to their portal, only the folder is restored and none of the contents. Re-directed restores to file shares work fine.  Symantec support says to upgrade to R2 to fix it, but that this is not a 'known issue'.  Funny, because they didn't troubleshoot long and had a quick fix ready to manually re-link the document to the site!!  Seems a little shady. I'm just wondering if this client has a unique issue or if anyone else out there has experienced this problem as well?  I'd like to warn our other clients with the MOSS 2007/ BUE 2010 combo if this is common. 

exec sqlcmd and bat files in a single bat fiel . How can I do it?


Hi all

I cannot seem to work out how to put it together I get all sorts of errors

 I would like to create a single bat file that execute 3 bat files and runs 10 sql scripts.

Any examples? help thanks a lot

Thanks for your help



How can I insert values from a query directly to a table in my database?
the query gets values from a linked server and i want to directly insert the results in my local database. This is how i thought would be done:

    INSERT INTO dbo.mytable
    EXEC dbo.QuerytoLinkedServer

When i execute dboSP_INSERTfromEXEC procedure, then i get this error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "blahblah" was unable to begin a distributed transaction.

Now, when I execute dbo.QuerytoLinkedServer procedure, i do get the values, there is no error. It just doesn't want to insert it I suppose.

Any help would be greatly appreciated!

EXEC(NULL) - Differing Behavior in SQL 2000 and SQL 2008


I'm working on converting a datbase currently in SQL 2000 over to SQL 2008 and found this peculiarity:

DECLARE @sql varchar(1)
SET @sql = NULL


On my SQL 2000 machine that code returns 0, but on my test box running SQL 2008 it returns 1 no matter the compatibility level of the database (80 or 100).  Is there a global setting / flag that might affect this behavior?  Or is it just a design difference in the two engines?

I haven't turned up anything when searching elsewhere, hoping someone can shed some light.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend