Error from sys.dm_db_index_physical_stats "No exceptions should be raised by this code&

I'm setting up a routine to do some reindexing. To get the info I need I'm trying to use the new function. The DB I think is causing the problem does pass DBCC CHECKDB. I'm not sure why I'm getting this but can not find anyone else that is getting this same error.

When I run this....

SELECT *

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'detailed')

I get this....

Location: qxcntxt.cpp:954

Expression: !"No exceptions should be raised by this code"

SPID: 236

Process ID: 1060

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

If I run only for the DB I think is causing the problem I get this...

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Well, since this created a dump I guess I'll be contacting product support services. Just thought I'd check online first.|||

I think when system code claims: Expression: !"No exceptions should be raised by this code" that would be the best thing...

Can you post here and tell us what the issue was?

|||Unfortunately, this is an internal server error due to unknown cause, and unlikely to get resolved with standard mini dump only.|||

This is was found to be a bug in SP1 of SQL 2005. The work around is to use limited instead of detailed. I don't get all the data I was wanting to collect but this will work ok until SP2 is released with the fix. We had three DBs on our 2005 server that would generate this error.

|||Thanks for letting us know. It is always interesting when someone else is having a similar problem if a resolution (or lack thereof :) is noted!

Error execute SSIS package

What this?

An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(error not found). Make sure you are accessing a local server via Windows security.".

Just a guess but...

Are you using SQL Server Destination by any chance?

If so, are you executing the package on the same server as your destination?

-Jamie

sql

Error during installation process

Microsoft SQL Server 2005 Setup
-

The setup failed to get IID_IIMSAdminBase object. The error code is -2147467262.

Hi there,

I have checked every possible site for this error but unable to find any solution. I received the above alert message during the installation of SQL2k5, Reporting Services. I am using window xp with sp2.

When click OK to alert box then it rollback all installed components of Reporting Services.

Please help.

Cheers,

Zafar.

I believe you have a typo on the error message you have listed. I believe it actually states that it can not find the IIS Imsadminbase. This is the metabase (http://msdn2.microsoft.com/en-us/library/ms525941.aspx). I would remove IIS and then add it back and try the SSRS installation again.

Larry

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli
sql

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error deploying a CLR Stored Procedure that uses a web service

Ok, first some background.

I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.

This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.

At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:

<code>

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)

at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)

at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)

at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)

at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, S

</code>

Anyone have any ideas?

Thanks!

Dave Borneman

Solution Architect,

anyWare Mobile Solutions.

OK, so when you are using Web services the .NET framework will dynamically generate an assembly based on your proxy code. Dynamic assembly generation is not allowed inside SQL Server (SQLCLR).

What you need to do is to pre-generate the proxy assembly by using the sgen tool and deploy that generated assembly into the database.

Look at this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74480&SiteID=1 and specifically the 6:th message in that thread how to achieve the pre-generation.

Niels

error converting datatypes

Hello,

Firstly, i need to work out why I cannot change my datatypes(please see query)

Code Snippet

SELECT * FROM (

SELECT top 10

ref,

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM (

SELECT 1 seq,

'ref' ref,

'RecordDate' RecordDate,

'TransactionID' TransactionID,

'TransactionStatus' TransactionStatus,

'StatusChangedTimeStamp' StatusChangedTimeStamp,

'PartyTransactionStatus' PartyTransactionStatus,

'BadDeliveryReason' BadDeliveryReason,

'TradingDaysRef' TradingDaysRef

UNION ALL

SELECT 2 seq,

cast(ref as bigint),

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM dbo.ParticipantTradeStatusChange

) x

order by seq, RecordDate

) y

The error returned is:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

The reason for me doing this, is exporting both column names & data to a xls file.

Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do

SELECT top 10 * FROM (

SELECT *

It falls over and dies

Help much appreciated

thanks,

i

The data -- literals -- that you have above your unions are not implicitly compatible with what is below the union. Look at this example:

Code Snippet

select 'Header'
union all
select 2

/*
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Header' to a column of data type int.
*/

I am basically getting the same error. You may need to explicitly declare the datatype of the lower part of the union to have them go as varchar -- maybe like:

Code Snippet

select 'Header' as Data
union all
select cast (2 as varchar)

/*
Data
Header
2
*/

|||

i've done a dirty workaround..

simply used cast(columnname as varchar(4000))

did the trick and i have my bcp file with headings

all todo now is make it run faster...

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP========
CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP=========
szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========
> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========
> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================
>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP========
CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP=========
szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========
> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========
> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================
>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP======== CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP========= szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Error connecting to remote SQL 2000 Server

I'm experiencing a problem connecting to a SQL 2000 server through my ASP code. My connection string is as follows:

<addname="TheConnectionString"connectionString="driver={Sql Server};provider=MSDASQL;server=10.0.1.42;database=dbname;uid=*********;pwd=*********"providerName="System.Data.Odbc" />

The problem doesn't occur when I run my ASP code from my workstation using VS.NET's builtin webserver. It makes the connections and executes the CRUD commands successfully. However, when I publish my site to the webserver (which resides on 10.0.1.16) it fails out with the following error:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

I've verified that the webserver can talk to the DB server by connecting to the remote DB server through SQL Enterprise Manager running locally on the webserver. If I try to do this with a DSN I get the same results. I get the same error from any other webserver on the internal network. The difference between my workstation and the internal network is that I'm using a VPN to connect to our internal network while the webservers are physically connected to it. Firewalling isn't the issue in this case because the webservers and DB server are on a trusted network. I've seen other ways of connecting to the DB server including using Named Pipes (which I would rather not do because I don't want to setup a named pipe on the production db server).

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know. I've been racking my brains trying to get this to work outside of the devel env.

Try with this:

<add name="TheConnectionString" connectionString="Server=10.0.1.42;Database=dbname;User ID=*********;Password=*********;"/>

|||

johnladda:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

First, this error is a generic one so your actual problem may range from incorrect credentials to a network problem.

Second, I'm not too sure why you've used ODBC library to connect to SQL while you've the SQL library.

johnladda:

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know.

Third, you can use "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" to connect to SQL Server. You can referwww.connectionstrings.com . This site provides a number of connection string examples for almost every database.

Hope this will help.

|||

This site is fantastic. It's exactly what I needed. I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

|||

johnladda:

I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

No, you've mistaken the Data Source property of the connection string. It is no way similar to any server control. In fact, you can user Data Source or Server property in your connection string. Below are some of the alternatives you can use in your connection string.

Data Source = Server
Initial Catalog = Database
User Id = uId
Password = pwd

The default port is 1433 for SQL Server TCP/IP connection. You can specify any other port in your connection string as below:

"Data Source=xxx,portNumber;Initial Catalog=xxx;User ID=xxx;Password=xxx". That is, after either the IP or the name of the server put a comma and then provide the port number.

Error Codes?

My data flow component is throwing an error and the only help I get is the following:

error code: -1071607694

error column: 257

What in the world does this mean? Can it get more cryptic than this?

Search is your friend.

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209072.html

You have a conversion issue.

Error code: 0x80004005

I created a package that refreshes tables on one SQL Server to another SQL Server. First, I use an ExecuteSQL task to truncate the tables. Then I use a Data Flow task to copy the tables from one server to the other. Finally I update a log table. It was working fine with four tables. I added another table to the refresh and now I get these error messages:

[Source - RbcAcctSegment [1096]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".

[Source - RbcAcctSegment [1096]] Error: Opening a rowset for "[dbo].[RbcAcctSegment]" failed. Check that the object exists in the database.

[DTS.Pipeline] Error: component "Source - RbcAcctSegment" (1096) failed the pre-execute phase and returned error code 0xC02020E8.

RbcAcctSegment is the new table that I added. The errors occur in the Data Flow task. We have tried running this on a different workstation with the same results.

What would cause this?

Fred

Try changing the connection propery 'Retain Same Connection' to FALSE.|||The 'Retain Same Connection' property is already set to FALSE for both Source and Destination Connection Managers.|||

I believe I found the problem.

I made a few changes in the Data Flow task. In the Properties of the Destination components I changed the OpenRowset property to include the database name beside the table name. Not all the Destination components had the database name.

Before it was [dbo].[TableName] and I changed it to [DatabaseName].[dbo].[TableName].

Fred

|||

It wasn't the problem. It worked one time.

Is there a limit on how many tables you can copy in one DataFlow task?

Fred

|||Now that you mention, I do remember we did have a similar error when we had around 20 in a data flow and then we moved some of them out to a new data flow and it worked fine. I had to put a precedence between the two.

Error code: 0x80004005

I created a package that refreshes tables on one SQL Server to another SQL Server. First, I use an ExecuteSQL task to truncate the tables. Then I use a Data Flow task to copy the tables from one server to the other. Finally I update a log table. It was working fine with four tables. I added another table to the refresh and now I get these error messages:

[Source - RbcAcctSegment [1096]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".

[Source - RbcAcctSegment [1096]] Error: Opening a rowset for "[dbo].[RbcAcctSegment]" failed. Check that the object exists in the database.

[DTS.Pipeline] Error: component "Source - RbcAcctSegment" (1096) failed the pre-execute phase and returned error code 0xC02020E8.

RbcAcctSegment is the new table that I added. The errors occur in the Data Flow task. We have tried running this on a different workstation with the same results.

What would cause this?

Fred

Try changing the connection propery 'Retain Same Connection' to FALSE.|||The 'Retain Same Connection' property is already set to FALSE for both Source and Destination Connection Managers.|||

I believe I found the problem.

I made a few changes in the Data Flow task. In the Properties of the Destination components I changed the OpenRowset property to include the database name beside the table name. Not all the Destination components had the database name.

Before it was [dbo].[TableName] and I changed it to [DatabaseName].[dbo].[TableName].

Fred

|||

It wasn't the problem. It worked one time.

Is there a limit on how many tables you can copy in one DataFlow task?

Fred

|||Now that you mention, I do remember we did have a similar error when we had around 20 in a data flow and then we moved some of them out to a new data flow and it worked fine. I had to put a precedence between the two.

error code sql server

Good morning
MOM server 2005 prompt this message during the installation
Failed to setup database security.
Error code: -2147217900 (user, group, or role 'SC DW DTS'
already exist in the current database)
what can i do in this case
http://groups.google.co.uk/group/mic...hread/thread/9
fc353baf7bde8b8/45c77c865aeb54a8?lnk=st&q=user%2C+group%2C+or+role +'SC+DW+DT
S'+already+exists&rnum=1&hl=en#45c77c865aeb54a8
"Djeff" <e.djeff@.free.fr> wrote in message
news:mn.b33c7d5c80e5082d.46131@.free.fr...
> Good morning
> MOM server 2005 prompt this message during the installation
> Failed to setup database security.
> Error code: -2147217900 (user, group, or role 'SC DW DTS'
> already exist in the current database)
> what can i do in this case
>

error code sql server

Good morning
MOM server 2005 prompt this message during the installation
Failed to setup database security.
Error code: -2147217900 (user, group, or role 'SC DW DTS'
already exist in the current database)
what can i do in this casehttp://groups.google.co.uk/group/mi...thread/thread/9
fc353baf7bde8b8/45c77c865aeb54a8?lnk=st&q=user%2C+group%2C+or+role+'SC+DW+DT
S'+already+exists&rnum=1&hl=en#45c77c865aeb54a8
"Djeff" <e.djeff@.free.fr> wrote in message
news:mn.b33c7d5c80e5082d.46131@.free.fr...
> Good morning
> MOM server 2005 prompt this message during the installation
> Failed to setup database security.
> Error code: -2147217900 (user, group, or role 'SC DW DTS'
> already exist in the current database)
> what can i do in this case
>

error code sql server

Good morning
MOM server 2005 prompt this message during the installation
Failed to setup database security.
Error code: -2147217900 (user, group, or role 'SC DW DTS'
already exist in the current database)
what can i do in this casehttp://groups.google.co.uk/group/microsoft.public.mom/browse_thread/thread/9
fc353baf7bde8b8/45c77c865aeb54a8?lnk=st&q=user%2C+group%2C+or+role+'SC+DW+DT
S'+already+exists&rnum=1&hl=en#45c77c865aeb54a8
"Djeff" <e.djeff@.free.fr> wrote in message
news:mn.b33c7d5c80e5082d.46131@.free.fr...
> Good morning
> MOM server 2005 prompt this message during the installation
> Failed to setup database security.
> Error code: -2147217900 (user, group, or role 'SC DW DTS'
> already exist in the current database)
> what can i do in this case
>

Error Code of 3238395904 and 3240034316

I get the following error for a cube that used to process just fine.

Executed as user: PREMIER_AD\SQLExec. ...ts xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Unspecified error." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /><Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Vw PASBSCD1291 Fact', Name of 'Drillthrough Data' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /><Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Reason' attribute of the 'Drillthrough Data' dimension from the 'Pas' database was being processed." Source="Microsoft SQL Server 200... The step succeeded.

I have no idea what Error Codes 3238395904 and 3240034316 are.

Looks like error belongs to the OLEDB provider you are using.

The error message reported by OLEDB provider is just "Unspecified error"

Looks like something went wrong with your relational database connection. Test your conneciton. See if you can process any other objects based on the same data source.

Hope that helps.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks, Edward.

The problem seems to have resolved itself. I believe the connectivity issues might be related to capacity problems were were having with our relational database late last week.

Dan