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

A critical SQL Server problem

Hi,

Once, I wanted to attach a database to SQL Server by means of "CREATE DATABASE ... FOR ATTACH" command, in Query Analizer.

I encountered an error and attaching database failed.
(The error was something like this : "the phisical file 'FILENAME' maybe is incorrect")

But after going to Enterprice Manager, I realised that a database whitout a name was created.

Since then, I wasn't able to do any process in SQL Server whitout either not recieving an error or failing the process.

I decided to uninstall and reinstall SQL Server again.
But after uninstalling, whenever I want to reinstall SQL Server I get this error :

"A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup."

Which restarting does not help.

Now :

1- What was the reason of creation a null-named database, and how can I prevent it next time?

2- What should I do to Install SQL Server again?

Ooh, that doesn't sound healthy at all. I think your best bet would've been to have dropped the database using something really similar to the script you used to create it.

Now that you've tried to uninstall SQL Server, I think things have got worse. You may need us to find registry entries for you to play with, and things like that (but I don't know them off the top of my head). If this doesn't help, you may find yourself needing to rebuild your system. :(

Rob|||

I tried to drop database in diferent ways but they didn't work. That was the reason of why

I unistalled the SQL Server. And I don't know how to deal with registry for this problem.

|||Very carefully, go through this article. I hope it helps:

http://support.microsoft.com/kb/312995

A critical SQL Server problem

Hi,

Once, I wanted to attach a database to SQL Server by means of "CREATE DATABASE ... FOR ATTACH" command, in Query Analizer.

I encountered an error and attaching database failed.
(The error was something like this : "the phisical file 'FILENAME' maybe is incorrect")

But after going to Enterprice Manager, I realised that a database whitout a name was created.

Since then, I wasn't able to do any process in SQL Server whitout either not recieving an error or failing the process.

I decided to uninstall and reinstall SQL Server again.
But after uninstalling, whenever I want to reinstall SQL Server I get this error :

"A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup."

Which restarting does not help.

Now :

1- What was the reason of creation a null-named database, and how can I prevent it next time?

2- What should I do to Install SQL Server again?

Ooh, that doesn't sound healthy at all. I think your best bet would've been to have dropped the database using something really similar to the script you used to create it.

Now that you've tried to uninstall SQL Server, I think things have got worse. You may need us to find registry entries for you to play with, and things like that (but I don't know them off the top of my head). If this doesn't help, you may find yourself needing to rebuild your system. :(

Rob|||

I tried to drop database in diferent ways but they didn't work. That was the reason of why

I unistalled the SQL Server. And I don't know how to deal with registry for this problem.

|||Very carefully, go through this article. I hope it helps:

http://support.microsoft.com/kb/312995

A .cmd file can be run on SQL7, but not on SQL2000

Hi,
I have a command file with the following SQL statement:
bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
-U -P -f %formatfile%
I can run this file on a machine with SQL7 installed, but have error when I
run it on a machine with SQL2000. Here is the error:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=2
Warning=[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()).
Does anyone know where is the problem? How can I fix it?
Thanks for the support!
On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:

>Hi,
>I have a command file with the following SQL statement:
>bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
>DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
>-U -P -f %formatfile%
>I can run this file on a machine with SQL7 installed, but have error when I
>run it on a machine with SQL2000. Here is the error:
>SQLState = 08001, Native Error=17
>Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
>exist or access denied.
>SQLState = 01000, Native Error=2
>Warning=[Microsoft][ODBC SQL Server Driver][Shared
>Memory]ConnectionOpen(Connect()).
>Does anyone know where is the problem? How can I fix it?
>Thanks for the support!
Hi June,
Some things to check:
- Are you sure the SQL Server 2000 service is running, and that the
computer that runs this script and the server where SQL Server is
installled can "see" each other?
- Try adding the "-S servername[\instance_name]" argument
- Try using a trusted connection instead of a SQL Server login (ie
replace the "-U -P" arguments with "-T").
- If you can't use a trusted connection, double-check that SQL Server is
configured to allow "Mixed Mode Authentication". The default is "Windows
Authentication", which allows only trusted connections. Also, add userid
and password to the command string (i.e. replace "-U -P" with "-U userid
-P password").
- Check the Client Network Utility on the workstation and the Server
Network Utility on the server. Are the same protocols enabled? Are the
properties the same? If there are any firewalls between the workstation
and the server, have you made sure that the prots required for SQL
Server are not blocked?
Hugo Kornelis, SQL Server MVP
|||Hi Hugo,
Thanks for your info.
1. Both the command file and SQL server are running on the same machine
(Win2000).
2. I have tried to add -S [instance_name] parameter to the command line, but
I got similar error messages:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=53
Warning=[Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen(Connect()).
3. I also tired to replace -T with -U -P, still got the same error.
What else should I try?
Thanks again!
"Hugo Kornelis" wrote:

> On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:
>
> Hi June,
> Some things to check:
> - Are you sure the SQL Server 2000 service is running, and that the
> computer that runs this script and the server where SQL Server is
> installled can "see" each other?
> - Try adding the "-S servername[\instance_name]" argument
> - Try using a trusted connection instead of a SQL Server login (ie
> replace the "-U -P" arguments with "-T").
> - If you can't use a trusted connection, double-check that SQL Server is
> configured to allow "Mixed Mode Authentication". The default is "Windows
> Authentication", which allows only trusted connections. Also, add userid
> and password to the command string (i.e. replace "-U -P" with "-U userid
> -P password").
> - Check the Client Network Utility on the workstation and the Server
> Network Utility on the server. Are the same protocols enabled? Are the
> properties the same? If there are any firewalls between the workstation
> and the server, have you made sure that the prots required for SQL
> Server are not blocked?
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Thu, 30 Mar 2006 14:55:01 -0800, June wrote:

>2. I have tried to add -S [instance_name] parameter to the command line, but
>I got similar error messages:
Hi June,
The correct usage of -S is either
-S server_name
or
-S server_name\instance_name

>What else should I try?
If none of these option work, try posting to a different group. This
group is actually intended for support to the "Microssoft English Query"
tool. Precious few people visit here.
Since your question is about bcp, one of the tools that come with SQL
Server, microsoft.public.sqlserver.tools would be the best place to ask.
Hugo Kornelis, SQL Server MVP

64 bit silent Installation

Hi:

Is it possible to install a 64 bit SQL Server 2005 silently?. I am wondering what are the command options that are needed to do a quiet Install of Enterprise Edition of SQL Server 2005 (x64) on a x64 OS.

Any info is appreciated.

Thanks

Ankith

The 64-bit command line parameters are the same as the 32-bit parameters. Since your install configutation options are so vast, my suggestion would be to open up template.ini (found on any SQL install media) and take a look at the numerous silent install examples in that file. Or search Books Online for silent install instructions. If you have specific questions, let us know.

Thanks,
Sam Lester (MSFT)