I have a temp table in my stored procedure when I try to insert into the temp table thro dynamic sql, it says that table has to be defined. What could be the problem. i have added the code below
CREATE PROCEDURE USP_RULE
AS
declare @TABLE1 table
(
SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
)
declare @EqNum varchar(25),@Pointnum varchar(25)
DECLARE @STRDBNAME VARCHAR(50)
SET @STRDBNAME = 'DB1'
EXEC('insert into '+@TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')
Hi, I have a proc, if i run that proc from query analyzer it completes in 12 sec. I want those results to be inserted into temp table. When i try to insert the results from Stored proc to temp table it is taking 5 min.
What could be the cause of the problem? Any help would be greately appreciated. sskris
I'm trying to insert any null values into a temp table. I'm storing the ixo_rlt_code as OldRole, and 'Other' as NewRole. I need both the ixo_rlt_code as OldRole and 'Other' as NewRole in the temp talbe in order to run an update I'm writing.
I know a case statement isn't necessary here, but this section is only one small part of a larger script, so I just used it to be consistent, and it will be easier to make any future changes this way. Below is my code and the error message I'm getting...
select ixo_key, ixo_title as Title, ixo_rlt_code as OldRole, CASEWHEN ixo_title is null or ixo_title = '' or ixo_title = ' ' or ixo_title = ' ' THEN 'Other'
END as NewRole
from co_customer (nolock) join co_individual_x_organization (nolock) on ixo_key = cst_ixo_key --and ixo_delete_flag = 0 where cst_delete_flag = 0 and cst_type = 'Individual' and ixo_rlt_code is null
This is the Error Message I get: Server: Msg 515, Level 16, State 2, Line 1095 Cannot insert the value NULL into column 'NewRole', table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________00000001698C'; column does not allow nulls. INSERT fails. The statement has been terminated.
I have an ASP page that opens a recordset on a stored procedure. I've had it in production for over a year, and it usually works fine - subsecond response. However, in the past couple weeks, the recordset open command has intermittently started to return timeout errors. It's very strange: when the timeout occurs, it can be dependably reproduced by opening the ASP page with the same parameter that produced the initial timeout. However, if you pass a different parameter to the procedure, it runs fine! It only times out when using the specific parameter that produced the initial timeout.
Even stranger: If, when attempting to debug it, I use QA to run the sp with the "defective" parameter, the sp invariably runs fine! And subsequent runs of the ASP page start working as well!
I have repeated this process many times in the past couple weeks: receive a report of the timeout error; open the asp page with the parameter that produced the timeout to verify; repeat; open the asp page with a different parameter - works fine; repeat with the initial parameter - timeout; Open QA and run the sp - no problem; refresh the asp page - no more symptom.
The connection to the SQL Server (7.0) uses a specific SQL login for all communications with the server. So all asp pages are logged in under the same name. I thought this might be a connection pooliing issue, so this morning, when the problem occurred, I logged into QA as the special name, but again, there was no problem running the sp from QA.
So - I've run SQL Profiler to track what's going on when the problem occurs and I've discovered the last statement to successfully run. Here is the sp:
/* * File Name: np_GetAvailablePreviews.sql
Returns a list of "reports" available to be previewed, defined as:
date/shift combination with "P" mode and "C" status (if there are Shift records, there must be an All record)
If none exist, check for the existence of accepted (mode = "A") Shift records without a corresponding All record - if so, return an All item for that date
Concatenate <OPTION> tags around each item */
CREATE PROCEDURE np_GetAvailablePreviews ( @DivisionNumber smallint) AS BEGIN set nocount on declare @count int, @datefrom datetime select @datefrom = dateadd(day,-900,getdate()) CREATE TABLE #PreviewReports ( ProductionDate datetime NULL, Shift char(3) NULL )
Insert #PreviewReports SELECT DISTINCT ProductionDate,Shift FROM dbo.LineItems WHERE DivisionNumber = @DivisionNumber AND Mode = 'P' AND Status = 'C' AND productiondate >= @datefrom
-->>>The above command completes successfully according to Profiler
Insert #PreviewReports SELECT distinct t1.ProductionDate, 'All' FROM (SELECT distinct ProductionDate, Shift, Mode, Status FROM LineItems WHERE DivisionNumber = @DivisionNumber AND Shift<>'All' AND Mode='A' AND Status='C' AND productiondate >= @datefrom AND timeperiod IN ('Day','Shift') ) t1 LEFT JOIN (SELECT distinct ProductionDate, Shift, Mode, Status FROM LineItems WHERE DivisionNumber = @DivisionNumber AND Shift='All' AND Mode='A' AND Status='C' AND productiondate >= @datefrom AND timeperiod IN ('Day','Shift') ) t2 ON t1.ProductionDate = t2.ProductionDate WHERE t2.ProductionDate Is Null AND NOT EXISTS (Select * from #PreviewReports where productiondate = t1.ProductionDate and Shift = 'All')
-->>>This one (the one before this comment) starts, but does not -->>>complete
set @count=(Select Count(*) From #PreviewReports)
-->>This one never begins - the ADO command times out
<snipped>
I did not provide a repro script because the problem is so intermittent that I doubted it would do anyone any good.
However if someone feels they want to try to reproduce this, I will attempt to create a repro script for them.
I suspect there is an issue with the temp table, but it's such a moving target that I haven't been able to pin it down.
I need to insert FK information into a temp table using database name and table name as a parameter.
I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.
IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs CREATE TABLE ##FKs ( [ForeginKeyName] [nvarchar](128) NULL, [TableSchema] [sysname] NULL, [TableName] [nvarchar](128) NULL, [RelatedTableSchema] [sysname] NULL,
Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.
declare @str1 varchar(80),@str2 varchar(80)
set @str1='create table #tmp(col1 int)' set @str2='insert into #tmp values (10)'
HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha
The dynamic sql is used for link server. Can someone help. Im getting an error CREATE PROCEDURE GSCLink ( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int ) AS SET NOCOUNT ON --Create temp table CREATE TABLE #TempTable ( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit ) INSERT INTO #TempTable (Name, AccountID, Active) --dynamic sql DECLARE @sql nvarchar(4000) SET @sql = 'SELECT a.Name, a.AccountID, a.Active FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b ON a.AccountID = b.oaAccountID WHERE oaAccountID IS NULL ORDER BY Name ASC' EXEC sp_executesql @sql --Find out the first and last record DECLARE @FirstRec int DECLARE @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) --Return the set of paged records, plus an indication of more records or not SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec
Error: Msg 156, Level 15, State 1, Procedure GSCLink, Line 22 Incorrect syntax near the keyword 'DECLARE'.
Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.
Code Block
create table #test( recno smallint PRIMARY KEY, value decimal (18,2))
insert into #test values (1, 3.57) insert into #test values (2, 5.32) insert into #test values (3,6.29) insert into #test values (4, 9.25) insert into #test values (5, 0.84)
Method 1: I tried inserting rows from #test into a temp table (#table) as follows
Code Block
declare @n as nvarchar(3) set @n = 1 while @n <= (select count(recno) from #test) begin exec (' insert into ##table select *, originalrecno = (select recno from #test where recno = '+@n+') from #test' ) set @n = @n + 1 end However, this yields an error message:
Code Block
Server: Msg 208, Level 16, State 1, Line 2 Invalid object name '##table'. Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.
Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert
declare @n as nvarchar(3) set @n = 1 while @n <= (select count(recno) from #test) begin exec (' insert into ##table select *, originalrecno = (select recno from #test where recno = '+@n+') from #test' ) set @n = @n + 1 end This worked - it inserted the data from the select statements in the loop into ##table.
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found". Reason can be Create and Insert statement gets executed as 2 different sessions. How to get the above requirement work fine? Thank you. HV
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1. Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID) This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
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
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
