Archive for September, 2007

Sep 27 2007

How to write a CURSOR loop over multiple databases in MSSQL 2000

Posted by: Rahmansaher in Technical, tags: , , , ,

I sure lot of people might want to use the script where they would want to loop over multiple databases sitting on a Single server. In my case, I had to check for  a Login Record  in all the databases since the person left the company and we wanted to disable his/her access. SO I wrote simple CURSOR script in MSSQL 2000. Hope this help to someone.  

PLAIN TEXT
SQL:

  1. USE master

  2.  

  3. DECLARE db_names CURSOR FOR

  4. SELECT name FROM sysdatabases

  5. —Where Clause, Enable if you want to filter out some databases.

  6. —where name like ‘databaseIwantName_%’ and name not like ‘databaseIdontwantName’

  7.  

  8. DECLARE @db_name varchar(100)

  9. DECLARE @QueryString NVARCHAR(500)

  10. DECLARE @UpdateString NVARCHAR(500)

  11.  

  12. OPEN db_names

  13. FETCH NEXT FROM db_names INTO

  14. @db_name

  15. WHILE @@FETCH_STATUS = 0

  16. BEGIN

  17. —PRINT @db_nam

  18. —In My case I used tbl_logins to do multiple select .

  19. SET @QueryString = ‘Select * from ‘ + @db_name + ‘..logins where login like ‘‘%loginname%’’ ‘

  20. EXEC sp_executesql @QueryString

  21.  

  22. IF @@RowCount> 0

  23. BEGIN

  24. —This gives the database name if there are any record found with the select state ran above

  25. PRINT @db_name

  26. END

  27.  

  28. —Similarly you can also have any Insert/Update Query IN a Loop.See example below

  29. —In My case I used tbl_logins to do multiple Update At Once.

  30. —SET @UpdateString = ‘update ’ + @db_name + ‘..tbllogins set password = ‘’account_blocked_out’‘, security_level = 0, approved = 0 where login like ‘’%loginname%’‘’

  31. —PRINT @UpdateString

  32. —EXEC sp_executesql @UpdateString

  33.  

  34. FETCH NEXT FROM db_names INTO

  35. @db_name

  36. END

  37.  

  38. CLOSE db_names

  39. DEALLOCATE db_names


 This is something written quick. Here is the Code for your Download. I am sure you can tune it better and if you do, please let me know . I would love to see your Ideas.

 Thanks

Sphere: Related Content

Comments No Comments »

Sep 14 2007

Troubleshooting Random null null Error in Coldfusion MX

Posted by: Rahmansaher in Technical, tags:

I have been looking so much for this error in Coldfusion MX  but could not find any solution to my problem. null null is a very vague exception error which gets thrown once in a while. Below are the steps I did to troubleshoot and one of them did the solved the problem.

 1) Apply this hot fix from Adobe.

2) Increase the JVM Heap Size. (I increased it  from 512 to 800)

3) Make sure your SELECT query does not pass any corrupt data. It might also be the very cause to  throw the exception error.Turning off query caching signifigantly increased the number of errors, so it seems to be something in the JDBC drivers.

4) Check to see if you have methods that accept parameters passed by reference(like getimagesize()) . Make sure you use right script language for example getimagesize uses VBSCRIPT not JSCRIPT. ( For me this was the case. I removed this method and it stopped the error completely).

Sphere: Related Content

Comments 1 Comment »