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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Using EXEC with a table variable?

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
 
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.


View Complete Post


More Related Resource Links

How to load a new table with the value of a variable from SSIS package?

  
Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

  
Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Stored Procedure utilizing table variable

  
I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

How to declare a table variable in SSIS and then insert rows into it

  
Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

Passing Table Variable to Stored Procedure

  
My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

can dynamic sql be used against a table variable?

  

This Works

create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)

This does not work

Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)

Error Msg:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.

Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?

The goal is to be able to pass in a string param like this to a proc

s1 = 'aa'',''bb'',''cc'

declare @s1 varchar(20)

Begin As

declare @tmp1(...)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In (''' + @s1 + ''')')
...

Thanks


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Variable value within SQL Server table Configuration is not being updated within load of DTSX packag

  

Hi,

I am probably missing something obvious here; -
I have created a package with a variable named; - ServerName and a value of 'KIERAN-PC'
I exported this variable as an SSIS configuration within a SQL Server table

I noticed the following record appeared within the SQL Server configuration table.

 

Loop through table records using variable

  

I know it’s possible to loop through table records using a variable and a For Each Loop Task. But is there a way to use the values in the variable and use them in a File System Task? When I tried, the File System Task complains that the variable is not a string. Any thoughts?

 

Objective – I need to loop though column values in a table which has the paths to the files that I’ll need to move using File System Task.   The table only has one column which stores the paths to the files to be moved.

 

Thanks in advance.


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


SSIS Create table using variable

  
Im working on my case study about inserting all the records of a flat file source to a OLEDB Destination. The problem is that I want to create a table using a sql query stored in a variable. How will I do that? Thanks

using table name variable in create function

  

anybody help me, i wanna create function using table name parameter.

this is my function code


ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
returns bigInt
as
begin
declare @RecID bigint
declare @Sql varchar(2000)
set @Sql = (select case when max(RecID) is null then 0 else max(RecID) + 1 end as RecID from @TableName)

return @Sql
end

when i run this code

i got error "Must declare the table variable "@TableName".

I have already declare a @TableName Parameter.

can you show me the way to solve my problem?

Thanks so much


Use variable in XML table definition

  
I am trying to use a local variable with html.  Here is the code:

BEGIN

 

-- SET NOCOUNT ON added to prevent extra result sets from

 

-- interfering with SELECT statements.

 

SET NOCOUNT ON;

 

 


Select only works with datetime variable, not table

  

Hello,

I am a learning-as-I-go person without formal training.  I have been working with Access databases for about 10 years, and have begun working with SQL in the last 5 years or so.  The system in user here is a 2K3 server (8xXeon 2.8Ghz, 12GB RAM, 1TB HDD) running SQL Server 2005 (32bit).  I am trying to develop a SQL query (view) which looks for values of one table' field at specific times based on a table of those times.  When I use a datetime variable, that particular value at that particular datetime will be returned.  When I try to replace the variable with the datetime result of a second query(view), no data is returned.  The code is below.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the the list of 'PriVolCk' datetimes
SET NOCOUNT 

using table variable in single quote

  

Hi!

I searched over internet but i couldn't find. How can i use table variable in single quote here some of code that i can do and i can't

for example if i can write like this it will work

declare @table table ([name] varchar(20))
insert into @table values('ahmet')
select * from @table
but if i can write like this does not work
exec('select * from ['+@table+']')
I can use any variable for table name like
declare @tableName varchar(20)
set @tableName = 'sampleTable'
exec('select * from ['+@tableName+

INSERT INTO table From EXEC -

  
Hello,

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:

CREATE PROCEDURE dbo.SP_INSERTfromEXEC
AS
BEGIN
    INSERT INTO dbo.mytable
    EXEC dbo.QuerytoLinkedServer
END

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!

Table Variable

  

Hi Experts,

Are table variables stored in tempdb, because we are trying to insert huge amount of data into table variable using sql syntax:-

INSERT	@TABLE-VARIABLE-NAME
SELECT
	*
FROM
	TABLE-NAME
WHERE
	ID = 0 OR ID = 1

We are getting the error message as :-

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Any work around, please help me out.

Thanks

Regards,

Kumar


ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType
Categories: 
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