SQL Server 2012 :: Exec Producing Table Out Of Scope
Dec 17, 2013
I am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS'
declare@FilterClause VARCHAR(512)=NULL
declare@Classes VARCHAR(100)=NULL
declare@IsExcel BIT = 1
DECLARE @SQL Nvarchar(MAX) = ''
SET NOCOUNT ON;
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
Correlated Subquery: SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek), FROM dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
BEGIN TRANSACTION Copy records from live to archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail IF transaction was committed BEGIN TRANSACTION Delete records from live the archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
I have an SSIS with several data flows I need to do some complex data evaluations so I have used a script as transform in two of the DFT's. If I run these separately everything works great and there are no problems what so ever. If I run them together I notices I was getting an error on the second one. I discovered that this seems to be some kind of namespace problem since both Scripts were using Input_0 buffer. So I changed the name of the second one and retested.
Well I no longer get the error and in fact it seems to run through the entire SSIS just fine. However when I look closer I notice that the second Script task just does not seem to do anything at all. The script task does a lot of evaluation of the incoming data and then does some calculations depending on the value in the service code. however when it runs through this in the second script task all of the define output rows are just empty.
I have gone through and made sure that all input and output buffers are unique names thinking this was a similar problem but no luck. I even changes all column and variable names to unique with no luck. Again If I run them separately everything work fine it is only when I run the entire package that this problem occurs.
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code: user_id, amount, status 1, £10, S 2, £20, P 3, £30, P 3, £40, E
Users:
Code: user_id, name 1, user A 2, user B 3, user C
And I want to produce a summary table like this:
Code: S P E Total user A £10 £10 user B £20 £20 user C £30 £40 £70
What I have is:
Code: SELECT Users.name, (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E'))) FROM Invoices LEFT JOIN Users ON Users.user_id = Invoices .user_id GROUP BY Invoices.user_id, Users.name ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
if a dataset doesnt use a stored proc to define a table variable, what is the scope of that table variable? Does the name need to be unique from such variables defined by other datasets?
I have a query which is part of bigger transaction:
;WITH CTE( SELECT id, q, totalQ=SUM(q) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) FROM dbo.myTable WITH (UPDLOCK, ROWLOCK) ), CTE1 AS( SELECT id, q_take=CASE WHEN c.Total<@qRequired THEN c.q ELSE @qRequired-c.Total+c.q END FROM CTE WHERE (CASE WHEN c.Total<@qRequired THEN c.q ELSE @qRequired-c.Total+c.q END)>0 ) UPDATE t set q-=c1.q_take FROM dbo.myTable t INNER JOIN CTE1 c1 ON t.id=c1.id
Because CTE query is executed separately of update query I must put UPDLOCK hint, otherwise myTable could be changed while my query executes by some other user.(I have snapshot row committed transaction).
The problem is that my hint will hold lock on rows of myTable until the end of outer transaction(the bigger one).
But I need lock only for the time of this small query execution. What would be the best way to achieve that?
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
My office gave me assignment to find out the scope & limitations of the two Microsoft database applications - MS Access and MS SQL Server. After the findings, we have to chose the more appropriate database for our new project, which will have a humble start but may grow big in a year or two.
I will apreciate the info in facts & figures (not only in general).
I am not sure the SQL code to use...I am to produce a list that shows total sales by customer for the first two months of the year. I am to show the customer name, customer number, and total sales in that order. The current format I have it in list every invoice separately and what I need to do is find a way (the code) to combine the invoice totals into one total amount and only have the company name listed once. I also do not know how to set a date range so it includes invoices only from 1/1/2005 to 2/28/2005 Right now this is the current list and SQL code I have:
NameCustomer NumberDateSales Ace Construction Co.100023/18/2005$62.25 Ace Construction Co.100022/13/2005$62.25 Ace Construction Co.100022/13/2005$97.50 Ace Construction Co.100022/13/2005$89.00 Ace Construction Co.100022/13/2005$194.25 Ace Construction Co.100021/29/2005$24.90 Ace Construction Co.100023/18/2005$67.80 Ace Construction Co.100023/18/2005$304.15 Ace Construction Co.100021/29/2005$695.00 Ace Construction Co.100021/29/2005$19.50 Ace Construction Co.100023/18/2005$299.00 Aho Electrical Services100233/20/2005$449.55 Aho Electrical Services100233/20/2005$149.55 Aho Electrical Services100233/20/2005$994.70 Aho Electrical Services100233/20/2005$276.45 Barren Construction100501/30/2005$298.41 Barren Construction100502/25/2005$62.85
SELECT DISTINCTROW tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate, [SInvPrice]*[SInvQuantity] AS Sales FROM (tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerNumber = tblSales.CustomerNumber) INNER JOIN (tblInventory INNER JOIN [tblSales-Inventory] ON tblInventory.InventoryItemCode = [tblSales-Inventory].InventoryItemCode) ON tblSales.InvoiceNumber = [tblSales-Inventory].InvoiceNumber ORDER BY tblSales.InvoiceNumber, [tblSales-Inventory].InventoryItemCode;
I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column.... script for BULK INSERT ...
trying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
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 How can i do this? This is throwing an error. Thanks.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
What is the best way to transfer data from the staging table into the main table.
Example: Staging Table Name: TableA_satge (# of rows - millions) Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing: - Load data into staging table (TableA_stage) - Remove any duplication of rows from the staging table (TableA_stage) - Disable all indexes on main table (TableA_main) - Insert into main table (TableA_main) from staging table (TableA_stage) - Remove any duplication of rows from the main table using CTE (TableA_main) - Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID) SELECT Client.ID FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup ( @whichgroup int ) RETURNS @RETTAB TABLE ( TABLENAME VARCHAR(50)
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
Hi, If i have an SP called mySP that accepts one parameter @param If I have a table of paramaters with only one column like this: Param1 Param2 .. ParamN
How do I do if I want to execute the SP on all the table fields: some thing like this: Exec my SP 'Param1' Exec mySP 'Param2' ... Exec mySP 'ParamN' I want that automatically since the parameters are going to be in a table called myTblParams Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
I like to use the table "Inserted" within exec(), but it doesn't work because the scope is different. Does anyone have some sort of solution to this problem? The reason I am doing it this way is because I have a table consist of 200+ columns of bit types that contains permission information (The worest design i have ever seen!).
Code Snippet --gather column names
declare @ScreenPermissions nvarchar(256)
declare c_Permission cursor for SELECT [name] FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE type = 'U' AND [NAME] = 'ScreenPermissions' ) and [name] like 'Allow%'
open c_Permission fetch next from c_Permission into @ScreenPermissions while @@fetch_status = 0 begin
exec('INSERT INTO EmployeeInRoles (EmployeeID, RoleID) ' + 'select i.EmployeeID, r.RoleID ' + 'from inserted as i ' + ' inner join ScreenPermissions AS sp on sp.EmployeeID = i.EmployeeID and sp.' + @ScreenPermissions + ' = 1 ' + ' inner join Roles AS r on r.LoweredRoleName = Lower(' + '''' + @ScreenPermissions + '''' + ')' )
fetch next from c_Permission into @ScreenPermissions end close c_Permission DEALLOCATE c_Permission
