db datatypes: record

Showing posts with label record. Show all posts

Friday, March 30, 2012

Problem with Linked Server

Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
Lalit
I posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit

Problem with Linked Server

Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
LalitI posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit

Wednesday, March 28, 2012

Problem with left join, please help !

I am doing a left join in this query, but only the records that have a join
are displayed.
There is a record in "cases" with no "casecomments" but it is not displayed
.. please help:
SELECT *
FROM cases a
left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)Please provide DDL, sample data and desired results, so we don't have to
guess about these things and so that we provide the right solution.
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:u0z2W0KKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> I am doing a left join in this query, but only the records that have a
join
> are displayed.
> There is a record in "cases" with no "casecomments" but it is not
displayed
> .. please help:
> SELECT *
> FROM cases a
> left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
> MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
>|||Well, I thought it was a fairly simple issue with the structure of the
query.
SELECT *
FROM cases a
left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
Seems like the left join has some issue because if I have a record in the
cases table and there is no record on the casecomments table with the same
caseid then the record is not displayed, that usually happens with 'inner
join', why would it happen with the statement above if it is a left join ?
A
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2O9F5KKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Please provide DDL, sample data and desired results, so we don't have to
> guess about these things and so that we provide the right solution.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:u0z2W0KKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> join
> displayed
>|||> Well, I thought it was a fairly simple issue with the structure of the
> query.
Too many assumptions. If you can't be bothered to provide DDL and something
other than a word problem describing the solution you're after, I guess that
solution isn't too important to you. Next thread for me.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aleks,
Looking at your query, there is no reason why every record from the cases
table should not be displayed... Are you sure the record you are expecting t
o
see is in there? And that the sql you posted is exacttly what you ran ?
Sorry to question that, but what posted seems to me to be inconsistent with
the results you got...
"Aleks" wrote:

> I am doing a left join in this query, but only the records that have a joi
n
> are displayed.
> There is a record in "cases" with no "casecomments" but it is not displaye
d
> ... please help:
> SELECT *
> FROM cases a
> left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
> MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
>
>|||Is not that I can't be bothered, is that I don't know how to do it.
A
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ee0XGPLKFHA.3064@.TK2MSFTNGP12.phx.gbl...
> Too many assumptions. If you can't be bothered to provide DDL and
> something
> other than a word problem describing the solution you're after, I guess
> that
> solution isn't too important to you. Next thread for me.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||You are right, it was something else, another inner join that should have
been left join, sorry, but thanks for the tip.
A
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:ADE383C9-0E7A-4B45-A73A-C849472AD2D6@.microsoft.com...
> Aleks,
> Looking at your query, there is no reason why every record from the
> cases
> table should not be displayed... Are you sure the record you are expecting
> to
> see is in there? And that the sql you posted is exacttly what you ran ?
> Sorry to question that, but what posted seems to me to be inconsistent
> with
> the results you got...
> "Aleks" wrote:
>|||> Is not that I can't be bothered, is that I don't know how to do it.
Did you even LOOK AT http://www.aspfaq.com/5006 ''|||Thank you, I was not aware of that page and you don't have to be so
agressive.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23w97lrLKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Did you even LOOK AT http://www.aspfaq.com/5006 ''
>sql

Monday, March 26, 2012

Problem with Intead of Insert Trigger

I'm having a rather weird problem with an Instead Of Insert trigger.
Its supposed to check to see if the record exists in the destination table,
and if so update it, otherwise insert the row in the table. All pretty
straightforward, but I'm getting weird behavior.
If the destination table is empty, each row gets properly inserted. If there
are records in the destination table, any that are found get properly
updated, but no new records get inserted.
Here is my table layout:
CREATE TABLE [dbo].[TestProspect] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProspectNumber] [int] NULL ,
[iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
And here is my trigger:
CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
INSTEAD OF INSERT
AS
--try to update the inserted record to an existing record
UPDATE p
SET
pLastName = ISNULL(i.LastName,''),
pFirstName = ISNULL(i.FirstName,''),
pPhone = ISNULL(i.Phone1,''),
Type = 'UPDATE'
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName
WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
--if it isn't in the table, insert it
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
I've tried all kinds of things, like wrapping it in an if-else clause or
rearranging the insert/update order, to no avail. Anybody see something that
I'm missing?
Thanks in advance.Verify that ANSI_NULLS was set ON when the trigger was created. Also, if
{iFirstName, iLastName, iPhone} is a key, then they shouldn't be nullable,
and you should create a unique constraint to ensure that you don't get any
duplicates.
"Brian Levine" <Brian Levine@.discussions.microsoft.com> wrote in message
news:462FFE90-CF5E-4575-B357-2B7D64E3B382@.microsoft.com...
> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination
> table,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If
> there
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something
> that
> I'm missing?
> Thanks in advance.|||Try this for the insert portion:
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT 1
FROM TestProspect p WHERE
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
"Brian Levine" wrote:

> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination table
,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If the
re
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something th
at
> I'm missing?
> Thanks in advance.

Friday, March 23, 2012

problem with INSERT on ExecuteNonQuery

I'm trying to INSERT a new record into a SQL database using the command object. It works if I use an UPDATE, but every time I try an INSERT, I get "ExecuteNonQuery: Connection property has not been initialized". The query has been checked in enterprise manager direct and works fine. Has anybody got any ideas, please?


 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah'), ConUsers")
 ConUsers.Open()
 cmdSql.ExecuteNonQuery()
 ConUsers.Close()

What are you using for your connection/connection string? is it exactly the same as the sub that works for the update statement?|||> is it exactly the same as the sub that works for the update statement?

yes, it is. The only thing that changes is the query itself and I've checked that on the database direct. I've even checked that servername/ASPNET has INSERT permission on that table. So I'm a bit stumped.|||Is that code copied and pasted? If so, you have a misplaced double quote. Should be like this:

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah')", ConUsers)

Terri

problem with If Exist select

Have patience, I'm just a script kiddie. I'm trying to write a
vbsscript that queries a sql 2005 database to see if a record exists
and if so update some values and if it doesn't then insert an entry. On
the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
getting the following error:
Char: 11
Error: Syntax error
Code: 800a03ea
Can anyone tell me what I'm doing wrong.
Thanks.
Set objCon = CreateObject("adodb.connection")
objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
uid=something; pwd=something")
on error resume Next
If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
& ")"
Else
strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
& _
"values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
strProc & ", " & strMem & ")"
End If
if err.number <> 0 then
msgbox err.description
end if
on error resume next
objCon.Execute(strSQL)
if err.number <> 0 then
msgbox err.description
end if
objCon.Closemcgrew.michael@.gmail.com wrote:
> Have patience, I'm just a script kiddie. I'm trying to write a
> vbsscript that queries a sql 2005 database to see if a record exists
> and if so update some values and if it doesn't then insert an entry. On
> the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
> getting the following error:
> Char: 11
> Error: Syntax error
> Code: 800a03ea
> Can anyone tell me what I'm doing wrong.
> Thanks.
>
> Set objCon = CreateObject("adodb.connection")
> objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
> uid=something; pwd=something")
> on error resume Next
> If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
> & ")"
> Else
> strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
> & _
> "values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
> strProc & ", " & strMem & ")"
>
instead of using if exits (select...........)
u should open a recordset of this SQL query and check condition....
let say if Rst as recorset..
then ur statement should be like this
if Rst.eof then
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
'" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
strMem
& ")"
else
....|||Better yet, put stored procedures in your database and call them from VB.
Pass criteria using the parameters objects. Dynamic SQL can get you into
all kinds of problems.
Also, you can include all of this logic in a single stored procedure and
make one call to the database, rather than having the VB app checking all of
this logic on the client. You need to look at the type of logic you need to
enforce and determine whether to do this on the client app or the database,
but it is worth considering.
SQL Injection and Parameters:
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Dynamic SQL in stored procedures:
http://www.sommarskog.se/dynamic_sql.html
"SQL-Star (Rajeev Shukla)" <dreams.alot@.gmail.com> wrote in message
news:1145465365.635163.158830@.e56g2000cwe.googlegroups.com...
> mcgrew.michael@.gmail.com wrote:
> instead of using if exits (select...........)
> u should open a recordset of this SQL query and check condition....
> let say if Rst as recorset..
> then ur statement should be like this
> if Rst.eof then
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
> '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
> strMem
> & ")"
> else
> ....
>

Wednesday, March 21, 2012

problem with GROUP BY and SUM

I'm having a problem with GROUP BY. There are multiple records for each registration.registrationid returned, and I need it to only return one record for each registration.registrationid and to add up all the TotalDollarsUsed for that registration.registrationid.

What ever I do I can't get it to SUM up the TotalDollarsUsed columns for each registrationid.

Does anyone have any ideas?

DECLARE@.SiteID int,
@.StartDate datetime,
@.EndDate datetime

SET @.SiteID = 2216
SET @.StartDate = '9/1/2003'
SET @.EndDate = '3/31/2004'

SELECT registration.registrationid, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI, [Session].Hours,
DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) AS TimeMinutes,
SUM(CASE [Session].Timebased WHEN 1 THEN DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) * (Rate / 60)
ELSE Hours * Rate
END) AS TotalDollarsUsed
FROM Attendance
INNER JOIN Registration ON Attendance.RegistrationID = Registration.RegistrationID
INNER JOIN [Session] ON Attendance.SessionID = [Session].SessionID
GROUP BY Registration.RegistrationID, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI,
[Session].Timebased, [Session].Hours, [Session].Rate, Attendance.AttendanceDate, Attendance.TimeOut, Attendance.TimeIn, Registration.SiteID
HAVING (NOT (Session.Rate IS NULL))
AND (Attendance.AttendanceDate BETWEEN @.startdate AND @.enddate)
AND LinkID IN (SELECT LinkID FROM Registration WHERE withdrawdate IS NULL AND SiteID = @.SiteID)
AND SiteID = @.SiteID
ORDER BY Registration.LinkID

The results returned from this script are like this:

RegistrationID, TotalDollarsUsed
1001, 200
1001, 100
1001, 50
4005, 200
4005, 200Your SELECT statement is returning more than RegistrationID and TotalDollarsUsed columns. Your indicated result shows that for RegistrationID 1001 there are 3 records with unidentical value for a particular column that is not yet clear from your code. Eliminating such a column from the result may be an option. Post your full result to facilitate better resolution to the problem.

Tuesday, March 20, 2012

Problem with Functions

I have a main table that stores the names of all the tables in that database & the next pkey that the record in the respective table should have when a new record is inserted in it(This is the requirement). I am writing a function which returns the primary key of the tablename entered.

The function that i have written is as follows:

CREATE Function next_pkey(@.in_tablename varchar(250),@.in_increment int)
RETURNS int
AS
BEGIN
DECLARE @.nextid int
DECLARE @.out_nextid int
SET @.out_nextid = -1
UPDATE main_ids SET nextid = nextid + @.in_increment WHERE tablename = @.in_tablename
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO main_ids (tablename, nextid) VALUES (@.in_tablename, 1 + @.in_increment)
SET @.nextid = 1
END
ELSE
BEGIN
SELECT @.nextid = nextid - @.in_increment FROM main_ids WHERE tablename = @.in_tablename
END
IF (@.@.ERROR = 0)
SET @.out_nextid = @.nextid
Return @.out_nextid
END
GO

This function gives 2 erros as mentioned below:
Invalid use of 'UPDATE' within a function.
Invalid use of 'INSERT' within a function.

I have the following query:
Is an update statement allowed in a function?

Can you tell me what should i do so that this function executes properly?
Plz Help???
Thanks.from BOL...

The types of statements that are valid in a function include:

DECLARE statements can be used to define data variables and cursors that are local to the function.

Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

Control-of-flow statements.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

EXECUTE statements calling an extended stored procedure.|||Can you tell me what should i do so that this function executes properly?
Turn it into a Procedure, making your return value an outbound parameter.

Functions are not allowed to change data.

Monday, March 12, 2012

Problem with FETCH LAST

Hello everyone, hope someone can help me with this.

I have a SQL stored procedure that inserts a record into a table,
creates a cursor to fetch the last record that was added to get the
unique key that was created and then writes that and other info to a
separate table. This procedure was working fine at our ISP under NT 4
and SQL 7.

We recently moved to another ISP on servers that are windows 2000 and
SQL 2000. Now this code is going kerplooey. It actually worked fine
in the staging area but now that it was moved into production, it is
not working. also wanted to mention that the production database was
restored from a backup. below is the code.

the first time this is run it is ok, for example the transaction
number is 1. the next time it is run, a new record is created in the
sweep results with a transaction number of 2. but for some reason,
when i declare the cursor to fetch the last record, it goes back to
the transaction number 1 record. so the counts from transaction 1
don't match counts from transaction 2 and the next step has an error
condition and doesn't work.

thanks in advance for any help you can provide

Ann Williams

-- update the sweep results table
INSERT tbl_sweepresults (del_wrkfeedback_count,
updnull_feedback_count, swp_feedback_count,
swp_count_error, del_error, updnull_error, swp_error, init_error,
sweep_date)
VALUES (@.var_del_wrkfeedback_count, @.var_updnull_feedback_count,
@.var_swp_feedback_count,
@.var_swp_count_error, @.var_del_error, @.var_updnull_error,
@.var_swp_error, @.var_init_error, GETDATE())
-- create cursor
DECLARE tbl_sweepresults_cursor SCROLL CURSOR FOR
SELECT transaction_no, sweep_date, init_error, updnull_feedback_count
FROM tbl_sweepresults
OPEN tbl_sweepresults_cursor
-- get transaction number, sweep date, init error, feedback sweep
count and pass to tbl_currentTrans for OPAL comparison
FETCH LAST FROM tbl_sweepresults_cursor INTO @.var_transaction_no,
@.var_sweep_date, @.var_init_error, @.var_swp_countzero
DELETE tbl_currentTrans
INSERT tbl_currentTrans (current_transaction_no, current_sweep_date,
current_init_error, current_swp_countzero)
VALUES (@.var_transaction_no, @.var_sweep_date, @.var_init_error,
@.var_swp_countzero)
-- close the cursor
CLOSE tbl_sweepresults_cursor
DEALLOCATE tbl_sweepresults_cursorIs the value you are trying to retrieve an IDENTITY column? If so,
SCOPE_IDENTITY() is what you need. It returns the last inserted identity
value.

Since your cursor declaration doesn't include an ORDER BY clause you've been
lucky that it ever gave a meaningful result. FETCH LAST will just return an
indeterminate row from the table. Moving to another system (perhaps one with
more read-ahead cacheing) has shown up this defficiency which relied on the
engine always returning the last-inserted row.

Q. Why insert the row you've just added into another table
(tbl_currentTrans)? After all you already have it in a table and you know
the primary key.

--
David Portas
----
Please reply only to the newsgroup
--

Friday, March 9, 2012

Problem with enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But we can't find any element in the queue.when we used command

(select*fromsys.transmission_queue )

sys.transmission_queue, in column 'transmission_status' we are getting

'The session keys for this conversation could not be created or accessed. The database master key is required for this operation.'

For solving this we have used command

USE master ;

ALTERDATABASE database name SET ENABLE_BROKER ;

but unable to solve it.

When we dequeue an element which already exist in the Queue we are able to dequeue it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

Add an WITH ENCRYPTION = OFF to your BEGIN DIALOG statements to avoid session keys altogether.

Alternatively you could ensure that the databaseaster key exists and is encrypted with service master key:

create master key encryption by password = 'MyPassword';

alter master key add ADD ENCRYPTION BY SERVICE MASTER KEY;

Saturday, February 25, 2012

Problem with delete trigger

I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table.

Here is my code:

if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE
inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0
BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table

Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't

When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted'

What am I doing wrong?

Roberta

1 - Use just [deleted]

2 - you do not need to count in order to prove existence. Use EXISTS operator.

if exists(select * from dbo.PMA_USE as a innner join deleted as b on a.PMA_NUM = b.PMA_NUM)

BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

AMB

Problem with Delete function within a Gridview

I've got an issue that when I update a record in the gridview it works fine. When I click the delete link to remove the record from the database, I get the following error, "System.FormatException: Input string was not in a correct format". Part of the Stack Trace refers to "String oldValuesParameterFormatString". This parameter is in my SqlDataSource. It was dynamically created when I originally created the SqlDataSource with VWD 2005 Express Edition. The delete function will work if I remove "OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient",and any reference to "original_" in the DeleteCommand the SqlDataSource. But if I do, the update function doesn't work. Anyway, here's the SqlDataSource: Any help would be greatly appreciated!!!!!

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"

ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.original_intID AND [street] = @.original_street AND [city] = @.original_city AND [state] = @.original_state AND [zip] = @.original_zip AND [status] = @.original_status AND [pDate] = @.original_pDate AND [sPrice] = @.original_sPrice AND [asPrice] = @.original_asPrice AND [actSalePrice] = @.original_actSalePrice AND [cToDate] = @.original_cToDate AND [rehabBudget] = @.original_rehabBudget AND [tDay] = @.original_tDay AND [eDate] = @.original_eDate AND [loDate] = @.original_loDate AND [rsDate] = @.original_rsDate AND [flooringDate] = @.original_flooringDate AND [estCompDate] = @.original_estCompDate AND [actCompDate] = @.original_actCompDate AND [coe] = @.original_coe AND [lDate] = @.original_lDate AND [credits] = @.original_credits AND [agent] = @.original_agent AND [insComplete] = @.original_insComplete AND [cEscrowDate] = @.original_cEscrowDate AND [bidValue] = @.original_bidValue AND [thomGuideNumber] = @.original_thomGuideNumber AND [locksmith] = @.original_locksmith AND [notes] = @.original_notes AND [hoa] = @.original_hoa"

InsertCommand="INSERT INTO [houses] ([street], [city], [state], [zip], [status], [pDate], [sPrice], [asPrice], [actSalePrice], [cToDate], [rehabBudget], [tDay], [eDate], [loDate], [rsDate], [flooringDate], [estCompDate], [actCompDate], [coe], [lDate], [credits], [agent], [insComplete], [cEscrowDate], [bidValue], [thomGuideNumber], [locksmith], [notes], [hoa]) VALUES (@.street, @.city, @.state, @.zip, @.status, @.pDate, @.sPrice, @.asPrice, @.actSalePrice, @.cToDate, @.rehabBudget, @.tDay, @.eDate, @.loDate, @.rsDate, @.flooringDate, @.estCompDate, @.actCompDate, @.coe, @.lDate, @.credits, @.agent, @.insComplete, @.cEscrowDate, @.bidValue, @.thomGuideNumber, @.locksmith, @.notes, @.hoa)"

OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient"

SelectCommand="SELECT * FROM [houses] WHERE ([intID] = @.intID)"

UpdateCommand="UPDATE [houses] SET [street] = @.street, [city] = @.city, [state] = @.state, [zip] = @.zip, [status] = @.status, [pDate] = @.pDate, [sPrice] = @.sPrice, [asPrice] = @.asPrice, [actSalePrice] = @.actSalePrice, [cToDate] = @.cToDate, [rehabBudget] = @.rehabBudget, [tDay] = @.tDay, [eDate] = @.eDate, [loDate] = @.loDate, [rsDate] = @.rsDate, [flooringDate] = @.flooringDate, [estCompDate] = @.estCompDate, [actCompDate] = @.actCompDate, [coe] = @.coe, [lDate] = @.lDate, [credits] = @.credits, [agent] = @.agent, [insComplete] = @.insComplete, [cEscrowDate] = @.cEscrowDate, [bidValue] = @.bidValue, [thomGuideNumber] = @.thomGuideNumber, [locksmith] = @.locksmith, [notes] = @.notes, [hoa] = @.hoa WHERE [intID] = @.original_intID">

If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"

<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters>

|||

limno:
If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:
DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"
<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters> 

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Something strange happened to the reply above. Here it is again...

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Hi tobias,

Please check in your Page_Load event handler to see if you have reloaded the DataSource by calling DataBind() method again. You need to check if it is a postback by using IsPostBack property.

If DataBind() is called, the operation will have no effect.

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006

Friday, March 30, 2012

Problem with Linked Server

Problem with Linked Server

Wednesday, March 28, 2012

Problem with left join, please help !

Monday, March 26, 2012

Problem with Intead of Insert Trigger

Friday, March 23, 2012

problem with INSERT on ExecuteNonQuery

problem with If Exist select

Wednesday, March 21, 2012

problem with GROUP BY and SUM

Tuesday, March 20, 2012

Problem with Functions

Monday, March 12, 2012

Problem with FETCH LAST

Friday, March 9, 2012

Problem with enqueue

Saturday, February 25, 2012

Problem with delete trigger

Problem with Delete function within a Gridview

problem with DEFAULT (getdate())

db datatypes

Blog Archive

About Me