exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
http://sqlblog.com/blogs/linchi_she...lification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
> http://sqlblog.com/blogs/linchi_she...lification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>>
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
Thanks
Hassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>
|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

Exec Stored Procedure

Hello

Which is faster :

to write a a big stored procedure with if conditions, or to separate them and call them using exec??

i.e:

if @.id=1
insert into ...
else if @.id=2
update...
--------
or
if @.id=1
exec InsertProcedure
else if @.id=2
exec UpdateProcedure

Please help

Its very difficult to jugde that which is faster inLine Query or Store procedure

Read What One of Microsoft Press books says about Store procedure

"

Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. That is, they are allowed to change data in tables, and even the schema of objects. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. You can perform input validation in stored procedures, and you can use stored procedures to allow activities only if they make sense as a whole unit, as opposed to allowing users to perform activities directly against objects.

Stored procedures also give you the benefits of encapsulation; if you need to change the implementation of a stored procedure because you developed a more efficient way to achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure's interface remains the same, the users and the applications are not affected. On the other hand, if you implement your business logic in the client application, the impact of a change can be very painful.

Stored procedures also provide many important performance benefits. By default, a stored procedure will reuse a previously cached execution plan, saving the CPU resources and the time it takes to parse, resolve, and optimize your code. Network traffic is minimized by shortening the code strings that the client submits to Microsoft SQL Server—the client submits only the stored procedure's name and its arguments, as opposed to the full code. Moreover, all the activity is performed at the server, avoiding multiple roundtrips between the client and the server. The stored procedure will pass only the final result to the client through the network"

And About User defined Funciton Or Say inline Query Statement

User-defined functions (UDFs) are routines that perform calculations/computations and return a value—scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints.

|||

probably the big stored procedure with everything in it will be faster but only by a matter of milliseconds. I would choose which ever option you will find easier to maintain and develop.

|||

Are there any clues or proves ??

Please I need that urgenlty

|||

It depends on what you are doing in the IF Loops. If you are just doing INSERTs one big proc should be fine, as far as performance is concerned. If you split it into multiple smaller procs, you have the ability to call individual proc if you need to reuse the INSERT to one of the tables rather than call the giant proc.

|||

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Thank you

|||

In case of splitting sp , will SQL server builds and stores execution plans for exec called procedures??

Plz Advice

|||

JRICE:

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Please re-read my earlier reply.

|||

Thanks for your reply,

I need an advice if Im concern about performance and I need reusability of my stored procedures, will it differ that much?

thank you in advance

exec sp_dropmergearticle @publication = N'RWBreathe_Publication', @article = N'Call'

hi :
i got error when i run above command
Cannot drop article 'Call' from publication 'RWBreathe_Publication'
because its snapshot has been run and this publication could have active
subscriptions.
i used merge replication , and PDA as subscriber.
Cheers
Nick
Nick,
this is one difference to transactional replication. In merge you can't drop
subscriptions then drop an article. In fact, as soon as the snapshot agent
has run, your options are limited. If you script it out then recreate it
without running the snapshot agent, you'll ba able to drop the article from
Enterprise Manager. Alternatively of course you could remove references to
the article in the script before running it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

EXCEPTION_ACCESS_VIOLATION: Odd Error in Event Viewer

I have a stored procedure that I use for call a aspx page from within SQL. The stored procedure itself runs fine
with no errors but I keep getting errors in the event viewer everytime
it runs (with .vbs, .NET, .vb6 client) outside Enterprise manager or SQL Query Analyzer....

This is error:

Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I think that this is the same problem seen in

http://www.sqlmonster.com/Uwe/Forum...in-Event-Viewer

I've test with XP sp2, W2003 server, SQL 2000 sp3a.....on the same machine.

ANY SUGGESTIONS??

Thanks

--
Message posted via http://www.sqlmonster.com"Flavio 2006 via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:713ecc4e10e34ee3baaee91a9fafe36d@.SQLMonster.c om...
>I have a stored procedure that I use for call a aspx page from within SQL.
>The stored procedure itself runs fine
> with no errors but I keep getting errors in the event viewer everytime
> it runs (with .vbs, .NET, .vb6 client) outside Enterprise manager or SQL
> Query Analyzer....
> This is error:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> I think that this is the same problem seen in
> http://www.sqlmonster.com/Uwe/Forum...in-Event-Viewer
> I've test with XP sp2, W2003 server, SQL 2000 sp3a.....on the same
> machine.
>
> ANY SUGGESTIONS??
> Thanks
> --
> Message posted via http://www.sqlmonster.com

If your problem also involves the MSXML COM object, then have you checked
the KB article from the previous post? If it doesn't apply to you, then you
might want to post more details of what your procedure does, but since
exceptions are basically problems with MSSQL itself, contacting PSS would
probably be the best idea.

Simon|||Hi Simon,

the procedure is quite similar to spSendWebServiceRequest, (i mean procedure in old thread...)

I've contact M$ PSS, and I will post here the response (if exists...:))

Thanks for interest

Flavio

--
Message posted via http://www.sqlmonster.com|||Solution:

MUST install msxml4_sp2 ...

--
Message posted via http://www.sqlmonster.com

Exception in EnumMergeDynamicSnapshotJobs

Hi,

I'm manually creating dynamic snapshots for a push merge subscription, however I often get an exception thrown when I call EnumMergeDynamicSnapshotJobs. The exception says:

Unable to cast object of type 'System.Byte' to type 'System.String'.

The stack trace is:

at System.Data.SqlClient.SqlBuffer.get_String()

at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)

at Microsoft.SqlServer.Replication.ReplicationObject.SafeGetString(SqlDataReader lclReader, Int32 nColumn, Int32 nLength)

at Microsoft.SqlServer.Replication.MergePublication.EnumMergeDynamicSnapshotJobs()

at ART.AlphaRMS.DatabaseManagementLibrary.SMOCommands.CreateDynamicSnapshot(MergePublication publication, MergeSubscription subscription, String dynamicSnapshotLocation) in ...

Has anyone seen this exception here before?

Thanks for your help

Graham

Can you post how you are creating the dynamic snapshots.

Using the UI to create a partition and the job and then enumerating the job using EnumMergeDynamicSnapshotJobs() does not show me any problems. Please post your code snippet.

|||

Hi Mahesh, I create the snapshot in code (RMO). I do not see this error every time though, it is one of those intermitant problems!

public string CreateDynamicSnapshot(MergePublication publication, MergeSubscription subscription, string dynamicSnapshotLocation)

{

string snapshotLocation = string.Empty;

try

{

// Pre-generate the dynamic snapshot as it may be that we

// want to ship it off to a remote server.

if (!string.IsNullOrEmpty(dynamicSnapshotLocation))

{

string publicationName = publication.Name;

string publicationDbName = publication.DatabaseName;

string publisherName = publication.SqlServerName;

string distributorName = publication.SqlServerName;

// Set a weekly schedule for the filtered data snapshot.

ReplicationAgentSchedule schedule = new ReplicationAgentSchedule();

schedule.FrequencyType = ScheduleFrequencyType.Weekly;

schedule.FrequencyRecurrenceFactor = 1;

schedule.FrequencyInterval = Convert.ToInt32(0x001);

// Set the value of Hostname that defines the data partition.

MergePartition partition = new MergePartition();

partition.DynamicFilterHostName = subscription.HostName;

MergeDynamicSnapshotJob snapshotAgentJob = new MergeDynamicSnapshotJob();

snapshotAgentJob.DynamicFilterHostName = subscription.HostName;

// Before creating the new job, make sure any old instances are deleted.

foreach (MergeDynamicSnapshotJob agentJob in publication.EnumMergeDynamicSnapshotJobs())

{

if (string.Compare(agentJob.DynamicFilterHostName, snapshotAgentJob.DynamicFilterHostName, true) == 0)

{

logger.Debug("Found existing dynamic snapshot job, deleting it.");

publication.RemoveMergeDynamicSnapshotJob(agentJob.Name);

}

}

logger.Debug("Creating new dynamic snapshot job for partition " + partition.DynamicFilterHostName);

// Create the partition for the publication with the defined schedule.

publication.AddMergePartition(partition);

publication.AddMergeDynamicSnapshotJob(snapshotAgentJob, schedule);

snapshotLocation = StartDynamicSnapshotJob(publication, subscription, snapshotLocation);

}

}

catch (Exception ex)

{

logger.Error("Failed to create the new dynamic snapshot.", ex);

throw;

}

return snapshotLocation;

}