sql bit data type: execute

Showing posts with label execute. Show all posts

Sunday, March 11, 2012

Blocking When doing bcp

Any ideas?

I execute a sproc from QA.
It interogates a folder:

Select @.Command_String = 'Dir ' + @.FilePath + '\*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @.Command_String
[/code[

Which I then Parse and get the details...

Set up a CURSOR (Booo hiss...)

[code]

DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]

WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN

Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)

SET @.cmd = 'bcp ' + @.db_name + '..' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + ' in '
+ @.FilePathAndName
+ ' -f' + 'd:\Data\Tax\' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + '.fmt'
+ ' -S' + @.@.servername + ' -U -P'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + '''' + ', NO_OUTPUT'

INSERT INTO #XLAT_BCP_Results(Col1) Exec(@.Command_String)

MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH

A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time...

I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

When I do sp_who, it shows the blocking...

I'm so confused...Can you get the table/index that has the blocking lock on it?|||Well the blocking spid I found in sp_who, is the QA window I lauched the sproc from...the blocked spid is the idependent bcp thread (well not so independent)..

There's no RI between these code tables, and just a trigger...that moves the rows to history table...

I'll check sp_locks...|||Every single lock on the "parent" spid has a status of "grant" and a type of IX...|||I am pretty sure there wasa way of telling a blocking lock from a regular lock. I will have to do a bit of checking, but this job thing has me a bit tied up.|||In the output of sp_lock, look for a status of "WAIT". This is the process that is attempting to get at the locked resource, and of course, must wait for the first process to get done with it. Alternatively, you can check the waitresource column in the sysprocesses table. This will also tell you what the processes that is blocked is waiting on.
This will give you the name of the table/index that you are getting hung up on. After this, I am afraid it is going to be a slog through the code to find the actual stumbling block.
Let us know how it comes out.|||Thanks...

Been there...

It seems that even though I expected lines inside of a sproc to be serial, it looks like the delete doesn't complete before the sproc continues to the nect line of code, which is the xp_cmdshell bcp...

I find this totally unbelievable, but in every instance, the independent xpshell thread (which is executed with a different login) is the one that is being blocked. And it is being blocked by the spid that executed the sproc...

With me so far...

I then placed the COMMIT immediatley after the DELETE, and viola!

No more problems...now tell me...are the lines in a sproc serial?

I always thought they were, and I'll be damned if I believe any differently, but I have NO other explination.

Thanks for everyones help.

WORK AROUND ...HO!

Wednesday, March 7, 2012

Blocked transaction problem

Hello,

I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.

SERVER2 is a linked server.

BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN

I have same configuration in other 2 computers and it works ok.

What is the problem?

Thank you!!

This should not lock your transaction. But depending on the server you are using it can be that all data will retrieved from the linked server first to do a filter on the calling server. That will take more time than just doing the Select on the remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

there are only 3 rows on remote server.

Both servers are SQL Server 2000.

|||What about if you run the thing without any transaction ? Is it the same problem ? Do you see any locks on the table during the stale time ?

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello,

if I run it without any transaction it finishes very fast.

But I want to know the reason of the problem, because I have one place that runs it this way, and I can not install a new one with same queries.

Thank you.

|||See which locks are allocated when using the command with transactions to see where the blocking is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You are essentially doing a distributed transaction because of the use of DML with linked server table. So the delay might be attributed to network issues. Check the MSDTC configuration including network configuration to make sure everything is fine. You can also see the transaction statistics in the MSDTC console (Component Services applet) to see the average response time. If that is close to your execution time then the problem is external to SQL Server and you should ask in the Windows newsgroups or search MSKB for optimizing MSDTC setup.

If the problem is not related to MSDTC, then you can determine the wait types in SQL and troubleshoot the issue. Run following:

1. DBCC SQLPERF(WAITSTATS, 'CLEAR') WITH NO_INFOMSGS

2. your current batch

3. DBCC SQLPERF(WAITSTATS) WITH NO_INFOMSGS

Now, based on the wait type you can figure out what is the cause of the delay. Search MSKB articles for more details on using DBCC SQLPERF.

|||

Hello,

I can not see anything because the execution of the query never ends.
I leave up to 30 minutes and it does not end.

I can execute the query without a transaction and it finishes in a few miliseconds. So, it is posible to query other server.

Could it be a problem with server names?
I mean, the name registered at SQL Server Enterprise Manager, the one at Linked Server and aliases (SQL Server Client Network Utility).

Currently all of them are the same, but at SQL Server installation time the server name was a diferent one.

Thanks!!

|||

Hi!

I have the same problem!!

A simple select againts a second server with a TRAN

BEGIN TRAN
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2
COMMIT TRAN

execution of the query never ends

Without TRAN it finishes in a few miliseconds

someone have any ideas to resolve this problem?

thanks in advance.

block a user from executing system stored procs?

How do you block a user from executing system stored procs? Seems a user is
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user
>> is granted access to execute them even when they are not part of any
>> schema or role. The user security page says "default schema > dbo" and
>> owned schemas are all blank, and no role members are checked. So why
>> can the user run system stored procs? I need to block that but don't
>> want to run a "deny" on every system stored proc if I can help it.
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help it.
>>
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to see
>> all the nice little stored procedures he could call to do damage. That's
>> just one example. I was surprised when someone showed me an example of
>> how a hacker could learn all the stored procs and other object
>> information in a database this way. I can't believe this access is
>> enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>|||> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is protected.
>> A user might be able to run sp_help, but the only objects they could see would be the objects
>> they own, or the ones they were specifically granted access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets compromised, the user can't run
>> things *like* sp_stored_procedures to see all the nice little stored procedures he could call to
>> do damage. That's just one example. I was surprised when someone showed me an example of how
>> a hacker could learn all the stored procs and other object information in a database this way.
>> I can't believe this access is enabled by default and not discussed in security practices
>> articles.
>> There are hundreds of system procs, as you know, and I wouldn't know which ones to manually DENY
>> without a lot of research, and then a lot of one-by-one commands. So, I want to block all the
>> system procs. This user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the Management Studio or
>> Enterprise Manager, so if you denied access to all procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user is granted access to
>> execute them even when they are not part of any schema or role. The user security page says
>> "default schema > dbo" and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but don't want to run a "deny"
>> on every system stored proc if I can help it.
>>
>>
>>
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is
>> protected.
>> A user might be able to run sp_help, but the only objects they could see
>> would be the objects they own, or the ones they were specifically granted
>> access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to
>> see all the nice little stored procedures he could call to do damage.
>> That's just one example. I was surprised when someone showed me an
>> example of how a hacker could learn all the stored procs and other
>> object information in a database this way. I can't believe this access
>> is enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema >
>> dbo" and owned schemas are all blank, and no role members are checked.
>> So why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>>
>

block a user from executing system stored procs?

Sunday, February 12, 2012

Bizare String Concatenation Issue - Explanation Needed

Hi all, can someone please explain why this is happening:
I execute the following:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM MyTable T
SELECT @.temp
Say [MyTable] looks like this:
|NAME|
--
A
B
C
D
Here are the result from running the above code on two different SQL Server
2000 installations:
SQL Server 2000 #1
[D]
SQL Server 2000 #2
[A][B][C][D]
I want #2. Is there some database option that I'm missing or something? Any
help would be appreciated. Thanks.SELECT @.@.version from each server? Might be different builds.
For me:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM master.dbo.sysdatabases T
SELECT @.temp
select @.@.version
SQL 2000, build 878 (Sp3a plus hotfixes):
[master][tempdb][model][msdb][pubs][Nort
hwind]
SQL 2005, IDW 13:
[master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
> Hi all, can someone please explain why this is happening:
> I execute the following:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM MyTable T
> SELECT @.temp
>
> Say [MyTable] looks like this:
> |NAME|
> --
> A
> B
> C
> D
> Here are the result from running the above code on two different SQL
> Server
> 2000 installations:
> SQL Server 2000 #1
> [D]
> SQL Server 2000 #2
> [A][B][C][D]
> I want #2. Is there some database option that I'm missing or something?
> Any
> help would be appreciated. Thanks.|||Thanks Mike. I should have posted an update, but I found this:
http://support.microsoft.com/defaul...b;EN-US;q287515
What I posted here was a simple example of my issue, my actual query
contained a little more SQL and it was on a view instead of a table. Doing
the following resolved my issue:
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM (SELECT * FROM MyTable) T
This caused the query optimizer to sort the results of the inner query
first, then apply the concatenations.
Thanks again for your response Mike.
"Mike Epprecht (SQL MVP)" wrote:

> SELECT @.@.version from each server? Might be different builds.
> For me:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM master.dbo.sysdatabases T
> SELECT @.temp
> select @.@.version
> SQL 2000, build 878 (Sp3a plus hotfixes):
> [master][tempdb][model][msdb][pubs][Nort
hwind]
> SQL 2005, IDW 13:
> [master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
> news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
>
>

Sunday, March 11, 2012

Blocking When doing bcp

Wednesday, March 7, 2012

Blocked transaction problem

block a user from executing system stored procs?

block a user from executing system stored procs?

block a user from executing system stored procs?

Sunday, February 12, 2012

Bizare String Concatenation Issue - Explanation Needed

sql bit data type

Blog Archive

About Me