Absurd project: stored

Showing posts with label stored. Show all posts

Friday, March 30, 2012

Help Identifying Full table scans with objectname

Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon

Help Identifying Full table scans with objectname

Wednesday, March 28, 2012

help getting parameters back from a stored procedure

Hello everyone,

I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.

--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation

--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0')}

--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer

' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn

' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@.RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@.BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@.BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@.BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@.ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@.RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@.BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@.BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@.BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@.DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@.ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum

' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)

' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@.RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@.BulkRan")
gstrBulkForName = rsTableInfo.Fields("@.BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@.BulkJit")
gstrID = rsTableInfo.Fields("@.ID")
intErrorNum = rsTableInfo.Fields("@.ErrorNum")

End Sub

any help would be appreciatedactually i just solved my own problem:

at the end of the stored procedure i selected the columns i wanted to return and that did it.

select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum

i also just did a regular sql statement where i called the EXEC command to run my stored procedure.

EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'

hopefully if someone else has the same problem i've had they can see what i did.

help forms

The below stored procedure works. However, I am trying to use a text box
from a temp form for the where clause.
WHERE Transactions.TransactionID = [forms]![form1]![text0]
I even tried changing first line:
Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
And changed last line to:
WHERE Transactions.TransactionID = @.TID
So far, I haven't been able to have this parameter work from a temp form
with the parameter typed into the text0 box.
any help here? I get ado error near "!" or something to that affect.

this below is what does work fine:

Alter PROCEDURE S3 @.TID varchar (255)
AS
SELECT Transactions.TransactionID, Transactions.AccountID,
Transactions.TransactionNumber, Transactions.TransactionDate,
Transactions.TransactionDescription, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE Transactions.TransactionID = @.TID"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> The below stored procedure works. However, I am trying to use a text box
> from a temp form for the where clause.
> WHERE Transactions.TransactionID = [forms]![form1]![text0]
> I even tried changing first line:
> Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> And changed last line to:
> WHERE Transactions.TransactionID = @.TID
> So far, I haven't been able to have this parameter work from a temp form
> with the parameter typed into the text0 box.
> any help here? I get ado error near "!" or something to that affect.
> this below is what does work fine:
> Alter PROCEDURE S3 @.TID varchar (255)
> AS
> SELECT Transactions.TransactionID, Transactions.AccountID,
> Transactions.TransactionNumber, Transactions.TransactionDate,
> Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> Transactions.DepositAmount
> FROM Transactions
> WHERE Transactions.TransactionID = @.TID

SQL Server is a pure server application - it doesn't know anything about
forms or other presentation of data. You need to capture the parameter value
in your client application (Access?), then pass it to the stored procedure
using a client library such as ADO. If you're unsure how to go about this,
you might want to post in a forum for whichever client application you have.

Simon|||I thought sql server was about sql. You're saying there's no way to pass
data from a form to it as a parameter?
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40e99857$1_2@.news.bluewin.ch...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
> news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> > The below stored procedure works. However, I am trying to use a text
box
> > from a temp form for the where clause.
> > WHERE Transactions.TransactionID = [forms]![form1]![text0]
> > I even tried changing first line:
> > Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> > And changed last line to:
> > WHERE Transactions.TransactionID = @.TID
> > So far, I haven't been able to have this parameter work from a temp form
> > with the parameter typed into the text0 box.
> > any help here? I get ado error near "!" or something to that affect.
> > this below is what does work fine:
> > Alter PROCEDURE S3 @.TID varchar (255)
> > AS
> > SELECT Transactions.TransactionID, Transactions.AccountID,
> > Transactions.TransactionNumber, Transactions.TransactionDate,
> > Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> > Transactions.DepositAmount
> > FROM Transactions
> > WHERE Transactions.TransactionID = @.TID
> SQL Server is a pure server application - it doesn't know anything about
> forms or other presentation of data. You need to capture the parameter
value
> in your client application (Access?), then pass it to the stored procedure
> using a client library such as ADO. If you're unsure how to go about this,
> you might want to post in a forum for whichever client application you
have.
> Simon|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I thought sql server was about sql. You're saying there's no way to pass
> data from a form to it as a parameter?

You are right SQL Server is about SQL, but forms are not SQL per se.

The difference between Access and SQL Server is that Access is a one-tier
application. That is, the query language knows about the GUI and can
interact with it directly. This makes it very easy to write application,
but it comes with a price: it does not scale well, and is best suited for
single-user applications. Also, since the application accesses the database
directly, that means a lot of network traffic if the database is on the
other machine.

SQL Server is intended for applications with two or more tiers. That is,
there is a client on one machine, which passes queries or remote procedure
calls to the server which sends data back. Since machine that hosts the
database does not have do care about drawing GUI:s it can concentrate
on managing the data. And since the server program works on the server,
only the data that the user actually requests has to travel the network.

There are of course several ways to pass a parameter from a form to SQL
Server, but the point is that once the parameter reaches SQL Server, SQL
Server has no idea where it came from. It could be from an input form, it
could have been a read from a file, or it could be a constant in the calling
client program.

To talk to SQL Server from a client, there is a whole range of client
libraries to choose from. From Access the most commonly used today is
ADO. But really how you this best in Access, is probably a question
for comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Monday, March 26, 2012

help for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

Help Fix Slow Query.

I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

I've simplified my actual queries for readability.

-- @.filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@.filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @.filter

When I run the query directly in Query Analyzer, it runs very fast.

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))

When I put the parameters in the stored proc it runs fast.

CREATE PROCEDURE queryPlayerStations
AS
DECLARE @.filter INTEGER
SET @.filter = NULL

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations

Anyone have any ideas what I can do to improve the stored proc case?Just a quick *guess* before I leave office for tonight...

The optimization in SQL Server works differently depending
on where the parameter is defined (as a procedure call argument or inside using DECLARE). In one of the cases,
it doesn't have enough info to optimize in the best way.|||As Coolberg implied, what happens if you do this:

ALTER PROCEDURE queryPlayerStations(@.filterIN INTEGER)
AS
DECLARE @.filter INTEGER
SET @.filter = @.filterIN

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filterIN INTEGER
SET @.filterIN = NULL
EXEC dbo.queryPlayerStations @.filterIN

Friday, March 23, 2012

Help Cursor Based Stored Procedure Is Getting Slower and Slower!

I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.

The procedure works like this.
The first Cursor stores a unique account and duedate combination from
the view.
It then finds all the accts in the view that have that account duedate
combo and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because a
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups.

Here is my procedure I hope someone can shead some light on this. My
boss is giving me heck about it. (I think he thinks Girls cant code!)
I got this far I hope someone can help me optimize it further.

CREATE PROCEDURE dbo.sp_PromiseStatus
AS

BEGIN
SET NOCOUNT ON

/* Global variables */
DECLARE @.tot_pay money
DECLARE @.rec_upd VARCHAR(1)
DECLARE @.todays_date varchar(12)
DECLARE @.mActivityDate2_temp datetime
DECLARE @.tot_paydate datetime
/* variables for cursor ACT_CUR1*/
DECLARE @.mAcct_Num1 BIGINT
DECLARE @.mDueDate1 datetime

/* variables for ACT_CUR2 */
DECLARE @.mAcct_Num2 BIGINT
DECLARE @.mActivity_Date2 datetime
DECLARE @.mPromise_Amt_1 money
DECLARE @.mPromise_Status varchar(3)
DECLARE @.mCurrent_Due_Amt money
DECLARE @.mDPD int
DECLARE @.mPromise_Date datetime

SELECT @.todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''

DECLARE ACT_CUR1 CURSOR FOR
SELECT DISTINCT
A.ACCT_NUM,
A.DUE_DATE
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS A
OPEN ACT_CUR1
FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.rec_upd = 'N '

DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)
WHERE B.ACCT_NUM = @.mAcct_Num1
ANDB.DUE_DATE = @.mDueDate1
ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASE
B.Time_Obtained
WHEN 0 THEN 0
ELSE 1
END Desc, B.Time_Obtained

OPEN ACT_CUR2
FETCH NEXT FROM ACT_CUR2
INTO @.mAcct_Num2 ,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

--CHECK----------------------
--DECLARE @.PrintVariable2 VARCHAR (8000)
--SELECT @.PrintVariable2 = CAST(@.MACCT_NUM2 AS VARCHAR)+'
'+CAST(@.MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@.MPROMISE_AMT_1 AS
VARCHAR)+' '+CAST(@.MPROMISE_STATUS AS VARCHAR)+'
'+CAST(@.mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@.mDPD AS VARCHAR)+'
'+CAST(@.mPromise_Date AS VARCHAR)
--PRINT @.PrintVariable2
--END
CHECK------------------

IF @.mDPD >= 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE < @.mActivity_Date2 + 15
--CHECK----------------------
--DECLARE @.PrintVariable3 VARCHAR (8000)
--SELECT @.PrintVariable3 ='Greater=30 DOLLARS COLLECTED'
--PRINT @.PrintVariable3
--END
CHECK------------------
END
ELSE IF @.mDPD < 30
BEGIN

SELECT @.tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @.mAcct_Num2
ANDC.ACTIVITY_DATE >= @.mActivity_Date2
ANDC.ACTIVITY_DATE BETWEEN @.mActivity_Date2 AND
@.mPromise_Date + 5
--CHECK---------------------
--DECLARE @.PrintVariable4 VARCHAR (8000)
--SELECT @.PrintVariable4 ='Less 30 DOLLARS COLLECTED'
--PRINT @.PrintVariable4
--END CHECK------------------
END

------------MY REVISED
LOGIC-----------------
IF @.rec_upd = 'N'
BEGIN

IF @.mDPD >= 30
BEGIN

SELECT @.mActivityDate2_temp = @.mActivity_Date2 + 15

--DECLARE @.PrintVariable5 VARCHAR (8000)
--SELECT @.PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'
--PRINT @.PrintVariable5

END
ELSE IF @.mDPD < 30
BEGIN
SELECT @.mActivityDate2_temp = @.mPromise_Date + 5

--DECLARE @.PrintVariable6 VARCHAR (8000)
--SELECT @.PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'
--PRINT @.PrintVariable6

END

IF @.tot_pay >= 0.9 * @.mCurrent_Due_Amt--used to be promise amt
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_COLL = @.tot_pay
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PK.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

SELECT @.rec_upd = 'Y '
END
IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS NULL)
AND( @.mActivityDate2_temp > @.todays_date )--need to put 1day
of month here for snapshot9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into OP which is the original Activity Date.
--The record will hold this date until it goes into PK,PB,or
IP.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.mActivity_Date2
WHERE CURRENT OF ACT_CUR2
END

END
ELSE IF ((@.tot_pay < 0.9 * @.mCurrent_Due_Amt) OR @.tot_pay IS
NULL)
AND( @.mActivityDate2_temp <= @.todays_date )--need to put 1day
of month here for snapshot 9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'PB',
TOTAL_DOLLARS_COLL = case when @.tot_pay is null
then 0 else @.tot_pay end
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into PB.
IF @.mPromise_Status IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END
END
ELSE IF @.rec_upd = 'Y'
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SETPROMISE_STATUS = 'IP',
TOTAL_DOLLARS_COLL = 0
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into IP.
IF @.mPromise_Status NOT IN ('IP')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @.todays_date
WHERE CURRENT OF ACT_CUR2
END

END

FETCH NEXT FROM ACT_CUR2 INTO @.mAcct_Num2
,@.mActivity_Date2,@.mPromise_Amt_1,@.mPromise_Status ,@.mCurrent_Due_Amt,@.mDPD,@.mPromise_Date
END
CLOSE ACT_CUR2
DEALLOCATE ACT_CUR2

FETCH NEXT FROM ACT_CUR1 INTO @.mAcct_Num1 , @.mDueDate1
END
CLOSE ACT_CUR1
DEALLOCATE ACT_CUR1

SET NOCOUNT OFF

END
GOWithout getting into code, I think you should consider using faster type
of cursor if possible. FORWARD ONLY for example|||If you are a beginner then avoid cursors. They are rarely a necessary or
good solution to a problem in SQL. You will learn bad practices if you mess
with cursors before you can write good set-based SQL.

It doesn't look like you are doing anything in your code for which a cursor
makes sense so I recommend you start again from scratch. If you need help,
try to pare the problem down to it's essential features and then post again
with more information (DDL, some sample data, required results). This
article explains how best to ask for help:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||>> I am beginner at best so I hope someone that is better can help. <<

Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!

>> The problem is that this procedure is taking longer and longer to
run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.

>> The first Cursor [magnetic tape file] stores a unique account and
duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.

>> It then finds all the accts in the view that have that account
duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)

>> .. and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromises
SET status_date = CURRENT_TIMESTAMP,
promise_status = CASE WHEN ..END,
total_dollars_coll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromises AS A, Applicablepayments AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromises.acct_num = A.account_num
AND ApplicablePromises.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.|||Philip Mette (philipdm@.msn.com) writes:
> I am begginner at best so I hope someone that is better can help.
> I have a stored procedure that updates a view that I wrote using 2
> cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> do it using reqular transact SQL.
> The problem is that this procedure is taking longer and longer to run.
> Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records every month.

While cursor-based solutions sometimes may be defendible and even be
the only solution, the cost for a cursor can be pretty severe. The
corresponding set-based solution is often several magnitudes faster.
Processing 30000 rows with a cursor is not going to be anywhere near
fast.

Just like David I would recommend you to start with a clean paper. I
started to look at your code, but, frankly, all the uppercase and the
poor formatting is quite deterring. And not knowing the tables and not
having sample data is not making things any easier.

In the short run, you may make some improvements by making the cursors
INSENSITIVE by adding this keyword before CURSOR, and by replacing
WHERE CURRENT OF with the actual key values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok well I have been yelled at worse;) No I see what you are saying. I
should have used Char variable not Varchar(1).That was a adjustment
later on that frankly wasnt changed properly. I used Cursors based on
a recommendation by a "SQL Guru" Obviously this is not a good
description of him. I am going to try this using the skeleton you gave
me.It is pointing me in a right direction I think.
Again please understand I am a beginner Query writer when it comes to
SQL and I am very good with VB ,.Net and Java(Used to do front end
work and analysis not DB.)I quess I was thinking of this like code
language,ie the use of arrays etc. Thanks for your 'direct' honesty
and help.

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0411242243.2c96bbf0@.posting.google.com>...
> >> I am beginner at best so I hope someone that is better can help. <<
> Virutally everything you have done is wrong.
> 1) The data element names violate ISO-11179 standards. What does a
> VARCHAR(1) mean?
> 2) You should never write a cursor in an applications. Thye are for
> utility programs.
> 3) You are using FLOAT and MONEY for currency amounts. And you got
> the CAST() syntax wrong.
> 4) Unlike the 3GL languages, SQL has temporal data types. Why did you
> avoid them in favor of COBOL-style string processing? You even load
> the current timestamp into a string!
> >> The problem is that this procedure is taking longer and longer to
> run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
> partly because we add new records [sic] every month. <<
> No. The basic algorithm you have written is a COBOL tape file merge.
> 30K rows is tiny.
> >> The first Cursor [magnetic tape file] stores a unique account and
> duedate combination from the view. <<
> The view name has a silly "vw_" prefix in violation of ISO-11179. And
> a table has no records!!! Rows are not records! Until you understand
> that you will never write good SQL. A table is a set, and in SQL we
> process data in whole sets, not single records.
> >> It then finds all the accts in the view that have that account
> duedate combo .. <<
> No, you write a query that finds the set of (account_nbr, duedate)
> >> .. and loads them into Cursor 2 this groups them together for data
> manipulation. The accounts have to be grouped this way because an
> account can have different due dates and multiple records within each
> account due date combo and they need to be looked at this way as
> little singular groups. <<
> Think about the phrase "single group" for a minute :) A GROUP BY is
> used to build grouped tables.
> What you seem to be trying to do is update a table of promises. That
> should be one UPDATE statement. The skeleton for the statement would
> be something like this:
> CREATE PROCEDURE PromiseStatus (..)
> AS
> UPDATE ApplicablePromises
> SET status_date = CURRENT_TIMESTAMP,
> promise_status = CASE WHEN ..END,
> total_dollars_coll
> = (SELECT SUM(P.pay_amt),
> FROM ApplicablePromises AS A, Applicablepayments AS P
> WHERE P.acct_num = A.acct_num
> AND ApplicablePromises.acct_num = A.account_num
> AND ApplicablePromises.due_date = A.due_date
> AND P.activity_date BETWEEN ...
> AND GROUP BY A.acct_num, A.due_date)
> WHERE ... ;
> You put all the IF_THEN_ELSE logic into a CASE expression. You put
> the total into a scalar subquery. You use temporal functions to get
> the current timestamp.
> Quit thinking about one column at a time. That is how you process a
> field in a record. A table is made of rows, and you think of the row
> as the unit of work.|||Thanks Erland.
A few Questions:
1.What does insensitive do?
2.Instead of Where Current Of
If I used Activity_Record_Num(The Primary Key)
which is distinctive would that work?
3.I use a statement called (UPDLOCK) in this part of the code:
DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWPROMISEACTIVITYRECORDS B (UPDLOCK)

Does that effect performance speed as well? This is the only process
that runs against this table so I am wondering if it is needed.

Just so you know I am working on a rewrite like you suggested. In the
short, I would like to add these suggestions that you made to the
cursor based SP.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ACF2C23D6DBYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > I am begginner at best so I hope someone that is better can help.
> > I have a stored procedure that updates a view that I wrote using 2
> > cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
> > do it using reqular transact SQL.
> > The problem is that this procedure is taking longer and longer to run.
> > Up to 5 hours now! It is anaylizing about 30,000 records. I think
> > partly because we add new records every month.
> While cursor-based solutions sometimes may be defendible and even be
> the only solution, the cost for a cursor can be pretty severe. The
> corresponding set-based solution is often several magnitudes faster.
> Processing 30000 rows with a cursor is not going to be anywhere near
> fast.
> Just like David I would recommend you to start with a clean paper. I
> started to look at your code, but, frankly, all the uppercase and the
> poor formatting is quite deterring. And not knowing the tables and not
> having sample data is not making things any easier.
> In the short run, you may make some improvements by making the cursors
> INSENSITIVE by adding this keyword before CURSOR, and by replacing
> WHERE CURRENT OF with the actual key values.|||Philip Mette (philipdm@.msn.com) writes:
> Thanks Erland.
> A few Questions:
> 1.What does insensitive do?

The meaning of INSENSITIVE is that the dataset does not change
once the cursor has been created. SQL Server copies the data to a
worktable. This may sound like some overhead, and it probably is, but
it's a small overhead in comparison with the iterative processing.

The default cursor type is keyset. I think it means that the keys are
stable, but updates in non-key columns will be reflected. But I have
never fully grasped keyset cursors so I could be wrong. What I have
seen is some really horrible query plans to set up a keyset-driven
cursor. Sure, it was in 6.5, but I see no reason to try it again.

> 2.Instead of Where Current Of
> If I used Activity_Record_Num(The Primary Key)
> which is distinctive would that work?

I didn't look to close on your code, but you should pick data from
the cursor which identifies the current row(s) being processed. (Note
that you could run a cursor over an aggregate, and thus update several
rows at same time. There are situations when this is a possible solution.

> 3.I use a statement called (UPDLOCK) in this part of the code:
>...
> Does that effect performance speed as well?

Not very likely. Since you don't seem to have an active transaction, the
effect of the UPDLOCK is none anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

I got the process run time to 2:00 minutes! The solution was to create
a composite index on Account and Due_date in the tables, which is what
is in my where clauses in my select statements. That and changing the
cursors to Insensitive, made this query fast. This amount of time is
more than acceptable. Thanks for pointing me in the right direction. I
am going to continue to look into a transact SQL solution in the
future.
Thanks Again,

Jill
(Using Phil's Login Name. Dont have a google acct.)

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95ADDFEC37F0BYazorman@.127.0.0.1>...
> Philip Mette (philipdm@.msn.com) writes:
> > Thanks Erland.
> > A few Questions:
> > 1.What does insensitive do?
> The meaning of INSENSITIVE is that the dataset does not change
> once the cursor has been created. SQL Server copies the data to a
> worktable. This may sound like some overhead, and it probably is, but
> it's a small overhead in comparison with the iterative processing.
> The default cursor type is keyset. I think it means that the keys are
> stable, but updates in non-key columns will be reflected. But I have
> never fully grasped keyset cursors so I could be wrong. What I have
> seen is some really horrible query plans to set up a keyset-driven
> cursor. Sure, it was in 6.5, but I see no reason to try it again.
> > 2.Instead of Where Current Of
> > If I used Activity_Record_Num(The Primary Key)
> > which is distinctive would that work?
> I didn't look to close on your code, but you should pick data from
> the cursor which identifies the current row(s) being processed. (Note
> that you could run a cursor over an aggregate, and thus update several
> rows at same time. There are situations when this is a possible solution.
> > 3.I use a statement called (UPDLOCK) in this part of the code:
> >...
> > Does that effect performance speed as well?
> Not very likely. Since you don't seem to have an active transaction, the
> effect of the UPDLOCK is none anyway.|||Hello guys,

I have may be similar problem.
I need to recalculate a progressive balance based on a table with the
following structure:

EcrID varchar(15)
AccountNumber varchar(15)
EcrDate DateTime
EcrAmount float
ProgBalance float

The program has to calculate the progbalance which is for each row :
progbalance=progbalance + EcrAmount
Rows are sorted by AccountNumber and EcrDate.

Is it possible to that by a single update statement or do I have to
use cursors?

(Sorry for my english)

Many thanks for your help.|||On 29 Nov 2004 10:11:58 -0800, badiane wrote:

>Hello guys,
>I have may be similar problem.
>I need to recalculate a progressive balance based on a table with the
>following structure:
>EcrID varchar(15)
>AccountNumber varchar(15)
>EcrDate DateTime
>EcrAmount float
>ProgBalance float
>The program has to calculate the progbalance which is for each row :
>progbalance=progbalance + EcrAmount
>Rows are sorted by AccountNumber and EcrDate.
>Is it possible to that by a single update statement or do I have to
>use cursors?
>(Sorry for my english)
>Many thanks for your help.

Hi badiane,

The best option is to remove ProgBalance from the table and calculate it
in a view instead. If it's a column in the table, you'll have to keep
updating large chunks of data in the table whenever rows are inserted (or
even changed) in a non-chronological order.

CREATE VIEW MyView
AS
SELECT a.EcrID, a.AccountNumber, a.EcrDate, a.EcrAmount,
(SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate) AS ProgBalance
FROM MyTable AS a
go
(untested)

If you really must store it in a table, you can use the same technique for
the update:

UPDATE MyTable
SET ProgBalance = (SELECT SUM(b.EcrAmount)
FROM MyTable AS b
WHERE b.AccountNumber = MyTable.AccountNumber
AND b.EcrDate <= MyTable.EcrDate)
WHERE ......
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The best option is to remove ProgBalance from the table and calculate it
> in a view instead. If it's a column in the table, you'll have to keep
> updating large chunks of data in the table whenever rows are inserted (or
> even changed) in a non-chronological order.

On the other hand, this may never happen in a table like this. (That
would be a busines rule that the balance shown is always in booked order.)
Computing the balance on each invocation may be very expensive, for an
account with many transactions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

badiane (alioune_badiane@.yahoo.com) writes:
> I have may be similar problem.
> I need to recalculate a progressive balance based on a table with the
> following structure:
> EcrID varchar(15)
> AccountNumber varchar(15)
> EcrDate DateTime
> EcrAmount float
> ProgBalance float
> The program has to calculate the progbalance which is for each row :
> progbalance=progbalance + EcrAmount
> Rows are sorted by AccountNumber and EcrDate.
> Is it possible to that by a single update statement or do I have to
> use cursors?

Standard remark:

If you in your posting include the following:

o CREATE TABLE statement for your table(s).
o INSERT statements with sample data.
o The desired result given that sample.

You have good chances to get a tested solution.

This is an untested solution:

UPDATE tbl
SET ProgBalance = (SELECT SUM(EcrAmount)
FROM tbl b
WHERE b.AccountNumber = a.AccountNumber
AND b.EcrDate <= a.EcrDate)
FROM tbl b

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 29 Nov 2004 22:30:51 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The best option is to remove ProgBalance from the table and calculate it
>> in a view instead. If it's a column in the table, you'll have to keep
>> updating large chunks of data in the table whenever rows are inserted (or
>> even changed) in a non-chronological order.
>On the other hand, this may never happen in a table like this. (That
>would be a busines rule that the balance shown is always in booked order.)
>Computing the balance on each invocation may be very expensive, for an
>account with many transactions.

Hi Erland,

Indeed. That is why I did include the UPDATE statement as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I think that the view is a good solution, I tested with the following
data and think that it is OK. Using a view is it a good solution for
performance point of vue?

The table:
---

CREATE TABLE [dbo].[test2_h_ecr] (
[numero_operation] [nvarchar] (14) NOT NULL ,
[numero_ecriture] [smallint] NOT NULL ,
[numero_compte] [nvarchar] (13) NULL ,
[date_ecriture] [datetime] NULL ,
[montant] [float] NULL ,
[solde_progressif] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test2_h_ecr] WITH NOCHECK ADD
CONSTRAINT [PK_test2_h_ecr] PRIMARY KEY CLUSTERED
(
[numero_operation],
[numero_ecriture]
) ON [PRIMARY]
GO

The sample Data :
------

10003350136522000811012003-12-16 12:41:10.000-425690.0-425690.0
10004012025622000811012004-01-12 00:00:00.00042569.0-383121.0
10004041103822000811012004-02-10 00:00:00.00012795.0-370326.0
10004041103922000811012004-02-10 00:00:00.00029774.0-340552.0
10004070030822000811012004-03-10 00:00:00.00042569.0-297983.0
10004106011322000811012004-04-15 00:00:00.00042569.0-255414.0
10004138009222000811012004-05-17 00:00:00.00042569.0-212845.0
10004167017422000811012004-06-15 00:00:00.00042569.0-170276.0
10004197033722000811012004-07-15 00:00:00.00021840.0-148436.0
10004197033822000811012004-07-15 00:00:00.00020729.0-127707.0
10004229009222000811012004-08-16 00:00:00.00042569.0-85138.0

The view (modified a bit):
--------
CREATE VIEW MyView
AS
SELECT a.numero_operation, a.numero_ecriture, a.numero_compte,
a.date_ecriture,
a.montant,
(SELECT SUM(b.montant)
FROM test2_h_ecr AS b
WHERE b.numero_compte = a.numero_compte
AND b.date_ecriture <= a.date_ecriture
AND b.numero_operation <= a.numero_operation
AND b.numero_ecriture <= a.numero_ecriture) AS ProgBalance
FROM test2_h_ecr AS a
go

The compared result sql :
--------

select * from MyView order by
date_ecriture,numero_operation,numero_ecriture

select * from test2_h_ecr order by
date_ecriture,numero_operation,numero_ecriture

I obtained the same result is it. Is it correct?

Many thanks guys.|||On 1 Dec 2004 11:22:14 -0800, badiane wrote:

>I think that the view is a good solution, I tested with the following
>data and think that it is OK.

Hi badiane,

I would test it with different values and different combinations of values
for numero_compte, date_ecriture, numero_operation and numero_ecriture;
your current test set is way too limited and I'm not sure if the view will
always return the results you expect.

> Using a view is it a good solution for
>performance point of vue?

I *think* it is, but it does depend. If you often change data without
following the chronological order, the view would definitely win (as such
a change would require updates to many rows in the table). On the other
hand, if most changes are for "recent" dates, the total change frequency
is low and the ProgBalance is used extensively, you might find prefer to
store the ProgBalance and use triggers to recalculate ProgBalance for
changed rows (and rows that come "later").

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

help creating a Stored Procedure

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..

I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null)go

Here is what i have so far for the stored procedure

create procedure getcustomProfile @.UserIduniqueidentifierasSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoifnot exists (select *from dbo.aspnet_Userswhere UserId = @.UserId)BEGINDeclare @.UserIduniqueidentifier update (dbo.content_Profile)set UserId = @.UserIdwhere UserId = @.UserIdEndELSEBEGIN INSERT INTO dbo.custom_Profile (UserId) VALUES (@.UserId)END

|||

Hi,

I'm sure you can achieve what you want from a stored procedure, but there are easier ways. The article at:

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

This shows how to create a table using SMO.

Hope this helps.

Paul

|||Thats cool something to look into later, but for now i'm just working on studying the creation of Stored Procedures. Won't learn how to create them if a program does most of the work for me, why i prefer to do it this way for now Smile

|||

Hi,

No problem with that. Mind you, think i mis-read it anyway - thought you needed to create a table from a stored procedure. You don't actually say what is wrong with what you've done so far. I might be wrong, but I don't think you need the line 'Declare @.UserIduniqueidentifier'as you have already passed this in as a parameter value.

Let me know what appears to be going wrong and I'll try and help further.

Paul

|||

I was just trying out the table and sp in my site, and when i create a new user account a new row isn't created in the column for the table..Trying to get the stored procedure to take the UserId from the aspnet_Users table example (59afcb85-c20c-4937-8ab9-a44a57e22ce0). Than Insert that UserId into my table custom_Profile (Column UserId) and do this for each new user account..

|||forgot to put in the T-SQL ...lol here you go

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,SexualOrientationnvarchar(20)null,Heightnchar(10)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null,Circumcisednvarchar(20)null)gocreate procedure getcustomProfile @.UserIduniqueidentifier asSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoBEGININSERT dbo.custom_Profile (UserId)VALUES (@.UserId)SELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdEND

|||

Oh and when i remove the Declare @.UserId uniqueidentifier i get a error::

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@.UserId".

|||

Hi,

Apologies, I misunderstood. The problem is passing of the uniqueidentifier - this will cause the SP to fail. You should change this to an nvarchar field. I messed around a little and the following SP will loop through all of the users:

CREATE procedure [dbo].[getcustomProfile] @.UserIdnvarchar(150)ASDECLARE UserInsertCURSORKEYSETFOR SELECT UserIDFROM dbo.aspnet_UsersDECLARE @.Usernvarchar(150)OPEN UserInsertFETCH NEXT FROM UserInsertINTO @.UserWHILE (@.@.fetch_status = 0)BEGININSERT dbo.custom_Profile (UserId)VALUES (@.User)FETCH NEXT FROM UserInsertINTO @.UserENDCLOSE UserInsertDEALLOCATE UserInsert

This does work, and you can use it as your starting point for the actual SP you need to call each time from your form.

Hope it helps,

Paul

|||So i need to call the SP from the code behind correct? for it to insert the UserId into the table upon creation of a new user account|||

Your logic in your original stored proc is incorrect. You have

IF NOT EXISTS(...)

UPDATE

ELSE

INSERT

If you think about it, how can you update something that doesnt exist? It should be the other way.

IF NOT EXISTS(...)

INSERT

ELSE

UPDATE

Fix your proc according to the logic above and post back if you have any issues.

|||

Hi,

Yes, you should call the SP from BLL once you've gathered together the results of your form. I'm assuming from the way you set out the SP that you're intending to do the User insert first and then then pass in the new UserID (with other parameters) - you should be setting up an output parameter from your Add_User SP. You actually don't need your If Exists part of the SP because you can check that the UserID has been created before allowing the call to this SP - the fact that it is a uniqueidentifier means it won't exist already. Personally, I always keep Insert, Update and Delete SPs seperate - but that is just personal preference.

Let e know if you need anything else on this.

Cheers,

Paul

|||

The UserId is the only data set getting populated automatically upon Registration of the site, the other columns aren't populated until they have registered. Than they get Re Directed to another page where they fill in the information via FormView in the User Control Panel. Than that information is displayed by another SqlDataSource and FormView on their profile page but without the Delete,Insert,Update etc. Basically using a Content Management system for the user profile to display textual information, on their page.

I'm going to use the UserId so i can pass it into the URL for unique pages like most communities (Myspace etc etc)..

|||Why is that the aspnet_Membership,Users,UsersInRoles etc don't use CodeBehind to pass the UserId into each table? cause thats what i want to do..Pass the UserId from the aspnet_Users Table into my table|||

Hi,

You can do all of that by customising the sqlMembershipProvider. It's not as difficult as you might think - thankfully! There's a pretty good tutorial to get you started at:

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

I hope this helps.

Paul

sql

Help coverting a stored procedure to a view

Can someone help me convert this stored procedure to a view? It is using two UDFs.

I appreciate this very much!

@.Startdatetime, @.End datetime ASSELECTC.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,dbo.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel, dbo.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description FROMtblClients CINNERJOIN tblPMTs PON C.Client_ID = P.Client_IDINNERJOIN tblPMTReceipts PRON P.PMT_ID = PR.PMT_IDINNERJOINtblClientAddresses AON C.Client_ID = A.Client_IDLEFTOUTER JOINtblSoftCreditsPMTS SCON C.Client_ID = SC.SC_Client_IDWHERE(PR.PaymentDateBETWEEN @.StartAND @.End)GROUPBY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.TitleORDERBY pmtLevelRETURN

Hi,

That stored procedure cannot be converted into a View because of the the parameters in the WHERE clause and the fact that there is a Group By that hides the PaymentDate from the results.

SQL Views do not support parameters. Because the PaymentDate is not part of the output the users of your View would not be able to provide filter by PaymentDate.

The alternative is to convert it to a SQL function that returns a table:

CREATE FUNCTION dbo.MyFunction(@.Startdatetime,
@.End datetime)

RETURNS TABLE
AS
RETURN

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description
FROM

tblClients CINNERJOIN
tblPMTs P

ON C.Client_ID = P.Client_IDINNERJOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_IDINNERJOIN
tblClientAddresses A

ON C.Client_ID = A.Client_IDLEFTOUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDateBETWEEN @.StartAND @.End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

This would allow you to pass parameters to the function from a SELECT statement a get a result set without the need to run a stored procedure.

Example of how you would use this function:

SELECT * FROM dbo.MyFunction('1/1/2007','6/1/2007')

Hope this helps!

David

|||

Thank you for sharing with me. I don't think I will be able to use the Table Valued Function with the reporting tool I have. It only works with tables and views.

|||

What is your reporting tool?

Wednesday, March 21, 2012

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Monday, March 19, 2012

help about sql server job

I created a stored procedure.When i execute the sp in Query Analyzer or in
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO

HELP ....difficulty in insert of 2 tables data with id

Hello frdz,

I have two tables in sqlserver 2005.

I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#

Table-1 CUSTOMERFields:
customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)
Table-2 CARD
Fields:
cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not null
 INSERT INTO CUSTOMER (customername ,address,city,emailid)
 VALUES (@.customername,@.address,@.city,@.emailid)
 SELECT @.customerid = SCOPE_IDENTITY()
/* HELP HERE NOT ABLE TO GET DATA OF CARD */
 SELECT @.cardid = cardid from CARD where customerid =@.cardid

Pls tell me how to insert the data ...

There is only one cardid for only one customerid both should be unique no duplication...

One customer can have only one and one card...

If I understand your design correctly, you'll have to insert the CARD record first and get the new identity fieldcardid into a variable. Then you insert into CUSTOMER, using the variable to populate CUSTOMERS.cardid.

This is because cardid is a foreign key in the CUSTOMER table - so it has to be generated first. Just switch your order of inserts and you should be fine. And I advise that you put both insert statements within a transaction (BEGIN TRAN and COMMIT) - if one insert fails, then the other one shouldn't be left in the table.

If you need more on stored procedures, here's a link to a free chapter ondeveloping stored procedures for sql server.

|||

Your database to me seems odd, I would think that each customer could have many cards, while each card could only have 1 customer, but you seem to have it reversed. In any case, this is how you would insert:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

and optionally:

SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

|||

Thanxs everyone for the replies...

Motley...thanxs ... ya i want that 1 customer can have only 1 card.
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

I have created stored procedur for card and customer.

I don't understand what is the above line and how it worksSELECT @.cardid,SCOPE_IDENTITY() ??

This should be written in which stored procedure ? I mean card or customer ?

Should a single stored procedure be created for this ?

Thanxs......waiting for reply

|||

That would return a resultset with 1 row and 2 columns. The first column would be the @.cardid variable that we set earlier in the code, and the second column would be SCOPE_IDENTITY(), which is a function that returns the value of the identity column for the row that was just inserted.

When I said optionally, I meant you could put at the end of the other code like this:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

SELECT @.cardid,SCOPE_IDENTITY()

|||

thanxs very much....

Monday, March 12, 2012

Help ! Nested Stored Procedure, is this the best way???

Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas

> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David
|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary

Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count = 7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas :)
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count => 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas :)
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary :)
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> > Hi all,
> > Excuse my lack of knowledge in this area but what I am trying to do is
> >
> > 1) The user requests that say 4 tickets are to be held
> > 2) The VB program calls a sproc that executes and tries to update the
> > status of each ticket in turn
> > 3) If 4 are available then all is dandy
> > 4) If 4 are not then any updates are rolled back and the user notifed
> >
> > Note this is a true multi user environment, the tickets don't have to
> > be locked in sequence, they are just places on an event
> >
> > Tried this but got odd rollback messages like
> > Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> > Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> > TRANSACTION statement is missing. Previous count = 6, current count => > 7.
> >
> > Line 23 is the return statement
> >
> > SQL 2000 is the beast in use
> >
> > HELP!! Ideas :)
> >
> > Sproc is
> >
> >
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas

> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].&#
91;Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary

Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4P
wEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tick
ets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so tes
t
> and post again when you get deadlocks or double bookings.
> David

Friday, March 9, 2012

Help - With Script

I am a sql novice and would appreciate any help with the following problem.

In a table I have property addresses stored in 6 fields. Field6 always hold
the Post Code. However, fields 4 and 5 are sometime NULL. Using the
desktop integration package we have which interfaces with MS Word when
printing an address in a letter the end results often end up looking like
this.

1 Any Street
AnyTown
AnyCounty
"Null"
"Null"
PostCode

It is not a normal Mail merge so it is not possible to use the functionality
available within MS Word to not print empty fields. Therefore I need to do
a check within SQL on the null field so that when I pass the values which
are printed as fields within MS Word the variables created by the SELECT
statement are passed over like this

1 Any Street
AnyTown
Anycounty
PostCode
"Null"
"Null"

So in brief I guess what I am after is a script which as it passes the
values in fields 1-6 to variable 1-6 it always ensures that the field
containing values end up in the first variables and the remaining variable
are left as Null.

I hope this explanation is not too confusing.

Thanks

David
--

David M Loraine

life is a holiday from eternity - eternity is a long time - so enjoy your
life !!

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004Do you have access to the SQL that generates your return results?

If so you could use the ISNULL() function (not sure if this is DB specific,
I know it works with MS SQL).

So you could do something like this:

SELECT
ISNULL ( Street, '' ),
ISNULL ( Town, '' ),
ISNULL ( County, ''),
ISNULL ( PostalCode, '' ),
ISNULL ( FieldX, '' )
ISNULL ( FieldY, '' )
FROM User_Addresses

Basically the server checks each value as it comes out of the database to
see if its Null, if it is it replaces the null value with whatever is in the
quotes. In my example the null value is simply replaced with an empty
string.

Hope this help.
</Muhd
"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Hi David

You can use something like the following. I had created a table called
'Address' with fields 'Address1', 'Address2', 'City', 'Postcode'. You can
remove the PRINTs. I let them stay in, in case you want to run it in Query
Analyzer for debugging.

DECLARE
@.Address1 varchar(50),
@.Address2 varchar(50),
@.City varchar(50),
@.Postcode varchar(50)

DECLARE Address_Cursor CURSOR
FOR SELECT Address1, Address2, City, Postcode FROM Address

OPEN Address_Cursor
FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.Address1 IS NULL) OR (@.Address1 = '')
BEGIN
SET @.Address1 = @.Address2
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.Address2 IS NULL) OR (@.Address2 = '')
BEGIN
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.City IS NULL) OR (@.City = '')
BEGIN
SET @.City = @.Postcode
SET @.Postcode = ''
END

PRINT @.Address1
PRINT @.Address2
PRINT @.City
PRINT @.Postcode
PRINT '----------'

FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode
END

CLOSE Address_Cursor
DEALLOCATE Address_Cursor

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

The variables par_adr_line1 etc are passed to MS Word to form the address
which is printed in the letters, field 6 always holds the the post code and
as you can see it is always formatted to be in uppercase.

Frequently though fields 4 and 5 are null and consequently when the address
is printed it looks a little untidy as there is a large gap between the last
address line and the post code. What I need to happen is that when a blank
field is found in the dbase the next value down is moved up so that for
example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends up
being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
value in ad.adr_line_6 ends up in par_adr_line5.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

"Muhd" <muhd@.binarydemon.com> wrote in message
news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> Do you have access to the SQL that generates your return results?
> If so you could use the ISNULL() function (not sure if this is DB
specific,
> I know it works with MS SQL).
> So you could do something like this:
> SELECT
> ISNULL ( Street, '' ),
> ISNULL ( Town, '' ),
> ISNULL ( County, ''),
> ISNULL ( PostalCode, '' ),
> ISNULL ( FieldX, '' )
> ISNULL ( FieldY, '' )
> FROM User_Addresses
> Basically the server checks each value as it comes out of the database to
> see if its Null, if it is it replaces the null value with whatever is in
the
> quotes. In my example the null value is simply replaced with an empty
> string.
> Hope this help.
> </Muhd>
> "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > I am a sql novice and would appreciate any help with the following
> problem.
> > In a table I have property addresses stored in 6 fields. Field6 always
> hold
> > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > desktop integration package we have which interfaces with MS Word when
> > printing an address in a letter the end results often end up looking
like
> > this.
> > 1 Any Street
> > AnyTown
> > AnyCounty
> > "Null"
> > "Null"
> > PostCode
> > It is not a normal Mail merge so it is not possible to use the
> functionality
> > available within MS Word to not print empty fields. Therefore I need to
> do
> > a check within SQL on the null field so that when I pass the values
which
> > are printed as fields within MS Word the variables created by the SELECT
> > statement are passed over like this
> > 1 Any Street
> > AnyTown
> > Anycounty
> > PostCode
> > "Null"
> > "Null"
> > So in brief I guess what I am after is a script which as it passes the
> > values in fields 1-6 to variable 1-6 it always ensures that the field
> > containing values end up in the first variables and the remaining
variable
> > are left as Null.
> > I hope this explanation is not too confusing.
> > Thanks
> > David
> > --
> > David M Loraine
> > life is a holiday from eternity - eternity is a long time - so enjoy
your
> > life !!
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||By no means am i an expert and god i hope im not stearing you wrong but im
pretty sure you can simply do what i suggested above, to help you out i
changed the first six lines of your script to reflect what i was talking
about in my earlier post. You shouldn't need to change any other parts of
your script. Note that if the value is null in the database intead of
returning "null" your script should now just return blank data (i.e. an
empty string).

Select isnull(initcap(ad.adr_line_1),'')
par_adr_line1,
isnull(initcap(ad.adr_line_2),'') par_adr_line2,
isnull(initcap(ad.adr_line_3),'') par_adr_line3,
isnull(initcap(ad.adr_line_4),'') par_adr_line4,
isnull(initcap(ad.adr_line_5),'') par_adr_line5,
isnull(upper(ad.adr_line_6),'') par_adr_line6

It might not be the "best" way but its "a" way and it should work.
Best,
Muhd.

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:7HjNb.23$S01.22@.news-binary.blueyonder.co.uk...
> Here is script in question, although it is really just the select part
that
> needs the work on it I believe.
> The variables par_adr_line1 etc are passed to MS Word to form the address
> which is printed in the letters, field 6 always holds the the post code
and
> as you can see it is always formatted to be in uppercase.
> Frequently though fields 4 and 5 are null and consequently when the
address
> is printed it looks a little untidy as there is a large gap between the
last
> address line and the post code. What I need to happen is that when a
blank
> field is found in the dbase the next value down is moved up so that for
> example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends
up
> being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
> value in ad.adr_line_6 ends up in par_adr_line5.
> I hope this clarifies my enquiry
> Select initcap(ad.adr_line_1) par_adr_line1,
> initcap(ad.adr_line_2) par_adr_line2,
> initcap(ad.adr_line_3) par_adr_line3,
> initcap(ad.adr_line_4) par_adr_line4,
> initcap(ad.adr_line_5) par_adr_line5,
> upper(ad.adr_line_6) par_adr_line6
>
> from tenancy_instances ti,
> household_persons ho,
> address_usages au,
> addresses ad
>
> where ti.tin_tcy_refno = '$tenancy_ref'
> and ad.adr_refno = au.aus_adr_refno
> and au.aus_aut_fao_code = 'PAR'
> and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)
> from
> address_usages au2
> where
> au2.aus_par_refno = au.aus_par_refno
> and
> au2.aus_aut_fao_code = 'PAR'
> and sysdate
> between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)
> and
> au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))
> and ti.tin_main_tenant_ind = 'Y'
> and ti.tin_hop_refno = ho.hop_refno
> and ho.hop_par_refno = au.aus_par_refno
> and sysdate between au.aus_start_date and nvl(au.aus_end_date,
sysdate+1)
>
>
>
> "Muhd" <muhd@.binarydemon.com> wrote in message
> news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> > Do you have access to the SQL that generates your return results?
> > If so you could use the ISNULL() function (not sure if this is DB
> specific,
> > I know it works with MS SQL).
> > So you could do something like this:
> > SELECT
> > ISNULL ( Street, '' ),
> > ISNULL ( Town, '' ),
> > ISNULL ( County, ''),
> > ISNULL ( PostalCode, '' ),
> > ISNULL ( FieldX, '' )
> > ISNULL ( FieldY, '' )
> > FROM User_Addresses
> > Basically the server checks each value as it comes out of the database
to
> > see if its Null, if it is it replaces the null value with whatever is in
> the
> > quotes. In my example the null value is simply replaced with an empty
> > string.
> > Hope this help.
> > </Muhd>
> > "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> > news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > > I am a sql novice and would appreciate any help with the following
> > problem.
> > > > In a table I have property addresses stored in 6 fields. Field6
always
> > hold
> > > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > > desktop integration package we have which interfaces with MS Word when
> > > printing an address in a letter the end results often end up looking
> like
> > > this.
> > > > 1 Any Street
> > > AnyTown
> > > AnyCounty
> > > "Null"
> > > "Null"
> > > PostCode
> > > > It is not a normal Mail merge so it is not possible to use the
> > functionality
> > > available within MS Word to not print empty fields. Therefore I need
to
> > do
> > > a check within SQL on the null field so that when I pass the values
> which
> > > are printed as fields within MS Word the variables created by the
SELECT
> > > statement are passed over like this
> > > > > 1 Any Street
> > > AnyTown
> > > Anycounty
> > > PostCode
> > > "Null"
> > > "Null"
> > > > So in brief I guess what I am after is a script which as it passes the
> > > values in fields 1-6 to variable 1-6 it always ensures that the field
> > > containing values end up in the first variables and the remaining
> variable
> > > are left as Null.
> > > > I hope this explanation is not too confusing.
> > > > Thanks
> > > > David
> > > --
> > > > David M Loraine
> > > > life is a holiday from eternity - eternity is a long time - so enjoy
> your
> > > life !!
> > > > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
> > >
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

Friday, March 30, 2012

Help Identifying Full table scans with objectname

Help Identifying Full table scans with objectname

Wednesday, March 28, 2012

help getting parameters back from a stored procedure

help forms

Help for stored procedure and Null...

Monday, March 26, 2012

help for creating stored procedure

Help Fix Slow Query.

Friday, March 23, 2012

Help Cursor Based Stored Procedure Is Getting Slower and Slower!

help creating a Stored Procedure

Help coverting a stored procedure to a view

Wednesday, March 21, 2012

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Monday, March 19, 2012

help about sql server job

HELP ....difficulty in insert of 2 tables data with id

Monday, March 12, 2012

Help ! Nested Stored Procedure, is this the best way???

Help ! Nested Stored Procedure, is this the best way???

Help ! Nested Stored Procedure, is this the best way???

Friday, March 9, 2012

Help - With Script

Absurd project

Blog Archive

About Me