log backup overwrite: stored

Showing posts with label stored. Show all posts

Tuesday, March 27, 2012

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Sunday, March 25, 2012

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!This is a multi-part message in MIME format.
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not directly. You could use a CASE structure to allow alternative =orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar =3D 'LastName'
SELECT LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message =news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does =Sql > 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it > doesn't work. My @.Variable is a VARCHAR(50).
> > Thanks!
> >
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Not directly. You could use a CASE =structure to allow alternative orderings. Here is one idea:
USE =NorthwindGO
DECLARE @.OrderVar =varchar(20)SET @.OrderVar =3D 'LastName'
SELECT LastName, FirstNameFROM EmployeesORDER BY CASE @.OrderVar = WHEN 'LastName' THEN LastName &=nbsp; WHEN 'FirstName' THEN FirstName END ASC, CASE @.OrderVar = WHEN 'LastName' THEN FirstName = WHEN 'FirstName' THEN LastName END ASC
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Dan E" =wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...>I =am writing a stored proc and do not want to use dynamic sql. Does Sql > =2005 allow ORDER BY @.Variable? The query compiler accepts it, but it => doesn't work. My @.Variable is a VARCHAR(50).> > Thanks!> >

--=_NextPart_000_0924_01C71867.3AEC95F0--|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!
Not directly. You could use a CASE structure to allow alternative orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>
|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!Not directly. You could use a CASE structure to allow alternative orderings.
Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl..
.
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
>
> Thanks!
>
>|||http://databases.aspfaq.com/databas...se
.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does SP have a max size?

Hi all. I'm getting a weird error in a stored procedure:
Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
lnea 65535
El nombre de objeto 'RegistrarTabla' no es vlido.
That's spanish but should be:
Server: message 208, level 16, state 6, procedure 'RegisterTable', line
65535
The object name 'RegisterTable' is not valid.
The procedure does not have so many lines, and the number 65535 makes me
distrust.
Does SP have a maximum size or something?
Regards,
Diego F.Diego
I think you should be concered about am error and not about a line of code.
Copy the stored procedure's code to Query Analyzer and run it. It may space
or empty strings within a stored procedure.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||That's QA which gives me that error. Doesn't apply to any line. It's a
strange error.
Regards,
Diego F.
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Diego
> I think you should be concered about am error and not about a line of
> code.
> Copy the stored procedure's code to Query Analyzer and run it. It may
> space or empty strings within a stored procedure.
>
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Diego
If you post DDL + samle data we will be able to test and maybe reproduce the
error's behaviour.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:OqS00qolFHA.1968@.TK2MSFTNGP14.phx.gbl...
> That's QA which gives me that error. Doesn't apply to any line. It's a
> strange error.
> --
> Regards,
> Diego F.
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
>|||Check that u are connected rt database in which 'RegisterTable' is
present|||While we wait for you to send some DDL, let me ask you this: is
'RegistrarTabla' the name of a user table or your procedure?
ML|||65,535 lines? Have you ever considered publishing that stored procedure as a
book?
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||OK, thank you all. It was a stupid problem. I was altering a SP with a name
changed, so the name of that SP didn't exist at all!.
I'm a bit embarrased :-P
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>

Thursday, March 22, 2012

Does SETROWCOUNT 10 apply to only the sp in which it used or outside it also?

I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.

Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?

The option SET ROWCOUNT affects only the current connection session. It does not transfer from one connection to another in a connection pooling environment. Nor does the scope exceed stored procedure boundaries

ifobject_id('usp1')isnot nulldrop proc usp1gocreate proc usp1asbegin set rowcount 10select *from sys.objectsendgoexec usp1goselect *from sys.objectsgoifobject_id('usp1')isnot nulldrop proc usp1go

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

Does RS recognize CHAR(13)?

I am making a query for mailing labels that I will generate in Reporting
Services. I have a SQL Server stored procedure that returns the address using
SQL like this
SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
+ Zip AS FullAddress
FROM ...
Assume the data is:
Address: 123 Main Street
Address2: Suite 3
City: Bellevue
State: WA
Zip: 98111
I assign the FullAddress field to a textbox in Reporting Services. I resize
it to be several lines high. The result I get is something like this:
123 Main StreetSuite3Bellevue
WA 98111
I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
double spaces. Does RS handle CHAR() output? Do I need to use separate fields?I've done this before but in the actual field expression not in the query.
Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
"Rick" wrote:
> I am making a query for mailing labels that I will generate in Reporting
> Services. I have a SQL Server stored procedure that returns the address using
> SQL like this
> SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> + Zip AS FullAddress
> FROM ...
> Assume the data is:
> Address: 123 Main Street
> Address2: Suite 3
> City: Bellevue
> State: WA
> Zip: 98111
> I assign the FullAddress field to a textbox in Reporting Services. I resize
> it to be several lines high. The result I get is something like this:
> 123 Main StreetSuite3Bellevue
> WA 98111
> I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> double spaces. Does RS handle CHAR() output? Do I need to use separate fields?|||Thanks. Your answer got me going in the right direction. I didn't get the
Chr(10) to work in the actual field expression, but chr(10) + chr(13) did.
For the benefit of future thread readers...I went a step further and created
a code block in the Report|Report Parameters command to make things a little
cleaner. It easily handles the common case where the second address line is
empty (Note: my query returns an empty string rather than a NULL if there is
no Address2). My code is this:
Function BuildAddress(ByVal A1 As String, ByVal A2 As String, ByVal C AS
String) As String
Dim strReturn As String
=Code.BuildAddress(Fields!Address.Value, Fields!Address2.Value,
Fields!CSZ.Value)
If A2.Length > 0 Then
strReturn += A2 + chr(10) + chr(13)
End If
strReturn += C
Return strReturn
End Function
And then I used the following expression in the textbox:
=Code.BuildAddress(
"David Bienstock" wrote:
> I've done this before but in the actual field expression not in the query.
> Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
> "Rick" wrote:
> > I am making a query for mailing labels that I will generate in Reporting
> > Services. I have a SQL Server stored procedure that returns the address using
> > SQL like this
> >
> > SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> > + Zip AS FullAddress
> > FROM ...
> >
> > Assume the data is:
> > Address: 123 Main Street
> > Address2: Suite 3
> > City: Bellevue
> > State: WA
> > Zip: 98111
> >
> > I assign the FullAddress field to a textbox in Reporting Services. I resize
> > it to be several lines high. The result I get is something like this:
> >
> > 123 Main StreetSuite3Bellevue
> > WA 98111
> >
> > I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> > double spaces. Does RS handle CHAR() output? Do I need to use separate fields?

Does Reporting Services allow you to select a dataset dynamically?

I have a remote report ...
I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.)
Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure.
I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?

Thanks in advance!
Your query can be expression-based, e.g.; =Iif(Parameters!SomeParameterValue = 0, "EXEC sp1 1<parameter list>", EXEC sp2 1<parameter list>"). Alternatively, if the sp choice can be based on a configuration convention, you can use the Report Server web.config file to store the config value. The later case is demonstrated by the DynamicConnection report in this download. The report uses a dynamic connection string but the same approach can be applied to the query.|||That worked. Thanks!
sql

Wednesday, March 21, 2012

Does RAISERROR cause performance to go down slightly?

I was curious if using RAISERROR in the catch block of a stored procedure does actually causes some hit on performance? I think it would, as compared to simply returning an error code in this sp's output parameter.

It will cause some impact as RAISEERROR will inevitably require some extra resource, where as you rightly say an error code just requires a value to be set within an existing block of memory.

Monday, March 19, 2012

Does not have permission to run DBCC CHECKIDENT

I have created a stored procedure in which i have created a temproray table with 2 fields, one is IDENTITY column and i set the seed by using DBCC CHECKIDENT but it gave an error about permission.

Please give suggestion for resolving this issue.

DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role

Pls check the ONLINE Document for more details..

Friday, March 9, 2012

Does Database Exist?

How can I run a query to see if a database exists?
I see the queries like using the sysobjects and xtype to query
different table names and stored procs...but can I use something
similar to see if a database exists?
Something along the lines of: if exists(Select * from sysobjects where
xtype = 'database' Where database name = 'Client1DB')?IF DB_ID('foo') IS NULL
PRINT 'does not exist';
ELSE
PRINT 'exists';
"INeedADip" <ineedadip@.gmail.com> wrote in message
news:1151520416.422554.20030@.j72g2000cwa.googlegroups.com...
> How can I run a query to see if a database exists?
> I see the queries like using the sysobjects and xtype to query
> different table names and stored procs...but can I use something
> similar to see if a database exists?
> Something along the lines of: if exists(Select * from sysobjects where
> xtype = 'database' Where database name = 'Client1DB')?
>|||Thanks.

Sunday, February 26, 2012

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.
Steve,
You may find an explanation of this at:
http://support.microsoft.com/default...b;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>
|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/default...b;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> i've
statement.
>
|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the[vbcol=seagreen]
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
msg
> statement.
>

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.Steve,
You may find an explanation of this at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> >
> > sql2k, nt5.
> > I have a stored procedure drop and re-create webtask... this is the msg
> i've
> > got when running it.
> >
> > Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL
statement.
> > [SQLSTATE 42000]
> >
> > does anyone know what it's about? thank you.
> >
> >
>|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> > Steve,
> >
> > You may find an explanation of this at:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
> >
> > Hope it helps,
> > Russell Fields
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> > >
> > > sql2k, nt5.
> > > I have a stored procedure drop and re-create webtask... this is the
msg
> > i've
> > > got when running it.
> > >
> > > Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL
> statement.
> > > [SQLSTATE 42000]
> > >
> > > does anyone know what it's about? thank you.
> > >
> > >
> >
> >
>

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.Steve,
You may find an explanation of this at:
http://support.microsoft.com/defaul...kb;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/defaul...kb;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> i've
statement.[vbcol=seagreen]
>|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
msg[vbcol=seagreen]
> statement.
>

does anybody knows....

does anyone knows how to get all the stored procedure by using C#
You can query the INFORMATION_SCHEMA.PROCEDURES view to retrieve the names of the stored procedures.
Check out Dave Penton'sINFORMATION_SCHEMA View Examples web page for some code on how to do this in C#.
|||

Hi,
If you run the below statement you wil get the list of stored procedures in that database.

SELECT * FROM SYSOBJECTS WHERE xtype = 'P'

Eralper
http://www.kodyaz.com

|||

eralper wrote:
If you run the below statement you wil get the list of stored procedures in that database.
SELECT * FROM SYSOBJECTS WHERE xtype = 'P'

Directly querying the sys* tables is discouraged by Microsoft as they are not guaranteed to be backwards-compatible. The INFORMATION_SCHEMA views are the recommended approach. See this discussion among a few SQL Server MVPs (including Adam Machanic) on this issue:Use the sys tables or INFORMATION_SCHEMA tables?.

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 sy

min 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 sy

min 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 sy

min 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

Tuesday, March 27, 2012

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c

Sunday, March 25, 2012

Does Sql 2005 allow ORDER BY @Variable ?

Does Sql 2005 allow ORDER BY @Variable ?

Does Sql 2005 allow ORDER BY @Variable ?

Does SP have a max size?

Thursday, March 22, 2012

Does SETROWCOUNT 10 apply to only the sp in which it used or outside it also?

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

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

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

Does RS recognize CHAR(13)?

Does Reporting Services allow you to select a dataset dynamically?

Wednesday, March 21, 2012

Does RAISERROR cause performance to go down slightly?

Monday, March 19, 2012

Does not have permission to run DBCC CHECKIDENT

Friday, March 9, 2012

Does Database Exist?

Sunday, February 26, 2012

does anyone know about this err msg?

does anyone know about this err msg?

does anyone know about this err msg?

does anybody knows....

Does a user have EXEC permission for a stored procedure?

log backup overwrite

Blog Archive

About Me