A DB block during stored procedure excecution...

Hi all,
I'd like to ask if is there any deference between executing a stored
procedure with 'exec' command in the Query analyzer and executing the code o
f
the stored procedure in Query analyzer too. I mean that I copied the source
of stored procedure in a window of the analyzer, I declare the parameters of
stored procedure as variables and I set the same values into them and I just
run the source...
When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
the parameters sometimes it occures a block... When I run the source
declaring the parameters as variables etc, all are fine and I have never any
block... How is it possible? Is there any idea?
Thanks in advance..Christos,
What do you mean by block?
See if this helps:
[url]http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/a5517668
94ed8781? q=%22what+is%22%2B%22parameter+sniffing%
22&hl=en&lr=&ie=UTF-8&rnum=1[/url
]
AMB
"Christos" wrote:

> Hi all,
> I'd like to ask if is there any deference between executing a stored
> procedure with 'exec' command in the Query analyzer and executing the code
of
> the stored procedure in Query analyzer too. I mean that I copied the sourc
e
> of stored procedure in a window of the analyzer, I declare the parameters
of
> stored procedure as variables and I set the same values into them and I ju
st
> run the source...
> When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
> the parameters sometimes it occures a block... When I run the source
> declaring the parameters as variables etc, all are fine and I have never a
ny
> block... How is it possible? Is there any idea?
> Thanks in advance..
>|||I mean that stored procedure never ends, so it is blocking other users from
using the same tables etc (the sp updates data in some tables...)|||Christos,
If the sp never ends could be because another process is blocking the
resources needed by the sp and it is waiting, or the workload is heavy.
You can use EM (Management - Current Activity) or execute sp_who2 from QA to
see the processes and locks. You can also use Profiler to trace locks.
AMB
"Christos" wrote:

> I mean that stored procedure never ends, so it is blocking other users fro
m
> using the same tables etc (the sp updates data in some tables...)|||The strange in this situation is that when I have a block during the sp
execution, if I kill the process of sp and I try again to run it separetely
with the same parameters in the Q Analyzer's environment, the blocking
happens again. If I 'export' the source code in the analyzer's window and ru
n
it again using the parameters as variables it finishes imediatelly without
any problem...sql

4GB sql file

I have a 4GB SQL db that is used for Backup Exec, but the file has
reached the maximum file size for the file system. I have tried
upgrading from 2000 to 2005 but I do not get any more options, such as
shrink or to do maintenance on the db through the Enterprise manager.
Is my install stuck on the msde license and won't let me install the
full blown sql server until I reboot the server? ThanksHi
If you have 4GB database and thinking to upgrare to SQL Server 2005 , you DO
NOT have MSDE ,MSDE has a limit 2GB
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
<jooooooohn@.gmail.com> wrote in message
news:1191878238.775355.215520@.r29g2000hsg.googlegroups.com...
>I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
>|||jooooooohn@.gmail.com wrote:
> I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
It's not clear if you are hitting the limit of the FAT32 file system or
the limit of MSDE/SQL Express. If the file system is the problem, then
change it to NTFS. If you already use NTFS, then try to shrink the file
using OSQL.EXE. For example, you can try DBCC SHRINKFILE(1,1). Which is
the big file: the MDF (data file) or the LDF (log file) ?
For more information, see Books Online topics for DBCC SHRINKFILE and
DBCC SHRINKDATABASE.
--
Razvan Socol
SQL Server MVP