Strange T-SQL Error On A Command...
Oct 19, 2007
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
Any ideas?
View 1 Replies
ADVERTISEMENT
Jun 4, 2007
"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.
Where is my wrong ? Please help me.Thanks!
View 3 Replies
View Related
Nov 4, 2006
Hi All,
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.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Sep 19, 2006
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
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
View 2 Replies
View Related
Aug 3, 2006
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
View 6 Replies
View Related
May 13, 2008
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()
View 2 Replies
View Related
Mar 25, 2004
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 ?
View 2 Replies
View Related
Nov 19, 2006
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 ?
View 1 Replies
View Related
May 19, 2008
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'.
Can anyone help?
Thanks
View 2 Replies
View Related
Aug 3, 2006
Hi,
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(????).
Anybody have any idea of this problem?
Thanks.
View 1 Replies
View Related
Apr 25, 2006
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?
Thanks for the information.
View 6 Replies
View Related
Oct 2, 2007
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.
View 10 Replies
View Related
Oct 30, 2006
Hello,
I am running the code below that simply adds a row with two values to a Table. When it is run, it gives me the following error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertCurrency':
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Sales.Currency'. Cannot insert duplicate key in object 'dbo.SalesCurrency'.
This seems to indicate I am adding a duplicate key, however I have confirmed several times that I am not adding a duplicate key.
Here is the code. Thanks for any suggestions about why this error is occuring.
<SqlProcedure()> _
Public Shared Sub InsertCurrency(ByVal currencyCode As SqlString, ByVal name As SqlString)
Using conn As New SqlConnection("context connection=true")
Dim InsertCurrencyCommand As New SqlCommand()
Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)
currencyCodeParam.Value = currencyCode
nameParam.Value = name
InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
InsertCurrencyCommand.Parameters.Add(nameParam)
InsertCurrencyCommand.CommandText = _
"INSERT SalesCurrency (CurrencyCode, Name) VALUES(@CurrencyCode, @Name)"
InsertCurrencyCommand.Connection = conn
'
conn.Open()
InsertCurrencyCommand.ExecuteNonQuery()
conn.Close()
End Using
End Sub
View 2 Replies
View Related
Feb 2, 2007
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
View 11 Replies
View Related
Apr 13, 2007
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?
View 3 Replies
View Related
Nov 16, 2004
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...
Any ideas?
Shawn
View 1 Replies
View Related
May 11, 2006
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~
View 3 Replies
View Related
Jan 2, 2008
Hi,
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 ;
View 4 Replies
View Related
Jun 25, 2007
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 =
DirectCast(xmlSerializer.Deserialize(myRead), CaseMessage)
View 7 Replies
View Related
Dec 2, 2007
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
View 3 Replies
View Related
Feb 13, 2008
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'
View 11 Replies
View Related
Aug 19, 2007
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
View 7 Replies
View Related
Sep 25, 2006
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.
View 4 Replies
View Related
Apr 21, 2008
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
View 6 Replies
View Related
May 11, 2008
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
View 2 Replies
View Related
Mar 30, 2005
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
[HttpException (0x80004005): Unable to connect to SQL Server session database.]
System.Web.SessionState.SqlStateConnection..ctor(String sqlconnectionstring) +191
System.Web.SessionState.SqlStateClientManager.GetConnection(Boolean& usePooling) +98
System.Web.SessionState.SqlStateClientManager.SetAsyncWorker(String id, SessionStateItem item, Byte[] buf, Int32 length, Boolean inStorage) +44
System.Web.SessionState.SqlStateClientManager.System.Web.SessionState.IStateClientManager.Set(String id, SessionStateItem item, Boolean inStorage) +147
System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +465
System.Web.SyncEventExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute() +60
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +87
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
please help on this, why is this happening?
View 2 Replies
View Related
Jul 9, 2001
Hi all.
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?
Any comments would be interesting.
Andy Tynan
View 1 Replies
View Related
Oct 10, 2005
Hi guys,
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:
View 3 Replies
View Related
Apr 6, 2007
This is part of the procedure that I write
DECLARE @RES INT
select @RES = (select * from usp_CheckSaleDocumentDate('001','0011','2006-03-01', 'S_INVOICE+', 1))
when I run the procedure I get the following Error:
Invalid object name 'usp_CheckSaleDocumentDate'.
there is a stored procedure usp_CheckSaleDocumentDate and the execution of the CREATE PROCEDURE show no errors
Any ideas?
View 12 Replies
View Related
Mar 1, 2004
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??
View 3 Replies
View Related
Aug 18, 2006
Hi,
I am getting this error in a Lookup component:
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.
Thanks,
Aref
View 3 Replies
View Related
Jul 12, 2007
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.
Thanks,
aqzman
View 5 Replies
View Related
Apr 22, 2008
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.
Thanks,
Mike Thomas
Below is the stored proc and a testing script
TESTING SCRIPTUSE biz03
GO
DECLARE
@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 = 10
SET @StartRowIndex = 0
SET @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) = null
AS
BEGIN
SET NOCOUNT ON
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
END
View 7 Replies
View Related
