sql report manager: procedure

Showing posts with label procedure. Show all posts

Thursday, March 22, 2012

Can't seem to get the newly created entry's identity field :(

Hi there,

I have a stored procedure that executes a transaction which updates/inserts into a table on a remote server and should then use that new entry's autogenerated primary key to update another table.
I try to store that key into a variable called @.NewlyCreatedPastelDClinkNumber but to no avail

I have used the @.@.Identity approach and now try to manually get that key to from other information but still no go..My thinking is: since i am using transactions, maybe the entry cant be found until the transaction has been committed...but surely it should find it after the commit statement? Here is my code: I have marked the interesting bit with blue for simplicity.

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

DECLARE @.PastelDCLinkNumber int
DECLARE @.ClientID int
DECLARE @.Name varchar(25)
DECLARE @.Surname nvarchar(25)
DECLARE @.POBoxAddr1 varchar(40)
DECLARE @.POBoxAddr2 varchar(40)
DECLARE @.PostalCode varchar(5)
DECLARE @.ClientCreditCardNumber nvarchar(50)
DECLARE @.NewlyCreatedPastelDClinkNumber int
DECLARE PastelClientPortCursor CURSOR FOR

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

OPEN PastelClientPortCursor;
FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber,@.ClientCreditCardNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN

--Step2 insert/update the client information into the Synergy invoice and client table
BEGIN TRANSACTION T1
IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET
Name = (@.Name + ' ' + @.Surname),
Account = (@.Name + ' ' + @.Surname),
Post1 = @.POBoxAddr1,
Post2 = @.POBoxAddr2,
PostPC = @.PostalCode,
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber
WHERE DCLink = @.PastelDCLinkNumber
End

ELSE
Begin
--Insert into the Pastel Client table
INSERT INTO [server].[Satra Corporate (Pty) Ltd].dbo.Client
(Account, Name, Post1, Post2, PostPC, AccountTerms, CT, Credit_Limit,
RepID, Interest_Rate, Discount, On_Hold, BFOpenType, BankLink,
AutoDisc,
DiscMtrxRow, CashDebtor, DCBalance, CheckTerms, UseEmail,
iCountryID, cAccDescription, iCurrencyID, bStatPrint,
bStatEmail, bForCurAcc,
fForeignBalance, bTaxPrompt, iARPriceListNameID)
VALUES
(
SUBSTRING(@.Name, 1,19), --Account
(@.Name + ' ' + @.Surname), --Name
@.POBoxAddr1, --Post1
@.POBoxAddr2, --Post2
@.PostalCode, --PostPC
0, --Account_Terms
1, --CT
0,--Credit_Limit
0, --Rep_ID
0, --interest_Rate
0, --Discount
0, --On_Hold
0, --BFOpenType
0, --BankLink
0, --AutoDisc
0, --DiscMatrix
0, --CashDebtor
0, --DCBalance
1, --CheckTerms
0, --UseEmail
0, --iCountryID
'Credit Card Number: ' + @.ClientCreditCardNumber,
0, --iCurrencyID
1, --StatPrint
0, --StatEmail
0, --bForCurAcc
0, --ForeignBal
1, --bTaxPrompt
1 --iARPriceListNameID
)
End

--If the rowcount is 0, an error has occured
IF @.@.ROWCOUNT <> 0
Begin
COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber, @.ClientCreditCardNumber
END
CLOSE PastelClientPortCursor;
DEALLOCATE PastelClientPortCursor;

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

You should use a remote sproc to do the update/insert stuff on the remote server. MUCH more efficient. Pass parameters in and then local stuff does work with a cached query plan and only one network trip.

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

Can't seem to get the newly created entry's identity field :(

Hi there,

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

--Update the Pastel Client table

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mikesql

Can''t seem to get the newly created entry''s identity field :(

Hi there,

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

--Update the Pastel Client table

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

cant seem to connect to a store procedure on SQL server

Hi,

I am trying to get a page to call on a store procedure in SQL server and can seem to get it right. Any advice would be appreiated

<%@. Page Language="vb" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.OleDb" %>
<script runat="server"
 Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False")
 Sub Submit(Sender as Object, e as EventArgs)
 Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)
 Dim objReader as OleDbDataReader
 objCmd.CommandType = CommandType.StoredProcedure
 Try
 objCmd.Connection.Open()
 response.write ("opening data")
 objReader = objCmd.ExecuteReader
 catch ex as OleDbException
 Response.write ("Error retrieving data.")
 end try
 DataGrid1.DataSource = objReader
 DataGrid1.DataBind()
 objCmd.Connection.Close()
 End Sub
</script>
<html>
<head>
 <title></title>
</head>
<body>
 <form method="post" runat="server">
 <tbody>
 <tr>
 <td>
 <br />
 </td>
 </tr>
 <tr>
 <td>
Login :
 </td>
 <td>
 <asp:textbox id="user" runat="server"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
Password :
 </td>
 <td>
 <asp:textbox id="Pass" runat="server" textmode="Password" MaxLength="10"></asp:textbox>
 <br />
 </td>
 </tr>
 <tr>
 <td>
 </td>
 <td>
 <asp:button id="Button1" onclick="submit" runat="server" text="submit"></asp:button>
 </td>
 </tr>
 <br />
 <tr>
 <td>
 </td>
 </tr>
 </tbody>
 <br />
 <asp:DataGrid id="DataGrid1" runat="server" HorizontalAlign="Left" Font-Names="Arial" ItemStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#CCCCCC" Font-Size="10pt" Font-Name="Arial" width="755px" cellpadding="2" gridlins="vertical" BorderColor="Black" AutoGenerateColumns="False">
 <HeaderStyle font-size="Small" font-names="Arial" font-bold="True"></HeaderStyle>
 <EditItemStyle horizontalalign="Left"></EditItemStyle>
 <AlternatingItemStyle backcolor="#CCCCCC"></AlternatingItemStyle>
 <ItemStyle horizontalalign="Right" backcolor="White"></ItemStyle>
 <Columns>
 <asp:BoundColumn DataField="invnum" HeaderText="<b>Invoice Number</b>"></asp:BoundColumn>
 <asp:BoundColumn DataField="invdate" HeaderText="Invoice Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
 <asp:BoundColumn DataField="duedate" HeaderText="Due Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
 <asp:BoundColumn DataField="invamt" HeaderText="Invoice Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
 <asp:BoundColumn DataField="payamt" HeaderText="Pay Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
 </Columns>
 </asp:DataGrid>
 </form>
</body>
</html>

The store proceudure takes 2 int parameters. I have coded it inline in order to test it. I also will implement validation once I get this working

Thanksuse sqlclient instead oledb. there is built in support for sql server.
heres some sample code :


Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim objcon as new sqlconnection("...") ' check www.connectionstrings.com for the right connection string
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "usp_Test"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))
myCommand.Parameters("@.userid").Value = Trim(userid)
'add other params
dim objreader as
If objCon.State = 0 Then objCon.Open()
objreader = mycommand.ExecuteReader() ' objdatareader is your sqldatareader.
myCommand.dispose()

hth

Tuesday, March 20, 2012

Cant see stored procedures

Hi,

Can anyone help me. I've created a stored procedure in sql server and I'm trying to run it from my asp.net page. On Database Explorer I can't see it, or any for that matter, however I can see tables in the same schema.

Also I can't see it when I build a table adapter either.

Can anybody help?

Thanks

Sam

My first suspicion is that you didn't succeed in creating the stored procedure as you think you have done. To allay that suspicion, can you see the proc in Sql Server Management Studio?

|||

Yes I can see it and execute it, it works fine through sql server

I'm thinking that it might be because I'm using the express versions of the software sql server studio and visual web developer. Do you think that might be limiting the functionality?

|||

No. That wouldn't limit what you can see in the Database explorer. I've just attached Northwind to SqlExpress, and can see the procs, views etc in DataBase Explorer in VWD.

Try clicking the Refresh button at the top of the Database Explorer panel.

|||

check you master database mybe you created it in default database which is usual master when you open New Query window.If it is true put line below on top of your proceureUSE [DATABASE NAME] GO

|||

As JPazgier mentioned you probably created the proc in the default master db. If so, script out the proc, drop the proc from master db, re-create it in the actual db. If that is not the case and the proc is already in the right database, then check your connection string to see if your application is connecting to the right database.

Can't see stored proc results

I have this stored proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@.caseID [nvarchar]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)
END

When I run this with an EXEC statement, the result pane shows no results but the message pane says it completed successfully and one row is affected. I know my input data is good. I also get nothing when I call this sproc from a VB front end. Any ideas?

Thanks.

What did you mean by --Insert statements for procedure here ? Is there something which you commented out for posting here ? The insert can prdouce x rows affected also as the Select part can. So the insert suceeded with X rows, but the query didn′t bring back any results.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||All comment lines in this post are from the template produced when I created the sproc in SQL Server 2005. The code posted is copied directly from my sproc - nothing deleted, altered or commented out.|||

I found the problem.

@.caseID [nvarchar] was changed to:

@.caseID nvarchar(12)

This ran correctly.

Thanks for your reply Jens.

can't see print messages during the running a of stored procedure

I have a stored procedure running from Query Analyzer that runs for a long
time and would like to have status messages print out during the procedure.
They all seem to come when the procedure is complete - is there any way to
have them print while it is running. I 've actually tried having the main
SP call a smaller SP with prints, but they don't come out till the end as
well.
Help to a new Transact SQL writer,
Thanks
TW
TW,
I don't know how to do just what you want, but you could try pumping your
messages into a trace table. Then you could query that table while the
long-running stored procedure continues. Transaction isolation levels would
affect how this works for you.
Russell Fields
"Weave" <tweaver60@.suscom.net> wrote in message
news:10apu6dm256v76@.corp.supernews.com...
> I have a stored procedure running from Query Analyzer that runs for a long
> time and would like to have status messages print out during the
procedure.
> They all seem to come when the procedure is complete - is there any way to
> have them print while it is running. I 've actually tried having the main
> SP call a smaller SP with prints, but they don't come out till the end as
> well.
> Help to a new Transact SQL writer,
> Thanks
> TW
>
|||You could use RAISERROR and the NOWAIT option instead of PRINT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Weave" <tweaver60@.suscom.net> wrote in message news:10apu6dm256v76@.corp.supernews.com...
> I have a stored procedure running from Query Analyzer that runs for a long
> time and would like to have status messages print out during the procedure.
> They all seem to come when the procedure is complete - is there any way to
> have them print while it is running. I 've actually tried having the main
> SP call a smaller SP with prints, but they don't come out till the end as
> well.
> Help to a new Transact SQL writer,
> Thanks
> TW
>

Monday, March 19, 2012

Cant save my stored procedure using VS2005

Hi!

I'm using VS2005 and trying to save my very simple stored procedure.
The error message "Invalid object name 'dbo.xxxxx' just pop's up.

The SP is written through the Server Explorer > Stored Procedures > Add new... etc.
Using SQL server 2000.

Don't think this has to do with the SP-code but here it is:

ALTER PROCEDUREdbo.KontrollUnikPersNr
@.PersNrnvarchar(50) =null OUTPUT
AS
SELECT@.PersNr = PersNrFROMUserWHEREPersNr = @.PersNr
RETURN@.@.ROWCOUNT

Thanx i advance for any help!

Looks like you are trying to ALTER a proc that does not exist. Try changing the ALTER to CREATE and see if it helps.

Also, I dont understand what you are trying to return from the stored proc - the rowcount or the value from the database?

|||

Thanx a lot!

I changed the Alter to Create and it works... didn't have to think of the in VS2003.

I'm returning the rowcount to see if there is any Users with the specific values that has to be unique in my user database table.

Thanks a lot again! Smile

|||

If you just need the count you could do it like this:

ALTER PROCEDURE dbo.KontrollUnikPersNr@.PersNrvarchar(50) =NULL,@.countint OUTPUTASBEGINSET NOCOUNT ONSELECT @.count =count(*)FROMUserWHERE PersNr = @.PersNrSET NOCOUNT OFFEND

|||

Hm, did'nt think of that... nice.

Thanx a lot again... let me know if I can help you with any tricky stuff.. erhm... Wink

Can't run Stored Procedure

I am trying to run a stored procedure from an SQL task:

First, here is the proc (execs fine in : SSMS query)

CREATE PROC [dbo].[sp_Hist_Max_Dates]

@.Interval varchar(8),

@.Name1 varchar(32),

@.Max_Date datetime OUTPUT

IF @.Interval = 'd'

BEGIN

SELECT @.Max_Date = MAX(Max_Daily_Date) FROM SiteTable WHERE Site_Name = @.Name1

END

ELSE IF @.Interval = 'm'

BEGIN

SELECT @.Max_Date = MAX(Max_Monthly_Date) FROM SIteTable WHERE SIte_Name = @.Name1

END

Here is the call from the Execute SQL Task:

EXEC sp_Hist_Max_Dates ?, ?, ? OUTPUT

Here are how the parameters are defined in the Execute SQL Task:

gv_VarInterval INPUT varchar 0

gv_Name1 INPUT varchar 1

gv_MaxDate OUTPUT datetime 2

(Variables used as input parameters defined as strings with package scope)

Here is the error I am getting:

[Execute SQL Task] Error: Executing the query "EXEC dbo.sp_Hist_Max_Dates ?, ?, ? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The connection is just OLEDB to SQL Server 2005. Any ideas why is this not working?

Thanks!

Kayda

Try adding the parameter name (as declared in the SP) and the '=' sign to the SQL command:

EXEC sp_Hist_Max_Dates @.Parametername1 = ?,@.Parametername2= ?, @.Parametername3 = ? OUTPUT

|||another cryptic error message? hardly surprising... check if the account under which a package is run, has rights to execute the stored procedure|||On your SQL Task, set the "BypassPrepare" to TRUE. Otherwise the validation tries to validate the ?'s, and can't.

cant run stored procedure

I trying to move one of my queries to stored procedure, i use sql server 2005

i went to the database, choosed "programability> stored procedure" and the "new stored procedure.

this is what i got after changing the text:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================create PROCEDURE [ShoreshDBUser].[shoreshsearch]-- Add the parameters for the stored procedure here@.tablenamevarchar(50), @.startpointint, @.wordvarchar(50),@.order varchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT L.*FROM (SELECT TOP (@.startpoint + 20) *FROM ShoreshDBUser.shoreshsearchWHERE FREETEXT (*, @.word)ORDER BY @.order desc) LLEFTJOIN (SELECT TOP (@.startpoint) *FROM ShoreshDBUser.shoreshsearchWHERE FREETEXT (*, @.word)ORDER BY @.order desc) RON L.id = R.idWHERE (R.idIsNULL)ORDER BY L.idEND

then i tried to run the procedure, i clicked "excute", enter the parameters, and i got error massege; Msg 208, Level 16, State 3, Procedure shoreshsearch, Line 19

Invalid object name 'ShoreshDBUser.shoreshsearch'.

i tried to call it from the asp.net code, but i get the same error: Invalid object name 'ShoreshDBUser.shoreshsearch'

but if i try to create new procedure in the same name, i get error massege: there is a object in that name.

i just can't understand what i do wrong... can any body help me?

The 2 part name means <objectOwner>.<objectName>. So by your script, SQL Server thinks [ShorehDbUser] is the owner of the object. Do you have any user with that name? Generally objects are prefixed with DBO as the owner. SO modify your proc to

CREATE PROCEDURE dbo.[shoreshsearch].

You might also want to drop the previous one, because you will end up with 2 procs - one with dbo as the owner and the other with [ShoreshDBUser] as the owner. This will further cause confusion to application or other users.

DROP PROC [ShoreshDBUser].[shoreshsearch] to drop the old one.

Sunday, March 11, 2012

Can't run SP with GETDATE()

I'm having a problem running this stored procedure that is supposed to count
the "Failed Domains" based on the beginning date and end date.
I'd like to simply run this sp with getdate()-2 and getdate-1, but when I
run this:
Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
I get the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
The Stored Procedure:
CREATE PROC PROC_SuccessCountByDate
@.begdate datetime,
@.enddate datetime
as
select count(domain_) as 'Failed Domain Count', T1m.domain_
from Table1 T1
join Table2 T2
on T2.memberid = T1.memberid_
where T2.completionstatusid in (301, 303)
and T2.finalattempt > @.begdate
and T2.finalattempt < @.enddate
group by T1.domain_
having count(domain_)>5
order by 'Failed Domain Count' descDeclare @.Today datetime
Declare @.Yesterday datetime
SET @.Today = Getdate()
SET @.Yesterday = DATEADD(dd,-1,Getdate())
Exec dbo.PROC_SuccessCountByDate @.Yesterday,@.Today
HTH, Jens SUessmeyer.
"savvy95" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
news:058453F4-9FE1-440E-ADB0-67D284B6957C@.microsoft.com...
> I'm having a problem running this stored procedure that is supposed to
> count
> the "Failed Domains" based on the beginning date and end date.
> I'd like to simply run this sp with getdate()-2 and getdate-1, but when I
> run this:
> Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
> I get the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
>
> The Stored Procedure:
> CREATE PROC PROC_SuccessCountByDate
> @.begdate datetime,
> @.enddate datetime
> as
> select count(domain_) as 'Failed Domain Count', T1m.domain_
> from Table1 T1
> join Table2 T2
> on T2.memberid = T1.memberid_
> where T2.completionstatusid in (301, 303)
> and T2.finalattempt > @.begdate
> and T2.finalattempt < @.enddate
> group by T1.domain_
> having count(domain_)>5
> order by 'Failed Domain Count' desc
>|||Yes, you can;t pass an "Expression". like [getdate() - 1], as a parameter
to a Stored Proc.
You can only pass constant values, or variables (either the variable value
or address)
So in your case, create a variable (Declare @.MyDateTime DateTime) and set
the value of that variableto getdate() - 1, and then make your call using th
e
variable...
Exec dbo.PROC_SuccessCountByDate @.MyDateTime, getdate()
And get rid of the extra parentheses after the comma
Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
"savvy95" wrote:

> I'm having a problem running this stored procedure that is supposed to cou
nt
> the "Failed Domains" based on the beginning date and end date.
> I'd like to simply run this sp with getdate()-2 and getdate-1, but when I
> run this:
> Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
> I get the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
>
> The Stored Procedure:
> CREATE PROC PROC_SuccessCountByDate
> @.begdate datetime,
> @.enddate datetime
> as
> select count(domain_) as 'Failed Domain Count', T1m.domain_
> from Table1 T1
> join Table2 T2
> on T2.memberid = T1.memberid_
> where T2.completionstatusid in (301, 303)
> and T2.finalattempt > @.begdate
> and T2.finalattempt < @.enddate
> group by T1.domain_
> having count(domain_)>5
> order by 'Failed Domain Count' desc
>|||declare @.bd datetime
declare @.ed datetime
set @.bd = dateadd(day, -1, getdate())
set @.ed = getdate()
Exec dbo.PROC_SuccessCountByDate @.bd, @.ed
...
AMB
"savvy95" wrote:

> I'm having a problem running this stored procedure that is supposed to cou
nt
> the "Failed Domains" based on the beginning date and end date.
> I'd like to simply run this sp with getdate()-2 and getdate-1, but when I
> run this:
> Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
> I get the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
>
> The Stored Procedure:
> CREATE PROC PROC_SuccessCountByDate
> @.begdate datetime,
> @.enddate datetime
> as
> select count(domain_) as 'Failed Domain Count', T1m.domain_
> from Table1 T1
> join Table2 T2
> on T2.memberid = T1.memberid_
> where T2.completionstatusid in (301, 303)
> and T2.finalattempt > @.begdate
> and T2.finalattempt < @.enddate
> group by T1.domain_
> having count(domain_)>5
> order by 'Failed Domain Count' desc
>|||This is why I love this community; so many willing to help. Thanks. All of
you had the same solution.
Thanks again
"Jens Sü?meyer" wrote:

> Declare @.Today datetime
> Declare @.Yesterday datetime
> SET @.Today = Getdate()
> SET @.Yesterday = DATEADD(dd,-1,Getdate())
> Exec dbo.PROC_SuccessCountByDate @.Yesterday,@.Today
> HTH, Jens SUessmeyer.
> "savvy95" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:058453F4-9FE1-440E-ADB0-67D284B6957C@.microsoft.com...
>
>|||Perhaps one time there will be an issue which you can also solve in here...
:-)
"savvy95" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@.microsoft.com...
> This is why I love this community; so many willing to help. Thanks. All
> of
> you had the same solution.
> Thanks again
> "Jens Smeyer" wrote:
>|||i want to make SP that enters values into my table, which has the default
value of the date column to getdate
so, if i didn't enter a value it will take the default value of the column
how can i describe this in the sp declaration ?
my sp is as follows :
---
create procedure add_new_abstract
@.abs_name nvarchar(20) ,
@.id_reg nvarchar(10),
@.id_topic nvarchar(10),
@.id_stat nvarchar(10),
@.abst nvarchar(20) ,
@.job_id nvarchar(20),
@.date_abs datetime
as
insert abstract
values (cast(cast(rand()*9285 as int)as
nvarchar(10)),@.abs_name,@.id_reg,@.id_topi
c,@.id_stat,@.abst,@.job_id,@.date_abs)
go
---
so, when i execute it with the getdate() it raise the error specified in
this discussion
and when i neglect it so that it takes the value as the default value of the
column , it asks for it
so, how can i solve this ?
thanx for ur help
--
regards
Maidoo.
"Jens Sü?meyer" wrote:

> Perhaps one time there will be an issue which you can also solve in here..
.
> :-)
>
> "savvy95" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@.microsoft.com...
>
>|||On Fri, 10 Jun 2005 10:59:27 -0700, maidoo wrote:

>i want to make SP that enters values into my table, which has the default
>value of the date column to getdate
>so, if i didn't enter a value it will take the default value of the column
>how can i describe this in the sp declaration ?
Hi maidoo,
Change your proc like this:
create procedure add_new_abstract
@.abs_name nvarchar(20) ,
@.id_reg nvarchar(10),
@.id_topic nvarchar(10),
@.id_stat nvarchar(10),
@.abst nvarchar(20) ,
@.job_id nvarchar(20),
@.date_abs datetime = null
as
insert abstract
values (cast(cast(rand()*9285 as int)as
nvarchar(10)),@.abs_name,@.id_reg,@.id_topi
c,@.id_stat,@.abst,@.job_id,
COALESCE(@.date_abs, CURRENT_TIMESTAMP))
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||create procedure add_new_abstract
@.abs_name nvarchar(20) ,
@.id_reg nvarchar(10),
@.id_topic nvarchar(10),
@.id_stat nvarchar(10),
@.abst nvarchar(20) ,
@.job_id nvarchar(20),
@.date_abs datetime
as
SET @.date_abs = ISNULL(@.date_abs, GETDATE())
insert abstract
values (cast(cast(rand()*9285 as int)as
nvarchar(10)),@.abs_name,@.id_reg,@.id_topi
c,@.id_stat,@.abst,@.job_id,@.date_abs)
go
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maidoo" <maidooalex@.msn.com> wrote in message
news:B70444AF-0A65-416E-91DB-05E4D911809E@.microsoft.com...
>i want to make SP that enters values into my table, which has the default
> value of the date column to getdate
> so, if i didn't enter a value it will take the default value of the column
> how can i describe this in the sp declaration ?
> my sp is as follows :
> ---
> create procedure add_new_abstract
> @.abs_name nvarchar(20) ,
> @.id_reg nvarchar(10),
> @.id_topic nvarchar(10),
> @.id_stat nvarchar(10),
> @.abst nvarchar(20) ,
> @.job_id nvarchar(20),
> @.date_abs datetime
> as
> insert abstract
> values (cast(cast(rand()*9285 as int)as
> nvarchar(10)),@.abs_name,@.id_reg,@.id_topi
c,@.id_stat,@.abst,@.job_id,@.date_abs
)
> go
> ---
> so, when i execute it with the getdate() it raise the error specified in
> this discussion
> and when i neglect it so that it takes the value as the default value of
> the
> column , it asks for it
> so, how can i solve this ?
> thanx for ur help
> --
> regards
> Maidoo.
>
> "Jens Smeyer" wrote:
>

Can't run my scheduled job which accesses a linked server: other options?

min."
So: How can I automatically run this stored procedure once per month
under these conditions?
Thanks,
RyanIn SQL 2000, change the job owner to sy

min.
In SQL 2005, change the job security context.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ryan_willow@.hotmail.com> wrote in message
news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
>I have a stored procedure in a database on a server, and this stored
> procedure uses a linked server.
> The login I was given is only granted DBO permissions on this database.
> I am trying to set this up so that the above stored procedure runs once
> per month, so I naturally tried to set up a Job which would do this.
> I have discovered from another group that:
> "You cannot access a linked server from a TSQL jobstep if you aren't
> sy

min."
> So: How can I automatically run this stored procedure once per month
> under these conditions?
> Thanks,
> Ryan
>|||I'm in SQL 2000.
I cannot change the job owner because my permissions do not allow this.
Apparently a dbo user named 'User' can only create a job with owner
'User'. I am trying to implement this without having to step outside
my dbo security level. Is this going to be possible, and if so how?
Thanks.
Arnie Rowland wrote:
> In SQL 2000, change the job owner to sy

min.
> In SQL 2005, change the job security context.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <ryan_willow@.hotmail.com> wrote in message
> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...|||That will not be possible without coordination with the server
administrator. Since Jobs have the potential for significant performance
issues as well as security issues for the server, the security context for
Jobs is sy

min. (in SQL2000).
And of course a SQL Server Administrator is probably not going to allow you
to have permission to create or change a job because of the potential that
it could execute any code or activity under the sy

min security context.
Jobs are a 'Server' level activity -NOT a database level activity. Even if a
job is confined to a single database, it still operates as a server level
activity.
That changes with SQL 2005.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ryan" <ryan_willow@.hotmail.com> wrote in message
news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
> I'm in SQL 2000.
> I cannot change the job owner because my permissions do not allow this.
> Apparently a dbo user named 'User' can only create a job with owner
> 'User'. I am trying to implement this without having to step outside
> my dbo security level. Is this going to be possible, and if so how?
> Thanks.
> Arnie Rowland wrote:
>|||Very informative, thanks Arnie.
Arnie Rowland wrote:
> That will not be possible without coordination with the server
> administrator. Since Jobs have the potential for significant performance
> issues as well as security issues for the server, the security context for
> Jobs is sy

min. (in SQL2000).
> And of course a SQL Server Administrator is probably not going to allow yo
u
> to have permission to create or change a job because of the potential that
> it could execute any code or activity under the sy

min security context.
> Jobs are a 'Server' level activity -NOT a database level activity. Even if
a
> job is confined to a single database, it still operates as a server level
> activity.
> That changes with SQL 2005.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ryan" <ryan_willow@.hotmail.com> wrote in message
> news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...

Can't run my scheduled job which accesses a linked server: other options?

I have a stored procedure in a database on a server, and this stored
procedure uses a linked server.
The login I was given is only granted DBO permissions on this database.
I am trying to set this up so that the above stored procedure runs once
per month, so I naturally tried to set up a Job which would do this.
I have discovered from another group that:
"You cannot access a linked server from a TSQL jobstep if you aren't
sysadmin."
So: How can I automatically run this stored procedure once per month
under these conditions?
Thanks,
RyanIn SQL 2000, change the job owner to sysadmin.
In SQL 2005, change the job security context.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ryan_willow@.hotmail.com> wrote in message
news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
>I have a stored procedure in a database on a server, and this stored
> procedure uses a linked server.
> The login I was given is only granted DBO permissions on this database.
> I am trying to set this up so that the above stored procedure runs once
> per month, so I naturally tried to set up a Job which would do this.
> I have discovered from another group that:
> "You cannot access a linked server from a TSQL jobstep if you aren't
> sysadmin."
> So: How can I automatically run this stored procedure once per month
> under these conditions?
> Thanks,
> Ryan
>|||I'm in SQL 2000.
I cannot change the job owner because my permissions do not allow this.
Apparently a dbo user named 'User' can only create a job with owner
'User'. I am trying to implement this without having to step outside
my dbo security level. Is this going to be possible, and if so how?
Thanks.
Arnie Rowland wrote:
> In SQL 2000, change the job owner to sysadmin.
> In SQL 2005, change the job security context.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> <ryan_willow@.hotmail.com> wrote in message
> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
> >I have a stored procedure in a database on a server, and this stored
> > procedure uses a linked server.
> >
> > The login I was given is only granted DBO permissions on this database.
> >
> > I am trying to set this up so that the above stored procedure runs once
> > per month, so I naturally tried to set up a Job which would do this.
> >
> > I have discovered from another group that:
> > "You cannot access a linked server from a TSQL jobstep if you aren't
> > sysadmin."
> >
> > So: How can I automatically run this stored procedure once per month
> > under these conditions?
> >
> > Thanks,
> > Ryan
> >|||That will not be possible without coordination with the server
administrator. Since Jobs have the potential for significant performance
issues as well as security issues for the server, the security context for
Jobs is sysadmin. (in SQL2000).
And of course a SQL Server Administrator is probably not going to allow you
to have permission to create or change a job because of the potential that
it could execute any code or activity under the sysadmin security context.
Jobs are a 'Server' level activity -NOT a database level activity. Even if a
job is confined to a single database, it still operates as a server level
activity.
That changes with SQL 2005.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Ryan" <ryan_willow@.hotmail.com> wrote in message
news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
> I'm in SQL 2000.
> I cannot change the job owner because my permissions do not allow this.
> Apparently a dbo user named 'User' can only create a job with owner
> 'User'. I am trying to implement this without having to step outside
> my dbo security level. Is this going to be possible, and if so how?
> Thanks.
> Arnie Rowland wrote:
>> In SQL 2000, change the job owner to sysadmin.
>> In SQL 2005, change the job security context.
>> --
>> Arnie Rowland, YACE*
>> "To be successful, your heart must accompany your knowledge."
>> *Yet Another Certification Exam
>>
>> <ryan_willow@.hotmail.com> wrote in message
>> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
>> >I have a stored procedure in a database on a server, and this stored
>> > procedure uses a linked server.
>> >
>> > The login I was given is only granted DBO permissions on this database.
>> >
>> > I am trying to set this up so that the above stored procedure runs once
>> > per month, so I naturally tried to set up a Job which would do this.
>> >
>> > I have discovered from another group that:
>> > "You cannot access a linked server from a TSQL jobstep if you aren't
>> > sysadmin."
>> >
>> > So: How can I automatically run this stored procedure once per month
>> > under these conditions?
>> >
>> > Thanks,
>> > Ryan
>> >
>|||Very informative, thanks Arnie.
Arnie Rowland wrote:
> That will not be possible without coordination with the server
> administrator. Since Jobs have the potential for significant performance
> issues as well as security issues for the server, the security context for
> Jobs is sysadmin. (in SQL2000).
> And of course a SQL Server Administrator is probably not going to allow you
> to have permission to create or change a job because of the potential that
> it could execute any code or activity under the sysadmin security context.
> Jobs are a 'Server' level activity -NOT a database level activity. Even if a
> job is confined to a single database, it still operates as a server level
> activity.
> That changes with SQL 2005.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Ryan" <ryan_willow@.hotmail.com> wrote in message
> news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
> > I'm in SQL 2000.
> >
> > I cannot change the job owner because my permissions do not allow this.
> > Apparently a dbo user named 'User' can only create a job with owner
> > 'User'. I am trying to implement this without having to step outside
> > my dbo security level. Is this going to be possible, and if so how?
> >
> > Thanks.
> >
> > Arnie Rowland wrote:
> >> In SQL 2000, change the job owner to sysadmin.
> >>
> >> In SQL 2005, change the job security context.
> >>
> >> --
> >> Arnie Rowland, YACE*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >> *Yet Another Certification Exam
> >>
> >>
> >> <ryan_willow@.hotmail.com> wrote in message
> >> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
> >> >I have a stored procedure in a database on a server, and this stored
> >> > procedure uses a linked server.
> >> >
> >> > The login I was given is only granted DBO permissions on this database.
> >> >
> >> > I am trying to set this up so that the above stored procedure runs once
> >> > per month, so I naturally tried to set up a Job which would do this.
> >> >
> >> > I have discovered from another group that:
> >> > "You cannot access a linked server from a TSQL jobstep if you aren't
> >> > sysadmin."
> >> >
> >> > So: How can I automatically run this stored procedure once per month
> >> > under these conditions?
> >> >
> >> > Thanks,
> >> > Ryan
> >> >
> >

Can't retrieve second resultset with jdbc sql server 2005 driver

Hi All,

My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple

Statement stmt = con.createStatement(); con is database connection

ResultSet rs = stmt.getResultSet();

//Do something

rs.close();

rs = stmt.getResultSet();

//Do something

Then I got the error: The result set is closed.

The code worked well with jdbc sql server 2000 driver.

Thanks for your help.

Eric

Hello lxiao,

The syntax for getting the second result set is

if(stmt.getMoreResults())

stmt.getResultSet();

Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:

CLOSE_CURRENT_RESULT

CLOSE_ALL_RESULTS

You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx

Regards,

Jaaved Mohammed

Can't retrieve second resultset with jdbc sql server 2005 driver

Hi All,

My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple

Statement stmt = con.createStatement(); con is database connection

ResultSet rs = stmt.getResultSet();

//Do something

rs.close();

rs = stmt.getResultSet();

//Do something

Then I got the error: The result set is closed.

The code worked well with jdbc sql server 2000 driver.

Thanks for your help.

Eric

Hello lxiao,

The syntax for getting the second result set is

if(stmt.getMoreResults())

stmt.getResultSet();

Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:

CLOSE_CURRENT_RESULT

CLOSE_ALL_RESULTS

You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx

Regards,

Jaaved Mohammed

Saturday, February 25, 2012

Cant pass search text into Stored Procedure

I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?
A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.

Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'

I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @.VisitorKeywords.

PROCEDURE dbo.KWsearch
AS
SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_Enabled
WHERE ( @.visitorKeywords ) AND (Companies.L_Enabled = 1)
ORDER BY Companies.L_Name

I am wanting the resulting WHERE portion to be:

WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)

Thank you

I noticed your starting character is '%something..'. Realize that this may not use any indexes on the L_Keywords column. Because your first character itself is a wildcard, SQL Server does not know where to start so it will not use the index. In order for indexes to be used the query should be like: 'something%'. Your query should look like this: Pass just the value to the proc. The value coming from your app for @.keyword1 would be '%sail%'.

SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( Companies.L_KeywordsLIKE @.KeyWord1AND Companies.L_KeywordsLIKE @.KeyWord2 )AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name

|||

I was able to pass in the values but I need to pass in the entire string because the number of LIKE statements is determined by the number of keywords the user enters. If they enter "sail boats", I need two LIKE statements but if they enter "big blue sail boats" I need four LIKE statements. That is why I was trying to pass in the entire string.

|||

Is it possible to pass a partial SQL statement string into a stored procedure and concantenate it with the resident code using a parameter?

|||

Yes you'd have to use dynamic SQL. Check out this article before attempting to do so:http://www.sommarskog.se/dynamic_sql.html

|||

Below is the routine I am using to create the partial WHERE statement. Does it present a secuity risk? I assumed that programically generating the SELECT in this way would offer greatly improved security.

What I am trying to do is pass the strings produced by this routine into the Stored Procedure I use to access the database.

protectedvoid KeyWordSqlSelect()
{
kwdString = UserKeyword.Text;
Criteria1 ="";
Criteria2 ="";
string[] ArrKwdString = System.Text.RegularExpressions.Regex.Split(kwdString," ");
int iCount = 0;
foreach (string sValuein ArrKwdString)
{
Criteria1 +=string.Format("tblCompany.L_Keywords LIKE '%{0}%'", sValue);
Criteria2 +=string.Format("tblFranchise.L_Keywords LIKE '%{0}%'", sValue);
if ((iCount + 1) < ArrKwdString.Length)
Criteria1 +=" AND ";
Criteria2 +=" AND ";
iCount += 1;
CKWsearch = Criteria1;
SKWsearch = Criteria2;
TextBox1.Text = Criteria1;
TextBox2.Text = Criteria2;
}

}

|||

Please check out the article link I posted. There's risks/performance issues that you need to get an understanding of with dynamic SQL.

|||

I read the article but do not see what risk you are suggesting. If the partial WHERE string is generated within the C# code I do not see the injection risk unless there is a problem with the " " space delimiter used in the Split method for the incoming keyword string.

|||

The biggest risk is the SQL Injection attack. Performance issues include bad query plans, not storing query plans if you dont use sp_Executesql etc.

|||

I somewhat understand what you are talking about now (only after reading the article three times). I have abandoned the stored procedure and now am generating the Select statements in the code behind accessing them directly using SqlCommand object. I am hoping that will protect me from injection attacks as none of them are exposed to the users. Thank you for the expert advice. Sql is not a place for amateurs like myself to be mucking about. Your sage advice is greatly appreciated.

|||

Welcome. Glad to help.

Cant pass 0 in stored procedure parameter

Hi
I have an if clause in my code to add the final parameter value to send to the database.

If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@.C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@.C_ID", Session("C_ID"))
End If

If the user is in the role, the error is triggered saying that @.C_IDis expected by the stored procedure. If i then change the value from 0to 10, the stored procedure works fine.
Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?
Thanking you in advance.Because the parameters.add is overridden based on what is passed (look at them when you hit the left parens). Since you are passing a numerical value of a low number, it thinks that it is the sqldbtype. You will need to cast the value to a string cStr(YOURVALUEHERE).
Nick

Sunday, February 12, 2012

can't install a CLR stored procedure

I have written 2 CLR stored procedures. I have a local copy of our database to which I was able to successfully deploy them. When I changed the connection string to our production database, it fails when I try to deploy. It gives me an error... EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database 'master'. I've logged onto the database server and set trustworthy on for that particular database. I also tried...

CREATE ASSEMBLY UpdateJobAdSearch FROM 'I:\DLL\Candidate.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Candidate.dll is the stored procedure dll. I also read on http://msdn2.microsoft.com/en-us/library/ms345106.aspx about doing something like....

USE master
GO

CREATE ASYMMETRIC KEY CandidateKey FROM EXECUTABLE FILE = 'I:\DLL\Candidate.dll'
CREATE LOGIN CandidateLogin FROM ASYMMETRIC KEY CandidateKey
GRANT EXTERNAL ACCESS ASSEMBLY TO CandidateLogin

but I get the errors...

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'CandidateKey', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'CandidateLogin', because it does not exist or you do not have permission.

I'm no db admin, so I'm not sure what I need to do to get these installed. I'm not clear on what they mean about creating a login mapped to an asymetric key. Can anyone clear this up in more plain english? Thanks!

Did you sign the Candidate.dll assembly? You can do that with Visual Studio or the .NET Framework 2.0 SDK.

|||

Not originally. I found the Sign option in properties so I checked off for it to sign it and gave it a new key name. But when I tried to deploy, I still got...

Error 1 EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database 'master'. JobAd

|||

After you signed the assembly, did you successfully create the asymetric key?

|||Bonnie, that's where I'm lost. I assume I have to create the asymetric key on the server? But when I go to Security>Asymetric Keys and right click, the only option I have is refresh. There's nothing there for me to create a new one. So, I'm not sure how to do that? Thanks!|||

You can create asymmetric keys using the DDL you mentioned above (CREATE ASYMMETRIC KEY). See the HandlingLOBUsingCLR sample for step by step instructions for how to install and use an assembly that requires external access. The latest samples MSI is located at http://msdn.microsoft.com/sql/downloads/samples/default.aspx. After you install the Samples MSI, by default you'll find the HandlingLOBUsingCLR sample at drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\HandlingLOBUsingCLR. In that folder will be a readme file that will walk you through it.

--Bonnie

|||

Thanks Bonnie, but I found this earlier and it worked great. Thanks for your help!

http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx

can't insert into ...because of nulls problme!

Hello,
I am trying to insert some records into a table from an SP but get this
message:
Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6
Cannot insert the value NULL into column 'CurEntryDate', table
'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I am inserting into only a few fields. How can I negotiate this?
Thanks,
RichYou cannot, unless you pass a value for every non-nullable column in the
table.
The column CurEntryDate sounds like a column that captures the insertion
time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
in the INSERT statement. If being passed through a stored procedure, declare
a local variable that has the current date/time value and pass it as an
input parameter.
You may also consult your database designer and see if he can set a default
on this column to eliminate the need to pass this value in every INSERT
statement.
Anith|||Thanks. What I did was to temporarily allow nulls in these fields. and the
n
restored the table back. Is this practice OK?
"Anith Sen" wrote:

> You cannot, unless you pass a value for every non-nullable column in the
> table.
> The column CurEntryDate sounds like a column that captures the insertion
> time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
> in the INSERT statement. If being passed through a stored procedure, decla
re
> a local variable that has the current date/time value and pass it as an
> input parameter.
> You may also consult your database designer and see if he can set a defaul
t
> on this column to eliminate the need to pass this value in every INSERT
> statement.
> --
> Anith
>
>|||If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that
without any success. My guess is that if a constraint was ever defined it wa
s
defined for a reason.
If you've now gone and inserted data through a backdoor, this just might
cause problems for you later on - i.e. with queries written against
non-nullable columns which now contain null values.
ML
http://milambda.blogspot.com/|||On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:

>Thanks. What I did was to temporarily allow nulls in these fields. and th
en
>restored the table back. Is this practice OK?
Hi Rich,
No, it definitely isn't.
There is probably a reason why this column is defined to not allow
NULLs. If you overide that restriction, you're probably entering invalid
data and endagnering the integrity of your database.
Would you also temporarly disable a CHECK constraint that allows only
'Male' and 'Female' in the gender column to enable the value 'Water' to
be entered there?
Hugo Kornelis, SQL Server MVP|||> Thanks. What I did was to temporarily allow nulls in these fields. and
> then
> restored the table back. Is this practice OK?
The practice doesn't make sense. Don't you think the NOT NULL constraints
are there for a reason? Why bother having them at all if you're just going
to remove them every time you insert data? Isn't this kind of like not
wearing your seatbelt?|||Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told
,
I am not at the guru level. I am going to have to pay my dues with a lot of
heart ache. The task was one of these add-hoc things, and I just started on
assuming the duties of this project which was already in motion for a while.
So I rushed! then I slowed down and created fake data ('' for varchars and
an agreed upon fake date for date fields) to accommodate the constraints.
That did the trick. The first reply said I can't insert records if nulls ar
e
not allowed unles I have some values. So I created the values. Anyway, I
really appreciate the replies. Even though I was suggesting ideas of an
amateur nature these replies will help to keep me on the proper course.
Rich
"Rich" wrote:

> Hello,
> I am trying to insert some records into a table from an SP but get this
> message:
> Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line
6
> Cannot insert the value NULL into column 'CurEntryDate', table
> 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I am inserting into only a few fields. How can I negotiate this?
> Thanks,
> Rich|||In case of lacking documentation maybe one of the users can help. And there'
s
got to be at least some shread of documentation. A leaflet, a greasy paper
napkin, something.
ML
http://milambda.blogspot.com/|||Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask
questions like this when we start out.
An amateur would have made the change and not bothered asking anyone if it
made sense.
One additional note though...
If you had removed the constraint in order to insert the null values, you
would not have been able to put the constraint back without first changing
the null values to actual values. If your data does not follow the rules of
the constraint you are trying to make, then the database will not let you
create the constraint.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@.microsoft.com...
> Thank you all for your input on this matter. I undid what I was going to
> try. Anyway, No, there is no documentation. I am on my own! Truth be
told,
> I am not at the guru level. I am going to have to pay my dues with a lot
of
> heart ache. The task was one of these add-hoc things, and I just started
on
> assuming the duties of this project which was already in motion for a
while.
> So I rushed! then I slowed down and created fake data ('' for varchars
and
> an agreed upon fake date for date fields) to accommodate the constraints.
> That did the trick. The first reply said I can't insert records if nulls
are
> not allowed unles I have some values. So I created the values. Anyway, I
> really appreciate the replies. Even though I was suggesting ideas of an
> amateur nature these replies will help to keep me on the proper course.
> Rich
> "Rich" wrote:
>
Line 6|||> Your idea was reasonable for someone new to database programming. You had
> some reservations and asked the right folks a question. We all have to as
k
> questions like this when we start out.
> An amateur would have made the change and not bothered asking anyone if it
> made sense.
I strongly agree. No one is born smart.
ML
http://milambda.blogspot.com/

Friday, February 10, 2012

cant get sqldatasource to return results from a stored procedure

I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.

This is the stored procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest]-- Add the parameters for the stored procedure here@.BeginDatedatetime ='1/1/2006', @.EndDatedatetime ='12/31/2006', @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...RETURN @.WinnerIDEND

It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.

Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.

Here's the code from the aspx codebehind file:

Partial Class Contest Inherits System.Web.UI.Page Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click Dim stuff As New System.Web.UI.DataSourceSelectArguments SqlDataSource1.Select(stuff) SqlDataSource1.DataBind() lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString End SubEnd Class

As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @.WinnerID parameter, but instead it sets it to "WinnerID".

Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> <asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa%>" SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" /> <asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" /> <asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" /> <asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>   <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

okay well i gave up on using a sqldatasource since nobody replied.

here's my final working code using an sqlCommand object instead:

PartialClass ContestInherits System.Web.UI.PageProtected Sub btnSelectWinner_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnSelectWinner.ClickDim objCommandAs New SqlClient.SqlCommandDim objConnAs New SqlClient.SqlConnectionDim strQueryResultAs New Object objConn.ConnectionString = ConfigurationManager.ConnectionStrings("appcheck-csConnectionString-sa").ConnectionString objCommand.Connection = objConn objCommand.CommandType = CommandType.StoredProcedure objCommand.CommandText ="AppcheckContest"Dim intWinnerIDParamAs New SqlClient.SqlParameter("@.WinnerID", SqlDbType.Int)Dim DateFromParamAs New SqlClient.SqlParameter("@.BeginDate", SqlDbType.DateTime)Dim DateToParamAs New SqlClient.SqlParameter("@.EndDate", SqlDbType.DateTime) intWinnerIDParam.Direction = ParameterDirection.Output DateFromParam.Direction = ParameterDirection.Input DateToParam.Direction = ParameterDirection.Input objCommand.Parameters.Add(intWinnerIDParam) DateFromParam.Value = Calendar_From.SelectedDate DateToParam.Value = Calendar_To.SelectedDate objCommand.Parameters.Add(DateFromParam) objCommand.Parameters.Add(DateToParam) objConn.Open() objCommand.ExecuteScalar() objConn.Close()Try lblWinnerID.Text ="Winner ID: <a target='_blank' href='http://forum.appcheck.net/web/user/Profile.aspx?UserID=" & intWinnerIDParam.Value & "'>" & intWinnerIDParam.Value & "</a>" Catch ex As Exception lblWinnerID.Text = "error"End Try End SubEnd Class

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> Contest Start Date:<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> Contest End Date:<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest] @.BeginDatedatetime, @.EndDatedatetime, @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGINSET NOCOUNT ON;SELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...END

|||Hi,

I've had a similar issue for the last few days.
I only found 1 page that helped me solve it:
http://dotnetjunkies.com/QuickStartv20/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParamsInsert.src

I hope this helps you.

Cheers
Luke

|||

The above posters link is correct. The output/return value parameters are only available during the "ED" events (Inserted/Updated/Deleted). So if you want them, you have to capture them during that event.

And just a FYI - NewID isn't random, not even close. Google a better random method, which usually involves taking the last 32 bits of newid and feeding it into the rand function.

Thursday, March 22, 2012

Can't seem to get the newly created entry's identity field :(

Can't seem to get the newly created entry's identity field :(

Can''t seem to get the newly created entry''s identity field :(

cant seem to connect to a store procedure on SQL server

Tuesday, March 20, 2012

Cant see stored procedures

Can't see stored proc results

can't see print messages during the running a of stored procedure

Monday, March 19, 2012

Cant save my stored procedure using VS2005

Can't run Stored Procedure

cant run stored procedure

Sunday, March 11, 2012

Can't run SP with GETDATE()

Can't run my scheduled job which accesses a linked server: other options?

Can't run my scheduled job which accesses a linked server: other options?

Can't retrieve second resultset with jdbc sql server 2005 driver

Can't retrieve second resultset with jdbc sql server 2005 driver

Saturday, February 25, 2012

Cant pass search text into Stored Procedure

Cant pass 0 in stored procedure parameter

Sunday, February 12, 2012

can't install a CLR stored procedure

can't insert into ...because of nulls problme!

Friday, February 10, 2012

cant get sqldatasource to return results from a stored procedure

sql report manager

Blog Archive

About Me