Displaying SQL PRINT ERROR MESSAGES For Missing Parameters Or Exceptions.
Jan 28, 2008Does anyone know of a way to capture in C# SQL PRINT ERROR MESSAGES generated from stored procedures? Any help would be greatly appreciated.
ADVERTISEMENT
Error Messages Versus Exceptions
Jan 19, 2008I am using .NET v2.0 and SqlServer2005 SB in 90 compatibility mode.
From my brief experience it appears that if I try to subscribe to SB with incorrect SQL or wrong names .NET will immediately throw an exception. However, if my code is correct but there is an error on the server (for example incorrect options as per http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=883992&SiteID=1) then SB will immediately fire the notification message but with €œerror€? instead of €œupdate€? content. How do I detect this?
My code is based on the http://msdn2.microsoft.com/en-us/library/3ht3391b(vs.80).aspx example. (I had too much trouble with the SqlDependency class, including overflowing error logs. So unlike the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903713&SiteID=1 I am NOT receiving an SqlNotificationEventArgs object. I wish I were getting one!) I€™ve included my Listen() method below.
The debug portion of this code gives me message body that looks like the following when everything is good. What should it look like, if for example my SET OPTIONS (first link above) were wrong?
messageBody: <qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="129" type="change" source="data" info="update" database_id="23" sid="0xF4F50461FD43CA4191A1173B9350632A"><qn:Message>7fd415c7-2986-411d-abfd-615cbce0af28</qn:Message></qn:QueryNotification>
Thanks. Code follows:
private void Listen()
{
// infinite loop on this thread: keep listening even after
// receiving notification
while( true )
{
// connects to SqlServer queue and sleeps until a notification msg arrives
using( SqlConnection conn = new SqlConnection( _connect ) )
{
WriteMessage( "RequestNotification: Listen connection opened." );
using( SqlCommand cmd = conn.CreateCommand() )
{
int msgBodyId = InvalidSubscriptionId;
cmd.CommandText = waitFor;
cmd.CommandTimeout = 0; // _notificationTimeout + 100;
try
{
conn.Open();
// the thread is blocked until the waitfor event occurs.
SqlDataReader reader = cmd.ExecuteReader();
// the waitfor has triggered when we've reached here.
WriteMessage( "RequestNotification: Notification received." );
// there may be mulitple notifications waiting, but there seems only
// ever to be a single record to read, but we still use the while
// construct to ensure we get them all...
while( reader.Read() )
{
// the messageBody is actually XML with a GUID embedded as the msgId, eg:
// <qn:Message>82120744-f2bf-4c62-a17c-867cc6eb040e</qn:Message>
String messageBody = FixupMessageText( System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString() );
msgBodyId = MessageBodyId( messageBody );
// doesn't work, sqlbinary...
// String messageBody = reader.GetSqlXml( 13 ).ToString();
#if __dbg
// used for debugging
// messageText = System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString();
Byte status = reader.GetByte( 0 );
Byte priority = reader.GetByte( 1 );
Int64 queuingOrder = reader.GetInt64( 2 );
Guid conversationGroupId = reader.GetGuid( 3 );
Guid conversationHandle = reader.GetGuid( 4 );
Int64 messageSeqNo = reader.GetInt64( 5 );
String serviceName = reader.GetString( 6 );
Int32 serviceId = reader.GetInt32( 7 );
String serviceContractName = reader.GetString( 8 );
Int32 serviceContractId = reader.GetInt32( 9 );
String messageTypeName = reader.GetString( 10 );
Int32 messageTypeId = reader.GetInt32( 11 );
String validation = reader.GetString( 12 );
StringBuilder sb = new StringBuilder( "MESSAGE", 1024 );
sb.AppendLine();
sb.Append( " " );
sb.Append( BuildMessage( "status", status ) );
sb.Append( BuildMessage( "priority", priority ) );
sb.Append( BuildMessage( "queuingOrder", queuingOrder ) );
sb.Append( BuildMessage( "conversationGroupId", status ) );
sb.Append( BuildMessage( "conversationHandle", conversationHandle ) );
sb.Append( BuildMessage( "messageSeqNo", messageSeqNo ) );
sb.Append( BuildMessage( "serviceName", serviceName ) );
sb.Append( BuildMessage( "serviceId", serviceId ) );
sb.Append( BuildMessage( "serviceContractName", serviceContractName ) );
sb.Append( BuildMessage( "serviceContractId", serviceContractId ) );
sb.Append( BuildMessage( "messageTypeName", messageTypeName ) );
sb.Append( BuildMessage( "messageTypeId", messageTypeId ) );
sb.Append( BuildMessage( "validation", validation ) );
sb.AppendLine();
sb.Append( " " );
sb.Append( BuildMessage( "messageBody", messageBody ) );
WriteMessage( sb.ToString() );
#endif
}
reader.Close();
// and now we need to hand things back to the user's passed delegate.
// orphan notifications can be left behind from previous runs,
// determine if this notification is one we want to pass on
if( _subscriptionId == msgBodyId )
{
WriteMessage( "Message " + msgBodyId.ToString() + " calling delegate." );
// see http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx
SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database );
_delegate( this, e );
}
else
{
WriteMessage( "Message " + msgBodyId.ToString() + " ignored." );
}
}
catch( SqlException )
{
// if the queue name doesn't match one in the db an exception will be thrown
throw;
}
finally
{
conn.Close();
}
}
}
WriteMessage( "RequestNotification: Listen connection closed." );
}
}
Handling A SQL Exceptions And Custom Error Messages
Aug 6, 2007Hello guys,I need some ideas on how to handle an exception or a user defined error message.I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?This is what I was thinking....Solution #1--------------My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.Solution #2--------------The proc should have an output param ( @CreationStatus CHAR(1) ).If the @CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.What are some of the ways you deal with this situation?Your suggestions are greatly appreciated.Thank you!
View 2 Replies View RelatedDisplaying Custom Error Messages In Reporting Services
Nov 12, 2007Hi all,
We are displaying an SQL Server 2005 Reporting Services report in our ASP.NET 1.1 application by accessing the report through URL and embedding the same in an iframe HTML web control. The user can export the report contents into CSV format by making use of the export functionality provided by Reporting Services.
Now we have been asked to display a warning mesage to the user when the no. of records in the report exceeds 1 million i.e. if the no. of records in the report exceeds 1 million and the user tries to export the report to CSV format by clicking on the "Exprot" link button, we need to display a warning message to the user.
My doubt is whether this can be done in Reporting Services. Are there any programmatic interfaces exposed by Reporting Services which might help us implement this requirement ?
Thanks,
CodeKracker.
Reporting Services :: If Divisor Is 0 Print Field Value But It Displaying Error
Oct 8, 2015If divisor is "0" I need to print the Field, but for me it displaying #Error. If I pass any value it is working fine. It is not working only for the Field.
RULE: Fields!B.Value=0, PRINTÂ Fields!A.Value.(IIf(B=0,A,A/B))
Working:Â IIF(Fields!B.Value=0,0,Fields!A.Value/IIF(Fields!B.Value=0,0,Fields!B.Value))
Not working: Â IIF(Fields!B.Value=0,Fields!A.Value,Fields!A.Value/IIF(Fields!B.Value=0,Fields!A.Value,Fields!B.Value))
   A   B   Cal(A/B)
E Â 1 Â Â 0 Â Â Â Â 1
F Â 4 Â Â 2 Â Â Â Â 2
G Â 9 Â Â 3 Â Â Â Â 3Â
PRINT Messages Before Executing A SP
Aug 1, 2007Hi,
I have a SP called Extraction_Info that calls other Stored Procedures(Create_Employee, Create_Detail).
When i execute the SP Extraction_Info, I want to print messages before execution of individual
Stored procedures within this SP.
Code that i currently use:
=================================================================
CREATE PROC Extraction_Info
AS
BEGIN
PRINT '** Employee Extraction ..In progress **'
EXEC Create_Employee
PRINT '** Employee Extraction .. Completed **'
END
.
.
BEGIN
PRINT '** Detail Extraction ... In progress **'
EXEC Create_Detail
PRINT '** Detail Extraction ... Completed **'
END
===================================================================
The problem is that the message '** Employee Extraction ..In progress **' gets printed only after the execution of the proc Create_Employee is completed
Please advise how i should change the code.
Thanks,
Displaying Messages To Users
Oct 26, 2006Hello, 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 RelatedSQLServer JDBC Exceptions :Controlling Exceptions Text Format
Sep 28, 2006Hi,
Using RAISERROR from within a stored prcedure will result in a SQLException being thrown at the client side of a JDBC connection.
I am trying to provide a meaningfull error message to be shown to the end user. However, all exceptions thrown will inevitably start with : "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]". The following questions then arise :
- Is there a way of configuring SQL Server not to display this header ?
alternatively,
- Is there another way of conveying error messages to the client through JDBC ?
Thank you for your answers,
Ben
Error The Parameter Is Missing A Value: Problem With Parameters ('Ver 1.1 Part 1') Passing To Report Viewer Control
Jan 25, 2008
Hello,
I am passing the partameters to MS Report Viewer control to view the report. It is working fine with normal parameters like 'abc', 'Jon' etc.
But fails with parameters having special characters like 'abc+', 'Version 1.1 Part 1', 'R1.8 RC' throws error "
The parameter is missing a value"
Could anyone please help on this.
Thanks,
Chandroday
Displaying Print Statements Of Stored Procedures In SSIS Logs
Sep 6, 2007
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
Msmq Missing Messages After Applying SP3a To SQL Server
Jan 12, 2004Used to work fine:
(windows 200 server, sql server 2000, JDBC driver SP2.)
Two java processes, one sending messages (using jdbc and sql as temp storage), second is receiving and placing on msmq.
After installing SP3a for SQL server, under high load (200 a sec) there are missing messages in msmq.
Installed everything from scratch on separate machine, works fine until SP3a is applied.
Any help is highly appreciated
MSSQLServer Logging Many Missing Stored Procedure Messages
Jul 10, 2007Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 7/10/2007
Time: 3:52:37 PM
Description:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
SQL 2012 :: Execution Plan With Many Similar Missing Index Messages?
Oct 30, 2014I'm in the process of trying to optimize a stored procedure with many queries. The execution plan provides a missing non-clustered index on nearly every query, and they're all fairly similar. The only real difference between them are what's in the INCLUDE statement. The two key columns are listed in every missing index. Let's say each query is approximately 5% of the total batch and 90% of the queries all fall into the category I listed above. How should I go about creating the missing indexes? Create all of the missing indexes or create one generic one that has all the INCLUDE columns? Create a minimal index with just a few of the common INCLUDE columns?
Here's an example of what I'm talking about with the missing indexes:
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9044%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ARTX],[C4ASTX],[C4ADNB],[C4CZST])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.5418%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
Error From Sys.dm_db_index_physical_stats No Exceptions Should Be Raised By This Code
Sep 20, 2006I'm setting up a routine to do some reindexing. To get the info I need I'm trying to use the new function. The DB I think is causing the problem does pass DBCC CHECKDB. I'm not sure why I'm getting this but can not find anyone else that is getting this same error.
When I run this....
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'detailed')
I get this....
Location: qxcntxt.cpp:954
Expression: !"No exceptions should be raised by this code"
SPID: 236
Process ID: 1060
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
If I run only for the DB I think is causing the problem I get this...
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Displaying Selected Parameters In A Report
Jun 29, 2006Hi,
In the report I'm working on, I want to display a list of the parameters selected by the user, as in:
Selected Cars:
Toyota Camry
Ford Taurus
Chevy Corvette
Saturn Ion
Note that these are selected items from a multi-value parameter. How can I go about doing this with Reporting Services 2005? In ASP.NET, I'd just use the parameter array as the datasource for a repeater/datagrid/gridview. Could I do something similar with SSRS? I'd really like to use the format specified above, whether or not it is in a table; I really don't want to do the following:
Selected Cars: Toyota Camry, Ford Taurus, Chevy Corvette, Saturn Ion.
Thanks,
Mark
Displaying Multi-valued Parameters
Jul 3, 2007Hi There.
I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.
This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!
Thanks, Mike
Problems Displaying Default Value Of First Cascading Parameters.
Mar 3, 2007I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.
It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.
Got any ideas?
Here's how I have it configured:
Allow null value CHECKED
Allow blank value CHECKED
The rest are unchecked.
Available values: From query
Default Values: Non-queried with a value supplied that exists in the dataset.
RDLC Client Report And Query Parameters And Print Button
Feb 9, 2007Hi, this is my first post here. I hope to be helpful trying to help and not only asking questions arround here. After I have my report ready I will share here the total experience from top to bottom!But for now, here's the issue:
I'm building a RDLC Repor on my ASP.Net VB web application. I added the .rdlc file to the application and created a table to show lines of data binded from a dataset. The thing is:
- The DataSet expects a parameter @intNumber, a identifier to get the correct data to display the correct report.
- I'm using ReportViewer to view the report, and by code I've passed a Report Parameter to the *.RDLC report with success, just like this:
Dim parms(0) As ReportParameter
parms(0) = New ReportParameter("intNumber", 37)
ReportViewer1.LocalReport.SetParameters(parms)
The present issue is the following:
I want to use that parameter sent to the report to be sent to the query of the DataSet as parameter to the query to return the data to fill the report. I've heard that this is not possible, just with report server...
Another issue is the print button, also heard that only can appear on report server...no way to display and work on RDLC reports?Very confused right now...these issues are stupid, MS tools should allow these operations, which are not efficient if this is not possibla on RDLC...
Reporting Services :: Displaying A Chart Based On Parameters Selected
Oct 2, 2015I am producing a report in Report Builder 2 containing a chart and table. Â The user is able to select single or multiple choices on a parameter called Specialty. Â My present chart is only suitable when one Specialty is selected. Â Therefore I only want this to be visible when the user has selected one Specialty. Â I am planning to create a second chart which is suitable for than more than one selection. So if the user selects more than one Specialty the existing chart will not be visible and instead will be replaced by the second.I think this should be done using Chart Properties>Visibility but cannot develop the logic of the expression.
View 5 Replies View RelatedFilters && Parameters Pane Missing In MDX Query Designer
Mar 13, 2007I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
View 1 Replies View RelatedSybase 9.0.2 + MS Reporting Services 2000 - RS Missing Parameters
Aug 15, 2007People, help me with this? Already broke my brain.
I have some data on Sybase 9.0.2.3198,
i'm tryin to render report with MSSQL ReportingServices 2000 Eval SP2.
the report designer is fyiReportDesigner 3.0 - and I can do preview with it.
after deployment I'm tryin to render it, RS gives me an error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'Data'. (rsErrorExecutingCommand) Get Online Help
Column '@start_date' not found
I tried connect through ODBC and OLEDB both - same result.
ps I'm gonna kill somebody real soon.
the sql:
WHERE PERIOD.BEGINDATE between CAST(@start_date as timestamp) and CAST(@end_date as timestamp)
these are the RDL-file pieces:
<QueryParameters>
<QueryParameter Name="@start_date">
<Value>=Parameters!start.Value</Value>
</QueryParameter>
<QueryParameter Name="@end_date">
<Value>=Parameters!end.Value</Value>
</QueryParameter>
</QueryParameters>
<ReportParameters>
<ReportParameter Name="start">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>2007-08-15</Value>
</Values>
</DefaultValue>
<Nullable>false</Nullable>
<AllowBlank>false</AllowBlank>
<MultiValue>false</MultiValue>
<Prompt>Start</Prompt>
</ReportParameter>
<ReportParameter Name="end">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>2007-08-15</Value>
</Values>
</DefaultValue>
<Nullable>false</Nullable>
<AllowBlank>false</AllowBlank>
<MultiValue>false</MultiValue>
<Prompt>End</Prompt>
</ReportParameter>
</ReportParameters>
Urgent : DB-Library Error 10007: General SQL Server Error: Check Messages From The SQL
Jul 20, 2005DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.CREATE PROCEDURE [dbo].[spu_Import_Export_Image](@srvr varchar(50),@db varchar(50),@usr varchar(15),@pwd varchar(50),@tbl varchar(50),@col varchar(50),@mod varchar(1),@imgpath1 varchar(1000),@pk varchar(50))ASBEGINdeclare @path varchar(50)declare @whr varchar(200)declare @fil varchar(100)declare @cmd varchar(1000)declare @imgpath varchar(800)declare @ext varchar(5)--declare @pk varchar(50)declare @KeyValue varchar(8000)declare @image varchar(50)--declare @imgpath1 varchar(1000)declare @imgpath2 varchar(1000)declare @sellist varchar(2000)set @path = 'c: extCopy.exe'select @sellist = 'DECLARE curKey CURSOR FOR SELECT ' + @pk +' FROM '+ @tbl + ' ORDER BY ' + @pkexec (@sellist)OPEN curKeyFETCH NEXT FROM curKey INTO @KeyValueWHILE (@@fetch_status = 0)BEGINset @whr = '"where '+ @pk +' = "' + @KeyValueset @fil = @imgpath1 + '' + @KeyValue --+ @extset @cmd = @path + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr+ ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr + '/F ' + @fil+ ' /' + @modexec Master..xp_cmdShell @cmdFETCH NEXT FROM curKey INTO @KeyValueENDCLOSE curKeyDEALLOCATE curKeyENDGOAbove srcipt runs fine with image data type in one table but when irun for some other table it gives me Error MessageTEXTCOPY Version 1.0DB-Library version 8.00.194SQL Server 'WSQL01' Message 170: Line 1: Incorrect syntax near '99'.(Concerning line 1)DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.ERROR: Could not use database 'test1'NULL-----------Aslo it only runs on server console if i run it from workstation uingsame files and tables it gives me an error again. Can anybody help meand reply me at Join Bytes! asap.thnx,dharmesh
View 1 Replies View RelatedI Am Getting An Error Message When I Try To Print Using Reporting Services: An Error Occurred During Printing (0x80004005)
May 22, 2007I am getting an error message when I try to print using reporting services: "an error occurred during printing (0x80004005)"
View 4 Replies View RelatedNo Help From The Error Messages
Dec 4, 2007I 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?
SQL Error Messages
Mar 9, 1999Is there a way to return the SQL Native error to a Visual Basic program?
View 3 Replies View RelatedError Messages
Mar 3, 1999Hey 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
*** DTS Error Messages ***
Jul 20, 2005Hello,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 RelatedLooking 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.
SqlBulkCopy Error Messages
Sep 6, 2006I'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 RelatedDynamic SQL Error Messages!
Mar 3, 2008Have 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 ^_^
Error Messages To A File?
Aug 23, 2001How to write the error messages generated in a job to a file?Thanks.
View 1 Replies View RelatedHow To Get More Info For Error Messages
Jul 10, 2000So 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.?