Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nate
Starting Member
17 Posts |
 Posted - 2002-03-14 : 12:55:00
|
| I am creating an archive program for a SQL 2000 database. In it I am creating cursors in a stored procedure for both the old and the new database. I am trying to dynamically pass in the archive database's name because this will be used at numerous clients who are using this database. When I try to dynamically create a cursor with the variable that I have passed into the stored procedure it gives me an error.I have tried this:Select @SQL = 'CURSOR SCROLL DYNAMIC FOR Select * From 'Select @SQL = @SQL + @DestinationDatabaseSelect @SQL = @SQL + '.t_InvoiceType'Set @ArchiveCursor = @SQLand Select @SQL = 'Select * from 'Select @SQL = @SQL + @DestinationDatabaseSelect @SQL = @SQL + '.t_InvoiceType'Set @ArchiveCursor = CURSOR SCROLL DYNAMIC FOR @SQLThe error message is: 'Error 206: Operand Type Clash: Varchar is incompatible with cursor'There doesn't seem to be any built-in functions to set which database the cursor is being created from in a dynamic way.Can anyone help me?Thanks. |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-03-14 : 13:05:29
|
| why even use a cursor in the first place? maybe instead of creating a cursor create temporay table with the database name, then load the cursor from the tempory table if you absolutely have to. WARNING: incoming rant on cursors Cursors are ment for row based processing, however, almost all processing can be done in a set based language such as SQL. Don't believe me? I am not admin of a db that processes a decision tree using cursors to generate APPROVAL LISTS. I did not create this, I have created the exact same thing except not using cursors and doing set-based modifications. The set-based performs much faster than the cursor based solution, cursor: ~5sec, set-based: ~.250ms. Get off your lazy ass and learn the full power of T-SQL ... cursors are good for annoying people at a movie... |
 |
|
|
Jay99
468 Posts |
Posted - 2002-03-14 : 13:52:17
|
| WARNING: incoming rant on double postsOK, I'm not sure if you know this or not, but this place isn't staffed with a paid 24-hour quick response team. You gave just over 30 minutes before you reposted the same question. Lower your expectations, son.PS. Cursors suck.Jay |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-03-14 : 15:53:41
|
| damn, forgot to give him that one too ... :-p thanks jay |
|
|
|
Nate
Starting Member
17 Posts |
Posted - 2002-03-14 : 16:13:36
|
| Thanks guys. Was just wondering whether it was worth posting a question to this site or not. You've answered it! |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-14 : 17:22:34
|
| ummmm, 1000s of people that have been helped out seem to think so. Pull your head out of your ass and stop being so impatient and ungrateful and you might learn something.Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-14 : 18:43:34
|
| Just one of those threads I guess. They seem to be popping up everywhere....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
2nd Post -Dynamically Created Cursor -anyone??