Help Please , Select The First 3 Messages For Each

Jul 18, 2006

I have a website with a DB table of messages sent by users.
I want to select the first 3 messages for each unique user.

I found a way to accomplish this by pulling all of the messages from the sql and filtering the results via ASP.net code but it seems to be really really slow.

I hope one of you guys can help me accomplish this via sql .


Messages Table

Mid Username SendDate
110016/07/06 13:00
210017/07/06 14:00
310117/07/06 14:30
410217/07/06 15:00
510318/07/06 16:00
610018/07/06 17:00
710218/07/06 18:01
810319/07/06 18:02
911019/07/06 19:03
1010219/07/06 19:04
1110021/07/06 20:00
1210321/07/06 20:00
1310221/07/06 20:00



Query result:

Mid
1
2
6
3
4
7
10
5
8
12
9


THANK you guys!

View 9 Replies


ADVERTISEMENT

Suppressing Messages From Select Statements

Jun 7, 2006

When you do a select statement, you get a message saying "n row(s) affected" where n is the number of rows affected. Is there any way of turning off this message?

View 4 Replies View Related

Replication :: Error Details Or Messages Of Select Session Not Shown

Jul 10, 2015

Since I had a bad error in replication, my replication monitor won't show the tab "Error details or messages of the select session". 

I am not able to activate or to show this "tab", to get all information about my error.

I added two screeshots, one with the 3 tab, and the other, were the information about my error are missing. How i can my default view back?

Replication Monitor OK:

My Replication Monitor with missing tab:

I had to know, which ID is causing a primary key violation. Instead of Replication Monitor, I would use t-sql.

View 3 Replies View Related

How To Write .net Code To Place XML Messages On Queues And Retrieve XML Messages From Queues.

Apr 2, 2008

Hello, please help!!

I have spent days searching the web and forums for an answer to this simple question and cannot find an example.

I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.

I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.

What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.

I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....

thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.

any example code that does this, would be really helpfull

kind regards,
David Weeden
Database Developer

View 2 Replies View Related

SQL 6.5 Messages

Feb 15, 1999

Trying to obtain a full list of SQL 6.5 error messages into a file. There are
2,215 messages present in master.sysmessages, but there is a total of 2,733
messages listed in the dialog box within enterprise manager. Message numbers
17026 thru 19020 don't seem to be stored in the sysmessages table.

Anyone know how I could obtain an extract to obtain a full list of messages ?

View 2 Replies View Related

Messages Are Gone.

Apr 15, 2008



After sending a message the message is just gone.
Our transmissionand and out our transactionqueue is empty.

This is our CLIENT:
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO

GRANT SEND ON SERVICE::[TargetService] to public

create master key
encryption by password = 'azertyuiopqsdfghjklm'
go


SERVER

CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO

GRANT SEND ON SERVICE::[TargetService] to public

create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
Hopefull you have an idea?

View 1 Replies View Related

No Help From The Error Messages

Dec 4, 2007

I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON
DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn =  CASE   WHEN @inMonth = 'January' THEN 'hitsInJan'  WHEN @inMonth = 'February' THEN 'hitsInFeb'  WHEN @inMonth = 'March' THEN 'hitsInMar'  WHEN @inMonth = 'April' THEN 'hitsInApr'  WHEN @inMonth = 'May' THEN 'hitsInMay'  WHEN @inMonth = 'June' THEN 'hitsInJun'  WHEN @inMonth = 'July' THEN 'hitsInJul'  WHEN @inMonth = 'August' THEN 'hitsInAug'  WHEN @inMonth = 'September' THEN 'hitsInSep'  WHEN @inMonth = 'October' THEN 'hitsInOct'  WHEN @inMonth = 'November' THEN 'hitsInNov'  WHEN @inMonth = 'December' THEN 'hitsInDec'  END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @ntid and @page = page) begin  if exists(select * from tblTracking where lastVisit = @thisHit)   begin    -- DO NOTHING!   end  else   begin    DECLARE @theColumn varchar (100)    SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page'    SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1    SET @monthCount = (Exec @theColumn)    SET @monthCount = @monthCount + 1    SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page'    Exec @updString   end endelse begin  Exec @insString endGO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?

View 9 Replies View Related

Private Messages

Sep 26, 2005

i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.

View 13 Replies View Related

SQL Error Messages

Mar 9, 1999

Is there a way to return the SQL Native error to a Visual Basic program?

View 3 Replies View Related

Error Messages

Mar 3, 1999

Hey everyone,

Here is what I'm doing:

exec xp_sendmail @recipients = 'ampx@hotmail.com',
@query = 'select * from information where recid <= 10',
@subject = 'Query test',
@message = 'hello',
@attach_results = 'TRUE',
@width = 250

I get this error:

Msg 2812, Level 16, State 4
Stored procedure 'xp_sendmail' not found.

Anyone know why? isn't xp_sendmail a function of SQL?

Thanks

View 1 Replies View Related

Concantenation Of Messages

Apr 30, 2007

Hi,

I have a table that has 4 fields and has free format text messages. The Identification field contains the client id
MNo field has Message Number specific client id. A single messge is split down into multiple rows and Seq field gives the relation which text message belongs to which MNo

Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20
CLIENT-01327002002regarding the Pete relocation, there is concern as to whether this
CLIENT-01327002003sale is good or not. It appears that the prospective buy
CLIENT-01327002004er will be in tommorrow if he does not produce a firm fina
CLIENT-01327002005ncing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle

I need to create a query where i need to join all the text messages that belong to the same MNo and Identification into one field. The output should be like the following:

Output Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20 regarding the Pete relocation, there is concern as to whether this sale is good or
or not. It appears that the prospective buyer will be in tommorrow if he does not produce a firm financing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle

In the above output table for Identification = CLIENT-01327 and Mno = 002 the entire text message has been concantenated into one field.

Is there any way that i can write in a single query to obtain the output file.. Please help.

View 2 Replies View Related

*** DTS Error Messages ***

Jul 20, 2005

Hello,I'd like to know if I use DTS. If I use it immediately, I can see the errorin the dialog box, instead, if I use it with scheduling, where Can I checkthe error ?ThanksSaimon(Florence)

View 2 Replies View Related

Can Messages Be Sent To A Wcf Endpoint From T-sql?

Aug 16, 2007

we've gotten this to work from sqlclr and understand the dependence wcf has on the framework but still feel compelled to ask if somehow t-sql can send a message to a wcf endpoint without getting sqlclr involved. I've moved this question over here from the t-sql forum.

View 1 Replies View Related

Sending Messages With ADO.NET 1.0

Mar 22, 2006

Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).

So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?

View 3 Replies View Related

No Messages In The Queues.

Mar 10, 2006

Hello,

I'm trying to do a very simple example of sending a message from Initiator queue to Target queue. The result is no messages are delivered.

Here's the code:

DECLARE @conversationHandle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @conversationHandle

FROM SERVICE GmiInitiatorService

TO SERVICE 'GmiTargetService'

ON CONTRACT GmiContract

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @conversationHandle MESSAGE TYPE GmiMessage ('test');

END CONVERSATION @conversationHandle

All three queues are empty (Initiator, Transmission, Target). When I comment out the last line ("end conversation"), the messages get stuck in the Initiator queue.

Please help!!

Thank you.

View 11 Replies View Related

Unreceivable Messages

Jan 10, 2006

Hey guys

I have another weird looking problem

I have a queue with a certain number of messages waiting in it to be processed. When I try and receive a message from this queue, I get back an empty row; no message. However, when I try and select from this queue, I see all the messages in there. Even sp_spaceused reports that there are rows in this queue. The status of each unreceivable message is 2. What would be the reason why I cannot receive these message?

I should add that newer messages added to this queue are receivable, meaning that if I add another message to the queue, when I do a receive, I get this new message and not the older ones in the list.

Thanks

View 4 Replies View Related

Reading Messages With ADO .Net C#

Jul 3, 2007

Hello,



I am trying to figure out how to properly read a single message(poll) at a time from ADO .Net in c# 2005.



I am assuming i need to write a sproc that does a receive, but I wanted to validate if there were already any examples of this already?



I have been reading quite alot but don't see this exactly as an example anywhere and it is probably easier then my brain is allowing it to be.



If you have an example or would help me i would appreciate it. I am "hoping" for more then just "write a sproc and call it" as I believe this is accurate but am hoping for more info.



Happy to read, happy to research, but I am atm, lost for where to go next. I am going to start doing some plain testing, but appreciate any help.



Thanks,



-Mike

View 1 Replies View Related

For MSFT, What Are The New Log Messages In SP2

Jan 15, 2007

[Reposted because no-one answered me before: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097115&SiteID=1]

Hi,

Quick one for anyone from Microsoft. Does there exist anywhere a definative list of all the new log messages that are being introduced in SP2?


Thanks

Jamie

View 2 Replies View Related

Looking Up Error Messages

Mar 10, 2008



Greetings
I'm learning SSIS and BIDS. I have extreme difficulty making sense of the error messages that come out.

First of all, what do the numbers mean? Each column, error, etc. is assigned a number that obviously means something yet I cannot relate them to anything. For example: The output column Name (713) on output Test (15) and Component (15) -- My table doesn't have 713 columns in it...

Then there are the error codes that obviously contain something useful. For example:

DTS Error: Microsoft.SqlServer.Dts.Runtime.TaskHost/QueueFuzzyName [33]SIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (46)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (46)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.


Where would I look up: DTS_E_INDUCEDTRANSFORMFAILUREONERROR and 0xC020907D? I understand that it tried to convert a value in something numbered 46 (no idea what that is) and the conversion failed. But that's it. How do I transmogrify 46 to something I can look at. I'm a little fuzzy on what a Destination Input is. Isn't an output a destination?

Or this one:

Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors"
Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

I have absolutely no idea what this means except that many things happened, one of them failed, I have an error of some sort and it's error code is 0x80040E21. It must be important, it's in there twice, but what does it mean?

I'm thinking that in the help somewhere all these error messages are listed and I can look them up, but I can't seem to find it anywhere. Am I supposed to be converting them to decimal first?

Any advice from you experts would be much appreciated.

View 5 Replies View Related

Lost Messages ?

Aug 10, 2006

Hi There

What happens to messages that go to error?

I know that they are returned to the queue of the service that sent them, but the message it the queue is the error message and description. Transmission queue is empty.

Where is the actual message that was sent ? In my case if the message went to error i must resend it.

But there is no error when you send the message, transmission queue is clear, the error just comes back to the queue but i need the original message so that i can resend it after the error is fixed but i cannot find it anywhere ?

Thanx

View 18 Replies View Related

SqlBulkCopy Error Messages

Sep 6, 2006

I'm using SqlBulkCopy.  Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks 

View 1 Replies View Related

Displaying Messages To Users

Oct 26, 2006

Hello, I need some help coming up with a good concept for returning stored procedure messages to a user. Here is my setup: Stored Proc:@ReturnCode  INT - OUT@ReturnDetails VARCHAR(150) - OUT If my stored procedure returns a value of less than 1 for the @ReturnCode I want to display the error message in @ReturnDetails, Now I have a DAL that returns back the @ReturnCode to the codebehind ( via a function ) but how can i display the @ReturnDetails to the user, I would like to use a javascript alert, I know how to display the message but my question is what is the best design for returning the message to the user? I was thinking about passing in the caller when i instantiate my DAL, but I don't really like that idea, My other idea was have my function return a string like "-1|Invalid Password" and parsing it but don't like that either. Also is there a way to find out the page caller from my DAL? That would help a lot. Your ideas are greatly appreciated. 

View 1 Replies View Related

Dynamic SQL Error Messages!

Mar 3, 2008

Have this dynamic sql statement that I'm working on.  It works fine outside the execute (running the query by iteself) and prints fine but when I execute it, I get errors.  Spacing is good.  Here is the SQL statement.
 set @sql = 'insert into #participantInfo (strfirstname,strlastname,strindividualfk,strusername,dtelastlogin,blninactive,fk_intrightid,imgPhoto, stre_mail,strmiddleinitial,straddress1,straddress2,strcity,fk_intlocationid,strpostalcode,strhomephone,strbusinessphone,strmiscinfo1, strmiscinfo2,strsecretquestion,dteDateOfBirth,intgender,strsecretanswer)  select p.strfirstname,p.strlastname,p.strindividualfk,l.strusername,l.dtelastlogin,p.blninactive,r.fk_intrightid,p.imgPhoto, p.stre_mail,p.strmiddleinitial,p.straddress1,p.straddress2,p.strcity,p.fk_intlocationid,p.strpostalcode,p.strhomephone,p.strbusinessphone, p.strmiscinfo1,p.strmiscinfo2,l.strsecretquestion,p.dteDateOfBirth,p.intgender,l.strsecretanswer  from tblparticipants p inner join  tblparticipantrights r on p.strindividualfk = r.strindividualfk inner join  tblparticipantlogin l on p.strindividualfk = l.strindividualfk  where p.fk_strsubgroupid = ''' +  @strsubgroupid + ''''
exec (@sql)
Error messages are:
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipants'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipantrights'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipantlogin'. 
Anyone see what may be the cause?
 thanks ^_^

View 6 Replies View Related

Show SQL Server Messages

Mar 22, 2004

Hello all,
I am making an auction system using C#, .NET and MS SQL Server.
I have a page to add new products to DB, which works fine.

What I like to have is that, I want to be able to show friendly confirmation and error messages to users.

So it is going to work like this:
- user adds a new product
- if successful there is a message on the page that reads: The products (product name) was successfully added to the database. And form fields are clear, ready for the next product info to be entered.
- if not successful, the message should tell the user and maybe indicates the reason too. Like: the product code used already exists.

Currently for the successful attempts I get the form page with all the fields filled with the entered data and for un-successful one the ASP.NET error page.

Can anybody help please? Does anybody knows about a tutorial or an article or ...?

Thanks a lot.

View 5 Replies View Related

Error Messages To A File?

Aug 23, 2001

How to write the error messages generated in a job to a file?Thanks.

View 1 Replies View Related

Looking For Script To Put Messages In Txt File

Sep 13, 2000

Looking for a script which allows me to define a message
and place that message in a txt file.

Thanks for help!!

View 1 Replies View Related

How To Get More Info For Error Messages

Jul 10, 2000

So far I have not been able to figure out how to get more info
regarding the sql server errors. For example, I get this error
from sql server query analyzer (this just an example):

-----------
Server: Msg 128, Level 15, State 1, Line 5
The name 'does' is not permitted in this context.
Only constants, expressions, or variables allowed here.
Column names are not permitted.
-----------


Is there a way to find more information regarding this error, or
like Oracle has MeataLink, where you can search, if someone else
already got this error and how it got resolved?

Or how you folks go about resolving errors you get? Is it based
on trail and error, or experience (like already encountered this
type before) etc.?

View 1 Replies View Related

Dbcc Error Messages

Sep 23, 1999

If you find error messages in the dbcc results, how do you find out which table it is according to the ID number 875696?

ie:

Checking 875696
The number of data pages in this table is .....

View 3 Replies View Related

SQL Server Error Messages

Jul 30, 2007

Hi All,

I was wondering if anybody knows where to get a complete list of SQL Server error messages. I am writing a stored procedure that scans SQL Server Logs for errors and if there are errors in the logs, I get paged.

Thanks.

View 2 Replies View Related

Logging Error Messages

May 7, 2002

Hi,

How can I avoid certain messages from SQL Server being recorded into the Event viewer ?
For example, every time I truncate the transaction log with 'Backup log with truncate_only', It is being recorded into the Event viewer as an Error. But, I know that it is not an error.
How can I avoid this ?

Thanks

View 1 Replies View Related

Raiserror Messages Not Sent To Clients

Jul 20, 2002

We’re having trouble getting error messages to show up on clients. Our ADO research indicates that the Errors collections is populated, “automatically” – what you do with it is up to the application. Our collection is not being populated. MS says the SQLOLEDB provider has a problem (the collection is not filled) if SET NOCOUNT is OFF. We have SET NOCOUNT ON and still have the problem. We have narrowed the problem down (the example below is an abbreviated version) to “the Errors Collection is not populated if the Raiserror follows a SELECT statement that returns a recordset”.

In the code below the simple select run after the first RAISERROR appears to “block” the Error Collection. Is this by design? Are you never supposed to be able to return records and messages from the same program? We can code around it if we have to, but the documentation seems to indicate our approach is viable.

Any ideas would be most appreciated.

SQL Stored Procedure:

CREATE PROCEDURE Address_Ck
(
@Address_ID INTEGER OUTPUT
, @MailOnly_LG BIT
, @Ctry_ID SMALLINT
, @StPv_ID SMALLINT
, @PostCode_ID INT
, @PostCode_Ext_CH CHAR(10)
, @Add1_VC VARCHAR(60)
, @Add2_VC VARCHAR(60)
, @Add3_VC VARCHAR(60)
, @Add4_VC VARCHAR(60)
, @City_VC VARCHAR(30)
, @City_Lock_LG BIT
, @Directions_VC VARCHAR(2000)
)

AS

DECLARE
@Finder_VC VARCHAR(20)
, @Label1_VC VARCHAR(60)
, @Label2_VC VARCHAR(60)
, @Label3_VC VARCHAR(60)
, @Label4_VC VARCHAR(60)
, @Label5_VC VARCHAR(60)
, @Label6_VC VARCHAR(60)


-- the error in the next line shows up when not commented out
--RAISERROR ( ‘ This error always shows up.’ ,16,1)


SELECT Address_ID
, Label1_VC
, Label2_VC
, Label3_VC
, Label4_VC
, Label5_VC
, Label6_VC
FROM Address_T
WHERE Finder_VC= @Finder_VC


RAISERROR ( ‘ Why won’t this error showup?. ’ ,16,1)
-- the error above never shows up


************************************************** ****
************************************************** ****


THE VB CODE:

Option Explicit
Dim db As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
On Error GoTo errmsg

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

With db
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=Jeanne;trusted_connection = true;integrated security=sspi"
.Open
.DefaultDatabase = "DevTime21"
End With

With cmd

.ActiveConnection = db
.CommandType = adCmdStoredProc
.CommandText = "address_findck_okinsert_m"
.Parameters.Append .CreateParameter("@address_id", _
adInteger, adParamOutput, 4)
.Parameters.Append .CreateParameter("@Mailonly_lg", _
adBoolean, adParamInput, 1, False)
.Parameters.Append .CreateParameter("@ctry_id", _
adInteger, adParamInput, 4, 1)
.Parameters.Append .CreateParameter("@stpv_id", _
adInteger, adParamInput, 4, 1)
.Parameters.Append .CreateParameter("@postid_id", _
adInteger, adParamInput, 4, 0)
.Parameters.Append .CreateParameter("@postcode_ext", _
adChar, adParamInput, 10, "")
.Parameters.Append .CreateParameter("@add1_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add2_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add3_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add4_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@city_vc", _
adVarChar, adParamInput, 30, "")
.Parameters.Append .CreateParameter("@city_lock_lg", _
adBoolean, adParamInput, 1, False)
.Parameters.Append .CreateParameter("@directions_vc", _
adVarChar, adParamInput, 2000, "")
End With
rs.CursorLocation = adUseClient

rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
If Not IsNull(cmd("@address_id")) Then


Else

MsgBox "Please contact System Administrator. There was a problem adding address."
End If
Exit Sub
errmsg:
Call errormsginfo

End Sub

View 2 Replies View Related

PLEASE HELP!!! Multiple Error Messages

Jan 3, 2008

I have a SS2K5 stored procedure that executes 2 others stored procedures
sp_zero1 and sp_zero2
sp_zero1 and sp_zero2 do the same thing ... raises an Divide by zero error
I need to (print / select into a database) both error messages using just one
try catch block instead of 2 blocks like in the next example:


-- THIS IS THE WORKING CODE THAT I DONT WANT
BEGIN TRY
exec sp_zero1
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ERROR_PROCEDURE()
END CATCH
BEGIN TRY
exec sp_zero2
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
print ERROR_PROCEDURE()
END CATCH



if I try the next code

--THIS IS THE NON WORKING CODE
BEGIN TRY
exec sp_zero1
exec sp_zero2
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
print ERROR_PROCEDURE()
END CATCH

only the first error message is printed and the execution is stopped

This is a generic example ... in reality I have a stored procedure that executes tens and hundreds of other stored procedures ... so thats the reason I need just one block of try catch instead of hundreds of blocks

thank you

View 6 Replies View Related

Reading The Error Messages

May 22, 2006

when a statement fails and it tell me for example

Server: Msg 137, Level 15, State 2, Procedure spinsertnew, Line 266
Must declare the variable '@'.

How can I find which line it is using query manager?

View 13 Replies View Related







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