I am trying to populate a column with date/time info, I am doing this so I can use getdate() on the data for other computations (the format is mon/day/yr/hours/mins, i was told only data having mon/day/yr format can be used with getdate()). The code below says that dateyest is an invalid object name. Actually, any kind of insert I do reports the same error, no matter what column it is (securty on my account is all set, as well as for the stored proc.). I must be overlooking something very simple, right? thanks for the help!
DECLARE @dateYest datetime
DECLARE Cur668 CURSOR
Keyset
FOR
SELECT cast(date1 as datetime) dt
FROM baclosetable
OPEN Cur668
fetch next from cur668 into @dateyest
WHILE @@FETCH_STATUS = 0
BEGIN
if @dateyest is not null
Insert dateyest VALUES (@dateYest)
fetch next from cur668 into @dateyest
END
CLOSE Cur668
DEALLOCATE Cur668
I am trying to update a name column in the following way: (I wrote a description, but I think this visual is easier to understand).
This is what I have:
name1 name2 address etc
Bob null 123 street
Sue null 123 street
Jack null ABC circle
This is what I want:
name1 name2 address etc
Bob Sue 123 street
Jack null ABC circle
I'm just trying to get 2 names on the same row if they have the same address and get rid of the spare row. Name2 is currently null. Seems simple enough but I don't know how to do it in SQL. I can do it in FoxPro, but that doesn't help me here.
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Desired results FdmsaccountnoParentidLast_Post_DateParent Last Post Date 8780201218848782333428892014010520140105 8780201278818782333428892014010320140105 8780201288898782333428892013123120140105 8787018098808782333428892013121720140105 8780201208868782333428892013120420140105 8782333428898782333428892013120120140105 8787018088828782333428892013113020140105 8780201158868782333428892013112020140105 8787570508848782333428892013101120140105
My query is
select #Account.Fdmsaccountno, #Account.Parentid, #Account.[Last_Post_Date], from #Account inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo where #Account.Parentid = '878233342889' order by [Last_Post_Date] desc
So the query I am looking for would basically add some values to the results where they meet the right criteria.
I am running the query through a view so I dont actually want to add the values to the physical tables only to the view of the results if you understand what I mean?
I have a big table (> 40,000,000 rows) in SQL Server 2008 R2 database . I added new column of type int to the table. What is the fastest way to populate the column with -1 value. The table can be locked.
I am having hard time to populate dynamic column based on parameters.For eg. Parameter-->Dates : Date(1) ,Date(2),Date(3)....Date (n) (Multiple-Parameters is selected by drop down list ) Report format: Id | Currency |Country |District |Date(1)- transaction |Date(2)-transaction |Date(n) 1 | $$ |USA |123 |523238 |643763573 |62376264
Now requirement is If user enter 1 date then only single column date(1) populate if enter (n) dates then n column Date(1) ,Date(2),Date(3)....Date (n) populate. kindly suggest me any solution requirement.
I tried to bind Visibility property of column with Parameters passed.So that user can see only those column for which they pass values.I am getting error "Index was outside the bounds of the array"
I write this expression under Column(Date)-->Property-->visibility --> hidden--> expression =iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true) Kindly suggest any working solution .Thank you
there is a free text column in the source A sampe record "
Try to login to the Operational Risk System- EDCS system (https://oprisk4-dev.ny.ssmb.com:26539/siteminderagent/forms/login.fcc?TYPE=33554433&REALMOID=06-000f3e21-1533-1105-9e71-8088cb990008&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=$SM$ZlvK8bQN3Gx6kXd9LY%2fFTznf3Vi5QSreVbn0vxHs7IUR6gJ9ncq2qnEXtM4wBS0%2fGP%2bU8qMBqC8%3d&TARGET=$SM$%2foprcs%2fjsp%2fcs%2ejsp"), but get the following after entering my id and password:
The page cannot be displayed There is a problem with the page you are trying to reach and it cannot be displayed.
Open the oprisk4-dev.ny.ssmb.com:26539 home page, and then look for links to the information you want. Click the Refresh button, or try again later.
Click Search to look for information on the Internet. You can also see a list of related sites.
HTTP 500 - Internal server error Internet Explorer "
The above record has to inserted into the destination but SSIS gives error. The above is record from an excel file while importing it directly also gives error.
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
Table: classes Columns: classID, hp Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.
create trigger new_hp on curr_chars.char_active instead of insert as declare @hp tinyint select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp go
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' ) Begin --Adding a sequence of numbers to the ServiceLineID column. SET @id = 0 UPDATE tmpMHPCLMDET SET @id = ServiceLineID = @id + 1; End;
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.
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.
This what I have so far, the StoreFees table only has 5 rows:
DECLARE @StoreFee as smallmoney DECLARE @StoreLineID as int
DECLARE MyStoreFees CURSOR FORWARD_ONLY FOR SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee
OPEN MyStoreFees
FETCH NEXT FROM MyStoreFees INTO @StoreLineId, @StoreFee
--Do my Inserts into other tables INSERT INTO OtherTable (...,...,.., @StoreFee) ... ... -- Done working with that row
I am trying to pull the data via fetching rows into a variable.
Begin Fetch Next Into @temp... Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)
Print @temp < this works fine and returns the value for each fetch
End
I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.
I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.
The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.
The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message: Server: Msg 3910, Level 16, State 1, Line 1 Transaction context in use by another session. [OLE/DB provider returned message: Unspecified error]
There seems to be a connection issue. Can someone help me work around this?
Thank you,
Neal
FIRST PROCEDURE (this one works perfectly): truncate table dbidname CREATE TABLE ##dbtemp ( dbname VarChar(50), dbsize varchar (25), dbownervarchar(50), dbid smallint, createdate datetime, statusvarchar(75) ) declare@servernamevarchar(50) declare dbupdate cursor global for select servername from sqlservers where status = 'a' open dbupdate fetch next from dbupdate into @servername while @@fetch_status = 0 begin print @servername EXEC sp_addlinkedserver @servername, 'SQL Server' >>>INSERT INTO ##dbtemp >>>exec (@servername + '.master..sp_helpdb') alter table dbidname alter column sqlservers_id int null insert into dbidname (dbsystemid, dbname) select dbid, dbname from ##dbtemp update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and dbidname.sqlservers_id is null update dbidname set whenupdate = getdate() alter table dbidname alter column sqlservers_id int not null exec sp_dropserver @servername delete ##dbtemp fetch next from dbupdate into @servername end close dbupdate deallocate dbupdate drop table ##dbtemp exec spal_db_files_update
SECOND PROCEDURE (see >>>> to note problem area): truncate table dbidname CREATE TABLE ##dbtemp ( dbname VarChar(50), dbsize varchar (25), dbownervarchar(50), dbid smallint, createdate datetime, statusvarchar(75) ) declare@servernamevarchar(50) declare dbupdate cursor global for select servername from sqlservers where status = 'a' open dbupdate fetch next from dbupdate into @servername while @@fetch_status = 0 begin print @servername EXEC sp_addlinkedserver @servername, 'SQL Server' >>>>INSERT INTO ##dbtemp >>>>exec (@servername + '.master..sp_helpdb') alter table dbidname alter column sqlservers_id int null insert into dbidname (dbsystemid, dbname) select dbid, dbname from ##dbtemp update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and dbidname.sqlservers_id is null update dbidname set whenupdate = getdate() alter table dbidname alter column sqlservers_id int not null exec sp_dropserver @servername delete ##dbtemp fetch next from dbupdate into @servername end close dbupdate deallocate dbupdate drop table ##dbtemp
Error Messages... Server: Msg 16933, Level 16, State 1, Line 0 The cursor does not include the table being modified. The statement has been terminated. Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)... The cursor does not include the table being modified. The statement has been terminated.
query:
declare cursor_test CURSOR for select emp_ssn, effective_date1 from temp_employee_benefit_load open cursor_test declare @ssn char(9), @process_date char(8) fetch next from cursor_test into @ssn, @process_date while (@@fetch_status=0) update test_cursor set ssn = @ssn, process_date = @process_date where current of cursor_test fetch next from cursor_test into @ssn, @process_date
I have stored procedure in that I am using a cursor to fetch the row by row. cursor is fetching 75000 records so that the procedure is taking long time. Is there any way to replace the cursor to fetch the records row by row.
We have an app using VB with Access as the front end to SQL. SQL statments were built and sent directly to the server obtaining set-oriented results. Response was fair.
We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on the internet. The app was designed using the same logic to build the SQL statements, but it is also using cursors to retrieve all data. RESPONSE IS TERRIBLE! The designer says that to access SQL over the Web, cursor use is a MUST! True? Not True? If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve response time?
I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:
I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.
***********************Start of VB Code**************** 'Set initial value for sTReportHeaderIdx With rs1 sTReportHeaderIdx = !ReportIdx & !HeaderIdx sPReportHeaderIdx = sTReportHeaderIdx End With
bCount = 1
'loop through rs and when streportheaderidx changes reset bCount to 1 'otherwise increment bcount by 1 and write to field R08SegmentValue Do While rs1.EOF = False And blnContinue = True DoEvents Do While sPReportHeaderIdx = sTReportHeaderIdx With rs1 .Edit !R08SegmentValue = bCount .Update bCount = bCount + 1 sPReportHeaderIdx = sTReportHeaderIdx .MoveNext If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx End With Loop sPReportHeaderIdx = sTReportHeaderIdx bCount = 1 Loop
According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.
Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.
Hi I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.
I suspect there is significant room for optimization here. Some of my ideas:
- Write/update back to the cursor rather than executing separate UPDATE statements. - Try a dynamic cursor instead of a READ ONLY cursor. - Dump new elements to a separate table or a file and then do a single large INSERT.
Anyone else think any of these ideas will work? Can anyone think of something better?
BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.
I've made minor simplifications to the code: - Took out code to handle last line - Took out progress logging code - Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.
Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
DECLARE [TM #] CURSOR FOR SELECT * FROM [2004 TERMS];
These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.
I am quite novice in my abilities and I am completely stumped on how to get around using them.
As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?
I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.
Any help or pointers or a link to a good tutorial would be woderful.
declare BadRecords cursor dynamic for select lngZipCodeID from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID
/* this is the syntax in Sybase open BadRecords; BadRecordsLoop: loop fetch next BadRecords into BadID; if sqlstate<>0 then leave BadRecordsLoop end if */
I have to convert it to sql , I m just checking whether my syntax is correct or not
open BadRecords while @@fetch_status = 0 begin set @BadId = fetch next BadRecords if @@Error = 0 end
I have doubt in my syntax. Can you help me out in this?
