I have two SQL servers onr is staging (SQL2005) the other is production (SQL2000). I have a primary key with constraints on a table and I script out the Create for the primary key with constraints. I run the generated T-SQL script on the staging (SQL2005) and it works great. No probs so far. Now I cut and paste the same generate T-SQL script into my production (SQL2000) server and I get this error...
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Now this is the same exact cut/paste of the script that ran perfectly fine on my SQL2005 server. So I copied it agan from production (SQL2000) and pasted it into staging (SQL2005) and it works.
Here's the script...
USE [TEST]
GO
ALTER TABLE [dbo].[fovecComments] ADD CONSTRAINT [PK_fovecContacts] PRIMARY KEY CLUSTERED
([iUserId] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
"INSERT INTO tblEquip(buydate,Country,feature,pc,Provider,Serial,Status,Warranty,Year,Typeid ) VALUES ('12/12/2008','Viet Nam','Supper Power ',0,'IBM','ABCDEF','Out of warranty','12/12/2008',1965,5);"
I use Visual 2005 IDE and code in C# , with an MS Access 2003 Databse.
Above SQL command is gotten from Debug.
It works well when i paste into a query in MS Access 2003.
But in my project, it return an error : Syntax error in INSERT INTO statement when I use a try catch statement.
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
I am using c# and ASP.NET 2 and I am getting a very strange error. I have a field called CompanyID in SQL Server 2005, and it allows null values in it. When this CompanyID is not NULL, ie for example it is 202, then when i press the update button it allows me to update perfectly in the database. However if the CompanyID is NULL, and I try to update the companyID, to lets say 202, the application just crashes with the following message:- System.InvalidCastException: Object cannot be cast from DBNull to other types. I tried to set some breakpoints in the application to debug, however it does not even pass through these breakpoints! Any ideas on what the problem can be or how I can debug? Thanks for your help and time Johann
So we currently are running a SQL 7 Server, which hopefully we will be updating this year but untill then we're stuck. I've got a web app using ASP.NET 2.0 and on occasion I get this error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
I Some sql that it keeps throwing the following error
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is the sql
UPDATE dbo.temptable SET code = 'MM' FROM dbo.temptable T JOIN dbo.Item I WITH (NOLOCK) ON T.ProductID = P.ProductID JOIN dbo.cars C WITH (NOLOCK) ON I.ModelID = C.ModelID JOIN dbo.carmakes CM WITH (NOLOCK) ON CM.MakeID = C.MakeID JOIN dbo.CarDealerMakes CDM WITH (NOLOCK) ON CM.MakeID = CDM.MakeID WHERE T.ProductID NOT IN (
SELECT distinct A.ProductID FROM dbo.Action A WITH (NOLOCK) JOIN dbo.ActionPurchases AP WITH (NOLOCK) ON A.ActionID = AP.ActionID WHERE A.CustomerID = 2
UNION
SELECT distinct A.ProductID FROM dbo.Action A WITH (NOLOCK) JOIN dbo.ActionServices S WITH (NOLOCK) ON A.ActionID = S.ActionID WHERE 2 = A.ProductID)
That sql will run on windows 2000 but it will not run on windows 2003. I have the latest service pack.
Has anyone ever come across this before or have and suggestions ?
Hello I have one PC with SQL server 2000 and I am working on one database called TaxReg but today I found it gray and (suspect) beside it could you please tell what is the problem and how to fix it ?
SELECT TOP 9 id,vehicleref,capid,manufacturer,model,derivative,ch,'3' as term,'10000' as mileage,'orderby' as orderby,cvehicle_shortmodtext,source,studioimages FROM vwAllMatrixWithLombardAndShortModel WHERE locatcode =1 GROUP BY id,vehicleref,capid,manufacturer,model,derivative,ch,term,mileage,orderby,cvehicle_shortmodtext,source,studioimages
Msg 207, Level 16, State 1, Line 2 Invalid column name 'mileage'. Msg 207, Level 16, State 1, Line 2 Invalid column name 'orderby'.
In my package I am executing 8 'Execute package' tasks which calls 8 different packages. All these tasks are indepent so they are not connected to each other. In each package I have defined a variable at the start of each package there is a script task to assign the 'StartTime' system variable value to the user defined variable.
If I run the tasks individually, it is running without any problem. When I am running all the tasks in the parent package, very often the package fails and each time different different tasks are failing. But the error happens at the script task which is used to define the start time. The error message is 'Error: The script threw an exception: Object reference not set to an instance of an object.'. But inside the script task, I have only one statement which assigns the system start time value to the user variable. Also, in my log file, I am getting 2 entries for all the tasks in that failing child package, but I have not specified any looping in my parent package. One more peculiar thing is even though as per the log entry it ran twice, no single record is inserted/updated(????).
I am getting the following error when I try to debug a package that needs to Copy Columns from an AS400 database into SQL 2005:
[SQL Server Destination [1684]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
Has anybody seen this error and what do I need to do and check for?
I am getting this error on an almost daily basis. Using SQL 2005 fully patched.
I searched the web and couldn't fin anthing about it. has anyone here seen this problem before...
Disk space is fine btw... 35GB free
Message An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.
I have a table called subscribers with the following fields CustID (identity)email (text) fname (text)lname (text) - may be emptysubDate (smalldate)Confirmed (boolean) - initially will be emptyConDate (smalldate) - initially will be empty I have a strongly typed dataset that contains only the one table. It has the method GetDataByIsSubscribed SELECT emailFROM SubscribersWHERE (email = @email) And I have a function which calls it Public Function ValSubscribe(ByVal vEmailAddr As String, ByVal vFName As String, ByVal vLName As String) As String Dim SubscriptionAdapter As New EmailSubscriptionsTableAdapters.SubscribersTableAdapter Dim dF As Data.DataTable = SubscriptionAdapter.GetDataByIsSubscribed(vEmailAddr) If dF.Rows.Count > 0 Then ValSubscribe = "This email address is already subscribed!" Else SubscriptionAdapter.InsertSubscriber(vEmailAddr, vFName, vLName, Now) ValSubscribe = "success" End IfEnd Function This is an existing table that contains duplicate email addresses, but I don't want to add any more dups. You can see I have another method to add records, which works fine, though I've only tried to add email addresses that I know aren't in the table. If I run the above code which first checks to see if an email address is in the table, and I use an email address that is, I get the following error System.Data.ConstraintException was unhandled by user code Message="Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Source="System.Data" StackTrace: at System.Data.DataTable.EnableConstraints() at System.Data.DataTable.set_EnforceConstraints(Boolean value) at System.Data.DataTable.EndLoadData() at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at EmailSubscriptionsTableAdapters.SubscribersTableAdapter.GetDataByIsSubscribed(String email) in C:WINNTMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswap48b4654224232ecApp_Code.ihd33v1_.4.vb:line 872 at Validate.ValSubscribe(String vEmailAddr, String vFName, String vLName) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPApp_CodeValidate.vb:line 90 at Validate.vCheckPage(String vEmailAddr, Int32 vLevel, String vFName, String vLName) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPApp_CodeValidate.vb:line 28 at advertise.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPform-advertise.aspx.vb:line 32 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) I think I understand what this error is telling my, but I don't get why. All the fields except CustID allow nulls. There are no foreign-keys, and I don't see where any fild by custID requires unique entries. If I run the query in the designer, I don't have a problem. What am I missing? Diane
i m using sql server 2000. My problem is tht no matter what date i enter, sql server always displays it as 1900-1-1. This is happening on all columns which have been specified as datetime. Whts the problem & the solution to the problem?
I have a function that retrieves a data set from a passed SQL string. I'm getting this weird error.
Input string was not in a correct format
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.FormatException: Input string was not in a correct format.
Source Error:
Line 205: sSQL = "Select * From LedgerDetails Where LedgerID = " & _LedgerID.ToString() Line 206: msgbox(sSQL) Line 207: TransactionDetails = GetDataSet(sSQL) Line 208: Line 209: _TransactionsIndex = CShort(Val(GetDataValue("Select Count() From LedgerDetails Where LedgerID = " & CStr(_LedgerID)))) -1
Source File: C:Documents and SettingsOwnerMy DocumentsUniversityFall 2004DSSASP WorkAlgorithmTest.aspx Line: 207
Stack Trace:
[FormatException: Input string was not in a correct format.] Microsoft.VisualBasic.CompilerServices.DoubleType.Parse(String Value, NumberFormatInfo NumberFormat) +184 Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +96
[InvalidCastException: Cast from string "Select * From LedgerDetails Wher" to type 'Integer' is not valid.] Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +211
I have no idea why, but it seems to trying to convert the string into an integer. Both the argument and the parameter in the function are strings. I checked on the internet and the usual response is this is because the SQL is incomplete, but I have it showing me the compiled SQL string, and it is not imcomplete. I've tried hotwiring the query to match something I know will work, and I still get the same error. I've also tried compile the string using .ToString() on my number portion, storing the converted number into a string and only combining strings on the call, using another string variable as a temporary holder, using the String.Concat function, and even CStr. There is no way possible that this thing is an integer when the call is sent...
I've been building a simple test application and things have been working all day. Suddenly, about a half an hour ago - I started getting this error every time I attempt to go to the main web page: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I have no idea what changed... Can someone help me figure this out please? Thanks!~A~
I keep getting this conversion error when I run this query: If I comment from the second part of the where clause it goes away? I can't figure out how to fix this!
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'B' to data type int.
Select sE.SSN, sE.enroll_yyyyQ_Code, sE.Sched_Switch, sE.gdb_Switch, sE.Fee_Switch, sE.Rank_Code, sE.Class_Code, sE.Status_Code, sE.Prim_Coll_Code, sE.Secondary_Coll_Code, sE.Dept_Major_Code, sR.Client_ID, sR.Contact_Number, master.dbo.fn_InitCap(nM.last_name) Last_Name, master.dbo.fn_InitCap(nM.first_name) First_Name, master.dbo.fn_InitCap(nM.mid_name) Middle_Name, nM.sfx_name, eA.Published_eMail_Address /* Tables ................................................................ */ From FCoBDB.Student_Current.dbo.enrollment sE With ( noLock ) JOIN FCOBDB.Student_Current.dbo.Name nM With ( noLock ) ON sE.SSN = nM.SSN LEFT OUTER JOIN FCoBDB.Student_Current.dbo.eMail_Address eA With ( noLock ) ON sE.SSN = eA.SSN LEFT OUTER JOIN ADIS_Import_Support.dbo.Student_Records sR With ( noLock ) ON sE.SSN = sR.SSN /* Selection Clauses ...................................................... */ Where /* Undergraduate Business Students .................................. */ ( sE.prim_coll_code = 'BUS' and sE.dept_major_code <> '805' and sE.status_code = 1 and /* Good citizens */ sE.enroll_yyyyq_code = 20074 and /* current quarter only */ sR.Client_ID IS NULL ) /* Data not in Maximizer! */ or /* Executive Education Students ..................................... */ ( sE.prim_coll_code = 'GRD' and sE.dept_major_code = '296' and sE.status_code = 1 and /* Good citizens */ sE.enroll_yyyyq_code = 20074 and /* current quarter only */ sR.Client_ID IS NULL ) /* Data not in Maximizer */ /* Sort Options ........................................................... */ order By nM.last_name, nM.first_name ;
So, I'm trying to deserialize a .NET object inside a CLR procedure; I've used SGEN to make an "xmlserialization" version of the object library, and registered it and the original assembly in SQL Server and referenced them from my CLR Procedure project.
Now, I receive the object as an XML Document and I'm trying to use XMLSerializer to deserialize the XML back into my object. If I try to specify the XMLRoot when I declare the XMLSerializer and deploy it SQL gives me the (in)famous can't use dynamically created assemblies error.
I could really use any help/inspiration/assistance!!
Here's most of the code:
Dim myCaseType As CaseMessageType = CType(myRead.ReadContentAsInt(), CaseMessageType) SqlContext.Pipe.Send("Case Type=" & myCaseType.ToString()) Dim myXmlRoot As New XmlRootAttribute(CaseMessageElement) myXmlRoot.Namespace = CaseMessageSchema SqlContext.Pipe.Send("Getting the type of the message") Dim myType As System.Type = GetCaseMessageSystemType(myCaseType)
SqlContext.Pipe.Send("Cretating xml serilizer object"
) 'this "works" but it won't deserialize Dim xmlSerializer As New XmlSerializer(myType) 'this doesn't work, it gives me the error 'Dim xmlSerializer As New XmlSerializer(myType, myXmlRoot) SqlContext.Pipe.Send("Deserialize the message") myCase =
Big problem! Win XP SP2 IE7. All up to date My user profile gets a script error many times like: When trying to open certain web pages. When trying to print any web page. When trying to highlight for a copy paste on certain web pages. When trying to print an email in Eudora 7. This only happens for my user profile. My wifes user account operates perfectly. When I created a new user account the problem is the same as my corrupted profile. I have been thru all the Microsoft fixes for this issue as documented in http://support.microsoft.com/kb/308260 Further I have followed thise instructions for both user profiles. There must be a way to correct the corrupted settings fro my profile? I use Norton Internet Security which is up to date and the problem persists even when NIS is deactivated. The script error window that appears contains no data (blank) for all fields that normally have diagnostic info. Please help Thanks, Paul
I get this SQL-sever error in my application. I guess it has something to do with the declaration of namespaces? Does anyone know for sure? Thanks in advance!
The type 'System.Data.Selclient.SqlConnection' exists in both 'c:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Data.dll' and 'crogramMicrosoft Visual Studio 8Common7IDEPublicAssembliesSystem.Data.SqlClient.dll'
I have a script that creates a database and sets up everything required for merge replication. Merge replication is pull with a single parameterized row filter based on HOST_NAME() and joins on other tables. It works perfectly. I recently decided to drop all my identity columns and use rowguid's instead.
I modified my script by only changing the primary key column definitions, basicaly I went from "[Id] [int] NOT FOR REPLICATION IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED" to "[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED".
The script still works fine. Everything is executed succesfully and even the snapshot agent finishes it's job.
But the problem is, that when I want to create a partition I get the following error:
A value for the parameter @host_name was specified, but no articles in the publication use HOST_NAME() for parameterized filtering. (.Net SqlClient Data Provider). Microsoft Sql Server, Error: 20672
If I try to synchronize a subscription then the subscription is created succesfully, but I get an error while synchronizing stating: Merge agent was unable to determine if another subscription exists for the current partition.
And yes, I do have my filters set up 100% correctly.
The funny thing is, if I create the publication via Management Studio, then I don't get that error. But if I create the publication via Management Studio, immediately afterwards generate the script for it, delete the publication and create the publication from the generated script, I get the error again
From this I have concluded that no way can the problem be in my new script... Everything is dandy fine with the server as well, because the original scipt still works flawleslly.
Does anyone have any ideas what I could try to get things working? Has anyone even gotten merge replication set up via script, while database -> uses only rowguids, hostname parameterized filter and joins? Or should I just report this as a bug to Microsoft and go back to my original script, including the identity hell?
PS! I'm using Windows Server 2003 Standard x86 SP2, SQL Server 2005 Standard x86 SP2
I'm trying to copy a database from my web host to local Sql Server 2005 instance using the Copy Database wizard. I get this error:An exception occurred while executing a Transact-SQL statement or batch. Server user 'celestine' is not a valid user id database 'db2'.However, 'db2' isn't the database I was trying to copy! I am trying to copy db1, but this error is complaining about db2, which is in fact somebody else's database that I don't have a login for. Why am I getting an error about db2 when I'm not trying to do anything with db2???I can however export the tables from db1 just fine using Export. But then I have to go back and generate script to re-create the stored procs, views, etc., so it is rather tedious to do it that way.
Using SQL Server 2005, I hve encountered a very strange error which I cannot figure out. The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter. When I enter the field names bapk, jhpk, etc everything works fine. The records are returned sorted by the field name entered into the parameter. The first eight variables are field names and types When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error: Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28Conversion failed when converting character string to smalldatetime data type. I don't see where this is coming from at all. The problem is in the stored proc lines beginning with 'CASE @SortCol'If I take out the lines and just use OVER (Order By descr) as RowNum -- everything is fineIf I delete the line WHEN 'descr' THEN descr -- everything is fine, it just falls through to the default. What is the problem? Where is a conversion to smalldatetime coming from? I've been working on this one for a while, and cannot figure it out. It must be something simple. Thanks,Mike Thomas Below is the stored proc and a testing script TESTING SCRIPTUSE biz03GODECLARE @bapk int, @jhpk int, @transdate smalldatetime, @number smallint, @descr nvarchar(50), @debit decimal(9,2), @credit decimal(9,2), @cmonth tinyint, @StartRowIndex int, @MaxRows int, @AlphaChar varchar(1), @SortCol varchar(20) SET @MaxRows = 10SET @StartRowIndex = 0SET @SortCol = 'descr'SET @AlphaChar = ''EXEC [dbo].[CMSCheck2] @bapk, @jhpk, @transdate, @number, @descr, @debit, @credit, @cmonth, @StartRowIndex, @MaxRows, @AlphaChar, @SortCol STORED PROC ALTER PROCEDURE [dbo].[CMSCheck2] @bapk int, @jhpk int, @transdate smalldatetime, @number smallint, @descr nvarchar(50), @debit decimal(9,2), @credit decimal(9,2), @cmonth tinyint, @StartRowIndex int, @MaxRows int, @AlphaChar varchar(1) = null, @SortCol varchar(20) = nullASBEGINSET NOCOUNT ONDECLARE @lPaging bitIF @AlphaChar is null SET @lPaging = 0ELSE SET @lPaging = 1;WITH BankListTemp AS (SELECT bapk, jhpk, transdate, number, descr, debit, credit, cmonth, ROW_NUMBER() OVER (ORDER BY CASE @SortCol WHEN 'bapk' THEN bapk WHEN 'jhpk' THEN jhpk WHEN 'transdate' THEN transdate WHEN 'number' THEN number WHEN 'descr' THEN descr -- problem here -- WHEN 'debit' THEN debit WHEN 'credit' THEN credit WHEN 'cmonth' THEN cmonth ELSE bapk END) as RowNum FROM bank)SELECT TOP (@MaxRows) bapk, jhpk, transdate, number, descr, debit, credit, cmonth, RownumFROM ( SELECT BankListTemp.*, (SELECT COUNT(*) FROM BankListTemp) AS RecCount FROM BankListtemp) BankEND
Hello, I'm tyring a simple piece of code to conenct to my database: SqlConnection con = new SqlConnection("Data Source=localhost; Initial Catalog=OrderingSystem; Integrated Security=SSPI"); try { // Try to open the connection. con.Open(); label1.Text = "<b>Server Version:</b> " + con.ServerVersion; label1.Text += "<br /><b>Connection Is:</b> " + con.State.ToString(); } catch (Exception err) { // Handle an error by displaying the information. label1.Text = "Error reading the database. " + err.Message; } finally { // Either way, make sure the connection is properly closed. // Even if the connection wasn't opened successfully, // calling Close() won't cause an error. con.Close(); label1.Text += "<br /><b>Now Connection Is:</b> " + con.State.ToString(); } When I try this, I get an error:"Error reading the database. Cannot open database "OrderingSystem" requested by the login. The login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE'." The strange thing is, this code works perfectly If I use it in a Windows Forms application. Is it a problem with SQL server, IIS or my code?I'm using SQL Server 2005 (SP2) 32bit developer edition + IIS 6.0 + Visual Studio 2008 Pro all runnign on my Windows XP x64 PC. Thankyou
hi, i got the application(from my first post) working in win2003. but this application was made in webmatrix, i.e it had only 2 files, the .aspx file and the web.config file. so i decided to convert this code to a vs.net web application project. i did that and got it working on my xp box. i copied the app folder on 2003, made that folder as an app in iis 6 of win 2003 and tried to run it, and got the following error:-
Server Error in '/emrtd' Application.
Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'. 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: Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException: Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 System.Web.SessionState.SqlStateConnection..ctor(String sqlconnectionstring) +92
I was just restoring one database and creating another concurrently on the same server. The restore took longer than the create due to the respective sizes. After the create had finished (done in Query Analyzer) I went back to the restore to see how it was getting on (running in Enterprise Manager). There was an error (which I didn't record exactly - DOH!) along the lines of "could not obtain a lock on the model database, restore terminating abnormally." I guess the create had model locked while it was creating the default objects, but what was a *restore* doing needing the model database anyway?
We've been experiencing this strange error from our SQL server for about a month now and i've tried alot of things, visited alot of websites and manufactured a few possible solutions but nothing helps! Its very frustrating. This is the error i get when i try and expand the server tree:
A connection could not be established to [OurServerName]
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties by clicking on [OurServerName] node) and try again.
What really gets to me is that everything works after i restart the server. Then things will go fine for about a day or two and then the same thing again. This i causing alot of downtime for us. Can anyone please just give me a suggestion? :confused:
The error message is: Location: recbase.cpp:1371 Expression: m_nVars > 0 SPID: 52 Process ID: 732 ODBC: Message 0£¬severity 16£¬state 1 Communication link failure ODBC: Message 0£¬severity 16£¬state 1 Communication link failure
god , I have never seen error like that! what will be reason for the error??
Error: 0xC020901E at Load_tblDelayfact, Lookup_DL_CODE [184]: Row yielded no match during lookup.
Error: 0xC0209029 at Load_tblDelayfact, Lookup_DL_CODE [184]: The "component "Lookup_DL_CODE" (184)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (186)" specifies failure on error. An error occurred on the specified object of the specified component.
Although the Lookup table is filled in with the following SELECT ststement:
SELECT DISTINCT DL_CODE, DL_COMMENT FROM DL_Temp AS T1
WHERE NOT EXISTS(
SELECT * FROM DL_CODE AS T2 WHERE T1.DL_CODE = T2.DL_CODE AND T1.DL_COMMENT = T2.DL_SENT_COMMENT
)
So there is no way that there is a record in DL_Temp (The data source) that does not exist in DL_CODE( the lookup table). Indded, I did serveral queries and tests to check that no such data exist and I found that no such record exists.
Please help me and tell me what can be the reason for this error. I used the same package on the same data yesterday and every thing went fine. Is that a bug that any of you faced before.
Hey guys, I'm getting this really strange incorrect sytax error with a program I'm writing in VB.NET for an ASP.NET website.
Here is my code:
Dim mySelectQuery2 As String = "SELECT MAX(TicketID) FROM tblTicket"
Dim myInsertQuery As String = "INSERT INTO tblCaller (TicketID)"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery2, myConnection)
Dim myCommand2 As New Data.SqlClient.SqlCommand(myInsertQuery, myConnection)
Dim retvalue As Integer
myConnection.Open()
retvalue = myCommand2.ExecuteNonQuery()
Console.WriteLine(retvalue)
Dim myReader2 As Data.SqlClient.SqlDataReader
myReader2 = myCommand.ExecuteReader()
myReader2.Close()
myConnection.Close()
Now, the actual error I'm getting is "Incorrect syntax near ')'." that is refering to the link of code "retvalue = myCommand2.ExecuteNonQuery()". I've played around with the code a little bit but I just can't seem to make that error go away.
My only guess is that in the "myInsertQuery" I should have "VALUE (...", but I'm not sure what to put there.
If anyone could make any suggestions on what I might want to try, or if you think that you need more information please let me know.
Using SQL Server 2005, I have encountered a very strange error which I cannot figure out. The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter. When I enter the field names bapk, jhpk, etc everything works fine. The records are returned sorted by the field name entered into the parameter. The first eight variables are field names and types
When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error:
Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28 Conversion failed when converting character string to smalldatetime data type.
I don't see where this is coming from at all. The @SortCol variable is always a string. It contains the name of a field. The problem is in the stored proc lines beginning with 'CASE @SortCol' .If I take out the lines and just use OVER (Order By descr) as RowNum -- everything is fine. If I delete the line WHEN 'descr' THEN descr -- everything is fine, it just falls through to the default.
What is the problem? Where is a conversion to smalldatetime coming from? I've been working on this one for a while, and cannot figure it out. It must be something simple.
DECLARE @lPaging bit IF @AlphaChar is null SET @lPaging = 0 ELSE SET @lPaging = 1;
WITH BankListTemp AS (SELECT bapk, jhpk, transdate, number, descr, debit, credit, cmonth, ROW_NUMBER() OVER (ORDER BY CASE @SortCol WHEN 'bapk' THEN bapk WHEN 'jhpk' THEN jhpk WHEN 'transdate' THEN transdate WHEN 'number' THEN number WHEN 'descr' THEN descr -- problem here -- WHEN 'debit' THEN debit WHEN 'credit' THEN credit WHEN 'cmonth' THEN cmonth ELSE bapk END) as RowNum FROM bank)
SELECT TOP (@MaxRows) bapk, jhpk, transdate, number, descr, debit, credit, cmonth, Rownum FROM ( SELECT BankListTemp.*, (SELECT COUNT(*) FROM BankListTemp) AS RecCount FROM BankListtemp) Bank
