[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions)
Code:
declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'
Now, I want to create a cursor to recalculate some values
I've tried:
Code:
declare cur_T cursor for exec(@sql) open cur_T
but it doesn't work.
Can I have another way to do that???
View Complete Forum Thread with Replies
Related Forum Messages:
How Do I Create A "Cursor" In SQL Server 7.0??
How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios. 1. If the record exist in the Destination and the Source I will do nothing. 2. If the record exist in the Source and not in the Destination then I will add the record to the destination. 3. If the record exist in the Destination and not in the Source then I will write to the transaction Log. Note: I am only concerned about updating one column in Destination which matches the only column in the source. Someone Please help??
View Replies !
SQL Server 2000 And JDBC,Registering Cursor As OUT Param
hi I am using JDK1.4,SQL Server 2000 and Microsoft's JDBC driver. I am stuck in accessing cursors. The SP i wrote is ************************************************** *** USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spSelectEmployees' and type = 'P') DROP PROCEDURE spSelectEmployees GO CREATE PROCEDURE spSelectEmployees @emp_cursor CURSOR VARYING OUTPUT AS SET @emp_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM employee OPEN @emp_cursor GO ************************************************** ****** The problem I face is that when i register the output param in java code,what datatype do I specify..code is written below. calStmt = conn.prepareCall("{CALL pubs.dbo.spSelectEmployees(?)}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); calStmt.registerOutParameter(1,Types.?????);-->What goes here??? java.sql.Types does not give any datatype for cursor or object.Any one knows if there is an extended type given by Microsoft Driver?? thanks in advance. Prashant prashantjainn@yahoo.com
View Replies !
Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View Replies !
Create View From Cursor
I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend
View Replies !
Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway? Is Declare Cursor same as Create Cursor and if not what is the major difference?
View Replies !
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
How To Capture The Value For A CURSOR Statement
Hi everyone, The following snippet of code returns something like that: string;string1;string2 Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task) Thanks a lot, declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
Error: Could Not Create An Acceptable Cursor.
I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.
View Replies !
Create A Cursor Inside A Sproc
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is: StudentInfo StudentID StudentName DeptID 101 John 10 102 Alex 10 103 Beth 20 ClassInfo ClassID DeptID 901 10 902 10 225 20 I want to create a Sproc which will retreive the student's classes in DeptID 10 Following is the Sproc and cursor: use master go Create PROCEDURE [dbo].[getEnclishClasses] @StudentID int AS Declare @printInsertStatement nvarchar(100) ECLARE NewRowID int Declare classCursor CURSOR FOR SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID) DECLARE @ClassID INT DECLARE @DeptID INT OPEN classCursor FETCH NEXT FROM classCURSOR INTO @ClassID, @DeptID WHILE (@@FETCH_STATUs=0) BEGIN PRINT 'SET @newID = Scope_Identity()' SET @printInsertStatement= (Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values(' +CONVERT(NVARCHAR (10), @ClassID) + ',' +CONVERT(NVARCHAR (2), @DeptID)+')' FROM [myTest].dbo.StudentInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID)) PRINT @printInsertStatement END CLOSE classCursor DEALLOCATE classCursor EXEC getEnclishClasses 101 Here is what I try to get (text with actual data from the table): SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10) SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10) Here is what I had got (returning multiple lines, more than number of records I have): Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
View Replies !
How To Use Cursor To Create Re-occuring Numbers?
SELECT pid, lname, visit_date, quantity FROM customer ORDER BY pid pid lname visit_date quantity ------------------------------------------- 23 wang 07/23/2006 100 23 wang 07/30/2006 140 23 wang 08/05/2006 130 23 wang 08/15/2006 135 23 wang 08/22/2006 110 34 linden 06/23/2006 99 34 linden 07/06/2006 110 34 linden 07/15/2006 120 34 linden 08/26/2006 99 How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid index pid lname visit_date quantity ---------------------------------------------------------- 1 23 wang 07/23/2006 100 2 23 wang 07/30/2006 140 3 23 wang 08/05/2006 130 4 23 wang 08/15/2006 135 5 23 wang 08/22/2006 110 1 34 linden 06/23/2006 99 2 34 linden 07/06/2006 110 3 34 linden 07/15/2006 120 4 34 linden 08/26/2006 99 Please let me know if I can explain the my question better. Thanks in advance
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
TOP Statement In SQL Server 2000
Code Snippet string sql = "SELECT TOP(@aantal) macID FROM MAC WHERE macID NOT IN (SELECT macID FROM ProductMAC) ORDER BY macAdres"; SqlParameter par = new SqlParameter("@aantal", aantal); How come this works in sql server 2005 but not in sql server 2000? The error is on the parameter. The parameter is an integer. Does anybody know the answer? Regards Tim D'haeyer
View Replies !
24000 Invalid Cursor State. Prepared Statement
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++. I am not using / importing any dlls. I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery. I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed. First time when I bind paramaters using SQLBindParameter it works perfect. When loop gets executed secondtime onwards, it gives an error. SQLState: 24000 [ODBC Client Interface]Invalid cursor state. If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error. I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time. Have I missed any step? Where & when I should code the cursor type? Any specific libraries I need to link? Thanks
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Unable To Create Subscriber On A 2000 Server From A 2005 Server Publisher
Hi, I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly. After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription). Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber. The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server. Any help would be appreciated. Thanks.
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
How To Create ASPNET User In SQL Server 2000
Respected Members, I have a question, when our aspx page is trying to access the SQL Server 2000 database than before this do we have to creaete a user with the name of ASPNET in SQL Server 2000, or without creating this paticular user(its the default user of ASP.NET account) our aspx pages can also access the SQL Server 2000,I have already SQL Server 2000 running in "SQL Server Authentication Mode" so while creating the user do I have to create it in "Windows Authentication" or "SQL Server Authentication", can you provide some link where I could find complete steps for craeting the ASPNET user or if some one can tell all the steps in details here.
View Replies !
How To Create BuiltinAdministrator Login In SQL Server 2000?
Hi, I am using SQL Server 2000. But i am not able to see the BuiltinAdministrator account in the logins list in Enterprise Manager but by default the owner of the system gets access to all databases and objects in the system. I would like to restrict that by restricting the access of BuiltInAdministrator account but it is not visible. Is there any option to be enabled to make that visible? Could you please help me in this issue.. Thanks.
View Replies !
How To Create ASPNET User In SQL Server 2000
Respected Members, I have a question, when our aspx page is trying to access the SQL Server 2000 database than before this do we have to creaete a user with the name of ASPNET in SQL Server 2000, or without creating this paticular user(its the default user of ASP.NET account) our aspx pages can also access the SQL Server 2000,I have already SQL Server 2000 running in "SQL Server Authentication Mode" so while creating the user do I have to create it in "Windows Authentication" or "SQL Server Authentication", can you provide some link where I could find complete steps for craeting the ASPNET user or if some one can tell all the steps in details here.
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
Cannot Create Diagrams In Upgraded SQL Server 2000 Database
Dear all, I am trying to save a newly created diagram in SQL Server 2005 SP1. The error message is: ----Beginning of pasted error message--- =================================== Cannot insert the value NULL into column 'diagram_id', table 'adt_db.dbo.sysdiagrams'; column does not allow nulls. INSERT fails. The statement has been terminated. The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider) ------------------------------ Program Location: at Microsoft.VisualStudio.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite) at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled) ----End of pasted error message--- The database I use has been upgraded from SQL Server 2000. I am running in SQL Server 2000 Compatibility Mode (80) but changing the compatibility mode to SQL Server 2050 (90) does not make any difference with regards to this error.
View Replies !
Can't Create Excel File With SQL Server 2000 Client.
Hi ; I am trying to create several Excel sheets using SQL 2000 views like so: Select * INTO [Excel 8.0;Database="C:spreadSheetsaNew.xls"] FROM [aView]. When I try and execute this in my app I get the following - Specified owner name 'Excel 8.0;Database=c:spreadSheetsaNew.xls' either does not exist or you do not have permission to use it. If I use the above Select statement with an OLEDB connection it works. I am using Imports System.Data.SqlClient, instantiating a new SQlConnection object, opening the connection, etc.. Thanks, Gordon
View Replies !
Select Statement In Sql Server 2000 Is Taking Too Long??
Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque
View Replies !
Moving From 2000 To 2005: Issue Accessing Cursor Returned By Sp
I have a number of triggers that call a stored procedure that returns a cursor. The triggers then use the results of this cursor to do other actions. My problem is that this works fine in SQL2000 but just won't work in SQL2005. When I try to access the results of the returned cursor, I get an error -2147217900 could not complete cursor operation because the set options have changed since the cursor was declared. If I port the code contained in the sp into the trigger, it runs fine. But having to port over the sp's code defeats the whole concept of being able to re-use the sp. Does anybody have any ideas of what could be going on? I look forward to a quick response. Dennis
View Replies !
Help Me Create A SQL Statement
I have a database that is far from being rational! I need to query the tables to get a hierarchy resultset. Here is the structure:levelOne levelOne_A levelTwo levelThree levelFourmy problem is that table levelOne_A and table levelTwo have data that are considered to fall underneath levelOne. However the data is far different from each other so I can not able merge both into one.so the it should look like this:levelOne levelOne_A AND levelTwo levelThree levelFourAny ideas how I can get my hierarchy structure?Thanks for your help!
View Replies !
How To Create A Custom SQL Statement.
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View Replies !
Create A Uniqueidentifier In SQL Statement
Hello, I am trying to autogenerate a new guid in my sql statement. Here is a sample of my code:string comments = "CREATE TABLE comments (domaininfoid UNIQUEIDENTIFIER PRIMARY KEY NOT NULL," + "comment TEXT NOT NULL)"; How can I make sql auto generate a guid?
View Replies !
Create Database Statement
exec ("create database " +@dbName) how do you incorporate the above statement with on ( NAME = @dbName, FILENAME = 'c:mssql7datacentral_Data.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH =5)
View Replies !
Name In CREATE USER Statement?
Hi. I was wondering if I could have a query like this: CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo] The combination of firstname and lastname does not seem to work. I have been using: sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname' but i cannot specify DEFAULT_SCHEMA with that sp. Any suggestions?
View Replies !
Generating Create Statement
Hi, I am new to SQL Server and need your help. I have a table (old_table) with about 500 columns and each column of type varchar. I have to generate a table (new_table) which has all the columns of the old table but with different data types. Is there a way I can use the syscolumns to generate a “create table� statement? Thanks.
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View Replies !
|
TRACKER
