Tidying Up Of Sproc

Jul 20, 2005

Hi

I've written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after


CREATE PROCEDURE dbo.spGetUnbilledInfoByClient
(
@ClientID varchar(5),
@Sort int = 0,
@Dir int = 0
) AS

SET NOCOUNT ON

-- The Main Query
SELECT mmatter, mdesc1, tkfirst + ' ' + tklast AS Contact,
CONVERT(varchar(10), mopendt, 103) As [Date], CONVERT(DECIMAL(18,2),
tTime.Cost) AS COST, tTime.Hours, tDisbursements.Disbursements



FROM matter
INNER JOIN timekeep ON mbillaty = timekeep.tkinit
lEFT JOIN (

SELECT cmatter, SUM(Disbursements) AS Disbursements FROM (
SELECT dbo.cost.cmatter,
CASE WHEN dbo.cost.cauth = 'SO' OR dbo.cost.cauth = 'CF'
THEN dbo.cost.cbillamt * 1.175
ELSE
dbo.cost.cbillamt
END AS Disbursements
FROM dbo.cost
WHERE (dbo.cost.cstatus = 'b') AND (dbo.cost.cinvoice IS NULL) AND
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
) AS costs
GROUP BY cmatter

) AS tDisbursements
ON dbo.matter.mmatter = tDisbursements.cmatter

INNER JOIN
(
SELECT dbo.timecard.tmatter, SUM(dbo.timecard.tbillhrs) AS Hours,
SUM(dbo.timecard.tbilldol) AS Cost
FROM dbo.timecard JOIN dbo.batch ON dbo.timecard.tbatch =
dbo.batch.bbatch
WHERE (dbo.timecard.tinvoice IS NULL)
AND (dbo.timecard.tstatus = 'b')
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
AND (dbo.timecard.tbilldt IS NULL)
AND ( NOT dbo.batch.bfindt IS NULL)
GROUP BY tmatter
) AS tTime
ON dbo.matter.mmatter = tTime.tmatter

--Sort (see http://www.sqlteam.com/forums/topic...942&whichpage=3)
ORDER BY
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 0
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 0
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 0
THEN tDisbursements.Disbursements
END DESC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 0
THEN mopendt
WHEN @Sort = 3 AND @Dir = 0
THEN mopendt
END DESC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 0
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 0
THEN tkfirst + ' ' + tklast

END DESC,

-- ASC
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 1
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 1
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 1
THEN tDisbursements.Disbursements
END ASC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 1
THEN mopendt
WHEN @Sort = 3 AND @Dir = 1
THEN mopendt
END ASC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 1
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 1
THEN tkfirst + ' ' + tklast

END ASC
GO


CREATE TABLE [matter] (
[mname] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[mmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[mrtcode] [smallint] NULL ,
[mdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[mopendt] [datetime] NULL ,
[mbillaty] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[mdesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO




CREATE TABLE [cost] (
[cindex] [int] IDENTITY (1, 1) NOT NULL ,
[cmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[camount] [money] NULL ,
[cbilldt] [datetime] NULL ,
[cbillamt] [money] NULL ,
[cinvoice] [int] NULL ,
[ccode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[cbatch] [int] NULL ,
[cauth] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [costcode] (
[cocode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[codesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [timecard] (
[tindex] [int] IDENTITY (1, 1) NOT NULL ,
[tmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ttk] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[tbilldt] [datetime] NULL ,
[tbillhrs] [decimal](16, 2) NULL ,
[tbillrt] [decimal](16, 2) NULL ,
[tbilldol] [money] NULL ,
[tinvoice] [int] NULL ,
[tcode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[tbatch] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [batch] (
[bbatch] [int] IDENTITY (1, 1) NOT NULL ,
[btype] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[bfindt] [datetime] NULL ,
[bop] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[bper] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[bpflag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[bdopen] [datetime] NULL ,
[btothrs] [decimal](12, 2) NULL ,
[btotdol] [money] NULL
) ON [PRIMARY]
GO

View 10 Replies


ADVERTISEMENT

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.

Feb 13, 2007

I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View 3 Replies View Related

EXEC Of A Sproc Within Another Sproc

Apr 23, 2004

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy



Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int

EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended

WHERE
EntryLogID = @EntryLogID

EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID



Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID

RETURN


*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID


UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID

View 2 Replies View Related

Exec Sproc In Sproc

Jan 20, 2004

create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID


GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??

Tks

View 2 Replies View Related

Need Help With An Sproc

Jan 25, 2008

This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE      dbo.[4HCamp_tblStoreInventory]SET               Quantity = Quantity -                            (SELECT      SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity                              FROM           dbo.[4HCamp_tblStoreTempCart]                              WHERE       dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
 Thanks!
Amanda

View 1 Replies View Related

SQL Sproc Help

May 2, 2008

I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
  SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type

FROM CISSoftware, Software, Manufacturers, SoftwareTypes

WHERE CISSoftware.SoftwareID = Software.SoftwareID

AND Software.ManufacturerID = Manufacturers.ManufacturerID

AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
 

View 4 Replies View Related

Help On Using LIKE In Sproc

Apr 10, 2004

hi all,

I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details

My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text

I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc

CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @srch)
GO

After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:

prmSearch.ParameterName = "@srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")

My original string looks like this

strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"


What am I doing wrong?? Thanks in advance for any help.

View 7 Replies View Related

Sproc Seems To Run Twice Instead Of Once...

Dec 2, 2004

I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)

___________________

SPROC:
___________________
CREATE PROCEDURE dbo.sp_addMembershipRole
@INCID Int
AS
declare @literal NVarChar (10)
SET @literal = 'RTRListing'

INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role])
VALUES (@INCID, @literal)
GO
___________________

CODE:
___________________
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Web.Security;

namespace admin
{
/// <summary>
/// Summary description for createMembership.
/// </summary>
public class createMembership : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator Vusername;
protected System.Web.UI.WebControls.RequiredFieldValidator Vpassword;
protected System.Web.UI.WebControls.RequiredFieldValidator Vretype;
protected System.Web.UI.WebControls.TextBox username;
protected System.Web.UI.WebControls.TextBox password;
protected System.Web.UI.WebControls.TextBox retype;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label lblDate;
protected System.Web.UI.WebControls.Button btnAdd;



private void Page_Load(object sender, System.EventArgs e)
{
if(! Page.IsPostBack)
{
string StrContactID = Request.QueryString["CID"].ToString();
Session["CID"]= StrContactID;
}
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

public void btnAdd_Click(object sender, System.EventArgs e)
{
int intContactID;
string StrCID = Session["CID"].ToString();
intContactID= Int32.Parse(StrCID);

if(password.Text != retype.Text)
{
lblError.Text = "Retyping of your desired password did not match. Please try again.";
return;
}

string pwdSalt = CreateSalt(5);
string pwd = password.Text;
string pwdHash = CreatePasswordHash(pwd, pwdSalt);
string connStr= "server=****; uid=****; pwd=****; database=****";
string cmdStr= "sp_addMembershipUandP";
string cmd2Str= "sp_addMembershipRole";

SqlConnection CONN = new SqlConnection(connStr);
CONN.Open();

SqlCommand cmd = new SqlCommand(cmdStr, CONN);
cmd.CommandType = CommandType.StoredProcedure;

SqlCommand cmd2= new SqlCommand(cmd2Str, CONN);
cmd2.CommandType = CommandType.StoredProcedure;

SqlParameter param;
param = cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50);
param.Value = username.Text;

param = cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50);
param.Value = pwdHash;

param = cmd.Parameters.Add("@salt", SqlDbType.NVarChar, 50);
param.Value = pwdSalt;

param = cmd.Parameters.Add("@CID", SqlDbType.Int, 4);
param.Value = intContactID;

SqlParameter param2;
param2 = cmd2.Parameters.Add("@INCID", SqlDbType.Int, 4);
param2.Value = intContactID;


cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();

CONN.Close();
return;

}
private static string CreateSalt(int size)
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);
return Convert.ToBase64String(buff);
}
private static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "SHA1");
return hashedPwd;
}
}
}

View 2 Replies View Related

Sproc In A Sproc

Jan 22, 2004

create procedure
GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
declare @ZipID integer
declare @AddrID integer
set @AddrID=1
if lTrim(@Addr1)<>''


EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)


set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
return(@AddrID)
end
GO




In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that

"Incorrect syntax near @City. "

Can you help me out? The same syntax works for passing one variable but not for three.

FYI this is the other sproc




CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6))
AS
BEGIN
DECLARE @CityID integer
DECLARE @StateID integer
DECLARE @ZipID integer

SET @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null

EXEC @CityID = dbo.GetCityID @City
EXEC @StateID = dbo.GetStateID @State

insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
Return @ZipID
print @ZIPID

END
GO





Thx in advance

View 1 Replies View Related

Sproc This!

May 17, 2004

ha ha ,

havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?

Use Contacts
GO

CREATE PROC MaserIn
@entreddatetime= GETDATE(),
@initialsvarchar(2)=Null,
@Vendorvarchar(50),
@CkNovarchar(20),
@expTypevarchar(15),
@ckDatedatetime,
@ckAmtmoney,
@mIdintOUTPUT

AS

INSERT INTO [Contacts].[dbo].[Master](
[entered], [initials], [Vendor],
[CkNo], [expType], [ckDate], [ckAmt])
VALUES(@entered,
@initials,
@Vendor,
@CkNo,
@expType,
@ckDate,
@ckAmt)

SELECT @mID=@@iDENTITY

View 6 Replies View Related

Is It Possible To Do This In A Sproc

Nov 28, 2007

Hi.

I wanted to know if its possible to do this in a sproc.


if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.


If yes how can i do it..

Any help will be appreciated.

Regards
Karen

View 8 Replies View Related

Help Needed With This Sproc

Dec 19, 2007

 
Hi, I am trying to Implement Multi parameter...
 If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.
This is my sproc
  ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@ClientId nvarchar(max)= NULL,
@StartDate datetime,
@EndDate datetime
AS
Declare @SQLTEXT nvarchar(max)
If @ClientId IS NULL
Begin
Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + @ClientId + ')
AND
o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + '
ORDER BY
o.OrderId DESC'
execute (@SQLTEXT)

END 
any help will be appreciated.
Regards
Karen

View 4 Replies View Related

SPROC To Select Top N

Jul 5, 2004

hi all

i need to design a SPROC which will return me top n rows from a table.

like GetTopN 4, will give me top 4 tuples


/**
GetTopN
To list top n rows
Date - 05 July 2004
Yogesh Jangam
*/

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'GetTopN' AND type = 'P')
DROP PROCEDURE GetTopN
GO

/************************ Actual SP Code *********************************/
Create procedure GetTopN (@intN int)
AS
BEGIN
SET NOCOUNT ON
declare

SELECT TOP @intN from Employee -- this part i am not able to write
end


is there a answer?
Thanks

View 2 Replies View Related

When A Sproc Or Table Was Last Used

Sep 21, 2005

How can I determine when a sproc or table was last used?
I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
Thanks,
DL

View 5 Replies View Related

MSSQL SPROC And VB6

May 14, 2006

I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?

Here is my complete code:


USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP

GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE@CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */

While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
End
Else
Begin
Set @SV=Cast(@ListStr as varchar)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End

Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and my VB6 Code:


Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String

Set adoConn = New ADODB.Connection
adoConn.Open connString

Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command

With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"

Set adoRS = .Execute

Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop

End With

Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

End Sub

Any ideas?

Thanks

View 2 Replies View Related

Newbie - Is This A Job For A Sproc?

Sep 23, 2006

I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.

I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.

I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?

Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.

Is it possible to do this with a sproc? Is this the right way to use a sproc?

View 9 Replies View Related

Now That Does Not Look Like The Right SQL For A System Sproc....

Mar 11, 2008

Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers

View 12 Replies View Related

Sproc In A Function

Jan 20, 2004

CREATE proc dbo.sp_address ( @Abbr char(2) )
as
DECLARE @StateID int
SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
set @StateID=53
IF ( @StateID is null )
INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
if @@ERROR = 0
SET @StateID = @@Identity
return(@StateID)
GO


Can I execute the above stored procedure in a function like this:

create function
sf_GetStateID( @Abbr char(2))
returns integer
begin
declare @StateID int
exec sp_address
return(@StateID)
end

I just want to cross check.

Tks

View 1 Replies View Related

Insert In A Sproc

Jan 23, 2004

I am trying to insert a value with Insert statement in a sproc

create procedure AddNewPersonsFromALSHeadr
as
begin

insert into dbo.Persons(lngSSN,
strNamePrefix,
strFirstName,
lngHomeID,
lngMailID)

select lngFedTaxID,
strNamePrefix,
strFirstName,

exec @HomeId = GetAddress H.strAddress1,strAddress2,H.strCity,H.strState,H.s trZip,H.intZip4,
@HomeId


from dbo.ALSHeadr H


end
go


The probl is while inserting the fourth column values I have to execute a sproc
"GetAddress" and take the value and insert it in "lngHomeID" and the the same value in
"lngMailID". I tried all possible ways for the syntax but 'm getting the error
incorrect sytanx near exec and also syntax error near " . " at H.strAddress1 etc......


FYI this is the sproc GetAddress(I omitted few lines in this sproc)


CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
------------------------------------------
------------------------------------------
--------------------------------------
EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5
return(@AddrID)
print (@AddrID)
end
GO



Tks

View 3 Replies View Related

SPROC PARAMETERS

May 20, 2008

Hi ALL,

wondering if anybody can help me, i've writen a stored procedure to query my database, i want to be able to added a wildcard into a parameter incase i don't want to use it.

(don't laugh at my efforts!!!!:))
i've tried

IF @<PARAM> = NULL
@<PARAM> = LIKE %

I sure this is a simple thing to add basically i need to replace a null with everything!!

can anybody help?

View 3 Replies View Related

Master SPROC Used On Different DBs

Oct 30, 2007

I'm trying to create a stored procedure on my master database that I can query other databases. The reason behind putting it on the master db is so I will have one place to modify the stored procedure instead of potentially a hundred different databases. I am trying to create a stored procedure like the one below, but it seems to not like it and I get the error below. Any suggestions are appreciated.

DECLARE @uName AS nvarchar(30), @dbName as nvarchar(5)
SET @uName='TU'
SET @dbName = '00000'
DELETE FROM [DASHDB].[dbo].users WHERE Username=@uName
EXECUTE('USE ['+@dbName+']')
DELETE FROM DashboardBranches WHERE Username=@uName
DELETE FROM MyDashboardSettings WHERE Username=@uName
DELETE FROM MyFavorites WHERE Username=@uName
DELETE FROM MyModules WHERE Username=@uName
DELETE FROM MyStocks WHERE Username=@uName
DELETE FROM BoardPackage WHERE PackageID IN (SELECT ID FROM BPackages WHERE Username=@uName)
DELETE FROM BPackages WHERE Username=@uName



ERROR:
Msg 208, Level 16, State 1, Line 6
Invalid object name 'DashboardBranches'.


Thanks,
Josh

View 4 Replies View Related

Can't Get SProc To Work

Aug 16, 2005

HiI can't seem to get this procedure to work correctly. Here's my code:DECLARE @PropertyDetails nvarchar (6)Select @PropertyDescription = PropertyDescription from Property whereApplicationID = @applicationidIf @PropertyDescription is not null or @PropertyDescription <> ''BeginIf (select isnumeric(PropertyDescription) from Property where ApplicationID =@applicationid) = 1INSERT INTO #errors VALUES (1410,@ApplicationID, 0, 'Y')ELSEINSERT INTO #errors values (1410, @ApplicationID, 0, 'N')EndI am trying to bring up an error advising users not to capture alphabets in afield called Property Description.I need to bring up the error from the #ERRORS table with the rule number 1410.My Syntax checks successful, but my error does not come up for the users. AmI missing something?Thanks for any help at all, guys.Driesen Pillay--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200508/1

View 5 Replies View Related

Trouble With SPROC

Oct 1, 2005

Hi guysI having trouble with this sproc. I get the following error when testing:Server: Msg 245, Level 16, State 1, Procedure UTL_CompletenessCheckLoan, Line231Syntax error converting the varchar value 'danwet w125 ' to a column of datatype int.I have declared @Sellername as Varchar. Please can someone tell me what I'mdoing wrong? All it needs to do is return the 'Y' value if there are numericsin the sellername.declare @sellername varchar(40),@applicationid INTselect @sellername = sellername from Seller where ApplicationId =@ApplicationIdIf @sellername is not null or @sellername <> ''beginif (select isnumeric(Sellername) from seller where ApplicationId =@ApplicationId) = 1select 'Y'ELSEselect 'N'endThanks for any helpDriesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200509/1

View 4 Replies View Related

How To Quit An Sproc

Jun 7, 2007

HiI am doing a check and on failing, i have a raiseerror command.I was assuming, once proc reaches raiseerror, it would stop the proc,but i see that the proc gives me an error message, but continues torun thru all the rest of the steps after raiseerror.how do i make the proc quit after reading raiseerror, only thrulabels ?IF @CheckIFFileHasOnlyOneOutputType 1BEGINRAISERROR ('Process Stopped. Input xls file is invalid, as it has morethan one output type specified in the OutputType Column', 16, 1)END--more proc steps are here ...Thanks in advance :RS

View 1 Replies View Related

How To Script A Sproc

Jan 8, 2008

I am trying to create a sproc that willl add a row to a table. I also want to be able to test the sproc by inserting literal values and executing it. Below is the sproc that I have created. I am not sure if I have done the last part with the values correctly. The names used in the VALUES portion are the same as the variables in my c# app. I want to be able to pass the alues from my c# app to this using these variables.

I have tried to add this a row to the table by changing the word CREATE to EXEC and then running it. I also changed the values to a value with quotes around it. I thought that might make the value a literal. I did this in an effort to test the sproc. I had no luck trying to write to the database this way.

Can someone please tell me what I am doing wrong?


USE VetClinic

GO

CREATE PROC spInsertNewPet

(



@CustID int,

@PetName varchar(20),

@BreedID int,

@Gender char(1),

@Weight decimal,

@DOB smalldatetime,

@DateFixed smalldatetime

)

AS

INSERT INTO Pets

(



CustId,

PetName,

BreedID,

Gender,

Weight,

DOB,

DateFixed

)

VALUES

(





CustId,

PetName,

BreedID,

Gender,

Weight,

DOB,

DateFixed



)

GO.................................................................................thanks

View 5 Replies View Related

Calling A Sproc

Apr 23, 2008

I'm trying to call a stored procedure like so...


EXECUTE usp_SVDO_CNTRL_SetPalletChild_WorkData N'a067c8ed-22d9-c568-7bcb-022f30245818',N'99999999-9999-9999-9999-999999999999',

N'99999999-9999-9999-9999-999999999999',N'4',N'Symbol 123',N'XES-RECVD',N'311801348000052',N'123555',N'311801348000062',

N'3211',N'3232',N'3230',N'3135',N'1',

CAST('2000-05-08 12:35:29.998' AS datetime)

,N'admin',

CAST('2000-05-08 12:35:29.998' AS datetime)

,N'admin1',N'1'



The error I get is:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '2000-05-08 12:35:29.998'.

View 13 Replies View Related

Use Of Xml Schema In Sproc

Dec 12, 2006

It appears to be impossible to create an
xml schema in a stored procedure and then use it in a temporary table in the
stored procedure. Did anyone else experience this? I am using this stored
procedure:



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[validate_data]

@variable_uid int,

@xml_schema xml,

@flag int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;



declare @schema xml



set @flag = 0



create xml schema collection dbo.xsch AS @xml_schema



CREATE TABLE
temp_row_values

(

row_value_uid
int,

xml_fragment xml (dbo.xsch) not null

)



drop xml schema collection dbo.xsch

drop table
temp_row_values



END



It produces this error message:



Msg 6314, Level 16, State 1, Procedure validate_data, Line 22

Collection specified does not exist in metadata : 'dbo.xsch'



If I execute this query:



exec validate_data 120, '<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element
name="int_ma">
<xs:annotation>
<xs:documentation xml:lang="en" /> </xs:annotation> <xs:complexType> <xs:all> <xs:element
minOccurs="1" maxOccurs="1" name="value"> <xs:simpleType> <xs:union> <xs:simpleType> <xs:restriction
base="xs:integer">
<xs:minInclusive value="1" /> <xs:maxInclusive
value="100" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType>
<xs:restriction base="xs:string"> <xs:enumeration
value="" /> <xs:enumeration value="none"
/>
</xs:restriction>
</xs:simpleType>
</xs:union>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="1" maxOccurs="1"
name="unit">
<xs:simpleType>
<xs:restriction base="xs:string"> <xs:enumeration
value="mm" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="1"
name="comment"> <xs:simpleType> <xs:restriction
base="xs:string" />
</xs:simpleType>
</xs:element>
</xs:all>
</xs:complexType>
</xs:element>
</xs:schema>', 1



Any help to €˜make it possible€™ would
be very much apprecaited. Many thanks.



Chris

View 3 Replies View Related

Expression From Within Sproc?

Jan 20, 2007

If I'm inside a stored procedure called by an MDX query, the following would query the cube and figure out the sales for the current tuple:

new Expression("[Measures].[Internet Sales Amount]").Calculate(null).ToDouble()

So I was expecting to be able to do that from within a stored proc called from a DMX predict query. I was hoping that I could evaluate the $Cluster expression from within a sproc and figure out what cluster the current row in the DMX query belongs to. Basically, I'm trying to make my sproc function signature more elegant so I don't have to pass in the NodeId like:

select $Cluster, MyAssembly.MySproc(PredictNodeId(Cluster())) as CoolData
from [Customer Clusters]
NATURAL PREDICTION JOIN
(select * from [Customer Clusters].Cases) as x
where $Cluster = 'Cluster 10'

Is there any way to do that?

View 1 Replies View Related

How To Debugg Sproc?

Mar 7, 2008

View 5 Replies View Related

Locking A SPROC

Dec 10, 2007

I need a way to lock a SPROC so that it cannot be executed concurrently. Basically, I have several clients calling this SPROC and I need to make sure it finishes before it can be called again. Each call changes data that will affect the next call. If the proc executed concurrently unpredictable results could occur. Any ideas?

View 3 Replies View Related

SProc Expects Parameter...Help

Aug 24, 2006

This code works EXACTLY as it should on a development server that has a Framework 1.1 hotfix (operating in the 'localhost' webserver environment) but not on a production server (in a live www. web app) that does NOT have the 1.1 hotfix. Is there a code problem (if so, why should it work in the localhost) or is the hotfix on the development server needed on the production server.
(in a button sub that does an update)...Dim currentUser As New AccBusiness.User( _                                      CType(Context.User, SitePrincipal))                Dim DDName As String = currentUser.DDName                Dim NosData As String                Dim AccountDetails As New SecureNums(txtDDPhone.Text, txtDDAddress.Text)                NosData = AccountDetails.EncryptedData()                AccountDetails.UpdateAcctDetails()----------------------(business object for the update)... Public Function UpdateAcctDetails() As Boolean            Dim DataUserAcctNo As New Data.SecureCard(myModuleSettings.ConnectionString)            Return DataUserAcctNo.UpdateAcctDetails( _                myDDName, _                myNosData)        End Function--------------------------------(data object)...Public Function UpdateAcctDetails(ByVal DDName As String, _                                            ByVal NosData As String) As Boolean            Dim rowsAffected As Integer            Dim parameters As SqlParameter() = { _                New SqlParameter("@DDName", SqlDbType.VarChar, 50), _                New SqlParameter("@NosData", SqlDbType.VarChar, 512)}            parameters(0).Value = DDName            parameters(1).Value = NosData            RunProcedure("sp_Accounts_UpdateAccount", parameters, rowsAffected)            Return CBool(rowsAffected = 1)        End Function------------------------------and the sproc:ALTER PROCEDURE sp_Accounts_UpdateAccount@DDNameVarChar(50),@NosDataVarChar(512)AsUPDATE Accounts_UsersSet NosData = @NosDataWHERE DDName = @DDName------------------------------------------------------------
It all works on the development server but on the production server it gives me an error :SProc sp_Accounts_UpdateAccount expects parameter "@DDName" which was not supplied.
Can anyone suggest why this works in one environment and not the other?
Thx for looking/Reid C. 

View 2 Replies View Related

Help With Sproc And Multi Parameter

Dec 11, 2006

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
 Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it  bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction

(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)

AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)

RETURN 

View 15 Replies View Related

Invalid Syntax In My Sproc?!?!

Feb 18, 2007

Hi I have a gridview that is being populated from a method that gets it's data from a table view.
SELECT     dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone,                       dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName,                       dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact,                       dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentIdFROM         dbo.aspnet_Users INNER JOIN                      dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN                      dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId
No big deal, works great.  Now when I click update I call this method

<DataObjectMethod(DataObjectMethodType.Update)> Public Sub UpdateAlumni(ByVal Street As String, ByVal City As String, ByVal State As String, ByVal Telephone As String, ByVal Occupation As String, ByVal Description As String, ByVal Zip As String, ByVal FirstName As String, ByVal LastName As String, ByVal YearGraduate As String, ByVal Email As String, ByVal Contact As Boolean, ByVal original_UserName As String, ByVal UaaStudentId As String)
Try
Dim connx As New SqlConnection(getConnectionString)
connx.Open()
Dim sqlCmd As New SqlCommand("cis_UpdateAlumniContact", connx)
sqlCmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NVarChar))
sqlCmd.Parameters("@UserName").Value = original_UserName
sqlCmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar))
sqlCmd.Parameters("@FirstName").Value = FirstName
sqlCmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar))
sqlCmd.Parameters("@LastName").Value = LastName
sqlCmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar))
sqlCmd.Parameters("@Email").Value = Email
sqlCmd.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar))
sqlCmd.Parameters("@Street").Value = Street
sqlCmd.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar))
sqlCmd.Parameters("@City").Value = City
sqlCmd.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar))
sqlCmd.Parameters("@State").Value = State
sqlCmd.Parameters.Add(New SqlParameter("@Occupation", SqlDbType.NVarChar))
sqlCmd.Parameters("@Occupation").Value = Occupation
sqlCmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.NVarChar))
sqlCmd.Parameters("@Description").Value = Description
sqlCmd.Parameters.Add(New SqlParameter("@Telephone", SqlDbType.NChar))
sqlCmd.Parameters("@Telephone").Value = Telephone
sqlCmd.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar))
sqlCmd.Parameters("@Zip").Value = Zip
sqlCmd.Parameters.Add(New SqlParameter("@Contact", SqlDbType.Bit))
sqlCmd.Parameters("@Contact").Value = Contact
sqlCmd.Parameters.Add(New SqlParameter("@YearGraduate", SqlDbType.NVarChar))
sqlCmd.Parameters("@YearGraduate").Value = YearGraduate
Dim cmd As SqlDataReader = sqlCmd.ExecuteReader
Catch ex As Exception
Dim er As New cis_ODS_Error
er.InsertError("cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)
End Try
End Sub
The sproc it calls is:

ALTER PROCEDURE dbo.cis_UpdateAlumniContact
@UserName as nvarchar(50),
@Street as nvarchar(50),
@City as nvarchar(50),
@State as nvarchar(2),
@Telephone as nvarchar(50),
@Occupation as nvarchar(50),
@Description as nvarchar(50),
@Zip as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@YearGraduate as nvarchar(4),
@Email as nvarchar(50),
@Contact as bit
AS
UPDATE cis_AlumniContact
 
SET Street = @Street, City = @City, State = @State, Telephone = @Telephone, Occupation = @Occupation, Description = @Description, Zip = @Zip, FirstName = @FirstName, LastName = @LastName, YearGraduate = @YearGraduate, Email = @Email, Contact = @Contact
 
FROM aspnet_Users INNER JOIN cis_AlumniContact
 
ON cis_AlumniContact.UserId = aspnet_Users.UserId
 
WHERE @UserName = aspnet_Users.UserName
 
RETURN
I get this vague error
cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'
 
If I execute the SQL from the editor it works fine.  The only thing different about this sproc and my other update sprocs is the inner join.  Any idea? Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved