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 Complete Forum Thread with Replies
Related Forum Messages:
[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 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 !
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 !
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
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 Replies !
Join Cursor With Table Outside Of Cursor
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 Replies !
SQL Server Cursor Problem
Hi. I have a cursor in my SQL Server 2000 usp that doesn't seem to be working. It returns 0's for all counts within the cursor. I assigned a value to one of the variables after the cursor and the assigned value came back to my asp.net code. This leads me to believe the cursor variables lose their value once the cursor is done. What am I doing wrong? -------------------------------------- DECLARE @numFPrintNA int DECLARE @txtPrintSuit varchar(1) DECLARE curs_Count CURSOR FOR SELECT numFPrintNA, txtPrintSuit FROM tblRecords WHERE txtLocationCode = @txtLocationCode AND (dtmOffDate >= @dtmFrom AND dtmOffDate <= @dtmTo) OPEN curs_Count FETCH curs_Count INTO @numFPrintNA, @txtPrintSuit IF @@FETCH_STATUS = 0 BEGIN IF @numFPrintNA = 0 SELECT @numNACount = @numNACount + 1 ELSE SELECT @numAppCount = @numAppCount + 1 IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1 IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1 IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1 FETCH curs_Count INTO @numFPrintNA, @txtPrintSuit END GO ---------------------------------------
View Replies !
Server Side Cursor
In Access 2002 (project) I have used the following code in order touse a server side cursor,Set cnnClientFrm = CurrentProject.ConnectioncnnClientFrm.CursorLocation = adUseServerDo you need to configure anything on the SQL server side to make thiswork or make it work more efficently?The reason I ask is that I just have tried switching this betweenadUseServer and adUseClients on a connection that then is used to loada particularly large recordset and it doesn't seem to make anyperformance difference that I can determine.Thanks,Bob.NB: I posted this originally in the Access group but got no replied soI am reposting here.
View Replies !
Server Side Cursor
How do you configure the server side query? What is the connection string lke? What are the advantages of using the serevr side query? When is it best to use it and why? Please explain so that l understand it.
View Replies !
SQL Server 2K Cursor Management And ADO
I was reading in a SQL Server 2000 book about Cursors in T-SQL. It says that there are 4 types of cursors with the Static being the most expensive in resources and the firehose (forward-only) the most efficient for read-only/forward-only access. However, it says that in T-SQL, when a firehose cursor is created, but the table has triggers, SQL Server 2K will convert automatically the cursor to a static cursor. My question is in regards to ADO. ADO uses API cursors rather than T-SQL cursors, but I was wondering if the same remains true. If I create a Server-Side/forward-only cursor in ADO (similar to the firehose in T-SQL) and my table has triggers, will SQL Server 2K create a static cursor internally, thus eliminating the gains the ADO developer (VB or C++) thinks is gaining? Any feedback is welcomed. Thanks.
View Replies !
Cursor To Linked Server?
Cursor to linked server: ----------------------------------------------- Declare Cursor_Loop_serverName Cursor for select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID, cast(crdate as varchar(25)) crdate from ServerName_A.master.dbo.sysdatabases ***How could I pass @serverName to change the from to from @RemoteServer.master.dbo.sysdatabases? I have tried dynamic sql, it did not work after the Declare Cursor for... ERROR: Server: Msg 154, Level 15, State 3, Line 48 variable assignment is not allowed in a cursor declaration. thanks for the help David
View Replies !
Server Side Cursor
Hi all, I am running a SQL query which has joins on 2 tables, and performs SORT. The Server side cursor is enabled when this query is submitted through my java application. The number of records that I am handling here is around 60 million on the main table. This query works perfectly well, when i sort on a non-indexed column, but if I try to sort on an indexed column the query runs indefinitely, and never stops. This same query runs good if I disable the Server side cursor. And works good even when the data size is less with server side cursor enabled. I am unable to figure out where the problem could be. Can anyone help me with this. Following is my server config : DataBase : SQLServer 2000 Standard Edition. OS : Win 2000 Server Disk Space : C(boot) - 4GB , D(Data) - 200GB, F(tempdb) - 40GB, E(trxn) - 40GB. Database Size - 72GB on D, rest of the space is unused. RAM size - 4GB OS Swapping space - 8GB Processors - 4 x 702 Mhz. Looking forward to your response. Thanks, Vijay.
View Replies !
Why CURSOR In Sql Server Is Inefficient ?
Hi ALL, I heard and reffered many sites to know abot cursor in sql server. I could not clear why exactly the cursor is ineffecient to use. I came to know that cursor needs more resources.BUT can anyone tell me wat type of resources it needs and why is it exactly ineffiecient? keval300@googlepages.com
View Replies !
Sql Server 2005 Cursor
I am not fimilar with cursors at all but need some assistance/direction on how to re-create the following. The result set will need to give me a list of new customers with sign dates of last year and this current year plus their sales for each year. If the customer has a date signed of 2007 I need to see the "year of sales" for both 2007 and 2008, whether or not they actually had sales. I'm needing to convert an oracle cursor to Sql Server 2005. If at all possible I would like to stay away from the cursor and create this some other way. Any help would be greatly appreciated. Customer Date Signed Year of Sale Amount 1111 7/1/07 2007 $50,000.0 2008 0.0 Below is the oracle cursor that was used to calculate this information in the past. DECLARE MBLDRN BAV_BUILDER_NEW.BLDRN%TYPE; MYR_SIGNED BAV_BUILDER_NEW.YEAR%TYPE; MCOMPANY BAV_BUILDER_NEW.COMPANY%TYPE; MSHORTNAME BAV_BUILDER_NEW.SHORTNAME%TYPE; MDSIGNED BAV_BUILDER_NEW.DSIGNED%TYPE; MDCANCELED BAV_BUILDER_NEW.DCANCELED%TYPE; MDMTERR BAV_BUILDER_NEW.DMTERR%TYPE; MDMNAME BAV_BUILDER_NEW.DMNAME%TYPE; MENGR_TEAM BAV_BUILDER_NEW.ENGR_TEAM%TYPE; MSALESREGON BAV_BUILDER_NEW.SALESREGON%TYPE; MDIVCODE BAV_BUILDER_NEW.DIVCODE%TYPE; MYRLOOP NUMBER; MLSTYR NUMBER; MSYEAR DATE; MEYEAR DATE; CURSOR CA IS SELECT YEAR,BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED, DMTERR,DMNAME,ENGR_TEAM,SALESREGON,DIVCODE FROM BAV_BUILDER_NEW ORDER BY YEAR; BEGIN SELECT YEAR INTO MLSTYR FROM CURRENT_DATES; OPEN CA; LOOP FETCH CA INTO MYR_SIGNED,MBLDRN,MCOMPANY,MSHORTNAME,MDSIGNED,MDCANCELED, MDMTERR,MDMNAME,MENGR_TEAM,MSALESREGON,MDIVCODE; EXIT WHEN CA%NOTFOUND; MYRLOOP := MYR_SIGNED; WHILE MYRLOOP <= MLSTYR LOOP SELECT MIN(SYEAR) INTO MSYEAR FROM UDBDATES WHERE YEAR=MYRLOOP; SELECT MAX(EYEAR) INTO MEYEAR FROM UDBDATES WHERE YEAR=MYRLOOP; BEGIN INSERT INTO BAV_BUILDER_NEW_DATA (YR_SIGNED,DIVCODE,SALESREGON, BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,ENGR_TEAM, DMTERR,DMNAME,YR_SALES) VALUES (MYR_SIGNED,MDIVCODE,MSALESREGON,MBLDRN,MCOMPANY,MSHORTNAME, MDSIGNED,MDCANCELED,MENGR_TEAM,MDMTERR, MDMNAME,MYRLOOP); END; MYRLOOP := MYRLOOP + 1; END LOOP; COMMIT WORK; END LOOP; COMMIT WORK; CLOSE CA; END; / COMMIT WORK;
View Replies !
Sql Server Paging Script And Cursor?
suppose from my code behind i will pass my sql query to sql server store procedure and i want that scriptwill be written in such a way that my store procedure will execute my query and populate cursor and then cursor will be return from my store procedure to code behind.so i want to know is it possible in sql server if so pls give me a sample sql server store procedure code.
View Replies !
Server Side Block Cursor
Forgive me if this is a stupid question. How do you use a Server Side block cursor? Lets say I have a db of 250,000 items and I want to retrieve the data 100 rows at a time. How would I do this (using a block cursor, not in general) thanks,
View Replies !
Error 1429: A Server Cursor Cannot Be Opened...
Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too).. We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed. This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute. Once in a while we will get a return from SQL .. "OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..." The OLE error code is 1429. An OLE Exception code 3604 is also returned. When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well. Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed. Here's the really fun part... Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME. Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console. Help?? Bill Kuhn - MCSE The Kuhn Group, Inc. http://www.kuhngroup.com
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 !
SQL Server Cursor Names - Missing From Dm_exec_cursors? API_CURSOR0000000000000003435 ?
Hi, We have an application that uses a large number of cursors, and I can run sys.dm_exec_cursors (0) to view them. However, the 'name' column always appears to be NULL no matter when I run the query. I see, when tracking some blocked processes, that the blocked processes usually have in inputbuffer that looks something like FETCH API_CURSOR00000000000003435 - which I'm trying to map back to the cursor itself. However, I can't seem to do so based on the name - since it isn't in the DMV. Can anyone shed any light on this one? Thanks in advance, Chris Camadella chris@vroominc.com
View Replies !
Mdac 2.8 And Sql 2005 Server Side Cursor Performace Issue
Hello I have a VB6 application using classic ado (MDAC 2.8) for connecting ms sql 2000 server. Application uses a lot of server side cursors. Now I want to switch to ms sql 2005 server but I have noticed very serious performance problem. Sql profiler results of execution of following commands: declare @p1 int set @p1=180150131 declare @p3 int set @p3=1 declare @p4 int set @p4=16388 declare @p5 int set @p5=22221 exec sp_cursoropen @p1 output,N' Select ... from ... where .... order by ...',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 on sql server 2000: CPU: 234 Reads: 82515 Writes: 136 Duration: 296 and on sql server 2005: CPU: 4703 Reads: 678751 Writes: 1 Duration: 4867 Both databases are identical, the servers runs on the same machine (Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums I've read that Microsoft doesn't recommend using server side cursors on sql 2005 but is there any way to increase performance to some acceptable level? thanks in advance szymon strus
View Replies !
Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors declare Q cursor for select systudentid from satrans declare @id int open Q fetch next from Q into @id while @@fetch_status = 0 begin declare c cursor for Select b.ssn, SaTrans.SyStudentID, satrans.date, satrans.type, SaTrans.SyCampusID, Amount = Case SaTrans.Type When 'P' Then SaTrans.Amount * -1 When 'C' Then SaTrans.Amount * -1 Else SaTrans.Amount END From SaTrans , systudent b where satrans.systudentid = b.systudentid and satrans.systudentid = @id declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money set @arbalance = 0 open c fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount while @@fetch_status = 0 begin set @arbalance = @arbalance + @amount set @before = @arbalance -@amount insert c2000_utility1..tempbalhistory1 select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance where( convert (int,@amount) <= -50 or @amount * -1 > @before * .02) and @type = 'P' fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount end close c deallocate c fetch next from Q into @id end close Q deallocate Q select * from c2000_utility1..tempbalhistory1 truncate table c2000_utility1..tempbalhistory1
View Replies !
SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this. Below is the exception from my log file The cursor type/concurrency combination is not supported. com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source) The following is the piece of code where the problem I'm assuming is happening, how can I correct it. varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); Have tried using both JDC v1.1 and 1.2 but of no use.
View Replies !
Convert Mysql Create Script To SQL Server Create Script
Hi guys Just a quick one. I have some create scripts that were created for mysql and i am wanting to convert them to a SQL Server create script. I am just wondering if anyone knows of any utilities or codeplex projects, etc that can perform the convert process. The script is fairly basic but it is not fully compatible but i have a lot of them thus why i want to automate the process. The majority of the script is just table create statements like the following: Code Snippet -- ----------------------------------------------------- -- Table `dbo`.`Staff` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dbo`.`Staff` ( `StaffId` INT NOT NULL AUTO_INCREMENT , `IndividualId` INT NOT NULL DEFAULT 0 , `PositionId` INT NOT NULL DEFAULT 0 , `EmploymentStartDate` DATETIME NULL , `EmploymentEndDate` DATETIME NULL , `SupervisorStaffId` INT NULL , `TerminatedEmploymentReasonId` INT NULL , `DeletedFl` BIT NOT NULL , `CreateDateTime` DATETIME NOT NULL , `CreateUserId` INT NOT NULL , `ChangeUserId` INT NOT NULL , `ChangeDateTime` DATETIME NOT NULL , `VersionNum` INT NOT NULL , PRIMARY KEY (`StaffId`) , INDEX Individual_Staff_IndividualId_Ref (`IndividualId` ASC) , INDEX Lookup_Staff_PositionId (`PositionId` ASC) , INDEX Staff_Staff_SupervisorStaffId_Ref (`SupervisorStaffId` ASC) , INDEX Lookup_Staff_TerminatedEmploymentReasonId (`TerminatedEmploymentReasonId` ASC) , CONSTRAINT `Individual_Staff_IndividualId_Ref` FOREIGN KEY (`IndividualId` ) REFERENCES `dbo`.`Individual` (`IndividualId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_PositionId` FOREIGN KEY (`PositionId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Staff_Staff_SupervisorStaffId_Ref` FOREIGN KEY (`SupervisorStaffId` ) REFERENCES `dbo`.`Staff` (`StaffId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_TerminatedEmploymentReasonId` FOREIGN KEY (`TerminatedEmploymentReasonId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION); Thanks for the help Anthony Note: i know if you have a mysql database you can go from one to the other, but i specifically need to take these mysql create scripts and convert them to sql server create scripts and there are a LOT of them i.e. more than 1000 script files that each contains a couple of hundred table create statements.
View Replies !
Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
View Replies !
&"Cursor-Fetch&" Problem:Oracle2SQL Server Migration
Dear all, I have a procedure in Oracle that contains the following cursor: CURSOR SCHED_TRIPS IS SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID FROM Dbo.SCHEDTRIPS_VIEW WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE) AND DISPOSITION <> 'V'; BEGIN FOR S IN SCH_TRIPS LOOP UPDATE dbo.SCHEDULES T SET T.DIRTYBIT = 1 WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA); UPDATE dbo.SCHEDULES T SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE; END LOOP; COMMIT ; END; My problem is with the line shown in Red. What will be the T-SQL equivalent for this line. Anxiously waiting for help!
View Replies !
Do I Need To Create A Domain To Get Connectivity To A Report Server Database On A Remote SQL Server Instance?
I'm setting up a simple SSRS implementation for a non-profit organization, using two servers hosted at a data center. The first server has SQL Server Standard Edition and Reporting Services installed. I've designed and deployed a number of useful reports on this server. I was hoping to isolate this first server by installing IIS and SSRS on the second server, have users browse from the Internet to that second server (over SSL, of course), and have all reports served up from databases (and, presumably, the report server database) on the first server. During the installation of SSRS on the second server, however, I'm being prompted to specify the service account. According to the help text: "Reporting Services. Service accounts are used to configure a report server database connection. Choose a domain user account if you want to connect to a report server database on a remote SQL Server instance. If you are using a local report server database, you can use a domain user account or Local System to run the service." I believe I want to configure SSRS to connect to a report server database on a remote SQL Server instance; therefore, it appears that I need to enter a domain user account. The only problem is, neither server belongs to a domain; they are members of a simple two-server workgroup. Does SSRS, configured to connect to a report server database on a remote SQL Server instance, require a domain? Does what I'm hoping to accomplish require a domain? Creating a two-server domain seems like overkill for this implementation, doesn't it? I appreciate any comments and suggestions. Thanks!
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 !
Create Temp Table On Linked Server From Local Server
Hi, I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large. If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that. I appreciate any advice / guidance anyone can offer me!
View Replies !
Converting Oracle Cursor Syntax Into Sql Server Syntax
declare -- Test statements here /* ------------------------------------------------------------*/ /* Procedure to update Person / Org without either a card issue date (ATTRIBUTE9) a card expiry date (ATTRIBUTE11) The new card issue date would be set to SYSDATE The new card expiry date would be set to SYSDATE + 12 months or SYSDATE + 6 months depends on customer's incentive Level. Selection C - Not Archived / either Attribute9 or/and Attribute11 IS NULL Author Version Date Description ------------------------------------------------------------ Emer Ryan (Detica) 0.1 19Mar2004 Initial Version */ /* ------------------------------------------------------------*/ CURSOR cur_BLANK IS SELECT P.PARTY_ID, ca.account_number, p.attribute2, ---Incentive Level p.attribute9, ---card Issue date p.attribute11, ---card renewal date p.attribute7, p.attribute15, ---Internal Status p.attribute6, ---Card Status p.last_updated_by, P.LAST_UPDATE_DATE from hz_cust_accounts ca, hz_parties p where ca.party_id = p.party_id and (p.attribute15 <> 'ARC' or p.attribute15 is null) and ca.account_number is not null and p.attribute2 IN ('Upper Tier', 'Lower Tier', 'Inactive') and (p.attribute9 is null OR p.attribute11 is null); row_count number := 0; total_rec number := 0; begin -- OUPTUT THE START TIME DBMS_OUTPUT.PUT_LINE('Running - UPDATE_BLANK card issue/ card renewal date'); DBMS_OUTPUT.PUT_LINE('-- Started at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')); /*enforced cursor to check if customers card issue date and card expiry date is blank when customers incentive level is Inactive*/ for i in cur_blank loop /* IF 1 - Inactive Customers */ if i.attribute2 = 'Inactive' then if i.attribute9 is null and i.attribute11 is null then /* Update both Card Issue and Card Expiry Dates */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), attribute11 = to_char(add_months(sysdate, 6), 'DDMMYYYY'), last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is null and i.attribute11 is not null then /* Update Card Issue Date */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is not null and i.attribute11 is null then /* Update Card Expiry Dates */ update ar.hz_parties set attribute11 = to_char(add_months(sysdate,6), 'DDMMYYYY'), Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; end if; /* IF 1 - Lower Customers */ IF i.attribute2 = 'Lower Tier' then if i.attribute9 is null and i.attribute11 is null then /* Update Card Status, Card Issue and Card Expiry Dates */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), attribute11 = to_char(add_months(sysdate,12), 'DDMMYYYY'), attribute6 = 'RLL', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is null and i.attribute11 is not null then /* Update Card Issue Date */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), attribute6 = 'RLL', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is not null and i.attribute11 is null then /* Update Card Statu and Card Expiry Date */ update ar.hz_parties set attribute11 = to_char(add_months(sysdate,12), 'DDMMYYYY'), attribute6 = 'RLL', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; end if; /* IF 1 - Upper Customers */ if i.attribute2 = 'Upper Tier' then if i.attribute9 is null and i.attribute11 is null then /* Update Card Status, Card Issue and Card Expiry Dates */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), attribute11 = to_char(add_months(sysdate, 12), 'DDMMYYYY'), attribute6 = 'RUU', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is null and i.attribute11 is not null then /* Update Card Issue Date */ update ar.hz_parties set attribute9 = to_char(sysdate, 'DDMMYYYY'), attribute6 = 'RUU', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; end if; if i.attribute9 is not null and i.attribute11 is null then update ar.hz_parties set attribute11 = to_char(add_months(sysdate,12), 'DDMMYYYY'), attribute6 = 'RUU', Last_updated_by = '100000001', LAST_UPDATE_DATE = sysdate where PARTY_ID = i.party_id; row_count := row_count + 1; total_rec := total_rec + 1; END IF; /* IF 2 - Commit loop */ if row_count = 1000 then commit; row_count := 0; end if; /* -- IF 2 - END */ end IF; /* IF 1 - END */ END LOOP; COMMIT; dbms_output.put_line('-- Total rec updated ' || total_rec); dbms_output.put_line('-- Ended at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')); -- Standard Exception Handling -- Handled by call to external procedure -- EXIT 0; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('ERROR - Data Patch Error ' || 'Code:' || SQLCODE || 'Error ' || SQLERRM); -- EXIT 1; end; / --exit '0' EXIT;
View Replies !
Create/Register A Server/ Server Group -- Trouble.
Hi, I am really new to SQL Server. I have trying to create/register a server. I came to know that I need to go to Query Analayser and then register a Server. Then in Enterprise Manager I need to register a Group. I did everything. But... Here is the error. Unable to connect server. Server: Msg 17, Level 16, State 1 [Microsoft] [ODBC SQL Server Driver] [Shared Memory] SQL Server does not exit or access denied. I really appreciate if u can give me steps. Thanks, Padma.
View Replies !
Create The Package On The Server, And Execute Through Sql Server Agent
Hi, Some of you guys seem to be gurus with the new Integration Services technology, so I hope someone can lend me some advice, as I haven't worked much with integration before. Ok, here we go. What I want to do is select some data from my database and export the result to a flat file (really a .csv file with values delimited by semicolons). Sounds like a simple thing to do? Well, I'm sure it is when you know how to do it :) I know I could manage the same thing by writing a C# class that creates that .csv file, but the decision has been made to use Integration Services for these kind of operations. I created an SSIS project in Business Intelligence Development Studio, and created a package (I defined the task flow etc.). By choosing "Execute package" from the IDE I managed to create the flat file, and everything seemed sweet. However, When trying to execute the package (package.Execute();) from C# code, it only results in a failure. I have read on several sites that this has to do with my program lacking the rights to run the package from the client side. OK, fair enough. I need to create the package on the server, and use an SQL Server Agent to execute the package through the agent. Can anyone tell me how I need to do this? How can I ensure that the package is created on the sql server instead of locally on my development computer? When I create a new SSIS project the package is already made, and it is created locally on my PC. I hope someone can give me some help. Even a little nudge would be appreciated ;) Thanks in advance! Greenies
View Replies !
CREATE TABLE Syntax, Combining CREATE, INSERT, SELECT With Character Placement
Working in Access Queries Access version 2003 MSSQL 2000 Using http://www.mckoi.com/database/SQLSyntax.html#2 and http://www.minisoft.com/pages/middleware/odbc32/pages/odbcae.htm for references I'm trying to just save the query and I keep getting a Quote: "Syntax error in CREATE TABLE statement". If I try to run/save the Select portion by itself, it's successful. If I try to run/save the Create Table portion by itself, it's successful. But I've got something wrong when I try to put them together w/ the Insert statement. Code: What I've got CREATE TABLE Month2 ( ID NUMBER, CustID NUMBER, Month TEXT, Year TEXT, Amount TEXT); INSERT INTO Month2 (col1, col2, col3, col4, col5); SELECT CustID, {fn RIGHT (CurApr, 3)}, {LEFT (CurApr, 3)}, CurApr FROM dbo_commenehisttrial; I'm trying to create a table then flow data in from a much larger table while converting the headers and data into respective columns. Also trying to convert the headers into two columns of meaningful data. I've got the header part more or less answered w/ Code: SELECT CustID , 'Apr 05' as [Date] , CurApr as Amount FROM dbo_commenehisttrial; union all The above statment is repeated 36 times for each of my 3 sets of 12 months. That works to create a "query table" but I wasn't sure how to run a second query off a first query results. I'd actually like to change the header "CurApr" into two fields: All "Cur" becomes 2005 and "Apr" is listed in the same fashion as the 2nd line of the last section of code. Something like.... Code: SELECT CustID , '2005' as [Year], , 'Apr' as [Month] , CurApr as Amount FROM dbo_commenehisttrial; union all repeating... I'm not sure exactly how to combine CREATE, INSERT, and SELECT, but as a simply display query that last bit works. So right now I'm getting hung up with the syntax error on CREATE TABLE. Thanks.
View Replies !
How To Create CE Db From Sql Server Db
I have a db in Sql Server 2005. Some of the tables will be part of a sql ce application running on a pda. My question is how to "convert" these tables into a CE db. The datatypes differ in CE from Sql Server for example. Then I want to run replication. Any ideas?
View Replies !
Create Script To Create/Refresh Identical Database
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns. I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for. Any help would be appreciated. Thanks!
View Replies !
|
TRACKER
