June 11, 2009 at 7:31 pm
I am having this issue where I have this huge program that calls stored procedures from another stored procedures in sql server. The issue I am running into is that sometimes this one stored procedure goes to insert records into this one table and sometimes it does it and sometimes it doesn't do it. This happens on certain occasions and for non system admin users. I built this big tool with an access adp project as a front end tool and sql server as a back end tool. When the non system admins run this tool and this one stored procedure does not insert records into the table, then I end up re-running the tool as a system admin and it works. It actually inserts the records. Is this some type of permission issue and if so what can I do to fix this? I've pretty much tried to turn on write privileges so the non-system admin users could write to the tables, but it doesn't seem to matter. Any suggestions would be greatful.
June 11, 2009 at 7:54 pm
What kind of error handling do you have? Can you post the code for the procedure?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2009 at 7:59 pm
Hello,
At first glance it does seem like a permissions issue.
You could run a Profile to ensure that the expected SQL Statements are being sent to the DB, and if so use the collected SQL to manually test under a user account. Do you have a test copy of the DB?
Are there any Insert Triggers on the target table? (Just to rule out one possible culprit).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 12, 2009 at 9:47 am
I don't really have any exceptional handling, but I should. Here is the stored procedure that is having the insert issues:
ALTER PROCEDURE [dbo].[qryAppend_to_Comparison]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--qrySettlement_Summary query.
CREATE TABLE #SETTSUMM (
Transaction_Date char(10),
Store_Number int,
Summary varchar(50),
Total_Amount money
)
-- -- qrySettlement_Summary query.
INSERT INTO #SETTSUMM(Transaction_Date, Store_Number, Summary, Total_Amount)
SELECT
CONVERT(char(10),dbo.tblSettlement_Data.Transaction_Date,110),
dbo.tblSettlement_Data.Store_Number,
dbo.tblCard_Types.Summary,
SUM(dbo.tblSettlement_Data.Total_Amount) AS Amount
FROM dbo.tblSettlement_Data INNER JOIN dbo.tblCard_Types ON dbo.tblSettlement_Data.Card_Type = dbo.tblCard_Types.Card_Type
GROUP BY dbo.tblSettlement_Data.Transaction_Date, dbo.tblSettlement_Data.Store_Number, dbo.tblCard_Types.Summary
ORDER BY dbo.tblSettlement_Data.Transaction_Date, dbo.tblSettlement_Data.Store_Number, dbo.tblCard_Types.Summary
SELECT *
FROM #SETTSUMM
ORDER BY Transaction_Date
--Setup Temp_POS_Info table from the tblPOS_INFORMATION
CREATE TABLE #Temp_POSINFO (
Transaction_Date char(10),
Store_Number int,
Card_Type nvarchar(12),
Amount money,
[Date] char(10)
)
INSERT INTO #Temp_POSINFO(Transaction_Date,Store_Number,Card_Type,Amount,[Date])
SELECT
CONVERT(char(10),Transaction_Date,110),
Store_Number,
Card_Type,
Amount,
CONVERT(char(10),"Date",110)
FROM dbo.tblPOS_Information
SELECT * FROM #Temp_POSINFO
--qryComparison query.
CREATE TABLE #XX (
Transaction_Date char(10),
Store_Number int,
Card_Type varchar(20),
POS money,
CITIBANK money,
VARIANCE money
)
INSERT INTO #XX(Transaction_Date,Store_Number,Card_Type,POS,CITIBANK,VARIANCE)
SELECT
#Temp_POSINFO.Transaction_Date,
#Temp_POSINFO.Store_Number,
#Temp_POSINFO.Card_Type,
#Temp_POSINFO.Amount AS POS,
CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END AS CITIBANK,
#Temp_POSINFO.Amount - (CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END) AS VARIANCE
FROM (#Temp_POSINFO LEFT OUTER JOIN #SETTSUMM ON
(#Temp_POSINFO.Card_Type= #SETTSUMM.Summary)
AND (#Temp_POSINFO.Store_Number = #SETTSUMM.Store_Number)
AND (#Temp_POSINFO.Transaction_Date = #SETTSUMM.Transaction_Date))
LEFT OUTER JOIN dbo.tblComparison ON
(#Temp_POSINFO.Transaction_Date = dbo.tblComparison.Transaction_Date)
AND (#Temp_POSINFO.Store_Number = dbo.tblComparison.Store_Number)
AND (#Temp_POSINFO.Card_Type = dbo.tblComparison.Card_Type)
WHERE (((#Temp_POSINFO.Transaction_Date) > GETDATE() - 60)
AND ((#Temp_POSINFO.Amount - (CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END)) 0))
ORDER BY #Temp_POSINFO.Transaction_Date
SELECT * FROM #XX ORDER BY Transaction_Date, store_number
--Setup temporary table to add new records to the tblComparison table.
CREATE TABLE #YY (
Store_Number int,
Transaction_Date char(10),
Card_Type nvarchar(12),
POS money,
CITIBANK money,
VARIANCE money,
Corrected bit,
Corrected_Id int
)
INSERT INTO #YY (Store_Number, Transaction_Date, Card_Type, POS, CITIBANK, VARIANCE,Corrected )
SELECT
#XX.Store_Number,
#XX.Transaction_Date,
#XX.Card_Type,
#XX.POS,
#XX.CITIBANK,
#XX.VARIANCE,
0
FROM #XX LEFT OUTER JOIN dbo.tblComparison ON
(#XX.Card_Type = dbo.tblComparison.Card_Type)
AND (#XX.Store_Number = dbo.tblComparison.Store_Number)
AND (#XX.Transaction_Date = dbo.tblComparison.Transaction_Date)
WHERE ((#XX.Transaction_Date >GetDate()-60) AND (dbo.tblComparison.Comparison_Id Is Null))
SELECT * FROM #YY
--Check for Duplicate records in the tblComparison records.
CREATE TABLE #tempdupdata(
Store_Number int,
Transaction_Date char(10),
Card_Type nvarchar(12),
POS money,
CITIBANK money,
VARIANCE money,
Corrected bit,
Corrected_Id int
)
INSERT INTO #tempdupdata
SELECT * FROM #YY
GROUP BY Store_Number,Transaction_Date,Card_Type,POS,CITIBANK,VARIANCE,Corrected,Corrected_Id
HAVING COUNT(*) > 1
DELETE #YY
FROM #YY
INNER JOIN #tempdupdata
ON #YY.Store_Number = #tempdupdata.Store_Number
AND #YY.Transaction_Date = #tempdupdata.Transaction_Date
AND #YY.Card_Type = #tempdupdata.Card_Type
AND #YY.POS = #tempdupdata.POS
AND #YY.CITIBANK = #tempdupdata.CITIBANK
AND #YY.VARIANCE = #tempdupdata.VARIANCE
AND #YY.Corrected = #tempdupdata.Corrected
AND #YY.Corrected_Id = #tempdupdata.Corrected_Id
INSERT INTO #YY
SELECT * FROM #tempdupdata
SELECT * FROM #YY
INSERT INTO dbo.tblComparison(Store_Number,Transaction_Date,Card_Type,POS,CITIBANK,VARIANCE,Corrected,Corrected_Id)
SELECT * FROM #YY
SELECT * FROM dbo.tblComparison
DROP TABLE #XX
DROP TABLE #YY
DROP TABLE #tempdupdata
DROP TABLE #SETTSUMM
DROP TABLE #Temp_POSINFO
END
June 12, 2009 at 9:56 am
I have placed the stored procedure in another reply.
You can take a look at the code and let me know if you see something out of syntax. I should include some error checking for the code when it does not insert the records into the table. I do not have any triggers written for this database.
ALTER PROCEDURE [dbo].[qryAppend_to_Comparison]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--qrySettlement_Summary query.
CREATE TABLE #SETTSUMM (
Transaction_Date char(10),
Store_Number int,
Summary varchar(50),
Total_Amount money
)
-- -- qrySettlement_Summary query.
INSERT INTO #SETTSUMM(Transaction_Date, Store_Number, Summary, Total_Amount)
SELECT
CONVERT(char(10),dbo.tblSettlement_Data.Transaction_Date,110),
dbo.tblSettlement_Data.Store_Number,
dbo.tblCard_Types.Summary,
SUM(dbo.tblSettlement_Data.Total_Amount) AS Amount
FROM dbo.tblSettlement_Data INNER JOIN dbo.tblCard_Types ON dbo.tblSettlement_Data.Card_Type = dbo.tblCard_Types.Card_Type
GROUP BY dbo.tblSettlement_Data.Transaction_Date, dbo.tblSettlement_Data.Store_Number, dbo.tblCard_Types.Summary
ORDER BY dbo.tblSettlement_Data.Transaction_Date, dbo.tblSettlement_Data.Store_Number, dbo.tblCard_Types.Summary
SELECT *
FROM #SETTSUMM
ORDER BY Transaction_Date
--Setup Temp_POS_Info table from the tblPOS_INFORMATION
CREATE TABLE #Temp_POSINFO (
Transaction_Date char(10),
Store_Number int,
Card_Type nvarchar(12),
Amount money,
[Date] char(10)
)
INSERT INTO #Temp_POSINFO(Transaction_Date,Store_Number,Card_Type,Amount,[Date])
SELECT
CONVERT(char(10),Transaction_Date,110),
Store_Number,
Card_Type,
Amount,
CONVERT(char(10),"Date",110)
FROM dbo.tblPOS_Information
SELECT * FROM #Temp_POSINFO
--qryComparison query.
CREATE TABLE #XX (
Transaction_Date char(10),
Store_Number int,
Card_Type varchar(20),
POS money,
CITIBANK money,
VARIANCE money
)
INSERT INTO #XX(Transaction_Date,Store_Number,Card_Type,POS,CITIBANK,VARIANCE)
SELECT
#Temp_POSINFO.Transaction_Date,
#Temp_POSINFO.Store_Number,
#Temp_POSINFO.Card_Type,
#Temp_POSINFO.Amount AS POS,
CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END AS CITIBANK,
#Temp_POSINFO.Amount - (CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END) AS VARIANCE
FROM (#Temp_POSINFO LEFT OUTER JOIN #SETTSUMM ON
(#Temp_POSINFO.Card_Type= #SETTSUMM.Summary)
AND (#Temp_POSINFO.Store_Number = #SETTSUMM.Store_Number)
AND (#Temp_POSINFO.Transaction_Date = #SETTSUMM.Transaction_Date))
LEFT OUTER JOIN dbo.tblComparison ON
(#Temp_POSINFO.Transaction_Date = dbo.tblComparison.Transaction_Date)
AND (#Temp_POSINFO.Store_Number = dbo.tblComparison.Store_Number)
AND (#Temp_POSINFO.Card_Type = dbo.tblComparison.Card_Type)
WHERE (((#Temp_POSINFO.Transaction_Date) > GETDATE() - 60)
AND ((#Temp_POSINFO.Amount - (CASE WHEN #SETTSUMM.Total_Amount IS NULL THEN 0 ELSE #SETTSUMM.Total_Amount END)) 0))
ORDER BY #Temp_POSINFO.Transaction_Date
SELECT * FROM #XX ORDER BY Transaction_Date, store_number
--Setup temporary table to add new records to the tblComparison table.
CREATE TABLE #YY (
Store_Number int,
Transaction_Date char(10),
Card_Type nvarchar(12),
POS money,
CITIBANK money,
VARIANCE money,
Corrected bit,
Corrected_Id int
)
-- INSERT INTO #YY(Comparison_Id,Store_Number,Transaction_Date,Card_Type,POS,CITIBANK,VARIANCE,Corrected,Corrected_Id)
-- SELECT
-- Comparison_Id,
-- Store_Number,
-- CONVERT(char(10),Transaction_Date,110),
-- Card_Type,
-- POS,
-- CITIBANK,
-- VARIANCE,
-- Corrected,
-- Corrected_Id
-- FROM tblComparison
INSERT INTO #YY (Store_Number, Transaction_Date, Card_Type, POS, CITIBANK, VARIANCE,Corrected )
SELECT
#XX.Store_Number,
#XX.Transaction_Date,
#XX.Card_Type,
#XX.POS,
#XX.CITIBANK,
#XX.VARIANCE,
0
FROM #XX LEFT OUTER JOIN dbo.tblComparison ON
(#XX.Card_Type = dbo.tblComparison.Card_Type)
AND (#XX.Store_Number = dbo.tblComparison.Store_Number)
AND (#XX.Transaction_Date = dbo.tblComparison.Transaction_Date)
WHERE ((#XX.Transaction_Date >GetDate()-60) AND (dbo.tblComparison.Comparison_Id Is Null))
-- CREATE TABLE #Count(
-- Store_Number int,
-- Counter int
-- )
--
-- DECLARE @store int
-- DECLARE @count int
--
-- SET @count = 1
--
-- --Create a cursor
-- DECLARE crsr CURSOR FOR
-- SELECT Store_Number
-- FROM #YY
--
-- --Get First Record
-- OPEN crsr
-- FETCH NEXT FROM crsr
-- INTO @store
--
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
-- INSERT INTO #Count(Store_Number,Counter)
-- VALUES(@store,@count)
--
-- PRINT @store
-- PRINT @count
--
-- SET @count = @count + 1
--
-- --Get next record
-- FETCH NEXT FROM crsr
-- INTO @store
-- END
--
-- CLOSE crsr
-- DEALLOCATE crsr
--
-- UPDATE #YY
-- SET #YY.Comparison_Id = #Count.Counter
-- FROM #Count
-- WHERE #YY.Comparison_Id = 987435 AND #YY.Store_Number = #Count.Store_Number
SELECT * FROM #YY
--Check for Duplicate records in the tblComparison records.
CREATE TABLE #tempdupdata(
Store_Number int,
Transaction_Date char(10),
Card_Type nvarchar(12),
POS money,
CITIBANK money,
VARIANCE money,
Corrected bit,
Corrected_Id int
)
INSERT INTO #tempdupdata
SELECT * FROM #YY
GROUP BY Store_Number,Transaction_Date,Card_Type,POS,CITIBANK,VARIANCE,Corrected,Corrected_Id
HAVING COUNT(*) > 1
DELETE #YY
FROM #YY
INNER JOIN #tempdupdata
ON #YY.Store_Number = #tempdupdata.Store_Number
AND #YY.Transaction_Date = #tempdupdata.Transaction_Date
AND #YY.Card_Type = #tempdupdata.Card_Type
AND #YY.POS = #tempdupdata.POS
AND #YY.CITIBANK = #tempdupdata.CITIBANK
AND #YY.VARIANCE = #tempdupdata.VARIANCE
AND #YY.Corrected = #tempdupdata.Corrected
AND #YY.Corrected_Id = #tempdupdata.Corrected_Id
INSERT INTO #YY
SELECT * FROM #tempdupdata
SELECT * FROM #YY
--Actually creating the tblComparison table.
-- DROP TABLE tblComparison
-- CREATE TABLE tblComparison (
-- Comparison_Id int,
-- Store_Number int,
-- Transaction_Date char(10),
-- Card_Type nvarchar(12),
-- POS money,
-- CITIBANK money,
-- VARIANCE money,
-- Corrected bit,
-- Corrected_Id int
-- CONSTRAINT Comparison_PK PRIMARY KEY (Store_Number,Transaction_Date,Card_Type)
-- )
INSERT INTO dbo.tblComparison(Store_Number,Transaction_Date,Card_Type,POS,CITIBANK,VARIANCE,Corrected,Corrected_Id)
SELECT * FROM #YY
SELECT * FROM dbo.tblComparison
DROP TABLE #XX
DROP TABLE #YY
DROP TABLE #tempdupdata
DROP TABLE #SETTSUMM
DROP TABLE #Temp_POSINFO
-- DROP TABLE #Count
END
June 12, 2009 at 5:40 pm
Hello again,
With Triggers eliminated then permissions would be the most likely cause.
It could also have been an issue with a constraint on tblComparison, but as you mention the same SP call works for a SysAdmin, then that is pretty well eliminated.
Definitely add some basic error handling to the SP. For example trapping and writing errors to a log, would make future diagnosis straight forward.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply