SQL 2005 - .Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 - Error Parsing Statements
Nov 2, 2006
Hi,
Does anyone else have this error message pop up in SSMS when you try to parse sql statements:
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
There was a thread back in March 2006 that mentioned this error, but the posted resolution was to install SP1. I have SP1 installed but I still get the error.
I only receive the error when I'm parsing statements, if I run the statement it's fine.
I think this might be a good place to ask the following question.
I am writing the error handling code for my data access layer for a web application. I am using the Enterprise Library Data Access Application Block. Although this supports generic database connections, I realized that I need to handle errors specific to each database type. Microsoft SQL is the only database type I am using for now, so I am using a try...catch (SqlException e).
In testing my code, I intentionally changed the instance name in web.config to a name that does not exist. I get the very popular error 26 - Error Locating Server/Instance Specified. This is returned as a SqlException, but the SqlError.Number property is set to -1.
Am I getting "-1" because the provider hasn't actually connected to SQL yet, so it doesn't have an actual SQL error number? Can I assume that (SqlError.Number == -1) is always a fatal, provider-level connection exception? Will the provider ever use another SqlError.Number of its own? Or do all numbers besides -1 come from the SQL sysmessages table?. Is there a comprehensive list of what exceptions might be raised by the SqlClient provider, including #26?
The reason for all the questions is that in a web application, I want to prevent the end-user from seeing the "real" exception if it has to do with configuration errors. However, maybe there are other errors that the user should see and handle? It's hard to know without a full list of SqlClient provider errors, along with the SqlError.Number that each error maps to.
SELECT @Query = 'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[DEL_' + UPPER(@Table_Name) + ']'')) DROP TRIGGER [dbo].[DEL_' + UPPER(@Table_Name) + ']' EXEC(@Query)
SELECT @Table_Desc = (SELECT a.value FROM sys.extended_properties a, sys.tables b WHERE a.major_id = b.object_id AND a.minor_id = 0 AND a.name = 'MS_DESCRIPTION' AND b.name = @Table_Name)
SELECT @Query = '''CREATE TRIGGER [DEL_' + UPPER(@Table_Name) + '] ON dbo.' + @Table_Name + ' FOR DELETE AS
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_PrimKey]'''''''') AND type in (N''''''''U'''''''')) DROP TABLE [dbo].[Temp_PrimKey]'''')
SELECT K.COLUMN_NAME INTO Temp_PrimKey FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE T.CONSTRAINT_TYPE = ''''PRIMARY KEY'''' AND T.TABLE_NAME = ''''' + @Table_Name + '''''
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U'''''''')) DROP TABLE [dbo].[Temp_Value]'''')
EXEC(''''CREATE TABLE [dbo].[Temp_Value]( [PValue] [VARCHAR](max) NOT NULL ) ON [PRIMARY]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U'''''''')) DROP TABLE [dbo].[Temp_Deleted]'''')
SELECT * INTO Temp_Deleted FROM deleted
DECLARE Curs_PrimKey CURSOR FOR SELECT * FROM Temp_PrimKey
OPEN Curs_PrimKey FETCH NEXT FROM Curs_PrimKey INTO @P_Key WHILE @@FETCH_STATUS = 0 BEGIN EXEC(''''INSERT INTO Temp_Value SELECT ''''+ @P_Key + '''' FROM Temp_Deleted'''') SELECT @P_Key_Value = (SELECT PValue FROM Temp_Value) EXEC(''''DELETE FROM Temp_Value'''') SELECT @P_Key_Insert = @P_Key_Insert + @P_Key + '''' = '''' + @P_Key_Value + '''', '''' FETCH NEXT FROM Curs_PrimKey INTO @P_Key END CLOSE Curs_PrimKey DEALLOCATE Curs_PrimKey
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U'''''''')) DROP TABLE [dbo].[Temp_Deleted]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U'''''''')) DROP TABLE [dbo].[Temp_Value]'''')
INSERT INTO TLOG(Log_Date, Log_Reference, Log_Comment) VALUES (GETDATE(), @P_Key_Insert, @Comment) ''' --PRINT @Query EXEC(@Query) Problem is when I run it gives error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'CREATE TRIGGER [DEL_EMPLOYEE] ON dbo.Employee FOR DELETEAS DECLARE @Old_Value VARCHAR(8000)DECLARE @New_Value VARCHA'.
If I print the @Query and run it in a query analyzer using the statement EXEC it worked. It's giving me headache, this supposed to be a simple exec statement. Please advise. Thanks.
Msg 6260, Level 16, State 1, Line 1 An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed. System.InvalidOperationException: at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount() at System.Data.Common.DbEnumerator.BuildSchemaInfo() at System.Data.Common.DbEnumerator.MoveNext()
Here is my code:
using System; using System.Data; using System.Data.Common; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.Collections;
public static class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")] public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID) { using (SqlConnection connection = new SqlConnection("context connection=true")) { string sqlstring = "select * from Activity where CatActivityID=@CatActivityID;";
connection.Open(); SqlCommand command = new SqlCommand(sqlstring, connection); command.Parameters.AddWithValue("@CatActivityID", CatActivityID);
} } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")] public static void rowfiller(Object obj, out SqlInt32 ActID, out SqlString ActName, out SqlInt32 ActCreatorID, out SqlString ActDesp, out SqlDateTime ActCreateDate, out SqlDateTime ActModifyDate, out SqlDateTime ActStartDate, out SqlDateTime ActEndDate, out SqlInt32 Status, out SqlInt32 Cost, ) {
SqlDataRecord row = (SqlDataRecord)obj; int column = 0;
ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++; ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++; ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++; ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++; ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++; ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++; ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++; ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++; Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++; Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++; }
};
Can anyone tell me what I am doing wrong? Many thanks
we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.
ALTER DATABASE R3P SET PARTNER resume
the error is: Msg 1447, Level 16, State 21, Line 1 ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.
I have the following SQL query (SQL 2005). Its basically retrieving some values using simple joins. However there appears to be a problem with the LEFT OUTER JOIN: "LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID) " When I try to compile the code i Get : Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "OrderLineItemTransaction.OrderLineItemTransactionI D" could not be bound.
Any help would be appreciated.
Cheers Bal
SELECT ord.orderDate, cc.forename + ' ' + cc.surname person, prod.description, oli.noofitems, deladdr.housenameno + ' ' + deladdr.addressLine1 + ' ' + deladdr.addressLine2 + ' ' + deladdr.city + ' ' + deladdr.postcode + ' ' + deladdr.county + ' ' + deladdr.country deladdress FROM product prod, OrderLineItem oli, [Order] ord, OrderTransaction ordT, OrderLineItemTransaction oliT, CustomerContact cc, Customer cust, DDDispatchedOrder dd, address deladdr, address invaddr LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID) WHERE prod.productID = oli.productID:eek: AND ord.orderID = oli.orderID AND ord.orderID = ordT.orderID AND oliT.orderlineitemID = oli.orderlineitemID AND cc.customercontactID = ord.customercontactID AND cc.customerID = cust.customerID AND ord.invoiceaddressID = invaddr.addressID AND ord.deliveryaddressID = deladdr.addressID AND ordT.dispatchTypeID = 2
I'm getting a specific error when i try to run a .sql script for a Custom TableProfileProvider ,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following error. It will produce the table but with "Errors" which dosn't do anyone any good to have errors in the table. I even used the Sql Server Surface Area Configuration to add a new Administrator with the name listed below (WYATT-PCWyatt), that didnt help either..Here is part of the Sample Scheme the problem lies in the [YOURMACHINENAMEASPNET] Server = WYATT-PCSQLEXPRESS Computer Name = Wyatt-PC OS: Windows Vista Home Premium =========================================================== use TableProfileProviders go -- --grants on ASP.NET stored procedures and tables used by the custom providers -- grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [YOURMACHINENAMEASPNET] grant EXECUTE on dbo.aspnet_Users_CreateUser to [YOURMACHINENAMEASPNET] grant SELECT on dbo.aspnet_Users to [YOURMACHINENAMEASPNET] grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [YOURMACHINENAMEASPNET] go --drop table dbo.ProfileTable_1 --go create table dbo.ProfileTable_1 ( UserId uniqueidentifier not null Primary Key, FirstName nvarchar(50) null, LastName nvarchar(50) null, Age int null, LastUpdatedDate datetime not null) go grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [YOURMACHINENAMEASPNET] go ================================================================================ Msg 15151, Level 16, State 1, Line 5 Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
Hi,I am getting the following error from the query below against SQLServer 8.00.2039 (SP4)Error:====Server: Msg 107, Level 16, State 2, Line 1The column prefix 'd' does not match with a table name or alias nameused in the query.Select Statement:=============select ....from trades a,gems_product_groups b,portfolio_nav_mapping c,products d,limit_types eleft outer join gems_prod_trade_mod f on d.product_id =f.product_IDI know this was a known bug in MS-SQL7, but I thought it had been fixedin 2000.Can anyone help?Thanks
Simple SQL Error Can anyone help for a new starter. Error MessageServer: Msg 209, Level 16, State 1, Line 6 Ambiguous column name 'CustomerID'.Codeselect CustomerID, ContactName, OrderId from CustomersInner Join Orders OnCustomers.CustomerID = Orders.CustomerIDDBMS = SQL Server 2000 SP 3aDatabase = NorthwindI have no idea why i receive this error
Hi, I would appreciate your help. I donot know why I am getting this error when I run this query : if (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice) <> (select stamp_number from vendor_invoice) select 'no' else select 'yes'
I get this Error Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. ---- yes (1 row(s) affected) I would appreciate your hlep
Can anybody help me with this syntax? select PHN.N_PHN_ID 'ID', PHN.N_PHN_AREACD 'AREAD1', PHN.N_PHN_PREFIX 'PREFIXD1', PHN.N_PHN_NUMBER 'NBRD1', PHN.N_PHN_EXTENTN 'EXTD1', PHN2.N_PHN_AREACD 'AREAD2', PHN2.N_PHN_PREFIX 'PREFIXD2', PHN2.N_PHN_NUMBER 'NBRD2', PHN2.N_PHN_EXTENTN 'EXTD2' from Donor_Visit2 DV2 RIGHT OUTER JOIN NAT_PHN_DB_REC PHN ON DV2.COUNT_INSTID = PHN.N_PHN_INSTID RIGHT OUTER JOIN NAT_PHN_DB_REC PHN2 ON DV2.COUNT_ID = PHN2.PHN.N_PHN_ID order by PHN.N_PHN_ID
here is the error message: Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'PHN2.PHN' does not match with a table name or alias name used in the query.
This table was created by coldfusion and has been given all priviliges to public and I am in the public group but I still cant select from it. It gives me this error message: Server: Msg 208, Level 16, State 1, Line 1. Help!
I'm getting this error in SQL server. Msg 8101, Level 16, State 1, Line 68 An explicit value for the identity column in table 'temp_notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.
My original script looked like this.
SET NOCOUNT ON
DECLARE @patient_id int ,@phn varchar(9) ,@full_name varchar(100) ,@birth_date datetime ,@entry_note varchar(8000) ,@entry_date datetime ,@rec_counter int ,@Clinic_identifier varchar(24) ,@chart_count int
set @Clinic_identifier = 'MEDOWL.' set @rec_counter = 0
DECLARE patients_cursor CURSOR FOR SELECT a.full_name, a.birth_date, a.phn, a.patient_id FROM patient as a ,clinicdoctors as b WHERE a.patient_id IN (51450,49741,57290) --= 51450 and datalength(a.phn) = 9 and b.clinicdoctor_id = a.clinicdoctor_id ORDER BY a.last_name,a.first_name,a.patient_id
OPEN patients_cursor FETCH NEXT FROM patients_cursor INTO @full_name, @birth_date, @phn, @patient_id
DECLARE chartcount_cursor CURSOR FOR select count(*) from ChartEntries where patient_id = @patient_id
OPEN chartcount_cursor FETCH NEXT FROM chartcount_cursor INTO @chart_count
--select @chart_count as chtcount
DECLARE chartentries_cursor CURSOR FOR select entry_datetime,entry_note from ChartEntries where patient_id = @patient_id order by Patient_id, ChartEntry_Id desc
-- Variable value from the outer cursor
OPEN chartentries_cursor FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note --select @entry_date,@entry_note
WHILE @@FETCH_STATUS = 0
BEGIN set @rec_counter = @rec_counter + 1 --select @@FETCH_STATUS as status IF @@FETCH_STATUS = 0
-- Get the next chart record count --FETCH NEXT FROM chartcount_cursor INTO @chart_count --select @chart_count as chtcount
END
CLOSE patients_cursor DEALLOCATE patients_cursor
GO
I then changed it to left pad the variables @rec_counter and @chart_count. SET NOCOUNT ON
DECLARE @patient_id int ,@phn varchar(9) ,@full_name varchar(100) ,@birth_date datetime ,@entry_note varchar(8000) ,@entry_date datetime ,@rec_counter int ,@Clinic_identifier varchar(24) ,@chart_count int
set @Clinic_identifier = 'MEDOWL.' set @rec_counter = 0
DECLARE patients_cursor CURSOR FOR SELECT a.full_name, a.birth_date, a.phn, a.patient_id FROM patient as a ,clinicdoctors as b WHERE a.patient_id IN (51450,49741,57290) --= 51450 and datalength(a.phn) = 9 and b.clinicdoctor_id = a.clinicdoctor_id ORDER BY a.last_name,a.first_name,a.patient_id
OPEN patients_cursor FETCH NEXT FROM patients_cursor INTO @full_name, @birth_date, @phn, @patient_id
DECLARE chartcount_cursor CURSOR FOR select count(*) from ChartEntries where patient_id = @patient_id
OPEN chartcount_cursor FETCH NEXT FROM chartcount_cursor INTO @chart_count
--select @chart_count as chtcount
DECLARE chartentries_cursor CURSOR FOR select entry_datetime,entry_note from ChartEntries where patient_id = @patient_id order by Patient_id, ChartEntry_Id desc
-- Variable value from the outer cursor
OPEN chartentries_cursor FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note --select @entry_date,@entry_note
WHILE @@FETCH_STATUS = 0
BEGIN set @rec_counter = @rec_counter + 1 --select @@FETCH_STATUS as status IF @@FETCH_STATUS = 0
-- Get the next chart record count --FETCH NEXT FROM chartcount_cursor INTO @chart_count --select @chart_count as chtcount
END
CLOSE patients_cursor DEALLOCATE patients_cursor
GO
That is when I received the error, so I inserted this command before the insert line but it made no difference.
SET IDENTITY_INSERT temp_notes ON insert into temp_notes values (@Clinic_identifier+@phn+'.' +REPLICATE('0',3),convert(varchar,@rec_counter)+'. ' +REPLICATE('0',3),convert(varchar,@chart_count), @phn, @full_name, @birth_date, @entry_date, @entry_note )
I'm concatenating four different variables together to insert into the column note_id.
What is the problem? I just don't see it. My table definition looks like this. CREATE TABLE [dbo].[temp_notes]( [temp_Id] [int] IDENTITY(1,1) NOT NULL, [note_Id] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [phn] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [full_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [birth_date] [datetime] NULL, [entry_datetime] [datetime] NULL, [Entry_Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_temp_notes] PRIMARY KEY CLUSTERED ( [temp_Id] ASC ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 8229000000047200001, which is too recent to apply to the database. An earlier log backup that includes LSN 8219000000021400001 can be restored
And what would the fix be?
I am trying to restore from a Full Backup and then the log files on a development machine.
If we run the query below with the values of 'rml' and 'ra' it runs but if we use 'cca' and 'cc' we get this error:
Server: Msg 1101, Level 17, State 10, Line 8 Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
from tblAsset, tblAsset_Book, tblCounty_Names, CER..tblLocation, tblGL_Account where tblAsset_Book.Level_1 = @Level_1 and tblAsset.Level_1 = tblAsset_Book.Level_1 and tblAsset_Book.Level_2 = @Level_2 and tblAsset.Level_2 = tblAsset_Book.Level_2 and tblGL_Account.Company = tblAsset.Level_2 and CER..tblLocation.SBU = tblGL_Account.Company and tblCounty_Names.County = tblAsset.County and CER..tblLocation.County = tblCounty_Names.County and CER..tblLocation.Code = tblAsset.Plant and tblGL_Account.Account = tblAsset.Account and tblAsset_Book.Book_Number = '1' order by tblAsset.State
Server: Msg 2537, Level 16, State 43, Line 1 Table error: Object ID 1376776012, index ID 0, page (1:56868), row 2. Record check (Valid SqlVariant) failed. Values are 7 and 0. Server: Msg 2537, Level 16, State 1, Line 1 ... so on
Dear (and mighty) all:I backed up a database (SQL server 7.0) and tried to restore it onanother system (SQL Server 2000). This is not the first time I'm doingthis and never had a problem before:-- in the source dbBACKUP db_icoaraci TO DISK = 'C:TEMPBACKUP-ICOARACI.DAT'--in the destination dbRESTORE db_icoaraci FROM DISK='E:TEMPBACKUP-ICOARACI.DAT'WITH MOVE='ICOARACI_DAT' TO 'E:DBICOARACI.MDF',MOVE='ICOARACI_LOG' TO 'E:DBICOARACI.LDF'Today, to my surprise, the destination SQL returned the followingerror:Processed 25592 pages for database 'db_icoaraci', file 'ICOARACI_DAT'on file 1.Processed 1 pages for database 'db_icoaraci', file 'ICOARACI_LOG' onfile 1.Server: Msg 1105, Level 17, State 2, Line 1Could not allocate space for object '(SYSTEM table id: 6)' in database'db_icoaraci' because the 'PRIMARY' filegroup is full.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.I searched the list and a found a few posts refering to the same error,but none in the exactly same scenario.I tried to restore the database with NORECOVERY, and, before restoringthe log, increase its size (ALTER DATABASE ... etc), but SQL Serversays that the DB couldn't be open because it's in the middle of arestore...I'll appreciate any help.Regards,Branco.
I am experiencing some frustration in regards to parsing an email that I need to work with. (I don't have control over the format of the email I am receiving)
Any help would be appreciated. Thank you Jake
Here is the error I get, when I use the SQL Query Analyzer to process my Stored Procedure. (Not every email gets this error, just some, others process just fine.)
String or binary data would be truncated. The statement has been terminated. Stored Procedure: eResponse.dbo.spParser Return Code = -6
My Code is as follows:
CREATE PROCEDURE spParser @_id nvarchar(50) AS --assumes the email is no longer than 4000 characters. if it might be longer, bump this number up. -- Common Varriables DECLARE @body nvarchar(4000) DECLARE @i int DECLARE @phone nvarchar(50) DECLARE @phonestart int DECLARE @phoneend int DECLARE @email nvarchar(50) DECLARE @emailstart int DECLARE @emailend int DECLARE @comments nvarchar(3000) DECLARE @commentsstart int DECLARE @commentsend int DECLARE @OrigID varchar(50) -- Jose Ole Variables DECLARE @firstname nvarchar(50) DECLARE @firstnamestart int DECLARE @firstnameend int DECLARE @lastname nvarchar(50) DECLARE @lastnamestart int DECLARE @lastnameend int DECLARE @address1 nvarchar(50) DECLARE @address1start int DECLARE @address1end int DECLARE @address2 nvarchar(50) DECLARE @address2start int DECLARE @address2end int DECLARE @city nvarchar(50) DECLARE @citystart int DECLARE @cityend int DECLARE @state nvarchar(5) DECLARE @statestart int DECLARE @stateend int DECLARE @zip nvarchar(15) DECLARE @zipstart int DECLARE @zipend int DECLARE @country nvarchar(50) DECLARE @countrystart int DECLARE @countryend int DECLARE @phone2 nvarchar(50) DECLARE @phone2start int DECLARE @phone2end int DECLARE @productname nvarchar(50) DECLARE @productnamestart int DECLARE @productnameend int DECLARE @upccode nvarchar(50) DECLARE @upccodestart int DECLARE @upccodeend int DECLARE @datecode nvarchar(50) DECLARE @datecodestart int DECLARE @datecodeend int DECLARE @purchaseat nvarchar(50) DECLARE @purchaseatstart int DECLARE @purchaseatend int DECLARE @purchasecity nvarchar(50) DECLARE @purchasecitystart int DECLARE @purchasecityend int DECLARE @datepurchased nvarchar(50) DECLARE @datepurchasedstart int DECLARE @datepurchasedend int DECLARE @dateopened nvarchar(50) DECLARE @dateopenedstart int DECLARE @dateopenedend int DECLARE @subject nvarchar(50) DECLARE @subjectstart int DECLARE @subjectend int -- Windosr Variables DECLARE @name nvarchar(50) DECLARE @namestart int DECLARE @nameend int DECLARE @company nvarchar(50) DECLARE @companystart int DECLARE @companyend int
--First replace all ASCII carriage returns and line feeds and assign the whole chunk of data to a variable. SET @body = (select replace( REPLACE(convert(varchar(4000), message),CHAR(10),' '),CHAR(13),'') from message where mailid = @_id) --below is the sample syntax for the individual replacements of line feeds and carriage returns, but it's combined into one statement above. --SELECT REPLACE(@jb,CHAR(10),'') as firstpass --SELECT REPLACE(@jb,CHAR(13),'') as secondpass IF left(@body,47) = 'Fromubject:contact info sent from samplesite.com' --If this is true.. then it is a sample company. BEGIN --The 12 in the start is compensating for the 12 Characters of "First Name: " --Below is collecting First Name SET @firstnamestart = (CHARINDEX('First name:',@body) + 11) SET @firstnameend = (CHARINDEX('Last name:',@body) - 1) SET @firstname = SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart) ----Below is collecting Last Name SET @lastnamestart = (CHARINDEX('Last name:',@body) + 10) SET @lastnameend = (CHARINDEX('Address 1:',@body) - 1) SET @lastname = SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart) --Below is collecting Address1 SET @address1start = (CHARINDEX('Address 1:',@body) + 10) SET @address1end = (CHARINDEX('Address 2:',@body) - 1) SET @address1 = SUBSTRING(@body,@address1start,@address1end-@address1start) --Below is collecting Address2 SET @address2start = (CHARINDEX('Address 2:',@body) + 10) SET @address2end = (CHARINDEX('City:',@body) - 1) SET @address2 = SUBSTRING(@body,@address2start,@address2end-@address2start) --Below is collecting city information SET @citystart = (CHARINDEX('City:',@body) + 5) SET @cityend = (CHARINDEX('State/Prov',@body) -1) SET @city = SUBSTRING(@body,@citystart,@cityend-@citystart) --Below is collecting state information SET @statestart = (CHARINDEX('State/Province:',@body) + 15) SET @stateend = (CHARINDEX('Zip/Postal',@body) -1) SET @state = SUBSTRING(@body,@statestart,@stateend-@statestart) --Below is collecting zip information SET @zipstart = (CHARINDEX('Zip/Postal Code:',@body) + 16) SET @zipend = (CHARINDEX('Country',@body) -1) SET @zip = SUBSTRING(@body,@zipstart,@zipend-@zipstart) --Below is collecting country information SET @countrystart = (CHARINDEX('Country:',@body) + 8) SET @countryend = (CHARINDEX('E-mail address',@body) -1) SET @country = SUBSTRING(@body,@countrystart,@countryend-@countrystart) --Below is collecting email information SET @emailstart = (CHARINDEX('E-mail address:',@body) + 15) SET @emailend = (CHARINDEX('Daytime',@body) -1) SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart) --Below is collecting phone information SET @phonestart = (CHARINDEX('Daytime Phone:',@body) + 14) SET @phoneend = (CHARINDEX('Evening Phone:',@body) -1) SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart) --Below is collecting phone2 information SET @phone2start = (CHARINDEX('Evening Phone:',@body) + 14) SET @phone2end = (CHARINDEX('Product Name',@body) -1) SET @phone2 = SUBSTRING(@body,@phone2start,@phone2end-@phone2start) --Below is collecting Product Name information SET @productnamestart = (CHARINDEX('Product Name:',@body) + 12) SET @productnameend = (CHARINDEX('UPC Code:',@body) -1) SET @productname = SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart) --Below is collecting UPC Code information SET @upccodestart = (CHARINDEX('UPC Code:',@body) + 9) SET @upccodeend = (CHARINDEX('Date Code:',@body) -1) SET @upccode = SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart) --Below is collecting Date Code information SET @datecodestart = (CHARINDEX('Date Code:',@body) + 10) SET @datecodeend = (CHARINDEX('Purchased At',@body) -1) SET @datecode = SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart) --Below is collecting Purchase At information SET @purchaseatstart = (CHARINDEX('Purchased At',@body) + 26) SET @purchaseatend = (CHARINDEX('Purchase City:',@body) -1) SET @purchaseat = SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart) --Below is collecting Purchase City information SET @purchasecitystart = (CHARINDEX('Purchase City:',@body) + 14) SET @purchasecityend = (CHARINDEX('Date Purchased',@body) -1) SET @purchasecity = SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart) --Below is collecting Date Purchased information SET @datepurchasedstart = (CHARINDEX('Date Purchased:',@body) + 15) SET @datepurchasedend = (CHARINDEX('Date Opened',@body) -1) SET @datepurchased = SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart) --Below is collecting Date Opened information SET @dateopenedstart = (CHARINDEX('Date Opened:',@body) + 12) SET @dateopenedend = (CHARINDEX('E-mail Subject:',@body) -1) SET @dateopened = SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart) --Below is collecting Email Subject information SET @subjectstart = (CHARINDEX('E-mail Subject:',@body) + 15) SET @subjectend = (CHARINDEX('Comments:',@body) -1) SET @subject = SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart) --Below is collecting message information SET @commentsstart = (CHARINDEX('Comments:',@body) + 10) SET @commentsend = len(@body) SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart) --below is the part that actually writes the data out to the Output table INSERT INTO OutputEmails (FirstName, LastName, Address1, Address2, City, State, Zip, Country, eMail, Phone, Phone2, ProductName, UPCCode, DateCode, PurchaseAt, PurchaseCity, DatePurchased, DateOpened, Subject, Comments, OrigID) SELECT ltrim(SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)) as FirstName, ltrim(SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)) as LastName, ltrim(SUBSTRING(@body,@address1start,@address1end-@address1start)) as Address1, ltrim(SUBSTRING(@body,@address2start,@address2end-@address2start)) as Address2, ltrim(SUBSTRING(@body,@citystart,@cityend-@citystart)) as City, ltrim(SUBSTRING(@body,@statestart,@stateend-@statestart)) as State, ltrim(SUBSTRING(@body,@zipstart,@zipend-@zipstart)) as Zip, ltrim(SUBSTRING(@body,@countrystart,@countryend-@countrystart)) as Country, ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail, ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone, ltrim(SUBSTRING(@body,@phone2start,@phone2end-@phone2start)) as Phone2, ltrim(SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)) as ProductName, ltrim(SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)) as UPCCode, ltrim(SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)) as DateCode, ltrim(SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)) as PurchaseAt, ltrim(SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)) as PurchaseCity, ltrim(SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)) as DatePurchased, ltrim(SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)) as DateOpened, ltrim(SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)) as Subject, ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments, @_id as OrigID FROM dbo.message WHERE (mailId = @_id) END ELSE BEGIN --below prints the whole value in the debugger (SQL Query Analyzer) --print @body --below prints the length of the whole value --print len(@body) --below prints the location of 'Callers Name:' --Print CHARINDEX('Name: ',@body) --Below is collecting the Name information --The 6 in the start is compensating for the 6 Characters of "Name: " SET @namestart = (CHARINDEX('Name:',@body) + 5) SET @nameend = (CHARINDEX('Email:',@body) - 1) SET @name = SUBSTRING(@body,@namestart,@nameend-@namestart) --Below is collecting email information SET @emailstart = (CHARINDEX('Email:',@body) + 6) SET @emailend = (CHARINDEX('Title:',@body) -1) SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart) --Below is collecting Company information SET @companystart = (CHARINDEX('Company:',@body) + 8) SET @companyend = (CHARINDEX('Phone Number:',@body) -1) SET @company = SUBSTRING(@body,@companystart,@companyend-@companystart) --Below is collecting phone information SET @phonestart = (CHARINDEX('Phone Number:',@body) + 13) SET @phoneend = (CHARINDEX('Comments:',@body) -1) SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart) --Below is collecting message information SET @commentsstart = (CHARINDEX('Comments:',@body) + 9) SET @commentsend = len(@body) SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart) --below is the part that actually writes the data out to the Output table INSERT INTO OutputEmails (Name, eMail, Company, Phone, Comments, OrigID) SELECT ltrim(SUBSTRING(@body,@namestart,@nameend-@namestart)) as Name, ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail, ltrim(SUBSTRING(@body,@companystart,@companyend-@companystart)) as Company, ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone, ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments, @_id as OrigID FROM dbo.message WHERE (mailId = @_id) END --At the very end when you're satisfied that the data has been processed properly, delete the appropriate message record from the message table DELETE FROM MESSAGE Where message.mailID = @_ID GO
Sample Data that has problems
Fromubject:contact info sent from samplesite.comBody:First name: Sample Last name: Name Address 1: 123 Testing Road Address 2: Don't filled City: Park Forest State/Province: FL Zip/Postal Code: 12345-1234 Country: USA E-mail address: validemail@email.com Daytime Phone: 123-123-1234 Evening Phone: Don't filled Product Name: Sample Product with Cheese UPC Code: 12345-89521 Date Code: 1251237 D PST . 5290 Purchased At (Store Name): StoreName Purchase City: Store City Date Purchased: 12/8/2007 Date Opened: 12/14/2007 E-mail Subject: nail in product Comments: when I purchased your product, there was something in it. Obviously I am not happy about this. Please contact me. Thank you John Q Public
I restored a database to my test server from a production server. I ran "sp_change_users_login 'report' " and found 1 orphan id. Then I ran "sp_change_users_login 'auto_fix', 'orphan_id' ". It gets the error. I have run it before with success. Any idea why this time is different?
I'm getting a specific error when i try to run a .sql script for a Custom TableProfile from the Asp.net site,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following errors::
Msg 15151, Level 16, State 1, Line 5
Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
Here is a Database script I wrote: I can't figure out what the problem is when I declare my primary Keys in the second and third table. Thanks in Advance
Errors:
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'IDENTITY'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'IDENTITY'.
CREATE DATABASE GoGreen
Go
USE GoGreen
Create TABLE Student
(StudentID INT NOT NULL IDENTITY PRIMARY KEY, <---no Error here
DrivePassCode char(3) NOT NULL,
LName varchar(50) NOT NULL,
FName varchar(50) NOT NULL,
Address1 varchar(50) NOT NULL,
Address2 varchar(50) NULL,
City varchar(50) NOT NULL,
PostalCode varchar(20) NOT NULL,
PhoneNumber varchar(50) NOT NULL,
Email varchar(50) NOT NULL,
Smoker char(2) NOT NULL,
NearestCityOrTown varchar(50) NOT NULL,
PaymentType varchar(20) NOT NULL,)
CREATE TABLE CoordinatedRideShare
(RideID INT NOT NULL, IDENTITY PRIMARY KEY, <---Error #1
StudentID INT NOT NULL REFERENCES Student(StudentID)
[ON DELETE {CASCADE}]
[ON UPDATE {CASCADE}],
M-W ARRIVAL TIME smalldatetime NOT NULL,
M-W DEPART TIME smalldatetime NOT NULL,
T-TH ARRIVAL TIME smalldatetime NOT NULL,
T-TH DEPART TIME smalldatetime NOT NULL,)
CREATE TABLE MeetingLocation
(LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER, <---Error#2
RideID INT NOT NULL REFERENCES Coordinated RideShare(RideID)
Basically the error that I am getting is in our test automation when running as non-admin on the box (regular user). We use .Net C# SQLConnection class to connect to SQL express 2005 impersonating with admin credentials. After getting the connection we try to execute a select command and it some time fails with following error: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()
This happen only in the non-admin scenario mentioned above. Any idea what is triggering this?
Well, I hope, after my endless search in the web... maybe, somebody can help me here.I'm passing a application to a web-based application, therefore I have to use ADO.net to access a sql server DB. But the performance is extremly poor!!!!Just to compare: with the normal application or with the SQL Query Analyzer, the query needs about 2 seconds. Well, by using ado.net, it's about 200 seconds......!!!!Of course, the query is quite long. But the difference is extrem... too extrem for using the same query....I already tried a lot... actually everything I found on the web. What is really strange is that neither the processor nor the memory is fully used... And the web server use more of the cpu power than the sql server itself (the 2 server are still on my machine where I'm programming). So, could it be that the problem is by filling up the dataset with the sqladapter or something similar?Thx for your help!!
