Using ADO.Net and SQL 2005, we have an exception handler in one of our .VB classes. We extract all the information from the exception and dump the stack into an email, but we would also like to see the query that caused the exception.
Is there an easy way to get the offending query without putting a try-catch around the hundreds of queries?
Thanks.
Hi, all experts here,
Thank you very much for your kind attention.
It's so frustrated that I dont really knwo what is going on and why is that and I have tried ages to try to figure it out and nothing really helps.
I have already moved the data files of tempdb database to a drive with enough space (many GB space left still), but then again I got the problem which run out all of the system drive space when I run a query against a large table? Why is that? And how to figure it out?
Please help me and thanks a lot in advance for your kind advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I am getting error
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at Galileo.Modules.Data.DBDeclare.RunProcedure(String sp, IDataParameter[] parameters) at GalileoModules.Data.PrivateAccomodation.Search3.Query(String[] values
If I don't cache this page, would that help?
Any other suggestions?
I just installed SQL7.0 on a windows 98 machine. When I go to register a new database, I get the following message.
MMC caused a general protection fault
I have rebooted the machine, removed & reinstalled the application.
Help - what else
Does anyone know of any SQL errors that will stop the SQLserve.exe service? I meant the error didn't hang the SQL server service, it stopped the service (from green light to red light).
Thanks in advice for any input or ideas.
Wing
I had a database in suspect mode this morning.
Fixed it by:
-placing in emergency mode.
-set single_user mode
-dbcc checkdb(N'dbname', REPAIR_ALLOW_DAT_LOSS)
-Rebuild the log.
-bring online.
DBCC Checkdb returned a bunch of errors:
Extent (3:394032) in database ID 7 is allocated by more than one allocation object. The error has been repaired.
Msg 8913, Level 16, State 1, Line 2
Extent (3:394032) is allocated to 'GAM' and at least one other object.The error has been repaired.
--Lots of these
Repair: Deleted off-row data column with ID 562825134080, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 28.
Repair: Deleted off-row data column with ID 562825199616, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 29.
Repair: Deleted off-row data column with ID 562825265152, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 30.
Lots of these:
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8977, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044088320 (type In-row data). Parent node for page (3:394009) was not encountered.
The error has been repaired.
[code]....
Can somebody guive me alittle guidance in how to make this? I have like 4 lookups but some can throw errors, and I need to know what happened when I go to the office in the moorning.
Thanks
My program use the mutil-threads, each thread would use AppendChunk to insert a binary file into sqlserver. But program always stopped exceptionally , But I can not catch any exception. It looks like be killed by someone or system.
Can anyone hekp me?
Thanks a lot.
Hello:
We have, on a devlopment server, running a long_running transaction for
almost two days. It was still running. This morning I decided to look at
three of two backup devices under EM to see if any backups were made to
these devices during the last couple of days on a de,mand basis by anyone.
The first one had a backup and the second and third device did not.
Somehow, and for some unexplained reason, the following message was put
into the MSSQL error log (and pretty much the same message went into the NT
event viewer).
----------------------------------------------------------------------
99/02/23 10:05:45.81 kernel dbsvolopen: Backup device
'E:MSSQLBACKUPEaglePro2_run02.DAT' failed to open, operating system
error = 2(The system cannot find the file specified.)
99/02/23 10:05:48.48 kernel dbsvolopen: Backup device
'E:MSSQLBACKUPEaglePro2_run02.DAT' failed to open, operating system
error = 2(The system cannot find the file specified.)
99/02/23 10:05:49.17 kernel dbsvolopen: Backup device
'E:MSSQLBACKUPEaglePro2_run03.DAT' failed to open, operating system
error = 2(The system cannot find the file specified.)
---------------------------------------------------------------------
This error, I beleive, caused DR watson to kick off and we could only sopt
it and revive the long running transaction by rebooting the machine, of
course thus requiring us to restart the lon_running transaction.
It look like NT couldn't find the backup device and therefore activated the
drwatson executible or so it seems to me.
Has anyone experienced this error before? IS it caused by NT? Or that
perhaps a certain threshhold was reacched on NT and drwatson kicked in?Any
other ideas?
Any information that can be provided would be GREATLY appreciated. THanks.
David Spaisman
I just updated MDAC on one of my machines and I'm now having trouble accessing my SQL server. It's a local connection using SQLOLEDB in a VB dll. It was working fine before the update and now I get:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/cms25/CMS.asp, line 33
Most of the information I can find on this refers to TCP/IP connections but the SQL server is local. My connect function looks like this:
Public Sub ADOConnect()
With m_ADOConnection
.Provider = "SQLOLEDB"
.CursorLocation = adOpenStatic
.CommandTimeout = 20
.Mode = adModeReadWrite
.ConnectionString = "Data Source=" & m_strServer & ";Database=" & m_strName & ";" & m_strUsernamePassword
.open
End With
End Sub
On this server, m_strServer = "(local)". What am I missing? Thanks for any help in advance!
a.
Hi.I need to make a trigger which fires when after an UPDATE is done ontable A in database X. The trigger must perform a statement whichcopies some of the data from the update to table B in database Y onthe same server.My question is: Can I access the data which caused the trigger to fireor just get an indication of which entry in table A had been updated?Best RegardsJens Christian Andersen.
View 2 Replies View RelatedHi,
Let's assume the client code call a stored procedure which reads: SELECT * FROM myTable WHERE ID < 100. Let's assume that for whatever reason, the query becomes very slow and takes 60 seconds to complete. This 60 secs, could be the time for the DB engine to fetch all the rows (happening before returning the resultset to client code), or during the transfer of the resultset to client code (slow network throughput).
Question1: could this prevent another user from doing SELECT on myTable? (could be onn different ID or even overlapping the ID between 1 and 100).
Question2: could this prevent another user from performing write (UPDATE/DELETE) on the rows with ID between 1 and 100?
Question3: can another user perform a write (INS, DEL, UPD) on rows outside of the ID between 1 and 100?
Thanks in advance for any help.
I am trying to import a fixed width file where some of the numeric columns are empty. The columns in question are defined as integer columns (of varying sizes) and I am guessing that "empty" columns come across as multiple spaces on the import.
Even though I have "Retain null values from source" checked off, I am still receiving the following error on these empty columns:
Error: 0xC02020A1 at Input Data, Flat File Source [1]: Data conversion failed. The data conversion for column "ToContractExpiryYear" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Input Data, Flat File Source [1]: The "output column "ToContractExpiryYear" (51)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ToContractExpiryYear" (51)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Input Data, Flat File Source [1]: An error occurred while processing file "\Nastinus-01ClearingDataOCC20060320ser2mst.20060317" on data row 1.
If it is truly the system treating the column as spaces (and not trimming the value), then the only solution I can think off is to source everything as strings, perform a transform that executes a Trim() (Derived Column or Script ??), THEN perform a transform that converts data types, then do whatever else I need...
Am I missing something? Is this the correct solution?
I wanted to prevent ntbackup from locking my databases during the nightly backup, so I excluded the ...Data folder from the backup set. However, I think that ntbackup is backing up the databases during the volume shadow copy phase of the backup, even though their folder was excluded from the backup set (I verified this setting in the .bks file). Although the lock only lasts a few seconds, the error message is worrisome and I would prefer to skip the databases completely. How can I stop the volume shadow copy from doing this?
Below is the the sequence of events leading up to the error, most recent first:
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 3/23/2007
Time: 3:01:49 AM
Description:
SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex). ErrorCode=(288)Attempt to release mutex not owned by caller.
. Process=8684. Thread=7556. Client. Instance=. VD=.
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 18264
Date: 3/23/2007
Time: 3:01:48 AM
Description:
Database backed up. Database: master, creation date(time): 2007/03/12(15:08:23), pages dumped: 1, first LSN: 268:352:37, last LSN: 269:24:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{66DC3082-FB76-4312-AD74-4BDAD9FC7209}1'}). This is an informational message only. No user action is required.
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3198
Date: 3/23/2007
Time: 3:01:47 AM
Description:
I/O was resumed on database master. No user action is required.
Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2003
Date: 3/23/2007
Time: 3:01:47 AM
Description:
lsass (956) Shadow copy 24 freeze stopped.
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3197
Date: 3/23/2007
Time: 3:01:45 AM
Description:
I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2001
Date: 3/23/2007
Time: 3:01:45 AM
Description:
lsass (956) Shadow copy 24 freeze started.
Event Type: Information
Event Source: NTBackup
Event Category: None
Event ID: 8018
Date: 3/23/2007
Time: 3:00:14 AM
Description:
Begin Operation
Hello
Am having a problem which started after I installed a databse with sqlexpress. I get this error when backing up "MSDEWriter" has reported an error 0x800423f4
I am running Windows 2003 SBS SP2.
I've checked to make sure that all of my databases are using simple in recovery mode.
Please help
Thanks
is it possible that this error during backup caused Sort Warning???
I sent to support of our Storage and he make result that this caused jobs which have Sort Warning....
if I checked this error on internet, i found problem with Storage or it can be also lot of fullscan and etc.
So it can be also that Order By caused some reading directly from disk and not from memory ? maybe if there is using tempdb right ?
Hi.I have an ancient preCambrian app that uses the db library to interface withsql server databases.It is written in c++.When testing it I noticed on one machine with sql server 2005 installed itruns fine, but on another it has some problems, notably.. it gets as a resultof a select statement with an order by clause the error:only fully keyset ddriven cursors can have 'order by', 'group by', or 'having'phrases.Error code 10080Can someone suggest a 'configuration' parameter that might be causing theotherwise identical systems to have this problem on one machine and not theother?ThanksJeffthanksJeffJeff Kish
View 1 Replies View RelatedHi all,
Report:
-For instance 2 small tables (eg. width 10cm = 3 inch?)
-And one wide table (eg. width 30cm = 10 inch?)
All separated by "insert pagebreak after table".
Problem:
When rendered, the pages with the small tables on have a lot of white blank space at the right of the table. This is probably caused by the big table on page 3.
This report is distributed by email in Excell format. So on sheet 1 and 2 there are a lot of white cells on the right of the tables. When trying to print, they just want to use the "landscape" option and the "fit to page" option. Because of the empty white cells, the fit to page option reduces the first 2 tables to a very small table which covers only 50 % of the page width. The other 50 % is reserved for the empty cells.
Off course, I know that deleting the empty cells offers a solutions, but it would be a lot more handier if there were no empty cells in the first place.
Anybody with a solution?
Hello,
I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?
Thank in advance for your assistance.
I used this query to get a result
select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime
from customer rf
but i am getting an error ?
"Difference of two datetime columns caused overflow at runtime."
Any idea ?
Hi,
Using SQL Reporting 2005:
I have a report which has the Interactive Height set to 0 so that it does not use paging. When I try and print preview the report, I can see the report okay until I go forward after the last page, and then I get the error "An error occured trying to render the report. 0x80004005". I can print the report and it prints fine, but I get a pop-up that says that there was an error printing the report. I've narrowed it down to the interactive size property, but am not sure how to get around it.
Thanks.
The script is failing at this point "DATEADD(mm, RowNum, salesdate) subscriptionrowdate" dont know exactly where i am going wrong.
This is my code
SELECT *, CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid, firstproductregistrationdate, salesdate, baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription
)a
At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.
View 2 Replies View RelatedI'm now getting this with great regularity. Dell PWS390,2gig,Big HD,experienced user C++...Refactor recently installed although I do not use it.
CLEARLY, the hang comes when one highlites a line and then cursors down to highlight additional lines...I believe this invokes refactoring --- while pre-refactoring install would get a hang occasionally, I now get one frequently. I am uninstalling refactor....
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3
Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40)
SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"';
SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource]
FROM JobWanted AS j INNER JOIN
Company AS c ON c.CompanyID = j.CompanyID
WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
Thanks in advance.
Ricky.
Hi,
Yesterday, we have had a sudden load in our SQL Server 2000 which resulted in several locks. There was not too much time to investigate as we had to rush. A team member had reviewed the processes in EM, Manegement, Current Activity. Looking for blocking processes and killed them.
She told me that as soon as the blocking SPID was killed, another one arose and she had to repeat the operation a dozen of time. When done, the server activity was back to normal. She noticed that more than half of the blocking processes showed that they executed the stored Proc "P_SearchProducts".
We don't own the server and the information on what had happened at that time (batches or resource intensive operations, etc.) is not available for now.
The team suggests that we set the Transaction Isolation Level to Read UNCOMMITTED for this SP. I would like to know better about locks before I go ahead.
P_SearchProducts returns 5 recordsets each one could contains from 1 to 200 rows. To achieve the results, it creates about 10 intermediate tables (SELECT ... INTO #TableX) these temp tables are then used progressively to arrive to the final results. Roughly the volume of these temp tables could be double than the final results. The developer who wrote this SP is not a guru in SQL, there is room for improvement. But here are my questions:
Q1. Could the series SELECT ... INTO #TableX in P_SearchProducts prevent or lock another connection from executing the same SP? If yes, under which conditions?
Q2. Let's assume that P_SearchProducts has a slow execution time. Could it prevent another connection from updating the Product table? And thus leading to a deadlock situation? Something like another transaction (by User2) has obtained lock on most of Product tables, except the Product table which were being slowly read by User1 executing P_SearchProducts. But User1 cannot read the other product tables b/c there are locks by User2.
Q3. If the contention issue was provoked by the slow execution time of many request to exec
P_SearchProducts (let's assume there were suddenly 50 users on the web hitting the search product feature at the same time). Could the Read Uncommitted magically resolve the contention issue, providing we accept the consequences of the dirty read.
Sorry for the long post and thank you in advance for any help.
I'll try re-posting this, as the other post didn't seem to take.
I'm a long time "classic" ASP developer used to SQL Server 2000. I'm messing around now with Visual Web Developer 2005 and SQL Server Express 2005 (both latest versions). I've inclided an MDF file with my ASP.NET project, and have created some tables and entered some data. From time to time, when starting the site, I receive the following error:
A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)
It always appears on this line:
Dim retReader As SqlDataReader = MyCommand.ExecuteReader
I've posted this question elsewhere, and haven't received any insight as to what the problem may be. Can anyone help?
Thanks;
Brad Brening
I'm using .NET 2.0
I designed my BLL to work as follows: 'Update existing company
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateCompany(ByVal Original_CompanyID As Int64, ...) As Boolean
'Get current record
Dim Companies As biodata.CompaniesDataTable = CompaniesAdapter.GetCompanyByID(Original_CompanyID)
'If no record exists, Update fails
If Companies.Count = 0 Then
Throw New ApplicationException("Company does not exist.")
End If
Dim Company As biodata.CompaniesRow = Companies(0)
'Set all the values to load into the database
Company.CompanyName = CompanyName
...
Dim RowsAffected As Integer = CompaniesAdapter.Update(Company)
Return (RowsAffected = 1)
End Function
Recently I deleted a column in my SQL database. In order to account for this change, I deleted the same column in my DAL. Then, because of the obvious compile errors that would occur if I didn't, I deleted all references to that column in my BLL. My DAL automatically creates the Update and Insert statements. Now, the error I'm getting is as follows:
Exception Details: System.Data.SqlClient.SqlException: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.The statement has been terminated.Source Error:
Line 18409: System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")> _
Line 18410: Public Overloads Overridable Function Update(ByVal dataRow As System.Data.DataRow) As Integer
Line 18411: Return Me.Adapter.Update(New System.Data.DataRow() {dataRow})
Line 18412: End Function
Line 18413:
Stack Trace:
[SqlException (0x80131904): Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The statement has been terminated.]
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +200
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +104
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +3042
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows) +345
biodataTableAdapters.CompaniesTableAdapter.Update(DataRow dataRow) in C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot67b7db20aea00f88App_Code.nf1apfb7.1.vb:18411
CompaniesBLL.UpdateCompany(Int64 Original_CompanyID, String CompanyName, String Division, String Website, Nullable`1 CompanyInactive, String CompanyInactiveReason, Nullable`1 EmployeeCount, Nullable`1 YearFounded, Nullable`1 YearStartedBiobased, Nullable`1 NAICS, String Address1, String Address2, String City, String StateCode, String Territory, String CountryCode, String Zip, String CompanyNotes, String Prefix, String FName, String LName, String Suffix, String Title, String Email, String Phone1, String PhoneType1, String Phone2, String PhoneType2, String Phone3, String PhoneType3, String UpdateType, String UpdatedBy, Int64 SecurityLevel, Int64[] Flags) in F:App_CodeBLLCompaniesBLL.vb:376
Now, this exception makes no sense to me because the DAL should automatically fix the update and insert statements itself, and I never directly access those statements. The error is occuring at CompaniesAdapter.Update(Company) in my code above. Any ideas?
Thanks,BSY E-718
Hello,
I am having trouble catching an sqlexception. When i try to put data into my database that is duplicate i want it to display an error message and so catch the exception however i am getting errors. The catch statement i am using is:Label lab1 = (Label)FormView1.FindControl("Label10");
try
{
EditSqlDataSource.Insert();Response.Redirect("Appointments.aspx");
}catch (Exception ex)
{lab1.Text = "Appointment Slot Already Taken: Booking Not Made.";
}
and the error that i am getting is:
Error 1 The type or namespace name 'SqlException' could not be found (are you missing a using directive or an assembly reference?)
Thanks in advance Mike
Hi,
Here is my stored procedure and when I'm trying to use it through my code, it throws this SqlException. As it shows @JulianDate is infact a parameter of my SP. What am I doin wrong here??
CREATE PROC CabsSchedule_Save
(
@JulianDatesmallint,
@SiteCodesmallint,
@YearMonthsmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvdvarchar(30),
@Remarksvarchar(50)
)
AS
DELETE FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode;
DECLARE @InsQuery NVARCHAR(2000)
DECLARE @ValInsQuery NVARCHAR(2000)
DECLARE @FullQuery NVARCHAR(4000)
SET @InsQuery = 'INSERT INTO CabsSchedule(JulianDate, SiteCode, YearMonth, CalendarDay, BillPeriod, WorkDay '
SET @ValInsQuery = 'VALUES (@JulianDate, @SiteCode, @YearMonth, @CalendarDay, @BillPeriod, @WorkDay'
IF NOT @CalDayBillRcvd IS NULL
BEGIN
SET @InsQuery = @InsQuery + ', CalDayBillRcvd, '
SET @ValInsQuery = @ValInsQuery + ', @CalDayBillRcvd, '
END
IF NOT @CalDayBillRcvd IS NULL
BEGIN
SET @InsQuery = @InsQuery + ', Remarks'
SET @ValInsQuery = @ValInsQuery + ', @Remarks'
END
BEGIN
SET @ValInsQuery = @ValInsQuery + ')'
SET @FullQuery = @InsQuery + @ValInsQuery
END
GO
Error Details
@JulianDate smallint, is not a parameter for procedure CabsSchedule_Save.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: @JulianDate smallint, is not a parameter for procedure CabsSchedule_Save
Any help Appreciated.
Thanks,
I am new to Sql Server 2005, I am using vb.net 2005 to manually connect to the database but I am getting the following error
Login failed for user 'BM-PC/B-Mustafa'
Can you please advise?
Thanks
I have extended a table adapter(the one generated with the VS wizard) for one of my tables so i could add an overload to the Fill method to return rows within a certain datetime range from a smalldatetime collumns in the same table
Code Snippet
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
[System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Fill, true)]
public int Fill(WrkDS.TransferenciasDataTable dataTable, DateTime Data1, DateTime Data2)
{
//Instanciar comando com filtro
string ComandoQuery = "SELECT * FROM Transferencias WHERE Data >= '" + Data1.ToString("yyyyMMdd") + "' and Data <= '" + Data2.ToString("yyyyMMdd") + "'";
SqlCommand ComandoComFiltro = new SqlCommand(ComandoQuery, this._connection);
this.Adapter.SelectCommand = ComandoComFiltro;
if ((this.ClearBeforeFill == true))
{
dataTable.Clear();
}
System.Windows.Forms.MessageBox.Show(ComandoComFiltro.CommandText);
int returnValue = this.Adapter.Fill(dataTable);
return returnValue;
}
as you can see the string "ComandoQuery" is in a valid sql format, in fact I executed the exact same query in SQL server management studio and it returned the rows i wanted but when i try it in my application it trows an SqlException "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
this is really making my head hurt, i cant seem to figure whats wrong, if someone has any clue please post here.
thanks.
Hi,
I get the following error when I try to run a stored procedure
"Cannot insert explicit value for identity column in table 'CurrentGroup' when IDENTITY_INSERT is set to OFF"
The procedure looks like so...
USE [AdminDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insert_group]
(
@cgpActivityGroupID nvarchar(30),
@cgpPreRegistredFrom nvarchar(30),
@cgpRegistredFrom nvarchar(30),
@cgpRegistredTo nvarchar(30),
@cgpMaximalNumber nvarchar(30),
@cgpStartDate nvarchar(40)
)
AS
INSERT INTO dbo.CurrentGroup(cgpID, cgpActivityGroupID, cgpPreRegistredFrom, cgpRegistredFrom, cgpRegistredTo, cgpMaximalNumber, cgpStartDate)
VALUES (@@identity, @cgpActivityGroupID, @cgpPreRegistredFrom, @cgpRegistredFrom, @cgpRegistredTo, @cgpMaximalNumber, @cgpStartDate)
Thanks in advance!