DBReindexing script runs through QA but fails through SQL Job

An interesting and new thing noticed with a script. A normal DB re-indexing script which has cursor for collecting DB names and another dynamic cursor for collecting table names and then performing ‘Alter Rebuild Index ALL’ on all tables of databases. Here the issue noticed is the script runs fine without any issue in query analyzer(QA) and rebuilds all indexes. BUT when scheduled through a job the same script fails with below error:

“A cursor with the name 'TableCursor' does not exist. [SQLSTATE 34000] (Error 16916).  The step failed.”

I verified weather any table/schema name conflicting with system functions but that’s not the issue. Thinking it’s a permission issue verified the owner of the job and made sure it’s ‘sa’,  but still job fails. When verified the code ‘TableCursor’ is the cursor I am trying to create dynamically to fetch table names. If it’s syntax error it should not run through QA as well.

But later on one of my colleague suggested weather the issue with CURSOR scope? And when verified the answer is YES. The scope of the cursor is the issue, so verified the DB option ‘Default CURSOR’ was set to ‘Local’ and once after changing this option to ‘Global’ this script started running fine through SQL job.

How to query data from 2 or more servers at a time without Linked Servers:

We can use Central Management Server(CMS) in SQL Server to achieve this.

Open SSMS and click on View and then select ‘Registered Servers’ as shown in below capture

Now it will connect to DB Engine and we can connect to Integration/Reporting/Analysis/Compact engines by clicking on the icons directly

Expand DB Engine and will notice 2 sections Local Groups or Central Management Server. We can use any of these options to connect servers. Now I am using CMS. By right clicking on CMS we need to select ‘Register CMS’ see below capture

Then a new window will where we need to select a CM server. After selecting a server save it.

Now I will create a group named ‘Test’ under this registered server as shown in below.

After creating Test group I will add 2 servers to this group. We can by right clicking on Test group we created as shown in below capture and select the ‘New Server Registration’.

I registered 2 servers one is MSSQLSERVERNEW and other is SQLEXPRESS servers under Test group.

Now by clicking on the Test group and select ‘New Query’ option a new query window will open and if I run any query it will fetch data from both servers that are registered under the Test group.

For example I will run “SELECT name FROM sysdatabases;”. If you look at the output it has databases names of both the servers as shown in below capture.

In the same way we can connect any number of servers and retrieve data from them.

Thanks

How to create tables in a schema other than dbo schema by default

First create Login if you don’t have already:

CREATE

LOGIN [best] WITH PASSWORD=N'best', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

then create user named ‘best’ with below command in the database you need. Login gives access only to server if you need to access a database you need to have that user in the DB

"CREATE

USER [best] FOR LOGIN [best] WITH DEFAULT_SCHEMA=[Test]"

make sure you give permission to this user 'best' to create tables.

Now login to server as user ‘best’ and create a table named ‘Test’ in the DB. And you will notice the table is created in Test schema by default.

Thanks