Table Schema Changed At Runtime In Cursor

Apr 8, 2008

none

View 3 Replies


ADVERTISEMENT

Could Not Complete Cursor Operation Because The Table Schema Changed

May 30, 2006

Microsoft SQL Server  2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue?  I suspect the application logic may cause this error. Please advise.

Thanks a lot!

 

View 16 Replies View Related

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

View 4 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

How To Configure A Dataflow Task Having A Runtime Source Table Name And A Runtime Destination Table Name

Apr 18, 2008

Hi,

I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...

am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.

The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....

is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)

Thanks
Radhika

View 4 Replies View Related

Schema Name Has Changed In SQL-server 2005!

Nov 9, 2007

I am using sql server 2005 express. When I created a new database my schema name was dbo and my stored procedures looks like this:
dbo.sp_name
Now when I am creating new sp:s my schema name has change to DOMAINUsername:
 DOMAINUsername.sp_name
 My webb application cant find any sp:s with this schema name and I don't now how to change schema name back to dbo. What has happened, what is this schema name and why has my schema name been changed!? Please help me out!
 

View 1 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View 1 Replies View Related

Transact SQL :: Update Table From Linked Table If Something Has Changed?

May 20, 2015

Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?

View 4 Replies View Related

Changed Table Structure

Mar 1, 2004

Hi all,

How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)

Pls let me know
TIA

View 3 Replies View Related

Can Table Ownership Be Changed?

Jul 20, 2005

When I create tables in SQL I can specify dbo as the owner using thesyntax below but when using the upsize wizard in Access 2000 I owneverything. Is there a way that the system administrator can changeownership after the fact?CREATE TABLE dbo.mytable (c1 int not null)Thank You,Randy KJoin Bytes!

View 1 Replies View Related

How To Put Only Changed Fields Into A New Table

Mar 6, 2008

Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)

is possible with trigger to insert only changed fields on t1 to t2 like

(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)

i hope you understand what i look for.

Is this trigger solution ?

CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND

View 7 Replies View Related

How To Check A Table Whether Data Is Changed Or Not..??

Jan 30, 2006

Hi..

I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....

I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans
Tha Proc will update the existing data in all tables whether it is Good or Bad Data.

when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.

I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.

Can any one has an idea on this,,,,

Thanks
Bob

View 2 Replies View Related

How To Find Out If A Spesific Table Has Changed?

Feb 28, 2008

Hi,

I have a small question regard how to find out if a specific table has chanced.
Is there any (sql)function inside MSSQL2005 that I can use to find if a spesific table has changed? I have heard of timestamp, but I don't know if that will solve my problem? Is there any thing in the sys.tables that kan help me, just do a fast query and check if a table has changed?

I'm developing a web-application with AJAX.NET, and I have UpdatePanels with datalist. When the tables in the db gets big the application is henging for a sec or two when it receiving the data, and when the timer (refresh rate) is 2 min it is not good! So my plan is to check if the table has chanced since last transfer of data, and if it has it will download the data, if not do nothing.

Jonny

View 4 Replies View Related

Subscriber Pull- Hows It Know Table Changed.

Jan 7, 2000

When you setup a subscriber to a publication, that is pull the publication, does it copy all the rows or does it keep track of which rows changed.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

Apr 6, 2015

I have a history table with the following values

CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

[Code] ....

I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result

OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18

View 4 Replies View Related

Column Compare In Same Table And Show Only Changed Value

Aug 18, 2014

How can I compare column in the same table and show only column and value that has been changed.

declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t
SELECT '11111','Text1','Text2','Text3'

insert into @t
SELECT '11111','Text1','Text2','Text4'

END RESULTS:

Column Name Old New
Column 3 |'Text3' |'Text4'

View 9 Replies View Related

Find The Last Time The Data In A Particular Table Was Changed

Mar 27, 2008



Is there a Tool/Utility/StoredProcedure/Query/View that can tell me the last time the data in a particular table was changed?

That change can be the result of either an ADD , INSERT, or DELETE statement.

I am using SQL Server 2005.

View 3 Replies View Related

Auditing:Extracting Changed Fields From Inserted Table

Jan 15, 2007

Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick

View 6 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

Runtime Temporary Table In SQL2000

Nov 22, 2006

I am trying to create a runtime temporary table at SQL2000 server in my VB 6.0 program. However, I am keeping getting "object not found" error from my program when I trying to issue a SELECT or INSERT statement in my program. Can anyone shows me how to do it properly.

I thought I can create a temporary table with a # prefix as a table name. Am I correct?

Help is very much appreciated.

John Tsoi

View 5 Replies View Related

SQLSERVER 2000,How Can I Detect The Last Time Where The Table Has Changed (updated)?

Jan 2, 2008

Hi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.

For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )

Hope to be clear,
Best regards
Ariel

View 4 Replies View Related

Storing Data In Temp Table During Runtime

Sep 12, 2006

hi all

i am displaying some information in a datagrid as part of shopping cart.i want to store the informations which i displayed and some informations
which the user enters in a temporary table, so that i can perform manipulations based on that.
how can i do that?
how to load those informations into a temporary table during runtime
can anyone explain me in detail? please
thanks in advance

i am using sqlserver2000
vb.net2003

View 1 Replies View Related

Create Target Table Dynamically Based On Source Table Schema?

Sep 13, 2005

I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, 
Bryan Ax

View 9 Replies View Related

Tempoary Table Question - How To Create One With The Same Schema As An Existing Table?

Jul 28, 2006

Hello,

I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?

I'd like to do something like:

CREATE TABLE #tempTable LIKE anotherTable

..instead of...

CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...

I'm sure there must be a simple way to do this!

Many thanks,

Ben S

View 3 Replies View Related

SQL Server Configuration Table Runtime Loading Problem

Jun 8, 2007

The package works fine in development site. It was compiled with "Enable package configurations" and sql server as configuration type. But after installed in production database using deployment manifest, the package does not load the configuration values at run time from the table, instead it uses the variables' value in design time.



If the configuration is a file in windows folder, I may be able to load it from the sql job agent's step configuration tab. When configuration values are stored in a table, how can I make it load the configuration from there at run time?

Thanks.

View 5 Replies View Related

Getting Count From Table In Linked Server Using Runtime Query

Mar 21, 2008

Hi Friends,
I want to have solution for one of the problem.
The requirement is like this :
I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod.
This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table.
I created the connection using the linked server

EXEC sp_addlinkedserver @SerevrName,N'SQL Server'



EXEC sp_addlinkedsrvlogin @SerevrName, False, Null, @ServerUserName,@SerevrPws

Now I am trying to get count using following query :
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'

But the question is that the execution goes this way :

exec(@SQLQuery)


Now how to assign this count value to some variable so that I can use it later ...?

Going forword I want to use cursor and get the rows in these table using cursor ...?
How can I assign values returned from any runtime query to temporary variable or table ...?


I tried another approach also:
I put remote connection and query execution in inner stored proc called usp_GetTableRowCount
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'

exec(@SQLQuery)

and in outer stored proc : referenced the inner stored proc like this

exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>>
The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows
but when I see value of AFSDataRowCount, I get 0.

Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow.
Thanks in advance.

View 6 Replies View Related

Altered Table-changed Column From Text To Varchar And Now Having Issues With Alter Switch

Feb 14, 2008

Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
When I attempted to run the ALTER TABLE SWITCH I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.

Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.

BOL States:

The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.

Thanks

View 1 Replies View Related

VB6 &&amp; VB2005 Runtime Error When Writting To Access Database Table

May 1, 2007

In both VB6 and VB2005 Both are loaded on same computer. When attempting to write to an access database table using ADO I get a runtime error: -2147217887(80040e21) "mutiple-step operation generates errors. Check each status value. " Reading the database is no problem. I only get the errors when writting to database. I have used the following code forever in VB6 and never got this error. Now that I have Visual Studio 2005 loaded on the same machine. I get this error. I get the exact same error when using this code in VB2005. I know VB6 but am just learning 2005. The following code is writting to a database table from various user imputed text and combo boxes. I used basicly the same code in VB2005 and get the same exception error. This code works on machines that do not have VB .net loaded. How do I fix this. My application is useless unless the user can make changes to their data.



Public Function ActionToDb()
Dim noPo As Integer
Dim SQL As String, rst As ADODB.Recordset, dataSourceName As String

dataSourceName = DBPATH & "LVBURV Database.mdb"
dbConn.ConnectionString = "DBQ=" & dataSourceName & ";Driver={Microsoft Access Driver (*.mdb)}"
dbConn.Open

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
With rst
SQL = "Select [mLVbuNo], [mStratInit], [mMerticLv],[mSubMetLv],[mTaskAction],[mImpact]," & _
"[mStatus], [mOwner], [mTitle],[mOrigDate],[mCurrentDate],[mCopmpleteDt],[mComment],[mSource]," & _ "[mStatColor],[mHyperlink],[CompCheck] From [tblMasterActionItem]"
.CursorLocation = adUseClient
.Open SQL, dbConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
noPo = .RecordCount
'If .RecordCount > 0 Then
.MoveLast
.AddNew
rst.Fields("mLVbuNo") = giRecNo
rst.Fields("mStratInit") = frmActionDe.cboStatInit.Text
rst.Fields("mMerticLv") = frmActionDe.cboMetricLever.Text
rst.Fields("mSubMetLv") = frmActionDe.cboSubMet.Text
rst.Fields("mTaskAction") = frmActionDe.txtTask.Text
rst.Fields("mImpact") = frmActionDe.txtImpact.Text
rst.Fields("mStatus") = frmActionDe.txtStatus.Text
rst.Fields("mOwner") = frmActionDe.cboOwn.Text
rst.Fields("mTitle") = gstOwnT
rst.Fields("mOrigDate") = frmActionDe.txtOrigDt.Text
rst.Fields("mCurrentDate") = frmActionDe.txtCurDt.Text
rst.Fields("mCopmpleteDt") = frmActionDe.txtCompDt.Text
rst.Fields("mComment") = frmActionDe.txtComments.Text
rst.Fields("mSource") = frmActionDe.cboSource.Text
rst.Fields("mStatColor") = frmActionDe.txtStatColor.Text
rst.Fields("mHyperlink") = frmActionDe.txtHyperlink.Text
If optCompCheck.Value = True Then
rst.Fields("CompCheck") = True
Else
rst.Fields("CompCheck") = False
End If
'End If
.Update
.Close
End With
dbConn.Close

ExitSub:
On Error Resume Next
dbConn.Close
Set rst = Nothing
Set dbConn = Nothing
Exit Sub
ErrorHandler:
WriteToErrorLogEnd Err.Description & " in ActionToDb"
Resume

End Function

View 6 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

Putting Data Into Table B From Table A Without Using A Cursor

Aug 24, 2006

on SQL Server 2005, no SP.

Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:

declare @HTR money
declare @Uniqueid varchar(15)

declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid

open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR

while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end

close cur_htr
deallocate cur_htr
...

This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?

View 10 Replies View Related

Getting Table Schema

Jan 15, 2007

Hi

I come from a MySQL background and have been having some trouble finding the MSSQL command that corresponds to MySQL's "describe." I Googled for around 1/2 hour but can't seem to find it.

Any help appreciated

View 3 Replies View Related

Table Schema

Jan 11, 2004

Hey,

I'm looking at creating a table in a stored procedure

[declare @tempTable table]

however, rather than then declaring the tables schema

[declare @tempTable table (pkField IDENTITY (1,1), SomeOtherField varchar(50), ...)]

I'd like it to simply use the schema of an already existing table (plus an additional column).

Is there a way to do this without having to manually write the table schema?

A simple example is:

I have a table
OriginalTable (idCol, NameCol, InfoCol)

I'd like to create a temp instance of that table called tempTable which would have a final schema of

tempTable (idCol, NameCol, InfoCol, myTempCol)

The reason I'd like to do this using the schema is so that I don't need to update all my procedures in the future when we decide to add some more detail to the originaltable which needs to be selected as well.

Thanks a lot for any help or direction you can provide.
-Ashleigh

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved