question about sp_generate_inserts (Vyas's SP)

Hello,
I compiled Vyas's SP sp_generate_inserts
(http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectly
on my test servers - all except one server. On this server, I get the
following errors
Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 332
Invalid length parameter passed to the substring function.
Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 333
Invalid length parameter passed to the substring function.
Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
37
No columns to select. There should at least be one column to generate the
output
(I compiled the SP onto the master database and am executing the SP as
EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
database where "MytableName" exists
The following statement seems to be returning a NULL (I modified the SP to
display @.Column_ID)
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
In fact, when I put the following SELECT
SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
in place of the original SELECT (above), I get rows with
TABLE_CATALOG='Master'
It almost looks like the SP is selecting from the Master database instead of
the user database that I have selected.
If I change the SELECT to hardcode the database name (as shown below), the
insert statements are generated correctly!
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
I have double checked to verify that I am running the EXEC in the correct
database (not the master database) and have also confirmed that the
sp_generate_inserts doesn't exist in any other database.
This SP works perfectly on two other servers that I have tried this on, so I
am baffled!
Any suggestions on how to debug this?
Thanks!Hi
This sounds like you need to call
EXEC sp_MS_marksystemobject sp_generate_inserts1
Have you checked
SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
John
"Bob" wrote:

> Hello,
> I compiled Vyas's SP sp_generate_inserts
> (http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectl
y
> on my test servers - all except one server. On this server, I get the
> following errors
> Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 3
32
> Invalid length parameter passed to the substring function.
> Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
33
> Invalid length parameter passed to the substring function.
> Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line
337
> No columns to select. There should at least be one column to generate the
> output
> (I compiled the SP onto the master database and am executing the SP as
> EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
> database where "MytableName" exists
> The following statement seems to be returning a NULL (I modified the SP to
> display @.Column_ID)
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> In fact, when I put the following SELECT
> SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> in place of the original SELECT (above), I get rows with
> TABLE_CATALOG='Master'
> It almost looks like the SP is selecting from the Master database instead
of
> the user database that I have selected.
> If I change the SELECT to hardcode the database name (as shown below), the
> insert statements are generated correctly!
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> I have double checked to verify that I am running the EXEC in the correct
> database (not the master database) and have also confirmed that the
> sp_generate_inserts doesn't exist in any other database.
> This SP works perfectly on two other servers that I have tried this on, so
I
> am baffled!
> Any suggestions on how to debug this?
> Thanks!|||Thank you John! That resolved it! I now renamed the SP back to
sp_generate_insert (I had added the 1 at the end to make sure that the SP
didn't exist anywhere else).
and it is working!
Just curious - Normally, shouldn't this be set to a system object when I
compile it into the master database? (it worked on my other servers without
having to call sp_MS_marksystemobject)
Thanks again!
"John Bell" wrote:
> Hi
> This sounds like you need to call
> EXEC sp_MS_marksystemobject sp_generate_inserts1
> Have you checked
> SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
> John
> "Bob" wrote:
>|||Hi
The install script uses another undocumented procedure
sp_MS_upd_sysobj_category
see http://tinyurl.com/bxpyp for an explanation.
sp_MS_upd_sysobj_category uses trace flag 1717 so that when you create the
procedure the MSShipped bit is automagically set, sp_MS_marksystemobject
updates sysobjects directly.
John
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:2AA1210D-1636-435C-AAE0-D4D0C8C243DE@.microsoft.com...
> Thank you John! That resolved it! I now renamed the SP back to
> sp_generate_insert (I had added the 1 at the end to make sure that the SP
> didn't exist anywhere else).
> and it is working!
> Just curious - Normally, shouldn't this be set to a system object when I
> compile it into the master database? (it worked on my other servers
> without
> having to call sp_MS_marksystemobject)
> Thanks again!
> "John Bell" wrote:
>sql

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks
<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegr oups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/de...qlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David