Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.


It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')


Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6

OPENROWSET – The object has no columns or the current user does not have permissions on that object

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.


The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')

More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.

T-SQL: How to generate a set of dates (time dimension) in a View itself?

Today I wanted to add all Saturdays and Sundays to our so called NonBusinessDays-table.
An easy join with a Time dimension/table would be fairly simple, but it also creates a dependency between the View and the Table itself. I didn’t want that, what I wanted to accomplish is to generate that mini Time dimension in the View itself (so without any other dependencies except for the actual so called NonBusinessDays-table).

So imagine something like:

SELECT * FROM dbo.tbl_NonBusinessDays
UNION
(
SELECT 'all Saturdays and Sundays between 2010 and 2020'
)

Normally a simple query like this would do the trick (inspired by this thread):

WITH AllWeekendDates(WeekendDate)
AS
(
SELECT CAST('2010-01-01 00:00:00.000' AS DATETIME) --From the year 2010
UNION ALL
SELECT DATEADD(DAY,1,WeekendDate) FROM AllWeekendDates
WHERE DATEPART(YEAR,DATEADD(DAY,1,WeekendDate)) < 2020 --Until the year 2020
)

SELECT
WeekendDate
,DATENAME(WEEKDAY,WeekendDate) AS DayName
FROM AllWeekendDates
WHERE DATENAME(WEEKDAY,WeekendDate) IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)


The results:

But! You’re not allowed to use the OPTION-statement in a View 🙂

OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions

An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View:
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/

Like this:

SELECT [WeekendDate]
FROM [dbo].[vw_WeekendDates]
OPTION (MAXRECURSION 0)


A trick that I found while Googling is to use numbers (like for example the surrogate key of a large table or by using ROW_NUMBER()):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139267 (bottom post)

SELECT DATEADD(d,number,'9/11/2009')AS Date
FROM master..spt_values
WHERE number BETWEEN 1 AND
DATEDIFF(d,'9/11/2009',GETDATE())
AND type = 'P'


Now, let’s combine the original query (that included the OPTION (MAXRECURSION 0) statement) with this fancy trick.
The ideal query to use in a View will be this one:


SELECT DATEADD(d, [Row],'2010-01-01') AS Date --From the year 2010
FROM (
SELECT TOP 10000 [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC)
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
) d
WHERE DATENAME(WEEKDAY, DATEADD(d, [Row],'2010-01-01')) IN ('Saturday','Sunday')
AND DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(d, [Row],'2010-01-01'))) < 2020 --Until the year 2020


The results:

How to determine the maximum length / value for each column in your database?

You might want to use this query (found it here) and made a few modifications. It’s an older query, but this may point you in the right direction.

DECLARE @Results table
(
ID uniqueidentifier,
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
NumericPrecision varchar(250),
DatetimePrecision varchar(250),
IsNullable varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @Results
SELECT
NEWID(),
t.TABLE_SCHEMA,
t.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE
WHEN DATA_TYPE NOT IN ('nvarchar','varchar') THEN 'N/A'
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
END,
NUMERIC_PRECISION,
DATETIME_PRECISION,
IS_NULLABLE,
'NA',
'SELECT Max(Len(' + COLUMN_NAME + ')) FROM ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
DECLARE @receiver table(theCount int)

DECLARE length_cursor CURSOR
FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)

UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE ID = @id

DELETE FROM @receiver

FETCH NEXT FROM length_cursor
INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
NumericPrecision,
DatetimePrecision,
IsNullable,
Longest
FROM
@Results

How to create a Table based on a View with the same column names and data types?

I found myself manually creating tables with identical column names and data types based on view-definitions…which felt a bit dump. As always, Google is my best friend. Found out that it is actually quite simple, by using the INTO clause. Of course I’ve used it before to store data in Temp-tables (#), but never thought about the possibility to use it for normal tables. 🙂

Let say you’ve created a view like this one:

CREATE VIEW dbo.vw_Resultset
AS
(
SELECT
CAST(1 AS INT) AS RecordId
,CAST(123 AS DECIMAL(9,2)) AS Amount
,CAST('NormalText' AS VARCHAR(20)) AS NormalText
,CAST(N'UnicodeText' AS NVARCHAR(50)) AS UnicodeText
,CAST(GETDATE() AS DATETIME) AS CurrentDateTime
,CAST(GETDATE() AS DATETIMEOFFSET) AS CurrentDateTimeOffset
)


How it looks in the physical world:

Ok, it’s time to create a new table with an identical structure (column names and data types):

SELECT *
INTO dbo.tbl_Resultset
FROM dbo.vw_Resultset


The result:

Drop a comment if you haven’t thought about this option before, so I don’t feel alone! Feeling dump right now…I’ll now focus on a few, more advanced, problem solving / designing work 😀

How To: Generate a CSV file that only contains deltas without SSIS?

Hi there, I wrote this particular post for SQL Server Express users that would like to export data once in a while. If you’re running a Standard or Enterprise Edition of SQL Server, I’ll advise you to use SSIS to export data.

Want to export deltas (for BI/DWH purposes with SCD) to CSV files in SQL Server without using SSIS? You might want to check out the SQL code which uses xp_cmdshell .

Important note: Do not use this solution (with xp_cmdshell) on Production environments or other web/internet exposed SQL Server Instances. Xp_cmdshell poses a major security risk, unless it’s setup in a correct matter: How do you secure the SQL Server instance after enabling xp_cmdshell?

Here’s an example of such a file:

The script (downloadable below) creates several database objects:

Stored Procedures:
[dbo].[usp_GenerateDeltas_ExampleDataset]
This SP returns the example dataset.

[dbo].[usp_GenerateDeltas_ExampleDataset_Check]
This SP is generates an example dataset (TOP 1) to validate the code and verify the existince of all SQL Server Database Objects.

[dbo].[usp_GenerateDeltas_ExportToCSV]
This SP actually exports the dataset to a local CSV file (in C:\Temp in this example).

Scalar-valued Functions:
[dbo].[fn_DateTimeOffsetToDateId]
This SvF converts a DATETIMEOFFSET-value into a DATE-value, but in a different format like ‘99991231’ for 9999-12-31.

[dbo].[fn_DateTimeOffsetToTimeId]
This SvF converts a DATETIMEOFFSET-value into a TIME-value, but in a different format like ‘123031’ for 12:30:31.

Make sure that xp_cmdshell is enabled:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE


Download the T-SQL code here:
Export-deltas-to-CSV-file-SQL (Word document – *.docx)

Have fun! 🙂

How to find a column in database (SQL Server)

Want to know where that specific column is located in your database?
Or just want to figure out where that data is stored?

You’re able to locate a specific column by using this query:

SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%keyword%'
ORDER BY SchemaName, ColumnName


Since I didn’t want to type it again and again…I made a Stored Procedure (SP) for it:

CREATE PROCEDURE [dbo].[usp_FindColumnInAllTables]
(
@KeyWord NVARCHAR(200)
)
AS

BEGIN

SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@KeyWord+'%'
ORDER BY SchemaName, ColumnName

END


SSIS – No status is available (NonColumnSpecificError)

In case your SSIS package fails to insert new records into a destination table and you receive an error message like:

No status is available.
[NonColumnSpecificError]

In my case, the Error Description (custom) component returned this message and wrote it to my errors-table:

More info about this component can be found here.

It’s probably a Foreign Key (FK) constraint, you might want to check those.

Rebuild or Reorganize ALL Indexes in a Database dynamically

I’ve found two scripts on how to rebuild or reorganize all indexes in a specific database:
http://geekswithblogs.net/robz/archive/2009/06/09/sql-server-2005-sql-server-2008—rebuild-or-reorganize.aspx
and
http://stackoverflow.com/questions/8326595/how-to-alter-index-for-every-table-in-my-database

But, the thing is that I also wanted a check whether the fragmentation index is higher or lower than 30% (and decide to reorganize or rebuild an index).
So I’ve hustled the script a bit and added a few lines of code and here’s how to execute/use it:


USE []

EXEC [dbo].[usp_Maintenance_RebuildAndReorganizeIndexes] @PreferredSchemaName = 'dbo' --optional input parameter
GO




Click here to download the script (*.docx):
usp_Maintenance_RebuildAndReorganizeIndexes

 

SQL Agent – Disable All Jobs (script)

Another addition to your cookbook, comes in very handy during releases!

The scripts below are easier, but if you want to avoid updating system tables directly…please scroll down.

————————————————–

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Enable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE [Name] LIKE 'Admin%';
GO

————————————————–

Disable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 0;
GO

Enable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 1;
GO

Disable All ENABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 1 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs


Enable All DISABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs