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 optimization

Guys,

I have a 40GB database being hosted on a NAS device (Network Appliance -
http://www.netapp.com/). The log file for this database grows at a rate of
5GB per day. So I do a log cutting on this database on a weekly basis to
free up space on my NAS device.

I have created two separate Q-Trees on the NAS device and I store the main
data file on one of the Q-Trees and the log file on the other Q-Tree.

I ONLY have a PRIMARY FILEGROUP. Should I change this to optimize my
database performance?

I also have a table in the database with approximately 12 million records.
The indexing on the table is well done - using covered index for better
querying. The table gets defragged quite often. So I do defrag the Indexes
on this table once a month (using DBCC DEFRAG).

What else can I do better the performance of my database?

Thank you,

SR"SR" <yosonu@.socal.rr.com> wrote in message
news:ba4c21f6.0310052349.283deaa@.posting.google.co m...
> Guys,
> I have a 40GB database being hosted on a NAS device (Network Appliance -
> http://www.netapp.com/). The log file for this database grows at a rate of
> 5GB per day. So I do a log cutting on this database on a weekly basis to
> free up space on my NAS device.

Not sure what you mean by "log cutting" Please explain.

> I have created two separate Q-Trees on the NAS device and I store the main
> data file on one of the Q-Trees and the log file on the other Q-Tree.

Well, I'm not familiar with Q-Trees (should be by the end of this week
ironically :-).

But the real answer comes down to physical disks.

> I ONLY have a PRIMARY FILEGROUP. Should I change this to optimize my
> database performance?

"Maybe". If your other filegroups would end up being on the same PHYSICAL
disks (even if logically they appear different) then no, you probably would
not notice a difference. (Assuming SQL 2000.)

> I also have a table in the database with approximately 12 million records.
> The indexing on the table is well done - using covered index for better
> querying. The table gets defragged quite often. So I do defrag the Indexes
> on this table once a month (using DBCC DEFRAG).
> What else can I do better the performance of my database?

Use Profiler, find out where any bottlenecks would be.

It's hard to say how to improve performance when a) we don't know how slow
it is now and how fast you need it to be, B) what other bottlenecks may
be... disk I/O, CPU, memory, etc.

> Thank you,
> SR|||SR (yosonu@.socal.rr.com) writes:
> I have a 40GB database being hosted on a NAS device (Network Appliance -
> http://www.netapp.com/). The log file for this database grows at a rate of
> 5GB per day. So I do a log cutting on this database on a weekly basis to
> free up space on my NAS device.

Don't you backup the transaction log more frequently than that?

> I also have a table in the database with approximately 12 million records.
> The indexing on the table is well done - using covered index for better
> querying. The table gets defragged quite often. So I do defrag the Indexes
> on this table once a month (using DBCC DEFRAG).
> What else can I do better the performance of my database?

As Greg said, since we don't know what you think is slow, and how fast
you want it to be, it is difficult to say. But running Profiler to
catch queries with duration > 1000 ms (or what may be suitable) could be
a start.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp