Exec Stored Proc

I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnNamenvarchar (50),
@.dtDate1 Date,
@.dtDate2 Date
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ' + ''' + @.dtDate1 + ''' + ' AND ' + ''' + @.dtDate2 + '''')i havent tried it but check if this works:

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName +
 ' BETWEEN ' + '''' + @.dtDate1 + '''' + ' AND ' + '''' + @.dtDate2 + ''''

hth|||Try this:

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ''' + @.dtDate1 + ''' AND ''' + @.dtDate2 + '''')|||How many possible values for @.strColumnName could there be? For performance and code reliability reasons I would write it like below. That way the SQL is precompiled (allowing the db engine to skip a step during execution and also allowing syntax errors to be spotted at the time of sp creation instead of execution.

ALTER PROCEDURE dbo.GetSearchByDateRange
(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as
 if (@.strColumnName = 'Birthdate')
 begin
 SELECT * FROM Customers
 WHERE Birthdate BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 if (@.strColumnName = 'LastPayment')
 begin
 SELECT * FROM Customers
 WHERE 'LastPayment'BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 ETC...

|||Thank you all for the infos.
Corbi I think you had a good point there and I will definatly take it in consideration.

Oh last thing how do I control the injection of a date from my code to the store proc if my db is using small dates? I see that it can cause problem if I don't send the right format to the sp.
Thanks again|||If possible, i would change the stored procedure parameters to "smalldatetime", process the different date formats in your application and convert your userinput to "smalldatetime" there.

Hth,

Moon|||the only things that need to be considered in the Execute(Whatever Valid SQL Query in string format) is to have double single qoutes for each date value in the query, and that those date values are converted to a valid string (this is because you need to concatenate a string, so try this

Declare @.SQLString As VarChar(8000)

Set @.SQLString = 'SELECT * FROM Employees WHERE ' + @.strColumnName + ' BETWEEN ''' + Convert(Char(8), @.dtDate1, 112) + ''' AND ''' + Convert(Char(8), @.dtDate2, 112) + ''''

Exec (@.SQLString)

This will Work fine

Delfino III Salinas Sepúlveda
DSS Hi-Tech, México
diiisalinas@.prodigy.net.mx|||Also use DateTime or smalldatetime types.

EXEC (@SQLString) Problem.

Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

This is my code. What is wrong here?

CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))

--Inbound
SET @.LadingType = 'inbound'

SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'

EXEC (@.SQLStr)If you supply the declares, that'd be a big help...|||Sorry, here are the declares:
CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)

AS

BEGIN
SET NOCOUNT ON

DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'

DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'

DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'

DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'

DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'

DECLARE @.SQLStr varchar(8000)|||This compiles fine...it's something else...

DECLARE @.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'

DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'

DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'

DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'

DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'

DECLARE @.SQLStr varchar(8000)

SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'|||Table compiles fine as well....

Post the whole sproc...

it's massive, isn't it.....|||Yes it is, but here t is... :) I have to submit it in parts since I can only post 1000 characters.
It does compile fine, but because I use EXEC a string I create it won't show you the error until you execute it.:

CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)

AS

BEGIN
SET NOCOUNT ON

DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'

DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'

DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'

DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'

DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'

DECLARE @.SQLStr varchar(8000)

IF @.LadingType ='' GOTO TotalsReport
IF @.LadingType <>'' GOTO LadingReport

LadingReport:
IF LOWER(@.LadingType) ='inbound' GOTO InboundReport
IF LOWER(@.LadingType) ='outbound' GOTO OutboundReport

OutboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.ShipperData + '.ShipperName AS ShipperName, ' +
@.ShipperData + '.ShipperName2 AS ShipperCompName, ' +
@.ShipperData + '.ShipperAddr AS ShipperAddr, ' +
@.ShipperData + '.ShipperCity AS ShipperCity, ' +
@.ShipperData + '.ShipperState AS ShipperState, ' +
@.ShipperData + '.ShipperZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)

END

GOTO Done|||InboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.AddrData + '.aName1 AS ShipperName, ' +
@.AddrData + '.aName2 AS ShipperCompName, ' +
@.AddrData + '.aAddr1 AS ShipperAddr, ' +
@.AddrData + '.aCity AS ShipperCity, ' +
@.AddrData + '.aState AS ShipperState, ' +
@.AddrData + '.aZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)

END

GOTO Done|||TotalsReport:

BEGIN
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))

--Inbound
SET @.LadingType = 'inbound'

SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)

--Outbound
SET @.LadingType = 'outbound'

SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)

--Misc
SET @.LadingType = 'misc'

SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)

SELECT InvoiceNum AS InvoiceNumber,
ShipperNum AS ShipperNumber,
InvoiceDate AS InvoiceDate,
PickupTransDate AS ShipDate,
ShipperName AS ShipperName,
ShipperName2 AS ShipperCompName,
ShipperAddr AS ShipperAddr,
ShipperCity AS ShipperCity,
ShipperState AS ShipperState,
ShipperZip AS ShipperZip,
bName1 AS ConsigneeName,
bName2 AS ConsigneeCompName,
bAddr1 AS ConsigneeAddr,
bCity AS ConsigneeCity,
bState AS ConsigneeState,
bZip AS ConsigneeZip,
bCountry AS ConsigneeCountry,
bPhone AS ConsigneePhone,
TrackingNum AS TrackingNum,
CustRef1 AS RefNum1,
CustRef2 AS RefNum2,
UPSZone AS Zone,
ServiceLevel AS ServiceLevel,
Weight AS Weight,
Lading AS LadingDesc,
SMPCodeDesc AS SMPDesc,
GrossCharge AS GrossCharge,
Incentive AS Incentive,
NetCharge AS NetCharge,
AccessorialTotal AS AccessorialTotal,
CodeRefDesc AS ComResDesc,
HundredWeight AS HundredWeight
FROM #TotalsTemp
ORDER BY Lading

END

GOTO Done

Done:

END
GO|||So it's the execute that throws the error...

Instead of doing EXEC do SELECT @.SQLStr and take a look at it...can you post just that?

That'll be easier to debug...

Hey what's an extra 4 post counts...|||Well I get the error when I call the sproc. I don't think that the EXEC throws the error because this sproc worked fine with EXEC until I needed to make a chage on the bottom section starting with TotalsReport. so if you look at my original post it shows only that section and my second post shows all the declare's.

Thanks for your help.

P.S. the more posts the better :)|||What I'm suggesting is that the SQL statement is malformed...just putting the string together is fine...it's when you execute the sql that there's a problem (well, like duh brett)...

I was suggesting post what it buils...should be easier to see,,,wait...I can do that...|||YUP!

INSERT INTO #TotalsTemp SELECT tbl1ReportData.InvoiceNum, tbl1ReportData.ShipperNum, tbl1ReportData.InvoiceDate
, tbl1InvoiceData.PickupTransDate, tbl1AddrData.aName1, tbl1AddrData.aName2, tbl1AddrData.aAddr1
, tbl1AddrData.aCity, tbl1AddrData.aState, tbl1AddrData.aZip, tbl1ReportData.bName1, tbl1AddrData.bName2
, tbl1AddrData.bAddr1, tbl1ReportData.bCity, tbl1ReportData.bState, tbl1AddrData.bZip AS, tbl1AddrData.bCountry
, tbl1AddrData.bPhone, tbl1ReportData.TrackingNum, tbl1InvoiceData.CustRef1, tbl1InvoiceData.CustRef2
, tbl1ReportData.UPSZone, tblLegendServiceLevel.ServiceLevel, tbl1ReportData.Weight, tblLegendLading.Lading
, tblLegendSMPCodes.[Desc], tbl1InvoiceData.GrossCharge, tbl1ReportData.Incentive, tbl1ReportData.NetCharge
, tbl1ReportData.AccessorialTotal, tblCodeRef.[Desc], tbl1InvoiceData.HundredWeight
FROM tbl1ReportData INNER JOIN tbl1InvoiceData ON tbl1ReportData.DataID = tbl1InvoiceData.DataID
INNER JOIN tbl1AddrData ON tbl1ReportData.DataID = tbl1AddrData.DataID
INNER JOIN tblLegendServiceLevel ON tbl1ReportData.ServiceStandard = tblLegendServiceLevel.ServiceStandard
INNER JOIN tblLegendLading ON tbl1ReportData.LadingCode = tblLegendLading.LadingCode
INNER JOIN tblLegendSMPCodes ON tbl1ReportData.SMP2 = tblLegendSMPCodes.SMPCode
INNER JOIN tblCodeRef ON tbl1InvoiceData.ComRes = tblCodeRef.Code
INNER JOIN tblShipperNumberLookUp AS LookUp ON tbl1ReportData.ShipperNum = LookUp.ShipperNumber
INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID
INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child
INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl
WHERE (tbl1ReportData.InvoiceDate BETWEEN 'Mar 17 2004 12:00AM' AND 'Mar 17 2004 12:00AM')
AND (tblOrg_Unit_Hier.parent = 1) AND (tblOrg_lvls.Root = 1) AND (tblOrg_lvls.[Name] = 'Shipper Number')
AND (tblLegendLading.LadingType = 'Brett')|||WOW, I can't beleive I missed that. But with an sproc like this, it's bound to happend.

Thank you so much.

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gv
gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:< )
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exception comes from where?

Being new to SQLServer, I may be doing something very basic wrong, but here's
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:

>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.

> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:

> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:

> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?

> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.

> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>

Excel Pivot Table

Hi all,

I have upgrade my AS 2000 cube to As 2005, but there has some thing wrong with the Excel Pivot Table. In the cube I have one Time dimension with three attributes, Year, Month, Day. Then I create a Hierachies with three levels, Year, Month, and Day. Finally I retrieve the data though Excel Pivot Table. I put the Time Hierachies in the page field, and put the Year attribtues in the column filed. But no matter how I filter in the page fields, ex select January 2006 and 2007, the column field will display the whole year of data for 2006 and 2007. I didn't have this problem in AS 2000, anyone know the reason.

Thanks,

Tomas

My first guess is the relationships between the attribute hierarchies may not be correct. Could you describe the attribute relationships explicitly defined in the dimension? Could you also clarify if Month is modeled as January, February, etc. or January 2006, February 2006, ..., January 2007, February 2007, etc.?

Thanks,
Bryan

|||

The attributes relationship as follow:

Date(Usage: key)

|__Calendar Month

ex, 2007-1-1

Calendar Month(Usage: Regular)

|__Calendar Year

ex, January 2007

Calendar Year(Usage: Regular)

ex, 2007

Hierachies

*Calendar Year

**Calendar Month

***Date

Thanks,

Tomas

|||

Thomas,

This post was marked as answered. Has the problem been resolved?

Thanks,
Bryan

|||

sorry my mistake to marked as answered. The problem has not been resolved.

Thanks

|||

Not really sure what's going on with this. I'd suggest seeing if this is a problem in other browsers. If it is, try replacing the dimension and see if the problem persists. You may also want to open profiler to snag the query being submitted by Excel to see if it is just assembling a weird statement.

B.

|||

Here is what Excel generate:

WITH MEMBER [Time].[Year - Quarter - Month - Date].[XL_QZX] AS 'Aggregate ( { [Time].[Year - Quarter - Month - Date].[Quarter].&[2005-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2004-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2003-01-01T00:00:00] } )' SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Time].[Year].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [MaxMinSales] WHERE ([Measures].[Store Sales], [Time].[Year - Quarter - Month - Date].[XL_QZX])

But the result is the whole year, instead of Quarter 1.

|||

I'm not sure exactly what the problem is, but quarter wasn't in the hierarchy description from above. Take a look at the relationship from month to quarter and then quarter to year. I'm thiking that's where the problem is likely at.

B.