new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.
Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John
sql

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:
> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

New SQL Server and Photos

I heard that you can store photos with the row in the new SQL Server (a
photo data type?). Is this true? If so, is there a speed advantage if
you have an e-commerce platform with thousands of products?

Help appreciated.
Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***I'm not sure what you're referring to - MSSQL already supports the
image data type for storing up to 2GB of binary data in a row (which
could be photos or anything else).

Although I haven't worked with a system like that myself, previous
newsgroup posts seem to suggest that many people prefer to store only a
path to the binary file in the database, and leave the file itself in
the filesystem.

Simon|||> Although I haven't worked with a system like that myself, previous
> newsgroup posts seem to suggest that many people prefer to store only
a
> path to the binary file in the database, and leave the file itself in
> the filesystem.

That's been my approach. The file system's on the whole a much more
efficient way to deal with such things, provided you've guarded against
path-traversal security problems and the like. For one, you're
funnelling less data through SQL Server itself, and two, NTFS is built
to handle exactly this kind of work in the first place.

There may be some circumstances where you need to have the data in the
row -- I think TerraServer uses this approach -- but most people are
not going to be building TerraServer!

New SP vs parameters

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.

This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.

Thanks,
JimHi Jim,
Okay.
Does the SP takes a long time to run as stand alone (executing it on
the server without calling from C#)?
Is it too much of a trouble to post the whole code? Can you time the SP
and what is it?
As a quick solution maybe you can modify the SP to save the result into
a table and return some number instead (as success). Then the C# just
access that table and once done drop it at the end. You can have a
recompile option when creating the stored procedure but might worth as
well if you can increase performance by minor fixes.

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

|||Your answer is: "it depends."

Instead of re-creating the stored proc, you can use the WITH RECOMPILE
option, which will probably do what you want:

CREATE PROC dbo.foo WITH RECOMPILE AS
select 'hello world';

http://msdn2.microsoft.com/en-us/li...59(SQL.80).aspx
-Dave

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

new custom flat file connection manager component

The standard flat file connection component does not handle flat files with different column counts on different rows. Since that's the type of file I have to read, I like to create a modified version of the flat file connection manager that can do this. I have found some info in how to create a connection manager. But I can't find anything that tels me how to build one that would be recognized by the flat file source dataflow component.

So how do you build an connection manager that can used by the standard flat file source component? Anyone ever did this or knows were to get the docs?

I know my problem is also solvable by reading the row into a single column and then using a script component to split them but that is not the solution I want.

The new connection manager is not going to solve this problem. The parsing logic is not part of the connection manager, it is part of the Flat File source adapter. You would need to build a new source, not connection manager.

Thanks,

Bob