AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
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|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> 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
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
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|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> 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
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
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

adding VbLf

When using the Xp_sendmail stored procedure from MS SQL 2000, I cannot use LF or CR of both otr VBnewLine in my messages.

For example

<code>
Trim(lblEmployee.Text) & " has requested a holiday. From " & CalendarPopupStartDate.SelectedDate & " till " & CalendarPopupStartDate.SelectedDate _
& "." & CARRIAGE RETURN WANTED &lblRequestedDays.Text & txtComments.Text
<code
where you find the CARRIAGE RETURN WANTED, I want to begin a new line. My email is not formated asked :(

any help?

Greetz,
Geoffyour looking for a CRLF

chr(13) & chr(10)|||yes I know. That is not the problem. The problem is that the xp_sendmail accepts a parameter @.message. this is build up from severall labels on my page. But I do not succeed in entering en CR or LF|||if your creating the message from a web page then use environment.newline to insert the CRLF|||SQL will parse it if you use CHAR(ascii)|||Yes I knwo that... I'll try to explain

When I build up a query I do this like this

Dim blabla as String = "Exec xp_sendmail @.message, @.and some others"
dim comBlaBla as sqlcommand(blabla , sqlconnection1)

----
Now when I fill up the parameter @. message and I use a char(10) or char(13) this is just printed as written in the string. I tried to use other characters that escaped these like \ and so one. Can someone give me an example of how I can add Lf or CR to a string that is passed as a parameter? Hopefully now you guys understand what I'm talking about.

Greets,
Geoff

Adding the parameters dynamicaly to SP

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

[red]When Sp is executed with link[/red]<A href="http://links.10026.com/?link=Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Swati Jain:

I get the error that nvarchar can not be converted int

Is your stored procedure parameter declared as an int or varchar?

|||

I tried all datatypes still I got the same error with sp as well as code

|||

Swati Jain:

I tried all datatypes still I got the same error with sp as well as code

Can you show us your input parameters for the stored procedures?

You would also have to specify the parameter value size as well to make sure there are no data type mismatch errors.

|||

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

AS

No Code is written for specifying the size of the datatype

|||

Swati Jain:

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

your stored procedures are declared with INTEGER input paramters, so you have to set the type for your parameters toSqlDbType.Int for both input parameters.

You should also set the size as well, I believe int datatypes in SQL Server is 8 bytes.

Adding Permissions

We created a database with approximately 80 tables and 170 stored procedures
.
We were not too bright to begin with and one thing we did not consider was
not using sa as the login. Now, we need to create a user that does not have
full sa privileges but can execute all stored procedures and view all data i
n
the tables. Some of the stored procedures use dynamic sql (sp_executesql). W
e
also will be creating a new database, which corresponds to a specific test
event, about two or three times a month.
Question I have is can we create one user that can access all the stored
procedures and tables in ALL databases (as they are created) and is there a
fast way of granting privileges to all these objects without having to go in
the permissions for each one?You can create a login and create a user which is in the data_reader role in
every database and grant it rights to execute all stored procedures.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
> We created a database with approximately 80 tables and 170 stored
> procedures.
> We were not too bright to begin with and one thing we did not consider was
> not using sa as the login. Now, we need to create a user that does not
> have
> full sa privileges but can execute all stored procedures and view all data
> in
> the tables. Some of the stored procedures use dynamic sql (sp_executesql).
> We
> also will be creating a new database, which corresponds to a specific test
> event, about two or three times a month.
> Question I have is can we create one user that can access all the stored
> procedures and tables in ALL databases (as they are created) and is there
> a
> fast way of granting privileges to all these objects without having to go
> in
> the permissions for each one?
>|||Also note that in SQL Server 2005, you can grant SELECT and EXECUTE
permissions at database level, so you don't need to perform grants for each
table or procedure.
Do you need to do this in all databases or only in databases of a certain
type? If the answer is all, consider setting this up in the model database.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:up6oLty7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> You can create a login and create a user which is in the data_reader role
> in every database and grant it rights to execute all stored procedures.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
> news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
>

Adding number of decimal places during table design

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

Adding new columns with check constraints using same batch

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @.Setup bit

Set @.Setup = 1

if @.Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

Interesting, it probably is an undocumented "fix" that exists pretty much because it is illogical to add a check constraint when something doesn't exist, so when they parse it (before they execute it) it fails.

I would personally just add the check constraint creation to the ADD column proc, if I were you. It would just require a parameter, and it would be easy enough. The parameter I would add would be @.checkCriteria. Then pass in 'MyField >= 1 and MyField <= 3)'

Then you can formulate the name (or at worst add another parameter as override) and build the statement in the proc. You can add the constraint when you are adding the column too:

create table test
(
testId int primary key
)
go
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

|||Another workaround might be this:

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0
exec('
Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)')

Steve Kass
Drew University
www.stevekass.com

|||

Thanks Louis and Steve.

I've just resolved problem. Since in Sql 2000 this problem did not exist (using same T-Sql code) I'd like to know if it's a bug...

Really, if I use Alter Table, instead of mySp_Add_Column to add new columns, I get error too... So if I can understand parser can't know what SP wants to do, why doesn't parser understand I'll add new column before of check constraints? Why can I add FKs on new fields while I can't add CKs?

It seems a parser bug...

Bye,

Giulio

|||

Really? When I execute:

create table test
(
testId int primary key
)
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

in a single batch, it works just fine. What error are you getting?

|||

ok, in this way it works...

Only when I add a column to an existing table and, after, I add a check constraint, all in one batch, I get error...

And only after I installed SqlSrv 2k5... on SqlSrv 2k it works fine...

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

Adding Field to Stored Procedure for Crystal Reports

Hello:

I have a stored procedure for generating our invoices in Crystal
Reports. I have added a new field to the SP, but when I try to add the
field to my Crystal Report invoice, the field isn't available in the
list. However, if I create a new, blank report using the same stored
procedure as the datasource, the field is available. I've seemingly
tried every iteration of "Verify Database" to no avail.

The obvious answer would be to simply drop the sp from my existing
report and then re-add it. However, if you do this, all your fields on
the report are dropped.

Any ideas?

Thanks,
ScottScott (scott@.oppligerbanking.com) writes:
> I have a stored procedure for generating our invoices in Crystal
> Reports. I have added a new field to the SP, but when I try to add the
> field to my Crystal Report invoice, the field isn't available in the
> list. However, if I create a new, blank report using the same stored
> procedure as the datasource, the field is available. I've seemingly
> tried every iteration of "Verify Database" to no avail.
> The obvious answer would be to simply drop the sp from my existing
> report and then re-add it. However, if you do this, all your fields on
> the report are dropped.

You probably get a better answer in a forum devoted to Crystal Reports.

In my shop we use ttx-files as the "database" for our report, so the
report has no connection to the SQL Server database. If one adds a column
to the result set, one has to add it to the TTX file too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding Field or Stored Procedure to MSDE

Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.

Thank you,Sure can. Check out the osql command-line utility, which lets you run individual T-SQL statements or script files. Or, there are several tools you can use to manage MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access.

Don|||If I write a stored procedure and then script it, how can I use osql to run the script and add the sp to a database on the server? Can you show me an example? Do I create a cmd file that contains the script?

Thank you,|||Here's how you run a script file:

osql -E -i "myScripts.sql"

And to run a line of T-SQL directly:

osql -E -Q "DROP DATABASE pubs"

There are a gaggle of options for osql, so you'll need to look at the docs to use the ones appropriate for your setup.

Don|||Can you show me a sample of a stored procedure to add a field to a table that is already in a database?

Thanks again for your help,|||

CREATE PROCEDURE MyProc
AS
 ALTER TABLE myTable ADD newCol VARCHAR(20) NULL
GO

Adding Execute permissions on Stored Procedures

How can i add Execute permissions on the Stored Procedures under the
created user permission iusing SQLDMO ?hi,
arfanameer@.hotmail.com wrote:
> How can i add Execute permissions on the Stored Procedures under the
> created user permission iusing SQLDMO ?

Dim oProcedure As SQLDMO.StoredProcedure
Dim oServer As SQLDMO.SQLServer
' connect to the server, etc...

'get a pointer to the procedure..
Set oProcedure = oServer.Databases("Pubs").StoredProcedures("byroyalty")
'grant EXEC permission to an existing user
oProcedure.Grant SQLDMOPriv_Execute, "user_name"

Set oProcedure = Nothing
Set oServer = Nothing

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply

Adding execute permission to stored procedures

Hi

I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.

For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.

In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.

Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.

If anyone can point me in the right direction on this...

I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?

Thanking you in advance

A db owner can execute, alternatively you could add server roles to the login such as sysadmin may be overkill though

Function SetLoginsDbOwner()Dim strcmdAsString

strcmd = "exec sp_addrolemember N'db_owner', N'MyLoginName_IWantSPExecutePerm'"

Dim dcAsNew OleDbConnection(connstringL)Dim cmdAsNew OleDbCommand(strcmd, dc)Try

dc.Open()

cmd.ExecuteNonQuery()

Catch exAs OleDbException

Label3.Text = ex.ToString()

Finally

dc.Close()

EndTryEndFunction

Adding entry to DB and getting unique ID at the same time

Hi all,

I'm writing a website with Cold Fusion and when a user submits a
request and it's stored in the MS SQL database, I want the unique ID
(Identity field in table) to be given to the user on screen plus
emailed to user.

Now can I store data to the database (where the ID is created) and
return this as a variable in the same statement? I've seen this done
on many websites, but I have no idea how to do it in one step.

Thanks,

Alex.Return to the user the value from @.@.identity (check this out in the BOL).
You can also have SQL trigger off the email if this fits within your
project's stated performance requirements. Send the email via
xp_smtp_sendmail(http://sqldev.net/xp/xpsmtp.htm) But basically your
requirement to do it in 1 step could all be handled with a stored procedure.

hth
Eric

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401291136.6ec0ee16@.posting.google.c om...
> Hi all,
> I'm writing a website with Cold Fusion and when a user submits a
> request and it's stored in the MS SQL database, I want the unique ID
> (Identity field in table) to be given to the user on screen plus
> emailed to user.
> Now can I store data to the database (where the ID is created) and
> return this as a variable in the same statement? I've seen this done
> on many websites, but I have no idea how to do it in one step.
> Thanks,
> Alex.

Adding Delete to Stored Procedures

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out

REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN

GOI dont believe that a simple answer like
DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
is what you are looking at. Can u please give us some more details on what is expected?|||From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:


declare @.err int
BEGIN TRAN
INSERT INTO TableB (col1, col2, col3, etc)
SELECT col1, col2, col3, etc)
FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

DELETE FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

COMMIT|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

ALTER PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
IF @.@.Error <> '0'

RETURN

GO|||inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.

Adding column to Merge Pub

I have a merge publication (SQL 2k) and needed to add a column to a
replicated table. I used the stored proc and settings below and it seemed
to work fine. When I synched with a subscriber, it seemed to have applied
the table modification first, then downloaded the updates that were on the
publication. I just want to make sure I am doing this correctly before I run
on the production database. Thanks.
David
exec sp_repladdcolumn @.source_object = 'ConfigFA'
, @.column = 'NextCaseNumber'
, @.typetext = 'int NOT NULL DEFAULT 1'
, @.publication_to_add = 'MCFIDataPub'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 1
That is exactly right, it has to do the table modification first to pull the
data back to the publisher and then merge it there. The data on the
publisher will already have the changed format.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u163fB4HGHA.2680@.TK2MSFTNGP09.phx.gbl...
>I have a merge publication (SQL 2k) and needed to add a column to a
>replicated table. I used the stored proc and settings below and it seemed
>to work fine. When I synched with a subscriber, it seemed to have applied
>the table modification first, then downloaded the updates that were on the
>publication. I just want to make sure I am doing this correctly before I
>run on the production database. Thanks.
> David
> exec sp_repladdcolumn @.source_object = 'ConfigFA'
> , @.column = 'NextCaseNumber'
> , @.typetext = 'int NOT NULL DEFAULT 1'
> , @.publication_to_add = 'MCFIDataPub'
> , @.force_invalidate_snapshot = 0
> , @.force_reinit_subscription = 1
>

Adding CLR stored procedure in ASP.Net website

Hi all,

I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.

You should be able to add the datbase project as part of the same solution as the ASP.NET web site. It is unlikely that if can be done within the same project.|||

I would not recommend mixing ASP and CLR stored procedures in one solution but you probably can do this. CLR stored procedures are deployed different way then T-SQL stored procedures or ASP web pages and they are very sensitive to server configuration (not all users can do this, server admin have to allow you to do this and server have to be setup to use CLR, and in most cases you need very high security right to deploy them) . If it is possible try to not use CLR and USE T-SQL, and from my experience it is almost always possible if you only access data on the server in your logic.

Anyway Good luck

Adding a variable to a stored procedure

Hi,

I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.

Help!!!

I want to add @.OrderBy at the end of :

------
SELECT *
FROM Annonces
WHERE CategoryID = @.CategoryID AND DateOnCreate >= @.DateFinale
------

My declaration :

Declare @.OrderBy nvarchar(50)

If @.Sort = 'date_de'
set @.OrderBy = ' ORDER BY DateOnCreate DESC'
Else
set @.OrderBy = ' ORDER BY DateOnCreate ASC'

You want your code to be built dynamically within in the SQL it sounds like.

I don't think you can do that. Variables can not have inline code in SQL.

|||

You can use a CASE in the ORDER BY with limited flexibility. Do a quick google search or search for my previous post. I think I posted a similar sample a while back.

|||

Dynamic SQL is the classic solution to this, if you need complete flexibility, as Kelsey said. CASE is way less flexible.

You can also write the query in SQLCLR code, if you're using SQL Server 2005 and need lots of flexibility.

Or you can wait until SQL Server 2008, which reportedly will have this capability.

Don

|||

Ok thanks,

Now, how can i add this line

--> AND dbo.fn_GetDistance(@.CodePostal, UserNumber) <= @.Dist <--

In this code :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + '''' + @.OrderBy

I tried many things but all failed.

Thanks|||

In fact, if the dataset is small, your front end is the best place to do this dynamic sorting.

|||

This should give you an idea.:

Declare @.stmtvarchar(1000), @.CodePostalvarchar(10), @.CategoryIDint, @.DateFinaledatetime, @.orderbyvarchar(10)Select @.CodePostal ='567', @.CategoryID = 10, @.DateFinale =getdate(),@.orderby =' name asc'select @.stmt ='SELECT * , dbo.fn_GetDistance(''' + @.CodePostal +''', UserNumber) AS DistanceFROM AnnoncesWHERE CategoryID =' +convert(Varchar,@.CategoryID) +' AND DateOnCreate >= ''' +convert(varchar,@.DateFinale) +'''' + @.OrderBy

|||

Hi,

I tried :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' AND dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) <=' + @.Dist + '' + @.OrderBy

But i get :

Conversion failed when converting the varchar value 'SELECT *, .........blablabla....... <=' to data type int.

I dont understand?

|||

ok i got it!!!

Finally...

My error was i tried to use an integer in a varchar statement.

So i use:

Set@.Distance =convert(Varchar, @.Dist)

And now i can do:

select@.stmt ='SELECT *, dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID ='+ @.CategoryID +' AND DateOnCreate >='''+ @.DateFinale +''' AND dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) <='+ @.Distance +''+ @.OrderBy

Adding a table, stored procedure, or view in push tranaction repl

We are using SQL 2k and use push replication. If I want to add a table, a
view or a stored procedure to a publishing table, view or stored procedure,
can I just create them using EM or query analyzer manually in both the
publishing table or subscribing table without involving replication?
If this is not a good way, what can I do to just push the new object over
without doing a snapshot?
Wing
Yes you can create them on both sides, but the table will not be
synchronized or kept current. To do this you need to use sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:982C7152-AF5A-4E21-A2E0-58A48C894FBC@.microsoft.com...
> We are using SQL 2k and use push replication. If I want to add a table, a
> view or a stored procedure to a publishing table, view or stored
> procedure,
> can I just create them using EM or query analyzer manually in both the
> publishing table or subscribing table without involving replication?
> If this is not a good way, what can I do to just push the new object over
> without doing a snapshot?
> Wing
|||You can create the article on the publisher as per usual then use
sp_addarticle and sp_addsubscription followed by running the snapshot agent
and distribution agent. You mention that you don't want a new snapshot, but
in the case of transactional replication, running the snapshot agent in this
case will just create a snapshot of the new article and not all the others.
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We will be running a merge replication to multiple laptop subscribers. Will
this same concept apply to us? e.g. would we run the sp_addarticle and
sp_addsubscription ? And would we need to run a new snapshot?
Also, is there a way to retrieve all of the names of the subscribers for a
publication? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23wsrAKhDGHA.3820@.TK2MSFTNGP12.phx.gbl...
> You can create the article on the publisher as per usual then use
> sp_addarticle and sp_addsubscription followed by running the snapshot
> agent and distribution agent. You mention that you don't want a new
> snapshot, but in the case of transactional replication, running the
> snapshot agent in this case will just create a snapshot of the new article
> and not all the others.
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You use sp_addmergearticle. A new snapshot is generated. IIRC if the article
is part of a join filter an entire new snapshot will be genereated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:uXjH6cvDGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We will be running a merge replication to multiple laptop subscribers.
> Will this same concept apply to us? e.g. would we run the sp_addarticle
> and sp_addsubscription ? And would we need to run a new snapshot?
> Also, is there a way to retrieve all of the names of the subscribers for a
> publication? Thanks.
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23wsrAKhDGHA.3820@.TK2MSFTNGP12.phx.gbl...
>
|||David,
try sp_helpsubscription for the list of subscriptions.
For merge you can use sp_addmergearticle or just add the article through the
publication properties dialog box. You'll still need to run the snapshot
agent but it works differently and a whole snapshot is generated, even
though only the new article is applied by the merge agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A new snapshot of your entire merge publication is always forced.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZF%23FgvDGHA.3828@.TK2MSFTNGP14.phx.gbl...
> You use sp_addmergearticle. A new snapshot is generated. IIRC if the
> article is part of a join filter an entire new snapshot will be
> genereated.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:uXjH6cvDGHA.1676@.TK2MSFTNGP09.phx.gbl...
>