Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Thursday, March 29, 2012

Does SQL Server put a shared lock on all tables within a transaction?

Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.

begin tran
update table1 set column1 = 100
if @.ERROR = 0
begin
declare @.stat int
set @.stat = (select stat from table2 where employeeid = 10)
insert into table3 (col1, col2) values (@.stat , 325)
if @.@.ERROR = 0
commit tran
else
rollback tran
end
else
roll back tran

The answer to your first question is no. The answer to your second question is no.|||

Here's the long answer:

To keep this long answer from becoming a book, I'm going to classify IS/S locks as "shared", IU/I locks as "Update" and IX/X locks as "Exclusive". The I-versions are basically the same, but has a few differences, and usually used prior to obtaining the non-I version to eliminate lock starvation.

I'm going to assume table1 only has 1 row in it for this.

Update table1 ... will initially request an update lock as it scans the table for the rows to update, once it's located the correct rows, it will acquire an exclusive lock on those pages and rows then release the update locks.

set @.stat=(SELECT .. will request shared locks on the table, retrieve it's results, then release it's shared locks.

insert into table3 will acquire an exlusive lock on either an existing page, or a new page if either none are available, or the available ones are already exclusively locked. (There is an additional exclusive lock placed on the particular row as well).

The exclusive locks will be held until the transaction is either commited or rolled back. This stored procedure will not block any other session from either reading table2 at any time, or inserting a new record into table3. It will however block any other session from updating it's only row until the transaction is complete.

Tuesday, March 27, 2012

Does Sql Server 2005 Enterprise Edition Support Managed Code ?

Dear friends,

please help,

let me tell you what happens,

1) first i installed Sql Server 2005 Enterprise Edition, everything was working fine except i was not able to create managed objects from BI, like stored procedures, triggers, UDT, etc etc.

2) so i installed VS.net 2005 complete + Sql Server 2005 Express Edition

now i was able to create stored procedures, triggers, etc etc, i'm talking about the CLR objects, ok, i.e. the managed code, but........ i was only able to do this from Sql Server 2005 Express Edition.

whenever i try to create managed objects CLR like stored procedures and stuff from the Sql Server 2005 Enterprise edition it gives me the following error :

"The Sql Server supplied by these connection properties, does not support managed code, please choose a different server"

please help meeeee.. please please, tell me what's the problem ?

below i'm attaching a screenshot of the error....

first off, I ASSURE you SQL Server 2005 EE supports SQLCLR. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

secondly, sql server 2005 has CLR Integration turned off by default and if you were to attempt using a sql 2005 EE default install/instance you could still deploy the sqlclr routines, its just that upon attempting to use them you would receive the error "Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option."

I can only reason that you are in fact NOT connecting to a SQL Server 2005 instance from a Visual Studio 2005 database project.

|||

yes even i think i was using, sql server 2000 your right.

thanks for your kind help,

regards,

Gurjit Singh

sql

Thursday, March 22, 2012

Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql server
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
TimNo, SB does not use DTC. As far as I understand, the re-try of message sending is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I understand it, the data
format (the protocol between two SB instances) isn't public, and it is in binary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, with lots of insight...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql server
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation is
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the service
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it use
> MSDTC. If so, I think a re-write of our code to piggy back ontop of service
> broker is not going to help us. Can someone elaborate on cross server query
> messages using service broker?
> Thanks,
> Tim

Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql server
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
Tim
No, SB does not use DTC. As far as I understand, the re-try of message sending is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I understand it, the data
format (the protocol between two SB instances) isn't public, and it is in binary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, with lots of insight...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql server
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation is
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the service
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it use
> MSDTC. If so, I think a re-write of our code to piggy back ontop of service
> broker is not going to help us. Can someone elaborate on cross server query
> messages using service broker?
> Thanks,
> Tim

Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql serve
r
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
TimNo, SB does not use DTC. As far as I understand, the re-try of message sendi
ng is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I
understand it, the data
format (the protocol between two SB instances) isn't public, and it is in bi
nary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, wi
th lots of insight...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRA
N
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql ser
ver
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation i
s
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the servic
e
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it u
se
> MSDTC. If so, I think a re-write of our code to piggy back ontop of servi
ce
> broker is not going to help us. Can someone elaborate on cross server que
ry
> messages using service broker?
> Thanks,
> Tim

Wednesday, March 21, 2012

Does RAISERROR store values for @@ERROR?

I tried to write some test code that looks like:
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:

> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql

Friday, March 9, 2012

Does dynamic SQL allow table variables?

Hello!
Please see below the test code that is using dynamic sql and table
variable. It is not working. I am not sure if the dynamic SQL allows
using table variables?
Thanks!
declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, 'abcd')
insert into #Dept values(2, 'xyz')
set @.s = 'insert into ' + @.Tblvar
' select * from #Dept'
exec (@.s)
select * from @.Tblvar
*** Sent via Developersdex http://www.examnotes.net ***The problem is that you didn't create the #Dept table in the same scope.
Inside the EXEC() there is no #Dept table.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23A2JudbLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||As Aaron says..this is a scope problem...table variables must be used
in the same batch...
exec('declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, ''abcd'')
insert into #Dept values(2, ''xyz'')
insert into @.Tblvar
select * from #Dept
select * from @.Tblvar')
MJKulangara
http://sqladventures.blogspot.com|||I see two problems here. First, you are trying to build your query string,
@.s, by concatenating a string with a table, which won't work. You could
instead to
set @.s = 'insert into @.Tblvar select * from #Dept'
but then you encounter another problem: neither the variable @.Tblvar or the
temporary table #Dept is defined in the scope that the query is executed
under with EXEC.
"Test Test" wrote:

> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes it does, but due to the fact that exec is opening another session
)verything you execute) it has to be put within the context of the (in
your case) INSERT INTO statement. Otherwise you can use a global
temporary data to share data with if you want to.
HTH, Jens Suessmeyer..|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
The dynamic SQL constitutes a scope on its own, and variables are
only visible in the direct scope that created it. This is in difference
to temp tables which are visible for inner scopes. (No less than two
posters gave incorrect information on this.)
A scope is a stored procedure, function, trigger - or a batch of dynamic
SQL.
A good demonstration of this is:
CREATE PROCEDURE nestlevel_sp AS
SELECT @.@.nestlevel
EXEC('SELECT @.@.nestlevel')
EXEC sp_executesql N'SELECT @.@.nestlevel'
go
EXEC nestlevel_sp
This prints 1, 2 3 in that order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to everyone!!!
*** Sent via Developersdex http://www.examnotes.net ***

Wednesday, March 7, 2012

Does anyone know where to find a simple sample code that uses asp.net 2.0 and SQL 2000 to

Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris

Hello:

Actually, for this part you can follow along with this article.

http://aspnet.4guysfromrolla.com/articles/040506-1.aspx

The key is how to connect to an existing database using the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe).

You can find this tool in your ASP.NET 2.0 installation folder:%WINDOWS%\Microsoft.NET\Framework\v2.0.50727

Hope this gives a start point. I ran this tool before and I cannot remember there is any difference compare to SQL Server 2005 Dev version. Express is a little different as you've already found out.

|||

It seems the link in previous post is about sql 2005 so I find another link :

how do I setup the new ASP.NET Membership, Role Management, and Personalization services to use a regular SQL Server instead of SQL Express

Hope it helps.

|||Actually I was looking for some sample code that has the connect string and what not. I have already read that, I keep getting errors on my host website. Mainly when trying to login. Anyone?|||

Could you show your error message ?

You can find different connection stringhere.

Sunday, February 26, 2012

Does anyone know how often the sysjobschedules table is updated?

Hi,
I'm trynig to write some simple code to check that all of my scheduled jobs have completed (successfully or not) by checking that the next run date and time for the active schedules is in the future. However when doing this the sysjobschedules tables
does not seem to update with the next information immediately. Does anynoe know of the frequency of this update or what controls it?
Cheers,
John
You might want to take a look at sp_help_job instead.
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does anyone know how often the sysjobschedules table is updated?

Hi,
I'm trynig to write some simple code to check that all of my scheduled jobs
have completed (successfully or not) by checking that the next run date and
time for the active schedules is in the future. However when doing this the
sysjobschedules tables
does not seem to update with the next information immediately. Does anynoe
know of the frequency of this update or what controls it?
Cheers,
JohnYou might want to take a look at sp_help_job instead.
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does anyone know how often the sysjobschedules table is updated?

Hi
I'm trynig to write some simple code to check that all of my scheduled jobs have completed (successfully or not) by checking that the next run date and time for the active schedules is in the future. However when doing this the sysjobschedules tables does not seem to update with the next information immediately. Does anynoe know of the frequency of this update or what controls it
Cheers
JohnYou might want to take a look at sp_help_job instead.
--
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does a user have EXEC permission for a stored procedure?

Given a user name and a stored procedure name, can someone provide me with
the TSQL code to set a boolean indicating whether that user has EXEC
permission to that proc?
The specific proc I'm interested in is xp_loginconfig.
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.SQL Server Books Online states that the execute permissions for
xp_loginconfig default to members of the db_owner fixed database role in the
master database and members of the symin fixed server role, but can be
granted to other database users. So if the user is not a member of the dbo
role in master, you can use a simple GRANT statement like:
GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
Anith|||Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
will check for exec priv.
declare @.name sysname, @.myboolean bit
set @.name = 'Jones'
if exists (select *
from sysprotects
where id = object_id('myproc')
and uid = (select uid from sysusers where name = @.name)
and action = 224
and protecttype <> 206)
set @.myboolean = 1
else
set @.myboolean = 0
select @.myboolean
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
> Given a user name and a stored procedure name, can someone provide me with
> the TSQL code to set a boolean indicating whether that user has EXEC
> permission to that proc?
> The specific proc I'm interested in is xp_loginconfig.
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.|||Hi Armando,
Thanks for your reply - it certainly gave me some things to try.
First off, if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('xp_loginconfig')
)
I get zero back. Also
SELECT COUNT(*) FROM dbo.sysobjects WHERE (id = OBJECT_ID('xp_loginconfig'))
returns zero. So this looks like a dead end.
Now if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
I get one back. So far so good. Next I run:
SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
which returns a single name, but not 'Jones' - who does have execute
permission.
So this also looks like a dead end.
Any thoughts or suggestions?
Thanks, Hal
"Armando Prato" wrote:

> Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
> will check for exec priv.
> declare @.name sysname, @.myboolean bit
> set @.name = 'Jones'
> if exists (select *
> from sysprotects
> where id = object_id('myproc')
> and uid = (select uid from sysusers where name = @.name)
> and action = 224
> and protecttype <> 206)
> set @.myboolean = 1
> else
> set @.myboolean = 0
> select @.myboolean
>
> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
>
>|||Anith,
Thank you for replying. However, my question is not "how do you grant?",
rather it is "how can you tell?"
Hal
"Anith Sen" wrote:

> SQL Server Books Online states that the execute permissions for
> xp_loginconfig default to members of the db_owner fixed database role in t
he
> master database and members of the symin fixed server role, but can be
> granted to other database users. So if the user is not a member of the dbo
> role in master, you can use a simple GRANT statement like:
> GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
> --
> Anith
>
>|||Couple of things come to mind
Did you run against the master database? Extended stored procs live
in master and are not found in newly created databases. Also, did you
explicitly
grant execute to the user(s) in question?
The query...
SELECT dbo.sysusers.name
FROM dbo.sysprotects
JOIN dbo.sysusers ON (dbo.sysprotects.uid = dbo.sysusers.uid)
WHERE (dbo.sysprotects.id = OBJECT_ID('xp_loginconfig'))
...looks ok. It just sounds like you need to explicitly grant execute
to all users you expect will have this permission outside of the dbo.
Here is the BOL snippet on this xp:
Execute permissions for xp_loginconfig default to members of the db_owner
fixed database role in the master database and members of the symin fixed
server role, but can be granted to other users.
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:81ED433C-8190-45F1-8FC5-07ED38DDCFD4@.microsoft.com...
> Hi Armando,
> Thanks for your reply - it certainly gave me some things to try.
> First off, if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id =
OBJECT_ID('xp_loginconfig'))ed">
> I get zero back. Also
> SELECT COUNT(*) FROM dbo.sysobjects WHERE (id =
OBJECT_ID('xp_loginconfig'))[color=darkr
ed]
> returns zero. So this looks like a dead end.
> Now if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
> I get one back. So far so good. Next I run:
> SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
> dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
> WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
> which returns a single name, but not 'Jones' - who does have execute
> permission.
> So this also looks like a dead end.
> Any thoughts or suggestions?
> Thanks, Hal
> "Armando Prato" wrote:
>
that
with|||>> However, my question is not "how do you grant?", rather it is "how can
Have you looked in to the PERMISSIONS() function?
Anith

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Friday, February 17, 2012

Document Object Code

Hi all,
I've spent the last two hours looking for an example of this, but I've
come up short. What I was trying to do was create a 'test' which would
toggle a rectangles hidden property. Unfortunately, I can't seem to
figure out how to send my toggle function a document object.
Here's what I'm after. A 'bundle' report for my sales team. From
parameters, they choose a client to print for, and then select a series
of true/false parameters. Each 'sub-report' is contained in a
rectangle which will render only if the corresponding true/false is = true. In the rectangle hidden property, I had set the expression
=Code.setHidden(this)
Hoping that it would send the 'rectangle2' object to the setHidden(),
which would test the parameters, and correspondingly hide where
appropriate.
Any ideas?
Thanks much.
Brian AckermannIs it not possible to pass a document object to a code block?
Thanks,
Brian Ackermann

Document Job Steps

How can I script out the job steps along with the DTS, Sp or T-SQL code within the step. This info must be stored somewhere. How can I access it?

Hi,

You can script out jobs, including their job steps, by using SQL Server Management Studio. In SQL Server Books Online, see the topic "How to: Script Jobs Using Transact-SQL (SQL Server Management Studio)"

The URL within the Books Online browser is

ms-help://sql90/udb9/html/fd9e6497-87ca-4da8-b194-55c9d5b03cec.htm

Copy and paste this URL into the Books Online browser to locate this topic. As an alternative, you can also search on the topic name.

Let me know if you have any further questions.

Best regards,

Laurel Hale

Document Job Steps

How can I script out the job steps along with the DTS, Sp or T-SQL code within the step. This info must be stored somewhere. How can I access it?

Hi,

You can script out jobs, including their job steps, by using SQL Server Management Studio. In SQL Server Books Online, see the topic "How to: Script Jobs Using Transact-SQL (SQL Server Management Studio)"

The URL within the Books Online browser is

ms-help://sql90/udb9/html/fd9e6497-87ca-4da8-b194-55c9d5b03cec.htm

Copy and paste this URL into the Books Online browser to locate this topic. As an alternative, you can also search on the topic name.

Let me know if you have any further questions.

Best regards,

Laurel Hale

Tuesday, February 14, 2012

DoCmd.RunSQL uses what library?

I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
Maury
RunSQL should be part of the Access object library.

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury

DoCmd.RunSQL uses what library?

I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
MauryRunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury