SQL Alert: error

Showing posts with label error. Show all posts

Sunday, March 25, 2012

accessing database problem

Hello friends when I am working in VWD and accessing sql data this error came?

I am using asp.net visual web developer edition and sql server 2005 expressedition.

plz check it out and help me.

The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

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: The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

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 (0x80131904): The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2406
 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +223
 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +371
 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +184
 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501
 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +510
 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
 System.Data.SqlClient.SqlConnection.Open() +159
 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +118
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +82
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1653
 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +82
 System.Web.UI.WebControls.ListControl.PerformSelect() +18
 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
 System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
 System.Web.UI.Control.PreRenderRecursiveInternal() +88
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5684

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

Hi radhekrishna,

The error message indicates that either the database file you are trying to attached has already been attached in sql express or there is data corruption in your database file(.mdf file) , thus the db file could not be attached. I would suggest you connect to your sql express server through management studio to make a verification (Note here that since Sql Express use customer instance, in management studio, you must login using the same account as the one you used in your application). If that db file has been attached, detach it and take another try.

If there is no such file attached already in your sql express, I would suggest you first attach that mdf file to your database. If you still get this error, it means that the mdf file is data corrupted (information in ldf file and mdf file does not match) and cannot be used anymore--You need to restore it then.

Hope my suggestion helps

Thursday, March 22, 2012

Accessing cubes parallely from 2 clients

Hello,

When 2 logins try to access a cube(in MSAS 2000), we are getting the following error message,

"This database is locked. User (....) on computer (....) has locked (....). Description : Cube edotir has locked cube (....) for writting."

Is there anyway we can access a cube simultaneously from 2 client machines (read only type of access)?

Thanks in advance....

Revin

Hi Revin,

Is the Analysis Manager open and modifying the cube structure at the same time. If so, close the Analysis Manager and try again. It will place a lock on the repository file and will not allow other connections.

If this doesn't work then stop and start the Analysis Server to free up any locks that are hanging around.

Hope this helps,

David

|||

Thanks David!!!

Yes the analysis service is open at 2 clients.

What i would like to know is that, is there any way i can access (read only type) the cube while another user is editing the cube in devlopment environment?

Thanks,

Revin

accessing asp.net app on client

I keep getting this "the request failed with http status 401: Unauthorized." error when i access the web app from a client machine.
my configuration:
client - client1
asp.net app - dev1
rs server/db - dev2
sql db - dev3
i'm using nt authentication
the following vb.net code is used for creditentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
and yes i do have impersonate = true
however, i do have aIf you impersonate client in you Web App you are hitting the limitation of
NT authentications. NTLM impersonation only survives one machine hop.
client->dev1 is OK, however dev1->dev2 is not possible after that. You need
to enable Kerberos delegations for this.
If you are not impersonating client in your web app, you need to run your
web app under account, known to machine dev2, such as domain account and
give that account rights to report server.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"mg" <mg@.discussions.microsoft.com> wrote in message
news:6B13373C-36E8-409A-BE6A-1FE8EBF6F31D@.microsoft.com...
> I keep getting this "the request failed with http status 401:
Unauthorized." error when i access the web app from a client machine.
> my configuration:
> client - client1
> asp.net app - dev1
> rs server/db - dev2
> sql db - dev3
> i'm using nt authentication
> the following vb.net code is used for creditentials
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> and yes i do have impersonate = true
> however, i do have a
>

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Tuesday, March 20, 2012

Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Accessing #temp table in a proc as a user with minimal rights.

I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:

> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>

Monday, March 19, 2012

access violation on sql server 7

an error has been returned by a long query shown below
SqlDumpExceptionHandler: Process 7 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.
we are running on sqlserver 7 sp4
if one field is removed the query will run
also if the top 1 is removed it will work
the script will also work on sql server 2000
the query is shown below:-
SELECT TOP 1 ISNULL(CONVERT(CHAR,SEC_ID),'0') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0039),PORTIA_DESCRIPTION_1), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0039),PORTIA_DESCRIPTION_2), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY_TYPE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_CASH_BALANCE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0012),PORTIA_CUSIP_ISIN), ' ') + '\17'
+ ISNULL(CONVERT(CHAR(0015),PORTIA_PRICE_SYMBOL), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_STATE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_COUNTRY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_EXCHANGE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_CURRENCY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_MOODY_RATING), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_SP_RATING), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_OTHER_RATING), ' ')
+ '\17' + ISNULL(CONVERT(CHAR,PORTIA_COUPON_RATE),'0')
+ '\17' +
ISNULL(CONVERT(CHAR,PORTIA_MATURITY_DATE,120),' ') + '\17'
+ ISNULL(CONVERT(CHAR,PORTIA_MATURITY_PRICE),'0') + '\17'
+
ISNULL(CONVERT(CHAR,PORTIA_ISSUE_PRICE),'0') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_TAX_TYPE), ' ') + '\17' +
ISNULL(CONVERT(CHAR,PORTIA_DATED_DATE,120),' ') + '\17' +
ISNULL(CONVERT(CHAR,PORTIA_ODD_FIRST_CPN,120),' ') + '\17'
+
ISNULL(CONVERT(CHAR,PORTIA_ODD_LAST_CPN,120),' ') + '\17'
+ ISNULL(CONVERT(CHAR(0008),PORTIA_POOL_NUMBER), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SEC_IDENTIFIER), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_DST_PMT_FREQ), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PATH), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PRICING_SOURCE), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0040),PORTIA_DTC_ELIGIBLE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0040),PORTIA_INTL_TAXSTATUS), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0040),PORTIA_INTL_INCOMETYPE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR,PORTIA_PAYMENT_DELAY),'0')
+ '\17'
FROM PORTIA_BLOOMBERG_SECURITIES
the create script for the table is shown below :-
it may look a bit odd but its an execute script to deal
with different databases
PRINT 'EXECUTING: Portia_bloomberg_SECURITIES.sql'
GO
if exists (select * from dbo.sysobjects where id = Object_id('dbo.PORTIA_BLOOMBERG_SECURITIES') and type in
('U','S'))
begin
drop table dbo.PORTIA_BLOOMBERG_SECURITIES
end
go
DECLARE @.LockClause VARCHAR(14)
IF CHARINDEX('Microsoft', @.@.VERSION) = 0 -- Sybase
SELECT @.LockClause = ' LOCK DATAROWS'
ELSE -- SQL Server
SELECT @.LockClause = ''
EXECUTE( 'CREATE TABLE dbo.PORTIA_BLOOMBERG_SECURITIES ('
+ 'SEC_ID NUMERIC(8,0) IDENTITY NOT NULL,'
+ 'RECEIVE_DATE DATETIME default getdate() NOT NULL,'
+ 'SEC_IDENTIFIER NVARCHAR(12) NOT NULL,'
+ 'SEC_IDENTIFIER_FLAG NVARCHAR(2) NOT NULL,'
+ 'SEC_LAST_UPDATE_DATE DATETIME NULL,'
+ 'SEC_TICKER NVARCHAR(8) NULL,'
+ 'SEC_COUPON_FI FLOAT NULL,'
+ 'SEC_COUPON FLOAT NULL,'
+ 'SEC_MATURITY DATETIME NULL,'
+ 'SEC_ISSUER NVARCHAR(80) NULL,'
+ 'SEC_ISSUE_AMOUNT NUMERIC(20,2) NULL,'
+ 'SEC_OUTSTANDING_AMOUNT FLOAT NULL,'
+ 'SEC_COUNTRY_CODE FLOAT NULL,'
+ 'SEC_CURRENCY_CODE FLOAT NULL,'
+ 'SEC_ISO_CURRENCY VARCHAR(3),'
+ 'SEC_ISSUE_PRICE FLOAT NULL,'
+ 'SEC_DATED_DATE DATETIME NULL,'
+ 'SEC_FIRST_COUPON_DATE DATETIME NULL,'
+ 'SEC_PENULTIMATE_COUPON_DATE DATETIME NULL,'
+ 'SEC_COUPON_FREQUENCY NUMERIC(9, 2) NULL,'
+ 'SEC_NEXT_REFIX_DATE DATETIME NULL,'
+ 'SEC_NEXT_COUPON_DATE DATETIME NULL,'
+ 'SEC_DAY_TYPE FLOAT NULL,'
+ 'SEC_SP_RATING NVARCHAR(8) NULL,'
+ 'SEC_MOODY_RATING NVARCHAR(8) NULL,'
+ 'SEC_FITCH_RATING NVARCHAR(8) NULL,'
+ 'SEC_DUFF_AND_PHELPS NVARCHAR(8) NULL,'
+ 'SEC_BB_COMPOSITE_RATING NVARCHAR(8) NULL,'
+ 'SEC_PRODUCT_GROUP FLOAT NULL,'
+ 'SEC_INDUSTRY_TYPE FLOAT NULL,'
+ 'SEC_CALCULATION_TYPE FLOAT NULL,'
+ 'SEC_PAYMENT_FREQUENCY FLOAT NULL,'
+ 'SEC_INTEREST_ACCRUAL_DATE DATETIME NULL,'
+ 'SEC_PRPL_FLAG NVARCHAR(1) NULL,'
+ 'SEC_FLOATER_FLAG NVARCHAR(1) NULL,'
+ 'SEC_BLOOMBERG_SECURITY NVARCHAR(12) NULL,'
+ 'SEC_POOL_NUMBER NVARCHAR(8) NULL,'
+ 'SEC_TRANCHE NVARCHAR(4) NULL,'
+ 'SEC_FACTOR FLOAT NULL,'
+ 'SEC_FACTOR_DATE DATETIME NULL,'
+ 'SEC_PAYMENT_DELAY FLOAT NULL,'
+ 'SEC_COLLATERAL NVARCHAR(8) NULL,'
+ 'SEC_CAP FLOAT NULL,'
+ 'SEC_FLOOR FLOAT NULL,'
+ 'SEC_WAC_CURRENT FLOAT NULL,'
+ 'SEC_WAC_ORIGINAL FLOAT NULL,'
+ 'SEC_WAM_REMAINING_MONTHS FLOAT NULL,'
+ 'SEC_WAM_ORIGINAL_MONTHS FLOAT NULL,'
+ 'SEC_WAL_MONTHS FLOAT NULL,'
+ 'SEC_PAC_LOWER_COLLAR FLOAT NULL,'
+ 'SEC_PAC_UPPER_COLLAR FLOAT NULL,'
+ 'SEC_PRE_PAYMENT_TYPE FLOAT NULL,'
+ 'SEC_PRE_PAYMENT_SPEED NUMERIC(8,3) NULL,'
+ 'SEC_PRIOR_MONTH_FACTOR FLOAT NULL,'
+ 'SEC_PRIOR_FACTOR_DATE DATETIME NULL,'
+ 'SEC_PAYMENT_ACCRUAL FLOAT NULL,'
+ 'SEC_NOTIONAL_PRINCIPLE_FLAG NVARCHAR(1) NULL,'
+ 'SEC_MORTGAGE_TYPE NVARCHAR(14) NULL,'
+ 'SEC_SERIES NVARCHAR(4) NULL,'
+ 'SEC_NEXT_CALL_DATE DATETIME NULL,'
+ 'SEC_NEXT_CALL_PRICE FLOAT NULL,'
+ 'SEC_PAR_CALL_DATE DATETIME NULL,'
+ 'SEC_NEXT_PUT_DATE DATETIME NULL,'
+ 'SEC_NEXT_PUT_PRICE FLOAT NULL,'
+ 'SEC_PAR_PUT_DATE DATETIME NULL,'
+ 'SEC_WORKOUT_DATE DATETIME NULL,'
+ 'SEC_WORKOUT_PRICE FLOAT NULL,'
+ 'SEC_STEP_UP_COUPON FLOAT NULL,'
+ 'SEC_STEP_UP_DATE DATETIME NULL,'
+ 'SEC_TREASURY_INDEX_FACTOR FLOAT NULL,'
+ 'SEC_TAX_STATUS NVARCHAR(1) NULL,'
+ 'SEC_CREDIT_ENHANCEMENTS NVARCHAR(28) NULL,'
+ 'SEC_STATE_CODE VARCHAR(15) NULL,'
+ 'SEC_PRE_REFUNDED_DATE DATETIME NULL,'
+ 'SEC_PRE_REFUNDED_PRICE FLOAT NULL,'
+ 'SEC_TAX_DESC NVARCHAR(18) NULL,'
+ 'SEC_STRIKE_PRICE_FI FLOAT NULL,'
+ 'SEC_STRIKE_PRICE FLOAT NULL,'
+ 'SEC_UNDERLYING_CUSIP NVARCHAR(9) NULL,'
+ 'SEC_EXPIRATION_DATE DATETIME NULL,'
+ 'SEC_PUT_CALL_IND NVARCHAR(1) NULL,'
+ 'SEC_CONTRACT_SIZE FLOAT NULL,'
+ 'SEC_DIVIDEND_FREQUENCY FLOAT NULL,'
+ 'SEC_LAST_DIVI_PER_SHARE NUMERIC(8,5) NULL,'
+ 'SEC_EX_DIVIDEND_DATE DATETIME NULL,'
+ 'SEC_DIVIDEND_PAY_DATE DATETIME NULL,'
+ 'SEC_DIVIDEND_REC_DATE DATETIME NULL,'
+ 'SEC_SPLIT_DATE DATETIME NULL,'
+ 'SEC_IMPLIED_VOLATILITY FLOAT NULL,'
+ 'SEC_DELTA FLOAT NULL,'
+ 'SEC_PAY_SPREAD FLOAT NULL,'
+ 'SEC_RECV_SPREAD FLOAT NULL,'
+ 'SEC_RECV_COUPON_FI FLOAT NULL,'
+ 'SEC_RECV_COUPON FLOAT NULL,'
+ 'SEC_RECV_COUNTRY FLOAT NULL,'
+ 'SEC_RECV_CURRENCY FLOAT NULL,'
+ 'SEC_RECV_FIRST_COUPON_DATE DATETIME NULL,'
+ 'SEC_RECV_COUPON_FREQ FLOAT NULL,'
+ 'SEC_RECV_NEXT_REFIX_DATE DATETIME NULL,'
+ 'SEC_RECV_NEXT_COUPON_DATE DATETIME NULL,'
+ 'SEC_RECV_DAY_TYPE FLOAT NULL,'
+ 'SEC_RECV_PAYMENT_FREQ FLOAT NULL,'
+ 'SEC_PAY_FIXED_RATE NVARCHAR(17) NULL,'
+ 'SEC_RECV_FIXED_RATE NVARCHAR(17) NULL,'
+ 'SEC_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'SEC_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'SEC_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'SEC_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'SEC_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'SEC_SHARES_PER_WARRANT FLOAT NULL,'
+ 'SEC_IS_SINKABLE VARCHAR(1) NULL,'
+ 'SEC_IS_CONVERTIBLE VARCHAR(1) NULL,'
+ 'SEC_INDUSTRY_SUBGROUP VARCHAR(24) NULL,'
+ 'SEC_RESET_INDEX VARCHAR(10) NULL,'
+ 'SEC_LAST_REFIX_DATE DATETIME NULL,'
+ 'SEC_FIRST_RATE_RESET_DATE DATETIME NULL,'
+ 'SEC_FIRST_PAYMENT_RESET_DATE DATETIME NULL,'
+ 'SEC_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'SEC_ISIN_NUMBER VARCHAR(12) NULL,'
+ 'SEC_SEDOL1_NUMBER VARCHAR(8) NULL,'
+ 'SEC_SEDOL2_NUMBER VARCHAR(8) NULL,'
+ 'SEC_CUSIP_NUMBER VARCHAR(9) NULL,'
+ 'SEC_CINS_NUMBER VARCHAR(8) NULL,'
+ 'SEC_COMMON_STK_ISIN_NUMBER VARCHAR(12) NULL,'
+ 'SEC_IS_144A_ELIGIBLE VARCHAR(4) NULL,'
+ 'SEC_IS_ZERO_COUPON VARCHAR(2) NULL,'
+ 'SEC_ROUND_LOT_SIZE NUMERIC(8,2) NULL,'
+ 'SEC_QUOTE_UNITS VARCHAR(12) NULL,'
+ 'SEC_IS_QUOTED_AS_A_PERC_OF_PAR VARCHAR(4) NULL,'
+ 'SEC_MINIMUM_PIECE NUMERIC(8,2) NULL,'
+ 'SEC_IS_OID_BOND VARCHAR(8) NULL,'
+ 'SEC_M_MKT_GUARANTOR VARCHAR(14) NULL,'
+ 'SEC_GUARANTOR VARCHAR(30) NULL,'
+ 'SEC_MUNI_REMARKETING_AGENT VARCHAR(8) NULL,'
+ 'SEC_MUNI_ALT_MIN_TAX VARCHAR(8) NULL,'
+ 'SEC_PUT_NOTIFICATION_MIN_DAYS NUMERIC(8,2) NULL,'
+ 'SEC_DAY_COUNT_DESC VARCHAR(20) NULL,'
+ 'SEC_CALCULATION_TYPE_DESC VARCHAR(8) NULL,'
+ 'SEC_MM_PROGRAM_TYPE VARCHAR(8) NULL,'
+ 'SEC_COLLATERAL_TYPE VARCHAR(28) NULL,'
+ 'SEC_BID_PRICE_DEC NUMERIC(8,2) NULL,'
+ 'SEC_LAST_UPDATE_DATETIME VARCHAR(10) NULL,'
+ 'SEC_INDUSTRY_SECTOR VARCHAR(24) NULL,'
+ 'SEC_INDUSTRY_GROUP VARCHAR(30) NULL,'
+ 'SEC_GICS_INDUSTRY_GROUP NUMERIC(7,2) NULL,'
+ 'SEC_GICS_INDUSTRY_GROUP_NAME VARCHAR(30) NULL,'
+ 'SEC_BLOOMBERG_SUBFLAG NUMERIC(4,0) NULL,'
+ 'SEC_COUNTRY_ISO_CODE VARCHAR(3) NULL,'
+ 'SEC_MAIN_ISO VARCHAR(3) NULL,'
+ 'SEC_QUOTE_LOT_SIZE NUMERIC(11,2) NULL,'
+ 'SEC_SECURITY_DESCRIPTION VARCHAR(15) NULL,'
+ 'SEC_SECURITY_TYPE_2 VARCHAR(28) NULL,'
+ 'SEC_SECTOR VARCHAR(10) NULL,'
+ 'SEC_SHORT_NAME VARCHAR(18) NULL,'
+ 'SEC_SIC_CODE VARCHAR(4) NULL,'
+ 'SEC_MTGE_IS_AGENCY_BACKED VARCHAR(4) NULL,'
+ 'SEC_DTC_ELIGIBLE VARCHAR(4) NULL,'
+ 'SEC_REDEMPTION_VALUE NUMERIC(16,6) NULL,'
+ 'SEC_IS_DEFAULTED VARCHAR(4) NULL,'
+ 'SEC_MTGE_PAYMENT_DELAY VARCHAR(8) NULL,'
+ 'SEC_MTGE_ORIGINAL_AMOUNT VARCHAR(17)NULL,'
+ 'SEC_CONVERSION_PRICE NUMERIC(16,6) NULL,'
+ 'SEC_CONVERSION_RATIO NUMERIC(16,4) NULL,'
+ 'SEC_CONVERTIBLE_START_DATE DATETIME NULL,'
+ 'SEC_CONVERTIBLE_UNTIL DATETIME NULL,'
+ 'SEC_FIXD_EX_RTE_CONVERTIBLES NUMERIC(12,6) NULL,'
+ 'SEC_MARKET_SECTOR_DESCRIPTION VARCHAR(6) NULL,'
+ 'SEC_SECURITY_TYPE VARCHAR(28) NULL,'
+ 'SEC_SIC_NAME VARCHAR(14) NULL,'
+ 'SEC_MTY_OR_REFUND_TYPE VARCHAR(18) NULL,'
+ 'SEC_MID_YLD2WORST_CONVENTION NUMERIC(14,6) NULL,'
+ 'SEC_MTGE_WAL_IN_YEARS_TO_CALL NUMERIC(8,4) NULL,'
+ 'SEC_MID_OAS_EFFECTIVE_DURATION NUMERIC(8,4) NULL,'
+ 'SEC_MID_OAS_CONVEXITY NUMERIC(8,4) NULL,'
+ 'SEC_MID_MODIFIED_DURATION NUMERIC(8,4) NULL,'
+ 'SEC_ISSUE_DATE DATETIME NULL,'
+ 'SEC_PREPAYMENT_TYPE NUMERIC(2, 0) NULL,'
+ 'SEC_PREPAYMENT_SPEED NUMERIC(8, 3) NULL,'
+ 'SEC_MTGE_GENERIC_TICKER VARCHAR(8) NULL,'
+ 'PORTIA_SECURITY VARCHAR(15) NULL,'
+ 'PORTIA_DESCRIPTION_1 VARCHAR(39) NULL,'
+ 'PORTIA_DESCRIPTION_2 VARCHAR(39) NULL,'
+ 'PORTIA_SECURITY_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_CASH_BALANCE VARCHAR(15) NULL,'
+ 'PORTIA_CUSIP_ISIN VARCHAR(12) NULL,'
+ 'PORTIA_PRICE_SYMBOL VARCHAR(15) NULL,'
+ 'PORTIA_STATE VARCHAR(15) NULL,'
+ 'PORTIA_COUNTRY VARCHAR(15) NULL,'
+ 'PORTIA_EXCHANGE VARCHAR(15) NULL,'
+ 'PORTIA_CURRENCY VARCHAR(15) NULL,'
+ 'PORTIA_MOODY_RATING VARCHAR(15) NULL,'
+ 'PORTIA_SP_RATING VARCHAR(15) NULL,'
+ 'PORTIA_OTHER_RATING VARCHAR(15) NULL,'
+ 'PORTIA_COUPON_RATE FLOAT NULL,'
+ 'PORTIA_MATURITY_DATE DATETIME NULL,'
+ 'PORTIA_MATURITY_PRICE FLOAT NULL,'
+ 'PORTIA_ISSUE_DATE DATETIME NULL,'
+ 'PORTIA_ISSUE_PRICE FLOAT NULL,'
+ 'PORTIA_TAX_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_DATED_DATE DATETIME NULL,'
+ 'PORTIA_ODD_FIRST_CPN DATETIME NULL,'
+ 'PORTIA_ODD_LAST_CPN DATETIME NULL,'
+ 'PORTIA_POOL_NUMBER VARCHAR(8) NULL,'
+ 'PORTIA_SEC_IDENTIFIER VARCHAR(15) NULL,'
+ 'PORTIA_TRADE_STATUS VARCHAR(10) default ''Received''
NULL,'
+ 'PORTIA_TRADE_STATUS_MESSAGE VARCHAR(255) NULL,'
+ 'PORTIA_DST_PMT_FREQ VARCHAR(15) NULL,'
+ 'PORTIA_DST_PATH VARCHAR(15) NULL,'
+ 'PORTIA_DST_PRICING_SOURCE VARCHAR(15) NULL,'
+ 'PORTIA_DTC_ELIGIBLE VARCHAR(40) NULL,'
+ 'PORTIA_INTL_TAXSTATUS VARCHAR(40) NULL,'
+ 'PORTIA_INTL_INCOMETYPE VARCHAR(40) NULL,'
+ 'PORTIA_SOURCE_CATEGORY VARCHAR(15) NULL,'
+ 'PORTIA_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
+ 'PORTIA_SETTLE_LOCATION VARCHAR(15) NULL,'
+ 'PORTIA_SHARES_OUTSTANDING FLOAT NULL,'
+ 'PORTIA_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'PORTIA_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'PORTIA_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'PORTIA_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'PORTIA_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'PORTIA_SHARES_PER_WARRANT FLOAT NULL,'
+ 'PORTIA_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'PORTIA_ST_SP_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ST_MOODY_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ST_OTHER_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ISSUER VARCHAR(15) NULL,'
+ 'PORTIA_DATA_SOURCE VARCHAR(15) NULL,'
+ 'PORTIA_SIC_CODE VARCHAR(5) NULL,'
+ 'PORTIA_INDUSTRY VARCHAR(15) NULL,'
+ 'PORTIA_PREPAY_TABLE VARCHAR(15) NULL,'
+ 'PORTIA_PREPAY_RATE NUMERIC(8, 3) NULL,' --
FLOAT in Portia
+ 'PORTIA_CASH_FLOW_SRC VARCHAR(15) NULL,'
+ 'PORTIA_CONV_SECURITY VARCHAR(15) NULL,'
+ 'PORTIA_CONV_PRICE NUMERIC(16,6)
NULL,' -- FLOAT in Portia
+ 'PORTIA_CONV_RATIO NUMERIC(16,4)
NULL,' -- FLOAT in Portia
+ 'PORTIA_CONV_START_DATE DATETIME NULL,'
+ 'PORTIA_CONV_END_DATE DATETIME NULL,'
+ 'PORTIA_CONV_EXER_RATE NUMERIC(12,6)
NULL,' -- FLOAT in Portia
+ 'PORTIA_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_STRING_01 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_DATE_02 DATETIME NULL,'
+ 'NEW_SECURITY_FLAG VARCHAR(1) default ''Y'' NOT NULL,'
+ 'FRACT_IND_RUN_STATUS VARCHAR(1) NULL,'
+ 'MAPPING_RUN_STATUS VARCHAR(1) NULL,'
+ 'LINE_NUMBER int NULL,'
+ 'P2_SECURITY VARCHAR(15) NULL,'
+ 'P2_DESCRIPTION_1 VARCHAR(39) NULL,'
+ 'P2_DESCRIPTION_2 VARCHAR(39) NULL,'
+ 'P2_SECURITY_TYPE VARCHAR(15) NULL,'
+ 'P2_CASH_BALANCE VARCHAR(15) NULL,'
+ 'P2_CUSIP_ISIN VARCHAR(12) NULL,'
+ 'P2_PRICE_SYMBOL VARCHAR(15) NULL,'
+ 'P2_STATE VARCHAR(15) NULL,'
+ 'P2_COUNTRY VARCHAR(15) NULL,'
+ 'P2_EXCHANGE VARCHAR(15) NULL,'
+ 'P2_CURRENCY VARCHAR(15) NULL,'
+ 'P2_MOODY_RATING VARCHAR(15) NULL,'
+ 'P2_SP_RATING VARCHAR(15) NULL,'
+ 'P2_OTHER_RATING VARCHAR(15) NULL,'
+ 'P2_COUPON_RATE FLOAT NULL,'
+ 'P2_MATURITY_DATE DATETIME NULL,'
+ 'P2_MATURITY_PRICE FLOAT NULL,'
+ 'P2_ISSUE_DATE DATETIME NULL,'
+ 'P2_ISSUE_PRICE FLOAT NULL,'
+ 'P2_TAX_TYPE VARCHAR(15) NULL,'
+ 'P2_DATED_DATE DATETIME NULL,'
+ 'P2_ODD_FIRST_CPN DATETIME NULL,'
+ 'P2_ODD_LAST_CPN DATETIME NULL,'
+ 'P2_POOL_NUMBER VARCHAR(8) NULL,'
+ 'P2_SEC_IDENTIFIER VARCHAR(15) NULL,'
+ 'P2_DST_PMT_FREQ VARCHAR(15) NULL,'
+ 'P2_DST_PATH VARCHAR(15) NULL,'
+ 'P2_DST_PRICING_SOURCE VARCHAR(15) NULL,'
+ 'P2_DTC_ELIGIBLE VARCHAR(40) NULL,'
+ 'P2_INTL_TAXSTATUS VARCHAR(40) NULL,'
+ 'P2_INTL_INCOMETYPE VARCHAR(40) NULL,'
+ 'P2_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
+ 'P2_SETTLE_LOCATION VARCHAR(15) NULL,'
+ 'P2_SHARES_OUTSTANDING FLOAT NULL,'
+ 'P2_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
+ 'P2_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'P2_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'P2_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'P2_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'P2_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'P2_SHARES_PER_WARRANT FLOAT NULL,'
+ 'P2_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'P2_ST_SP_RATING VARCHAR(15) NULL,'
+ 'P2_ST_MOODY_RATING VARCHAR(15) NULL,'
+ 'P2_ST_OTHER_RATING VARCHAR(15) NULL,'
+ 'P2_ISSUER VARCHAR(15) NULL,'
+ 'P2_DATA_SOURCE VARCHAR(15) NULL,'
+ 'P2_SIC_CODE VARCHAR(5) NULL,'
+ 'P2_INDUSTRY VARCHAR(15) NULL,'
+ 'P2_PREPAY_TABLE VARCHAR(15) NULL,'
+ 'P2_PREPAY_RATE NUMERIC(8, 3) NULL,' --
FLOAT in Portia
+ 'P2_CASH_FLOW_SRC VARCHAR(15) NULL,'
+ 'P2_CONV_SECURITY VARCHAR(15) NULL,'
+ 'P2_CONV_PRICE NUMERIC(16,6)
NULL,' -- FLOAT in Portia
+ 'P2_CONV_RATIO NUMERIC(16,4)
NULL,' -- FLOAT in Portia
+ 'P2_CONV_START_DATE DATETIME NULL,'
+ 'P2_CONV_END_DATE DATETIME NULL,'
+ 'P2_CONV_EXER_RATE NUMERIC(12,6) NULL,' --
FLOAT in Portia
+ 'P2_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_STRING_01 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_DATE_02 DATETIME NULL,'
+ 'CONSTRAINT pk_sec_id PRIMARY KEY NONCLUSTERED
(SEC_ID))' + @.LockClause
)
GO
CREATE NONCLUSTERED INDEX idx_new_sec_flag ON
dbo.PORTIA_BLOOMBERG_SECURITIES(NEW_SECURITY_FLAG)
GO
any help would be very much appreciated
thanksThese types of error are most often errors in SQL Server. Assuming that you have already searched KB
and are current on service pack, I suggest you open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Mark Percival" <anonymous@.discussions.microsoft.com> wrote in message
news:092601c39714$a1454f40$a501280a@.phx.gbl...
> an error has been returned by a long query shown below
> SqlDumpExceptionHandler: Process 7 generated fatal
> exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
> process.
> we are running on sqlserver 7 sp4
> if one field is removed the query will run
> also if the top 1 is removed it will work
> the script will also work on sql server 2000
> the query is shown below:-
>
> SELECT TOP 1 ISNULL(CONVERT(CHAR,SEC_ID),'0') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0039),PORTIA_DESCRIPTION_1), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0039),PORTIA_DESCRIPTION_2), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY_TYPE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_CASH_BALANCE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0012),PORTIA_CUSIP_ISIN), ' ') + '\17'
> + ISNULL(CONVERT(CHAR(0015),PORTIA_PRICE_SYMBOL), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_STATE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_COUNTRY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_EXCHANGE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_CURRENCY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_MOODY_RATING), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_SP_RATING), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_OTHER_RATING), ' ')
> + '\17' + ISNULL(CONVERT(CHAR,PORTIA_COUPON_RATE),'0')
> + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_MATURITY_DATE,120),' ') + '\17'
> + ISNULL(CONVERT(CHAR,PORTIA_MATURITY_PRICE),'0') + '\17'
> +
> ISNULL(CONVERT(CHAR,PORTIA_ISSUE_PRICE),'0') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_TAX_TYPE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_DATED_DATE,120),' ') + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_ODD_FIRST_CPN,120),' ') + '\17'
> +
> ISNULL(CONVERT(CHAR,PORTIA_ODD_LAST_CPN,120),' ') + '\17'
> + ISNULL(CONVERT(CHAR(0008),PORTIA_POOL_NUMBER), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SEC_IDENTIFIER), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_DST_PMT_FREQ), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PATH), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PRICING_SOURCE), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0040),PORTIA_DTC_ELIGIBLE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0040),PORTIA_INTL_TAXSTATUS), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0040),PORTIA_INTL_INCOMETYPE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR,PORTIA_PAYMENT_DELAY),'0')
> + '\17'
> FROM PORTIA_BLOOMBERG_SECURITIES
> the create script for the table is shown below :-
> it may look a bit odd but its an execute script to deal
> with different databases
>
> PRINT 'EXECUTING: Portia_bloomberg_SECURITIES.sql'
> GO
>
> if exists (select * from dbo.sysobjects where id => Object_id('dbo.PORTIA_BLOOMBERG_SECURITIES') and type in
> ('U','S'))
> begin
> drop table dbo.PORTIA_BLOOMBERG_SECURITIES
> end
> go
>
> DECLARE @.LockClause VARCHAR(14)
> IF CHARINDEX('Microsoft', @.@.VERSION) = 0 -- Sybase
> SELECT @.LockClause = ' LOCK DATAROWS'
> ELSE -- SQL Server
> SELECT @.LockClause = ''
> EXECUTE( 'CREATE TABLE dbo.PORTIA_BLOOMBERG_SECURITIES ('
> + 'SEC_ID NUMERIC(8,0) IDENTITY NOT NULL,'
> + 'RECEIVE_DATE DATETIME default getdate() NOT NULL,'
> + 'SEC_IDENTIFIER NVARCHAR(12) NOT NULL,'
> + 'SEC_IDENTIFIER_FLAG NVARCHAR(2) NOT NULL,'
> + 'SEC_LAST_UPDATE_DATE DATETIME NULL,'
> + 'SEC_TICKER NVARCHAR(8) NULL,'
> + 'SEC_COUPON_FI FLOAT NULL,'
> + 'SEC_COUPON FLOAT NULL,'
> + 'SEC_MATURITY DATETIME NULL,'
> + 'SEC_ISSUER NVARCHAR(80) NULL,'
> + 'SEC_ISSUE_AMOUNT NUMERIC(20,2) NULL,'
> + 'SEC_OUTSTANDING_AMOUNT FLOAT NULL,'
> + 'SEC_COUNTRY_CODE FLOAT NULL,'
> + 'SEC_CURRENCY_CODE FLOAT NULL,'
> + 'SEC_ISO_CURRENCY VARCHAR(3),'
> + 'SEC_ISSUE_PRICE FLOAT NULL,'
> + 'SEC_DATED_DATE DATETIME NULL,'
> + 'SEC_FIRST_COUPON_DATE DATETIME NULL,'
> + 'SEC_PENULTIMATE_COUPON_DATE DATETIME NULL,'
> + 'SEC_COUPON_FREQUENCY NUMERIC(9, 2) NULL,'
> + 'SEC_NEXT_REFIX_DATE DATETIME NULL,'
> + 'SEC_NEXT_COUPON_DATE DATETIME NULL,'
> + 'SEC_DAY_TYPE FLOAT NULL,'
> + 'SEC_SP_RATING NVARCHAR(8) NULL,'
> + 'SEC_MOODY_RATING NVARCHAR(8) NULL,'
> + 'SEC_FITCH_RATING NVARCHAR(8) NULL,'
> + 'SEC_DUFF_AND_PHELPS NVARCHAR(8) NULL,'
> + 'SEC_BB_COMPOSITE_RATING NVARCHAR(8) NULL,'
> + 'SEC_PRODUCT_GROUP FLOAT NULL,'
> + 'SEC_INDUSTRY_TYPE FLOAT NULL,'
> + 'SEC_CALCULATION_TYPE FLOAT NULL,'
> + 'SEC_PAYMENT_FREQUENCY FLOAT NULL,'
> + 'SEC_INTEREST_ACCRUAL_DATE DATETIME NULL,'
> + 'SEC_PRPL_FLAG NVARCHAR(1) NULL,'
> + 'SEC_FLOATER_FLAG NVARCHAR(1) NULL,'
> + 'SEC_BLOOMBERG_SECURITY NVARCHAR(12) NULL,'
> + 'SEC_POOL_NUMBER NVARCHAR(8) NULL,'
> + 'SEC_TRANCHE NVARCHAR(4) NULL,'
> + 'SEC_FACTOR FLOAT NULL,'
> + 'SEC_FACTOR_DATE DATETIME NULL,'
> + 'SEC_PAYMENT_DELAY FLOAT NULL,'
> + 'SEC_COLLATERAL NVARCHAR(8) NULL,'
> + 'SEC_CAP FLOAT NULL,'
> + 'SEC_FLOOR FLOAT NULL,'
> + 'SEC_WAC_CURRENT FLOAT NULL,'
> + 'SEC_WAC_ORIGINAL FLOAT NULL,'
> + 'SEC_WAM_REMAINING_MONTHS FLOAT NULL,'
> + 'SEC_WAM_ORIGINAL_MONTHS FLOAT NULL,'
> + 'SEC_WAL_MONTHS FLOAT NULL,'
> + 'SEC_PAC_LOWER_COLLAR FLOAT NULL,'
> + 'SEC_PAC_UPPER_COLLAR FLOAT NULL,'
> + 'SEC_PRE_PAYMENT_TYPE FLOAT NULL,'
> + 'SEC_PRE_PAYMENT_SPEED NUMERIC(8,3) NULL,'
> + 'SEC_PRIOR_MONTH_FACTOR FLOAT NULL,'
> + 'SEC_PRIOR_FACTOR_DATE DATETIME NULL,'
> + 'SEC_PAYMENT_ACCRUAL FLOAT NULL,'
> + 'SEC_NOTIONAL_PRINCIPLE_FLAG NVARCHAR(1) NULL,'
> + 'SEC_MORTGAGE_TYPE NVARCHAR(14) NULL,'
> + 'SEC_SERIES NVARCHAR(4) NULL,'
> + 'SEC_NEXT_CALL_DATE DATETIME NULL,'
> + 'SEC_NEXT_CALL_PRICE FLOAT NULL,'
> + 'SEC_PAR_CALL_DATE DATETIME NULL,'
> + 'SEC_NEXT_PUT_DATE DATETIME NULL,'
> + 'SEC_NEXT_PUT_PRICE FLOAT NULL,'
> + 'SEC_PAR_PUT_DATE DATETIME NULL,'
> + 'SEC_WORKOUT_DATE DATETIME NULL,'
> + 'SEC_WORKOUT_PRICE FLOAT NULL,'
> + 'SEC_STEP_UP_COUPON FLOAT NULL,'
> + 'SEC_STEP_UP_DATE DATETIME NULL,'
> + 'SEC_TREASURY_INDEX_FACTOR FLOAT NULL,'
> + 'SEC_TAX_STATUS NVARCHAR(1) NULL,'
> + 'SEC_CREDIT_ENHANCEMENTS NVARCHAR(28) NULL,'
> + 'SEC_STATE_CODE VARCHAR(15) NULL,'
> + 'SEC_PRE_REFUNDED_DATE DATETIME NULL,'
> + 'SEC_PRE_REFUNDED_PRICE FLOAT NULL,'
> + 'SEC_TAX_DESC NVARCHAR(18) NULL,'
> + 'SEC_STRIKE_PRICE_FI FLOAT NULL,'
> + 'SEC_STRIKE_PRICE FLOAT NULL,'
> + 'SEC_UNDERLYING_CUSIP NVARCHAR(9) NULL,'
> + 'SEC_EXPIRATION_DATE DATETIME NULL,'
> + 'SEC_PUT_CALL_IND NVARCHAR(1) NULL,'
> + 'SEC_CONTRACT_SIZE FLOAT NULL,'
> + 'SEC_DIVIDEND_FREQUENCY FLOAT NULL,'
> + 'SEC_LAST_DIVI_PER_SHARE NUMERIC(8,5) NULL,'
> + 'SEC_EX_DIVIDEND_DATE DATETIME NULL,'
> + 'SEC_DIVIDEND_PAY_DATE DATETIME NULL,'
> + 'SEC_DIVIDEND_REC_DATE DATETIME NULL,'
> + 'SEC_SPLIT_DATE DATETIME NULL,'
> + 'SEC_IMPLIED_VOLATILITY FLOAT NULL,'
> + 'SEC_DELTA FLOAT NULL,'
> + 'SEC_PAY_SPREAD FLOAT NULL,'
> + 'SEC_RECV_SPREAD FLOAT NULL,'
> + 'SEC_RECV_COUPON_FI FLOAT NULL,'
> + 'SEC_RECV_COUPON FLOAT NULL,'
> + 'SEC_RECV_COUNTRY FLOAT NULL,'
> + 'SEC_RECV_CURRENCY FLOAT NULL,'
> + 'SEC_RECV_FIRST_COUPON_DATE DATETIME NULL,'
> + 'SEC_RECV_COUPON_FREQ FLOAT NULL,'
> + 'SEC_RECV_NEXT_REFIX_DATE DATETIME NULL,'
> + 'SEC_RECV_NEXT_COUPON_DATE DATETIME NULL,'
> + 'SEC_RECV_DAY_TYPE FLOAT NULL,'
> + 'SEC_RECV_PAYMENT_FREQ FLOAT NULL,'
> + 'SEC_PAY_FIXED_RATE NVARCHAR(17) NULL,'
> + 'SEC_RECV_FIXED_RATE NVARCHAR(17) NULL,'
> + 'SEC_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'SEC_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'SEC_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'SEC_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'SEC_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'SEC_SHARES_PER_WARRANT FLOAT NULL,'
> + 'SEC_IS_SINKABLE VARCHAR(1) NULL,'
> + 'SEC_IS_CONVERTIBLE VARCHAR(1) NULL,'
> + 'SEC_INDUSTRY_SUBGROUP VARCHAR(24) NULL,'
> + 'SEC_RESET_INDEX VARCHAR(10) NULL,'
> + 'SEC_LAST_REFIX_DATE DATETIME NULL,'
> + 'SEC_FIRST_RATE_RESET_DATE DATETIME NULL,'
> + 'SEC_FIRST_PAYMENT_RESET_DATE DATETIME NULL,'
> + 'SEC_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'SEC_ISIN_NUMBER VARCHAR(12) NULL,'
> + 'SEC_SEDOL1_NUMBER VARCHAR(8) NULL,'
> + 'SEC_SEDOL2_NUMBER VARCHAR(8) NULL,'
> + 'SEC_CUSIP_NUMBER VARCHAR(9) NULL,'
> + 'SEC_CINS_NUMBER VARCHAR(8) NULL,'
> + 'SEC_COMMON_STK_ISIN_NUMBER VARCHAR(12) NULL,'
> + 'SEC_IS_144A_ELIGIBLE VARCHAR(4) NULL,'
> + 'SEC_IS_ZERO_COUPON VARCHAR(2) NULL,'
> + 'SEC_ROUND_LOT_SIZE NUMERIC(8,2) NULL,'
> + 'SEC_QUOTE_UNITS VARCHAR(12) NULL,'
> + 'SEC_IS_QUOTED_AS_A_PERC_OF_PAR VARCHAR(4) NULL,'
> + 'SEC_MINIMUM_PIECE NUMERIC(8,2) NULL,'
> + 'SEC_IS_OID_BOND VARCHAR(8) NULL,'
> + 'SEC_M_MKT_GUARANTOR VARCHAR(14) NULL,'
> + 'SEC_GUARANTOR VARCHAR(30) NULL,'
> + 'SEC_MUNI_REMARKETING_AGENT VARCHAR(8) NULL,'
> + 'SEC_MUNI_ALT_MIN_TAX VARCHAR(8) NULL,'
> + 'SEC_PUT_NOTIFICATION_MIN_DAYS NUMERIC(8,2) NULL,'
> + 'SEC_DAY_COUNT_DESC VARCHAR(20) NULL,'
> + 'SEC_CALCULATION_TYPE_DESC VARCHAR(8) NULL,'
> + 'SEC_MM_PROGRAM_TYPE VARCHAR(8) NULL,'
> + 'SEC_COLLATERAL_TYPE VARCHAR(28) NULL,'
> + 'SEC_BID_PRICE_DEC NUMERIC(8,2) NULL,'
> + 'SEC_LAST_UPDATE_DATETIME VARCHAR(10) NULL,'
> + 'SEC_INDUSTRY_SECTOR VARCHAR(24) NULL,'
> + 'SEC_INDUSTRY_GROUP VARCHAR(30) NULL,'
> + 'SEC_GICS_INDUSTRY_GROUP NUMERIC(7,2) NULL,'
> + 'SEC_GICS_INDUSTRY_GROUP_NAME VARCHAR(30) NULL,'
> + 'SEC_BLOOMBERG_SUBFLAG NUMERIC(4,0) NULL,'
> + 'SEC_COUNTRY_ISO_CODE VARCHAR(3) NULL,'
> + 'SEC_MAIN_ISO VARCHAR(3) NULL,'
> + 'SEC_QUOTE_LOT_SIZE NUMERIC(11,2) NULL,'
> + 'SEC_SECURITY_DESCRIPTION VARCHAR(15) NULL,'
> + 'SEC_SECURITY_TYPE_2 VARCHAR(28) NULL,'
> + 'SEC_SECTOR VARCHAR(10) NULL,'
> + 'SEC_SHORT_NAME VARCHAR(18) NULL,'
> + 'SEC_SIC_CODE VARCHAR(4) NULL,'
> + 'SEC_MTGE_IS_AGENCY_BACKED VARCHAR(4) NULL,'
> + 'SEC_DTC_ELIGIBLE VARCHAR(4) NULL,'
> + 'SEC_REDEMPTION_VALUE NUMERIC(16,6) NULL,'
> + 'SEC_IS_DEFAULTED VARCHAR(4) NULL,'
> + 'SEC_MTGE_PAYMENT_DELAY VARCHAR(8) NULL,'
> + 'SEC_MTGE_ORIGINAL_AMOUNT VARCHAR(17)NULL,'
> + 'SEC_CONVERSION_PRICE NUMERIC(16,6) NULL,'
> + 'SEC_CONVERSION_RATIO NUMERIC(16,4) NULL,'
> + 'SEC_CONVERTIBLE_START_DATE DATETIME NULL,'
> + 'SEC_CONVERTIBLE_UNTIL DATETIME NULL,'
> + 'SEC_FIXD_EX_RTE_CONVERTIBLES NUMERIC(12,6) NULL,'
> + 'SEC_MARKET_SECTOR_DESCRIPTION VARCHAR(6) NULL,'
> + 'SEC_SECURITY_TYPE VARCHAR(28) NULL,'
> + 'SEC_SIC_NAME VARCHAR(14) NULL,'
> + 'SEC_MTY_OR_REFUND_TYPE VARCHAR(18) NULL,'
> + 'SEC_MID_YLD2WORST_CONVENTION NUMERIC(14,6) NULL,'
> + 'SEC_MTGE_WAL_IN_YEARS_TO_CALL NUMERIC(8,4) NULL,'
> + 'SEC_MID_OAS_EFFECTIVE_DURATION NUMERIC(8,4) NULL,'
> + 'SEC_MID_OAS_CONVEXITY NUMERIC(8,4) NULL,'
> + 'SEC_MID_MODIFIED_DURATION NUMERIC(8,4) NULL,'
> + 'SEC_ISSUE_DATE DATETIME NULL,'
> + 'SEC_PREPAYMENT_TYPE NUMERIC(2, 0) NULL,'
> + 'SEC_PREPAYMENT_SPEED NUMERIC(8, 3) NULL,'
> + 'SEC_MTGE_GENERIC_TICKER VARCHAR(8) NULL,'
> + 'PORTIA_SECURITY VARCHAR(15) NULL,'
> + 'PORTIA_DESCRIPTION_1 VARCHAR(39) NULL,'
> + 'PORTIA_DESCRIPTION_2 VARCHAR(39) NULL,'
> + 'PORTIA_SECURITY_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_CASH_BALANCE VARCHAR(15) NULL,'
> + 'PORTIA_CUSIP_ISIN VARCHAR(12) NULL,'
> + 'PORTIA_PRICE_SYMBOL VARCHAR(15) NULL,'
> + 'PORTIA_STATE VARCHAR(15) NULL,'
> + 'PORTIA_COUNTRY VARCHAR(15) NULL,'
> + 'PORTIA_EXCHANGE VARCHAR(15) NULL,'
> + 'PORTIA_CURRENCY VARCHAR(15) NULL,'
> + 'PORTIA_MOODY_RATING VARCHAR(15) NULL,'
> + 'PORTIA_SP_RATING VARCHAR(15) NULL,'
> + 'PORTIA_OTHER_RATING VARCHAR(15) NULL,'
> + 'PORTIA_COUPON_RATE FLOAT NULL,'
> + 'PORTIA_MATURITY_DATE DATETIME NULL,'
> + 'PORTIA_MATURITY_PRICE FLOAT NULL,'
> + 'PORTIA_ISSUE_DATE DATETIME NULL,'
> + 'PORTIA_ISSUE_PRICE FLOAT NULL,'
> + 'PORTIA_TAX_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_DATED_DATE DATETIME NULL,'
> + 'PORTIA_ODD_FIRST_CPN DATETIME NULL,'
> + 'PORTIA_ODD_LAST_CPN DATETIME NULL,'
> + 'PORTIA_POOL_NUMBER VARCHAR(8) NULL,'
> + 'PORTIA_SEC_IDENTIFIER VARCHAR(15) NULL,'
> + 'PORTIA_TRADE_STATUS VARCHAR(10) default ''Received''
> NULL,'
> + 'PORTIA_TRADE_STATUS_MESSAGE VARCHAR(255) NULL,'
> + 'PORTIA_DST_PMT_FREQ VARCHAR(15) NULL,'
> + 'PORTIA_DST_PATH VARCHAR(15) NULL,'
> + 'PORTIA_DST_PRICING_SOURCE VARCHAR(15) NULL,'
> + 'PORTIA_DTC_ELIGIBLE VARCHAR(40) NULL,'
> + 'PORTIA_INTL_TAXSTATUS VARCHAR(40) NULL,'
> + 'PORTIA_INTL_INCOMETYPE VARCHAR(40) NULL,'
> + 'PORTIA_SOURCE_CATEGORY VARCHAR(15) NULL,'
> + 'PORTIA_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
> + 'PORTIA_SETTLE_LOCATION VARCHAR(15) NULL,'
> + 'PORTIA_SHARES_OUTSTANDING FLOAT NULL,'
> + 'PORTIA_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'PORTIA_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'PORTIA_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'PORTIA_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'PORTIA_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'PORTIA_SHARES_PER_WARRANT FLOAT NULL,'
> + 'PORTIA_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'PORTIA_ST_SP_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ST_MOODY_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ST_OTHER_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ISSUER VARCHAR(15) NULL,'
> + 'PORTIA_DATA_SOURCE VARCHAR(15) NULL,'
> + 'PORTIA_SIC_CODE VARCHAR(5) NULL,'
> + 'PORTIA_INDUSTRY VARCHAR(15) NULL,'
> + 'PORTIA_PREPAY_TABLE VARCHAR(15) NULL,'
> + 'PORTIA_PREPAY_RATE NUMERIC(8, 3) NULL,' --
> FLOAT in Portia
> + 'PORTIA_CASH_FLOW_SRC VARCHAR(15) NULL,'
> + 'PORTIA_CONV_SECURITY VARCHAR(15) NULL,'
> + 'PORTIA_CONV_PRICE NUMERIC(16,6)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_CONV_RATIO NUMERIC(16,4)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_CONV_START_DATE DATETIME NULL,'
> + 'PORTIA_CONV_END_DATE DATETIME NULL,'
> + 'PORTIA_CONV_EXER_RATE NUMERIC(12,6)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_STRING_01 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_DATE_02 DATETIME NULL,'
> + 'NEW_SECURITY_FLAG VARCHAR(1) default ''Y'' NOT NULL,'
> + 'FRACT_IND_RUN_STATUS VARCHAR(1) NULL,'
> + 'MAPPING_RUN_STATUS VARCHAR(1) NULL,'
> + 'LINE_NUMBER int NULL,'
> + 'P2_SECURITY VARCHAR(15) NULL,'
> + 'P2_DESCRIPTION_1 VARCHAR(39) NULL,'
> + 'P2_DESCRIPTION_2 VARCHAR(39) NULL,'
> + 'P2_SECURITY_TYPE VARCHAR(15) NULL,'
> + 'P2_CASH_BALANCE VARCHAR(15) NULL,'
> + 'P2_CUSIP_ISIN VARCHAR(12) NULL,'
> + 'P2_PRICE_SYMBOL VARCHAR(15) NULL,'
> + 'P2_STATE VARCHAR(15) NULL,'
> + 'P2_COUNTRY VARCHAR(15) NULL,'
> + 'P2_EXCHANGE VARCHAR(15) NULL,'
> + 'P2_CURRENCY VARCHAR(15) NULL,'
> + 'P2_MOODY_RATING VARCHAR(15) NULL,'
> + 'P2_SP_RATING VARCHAR(15) NULL,'
> + 'P2_OTHER_RATING VARCHAR(15) NULL,'
> + 'P2_COUPON_RATE FLOAT NULL,'
> + 'P2_MATURITY_DATE DATETIME NULL,'
> + 'P2_MATURITY_PRICE FLOAT NULL,'
> + 'P2_ISSUE_DATE DATETIME NULL,'
> + 'P2_ISSUE_PRICE FLOAT NULL,'
> + 'P2_TAX_TYPE VARCHAR(15) NULL,'
> + 'P2_DATED_DATE DATETIME NULL,'
> + 'P2_ODD_FIRST_CPN DATETIME NULL,'
> + 'P2_ODD_LAST_CPN DATETIME NULL,'
> + 'P2_POOL_NUMBER VARCHAR(8) NULL,'
> + 'P2_SEC_IDENTIFIER VARCHAR(15) NULL,'
> + 'P2_DST_PMT_FREQ VARCHAR(15) NULL,'
> + 'P2_DST_PATH VARCHAR(15) NULL,'
> + 'P2_DST_PRICING_SOURCE VARCHAR(15) NULL,'
> + 'P2_DTC_ELIGIBLE VARCHAR(40) NULL,'
> + 'P2_INTL_TAXSTATUS VARCHAR(40) NULL,'
> + 'P2_INTL_INCOMETYPE VARCHAR(40) NULL,'
> + 'P2_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
> + 'P2_SETTLE_LOCATION VARCHAR(15) NULL,'
> + 'P2_SHARES_OUTSTANDING FLOAT NULL,'
> + 'P2_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
> + 'P2_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'P2_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'P2_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'P2_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'P2_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'P2_SHARES_PER_WARRANT FLOAT NULL,'
> + 'P2_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'P2_ST_SP_RATING VARCHAR(15) NULL,'
> + 'P2_ST_MOODY_RATING VARCHAR(15) NULL,'
> + 'P2_ST_OTHER_RATING VARCHAR(15) NULL,'
> + 'P2_ISSUER VARCHAR(15) NULL,'
> + 'P2_DATA_SOURCE VARCHAR(15) NULL,'
> + 'P2_SIC_CODE VARCHAR(5) NULL,'
> + 'P2_INDUSTRY VARCHAR(15) NULL,'
> + 'P2_PREPAY_TABLE VARCHAR(15) NULL,'
> + 'P2_PREPAY_RATE NUMERIC(8, 3) NULL,' --
> FLOAT in Portia
> + 'P2_CASH_FLOW_SRC VARCHAR(15) NULL,'
> + 'P2_CONV_SECURITY VARCHAR(15) NULL,'
> + 'P2_CONV_PRICE NUMERIC(16,6)
> NULL,' -- FLOAT in Portia
> + 'P2_CONV_RATIO NUMERIC(16,4)
> NULL,' -- FLOAT in Portia
> + 'P2_CONV_START_DATE DATETIME NULL,'
> + 'P2_CONV_END_DATE DATETIME NULL,'
> + 'P2_CONV_EXER_RATE NUMERIC(12,6) NULL,' --
> FLOAT in Portia
> + 'P2_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_STRING_01 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_DATE_02 DATETIME NULL,'
> + 'CONSTRAINT pk_sec_id PRIMARY KEY NONCLUSTERED
> (SEC_ID))' + @.LockClause
> )
> GO
>
> CREATE NONCLUSTERED INDEX idx_new_sec_flag ON
> dbo.PORTIA_BLOOMBERG_SECURITIES(NEW_SECURITY_FLAG)
> GO
> any help would be very much appreciated
> thanks
>

Access Violation occurred writing address

Hi folks,
It seems for the last few days my SQL Server has been producing memory dumps
(with errors) about three times a day.
The main error seems to be the following :-
Computer type is AT/AT COMPATIBLE.
Current time is 22:11:16 09/20/05.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 75%
Total Physical = 991 MB
Available Physical = 240 MB
Total Page File = 2926 MB
Available Page File = 2269 MB
Total Virtual = 2047 MB
Available Virtual = 980 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0029.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 09/20/05 22:11:16 spid 0
*
* Exception Address = 004093CA
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000807
This error with differing memory address happens twice a day at various
times of the day, I had hoped it was linked to server usage however this
doesnt seem the case.
This SQL Server is used as the backend of an Online Web Browser game, and
typically its most active in the evenings however it seems to produce these
errors at any time of the day.
Although if it errors in the morning it wont do it in the evening ... but
then if it errors in the evening it doesnt do it in the morning.
Its almost like SQL is playing up for attention but its not speaking a
language I know.
Does anyone have any Ideas, any help greatly appreciated.
Thanks
Hi
We had this for a while on one of our servers.
We rebuilt all the indexes on all the databases and the problem went away.
Regards-
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Daniel Paull" <DanielPaull@.discussions.microsoft.com> wrote in message
news:7B5217FF-4635-47DC-8F0C-4208A21E99C4@.microsoft.com...
> Hi folks,
> It seems for the last few days my SQL Server has been producing memory
> dumps
> (with errors) about three times a day.
> The main error seems to be the following :-
> Computer type is AT/AT COMPATIBLE.
> Current time is 22:11:16 09/20/05.
> 1 Intel x86 level 15, 3 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> MemoryLoad = 75%
> Total Physical = 991 MB
> Available Physical = 240 MB
> Total Page File = 2926 MB
> Available Page File = 2269 MB
> Total Virtual = 2047 MB
> Available Virtual = 980 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0029.txt
> *
> ************************************************** ***************************
> **
> *
> * BEGIN STACK DUMP:
> * 09/20/05 22:11:16 spid 0
> *
> * Exception Address = 004093CA
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000807
> This error with differing memory address happens twice a day at various
> times of the day, I had hoped it was linked to server usage however this
> doesnt seem the case.
> This SQL Server is used as the backend of an Online Web Browser game, and
> typically its most active in the evenings however it seems to produce
> these
> errors at any time of the day.
> Although if it errors in the morning it wont do it in the evening ... but
> then if it errors in the evening it doesnt do it in the morning.
> Its almost like SQL is playing up for attention but its not speaking a
> language I know.
> Does anyone have any Ideas, any help greatly appreciated.
> Thanks
|||Hi thanks for the reply,
Did you use DBCC DBREINDEX or physically drop and recreate the indexes ?

Access Violation occurred writing address

Hi folks,
It seems for the last few days my SQL Server has been producing memory dumps
(with errors) about three times a day.
The main error seems to be the following :-
Computer type is AT/AT COMPATIBLE.
Current time is 22:11:16 09/20/05.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 75%
Total Physical = 991 MB
Available Physical = 240 MB
Total Page File = 2926 MB
Available Page File = 2269 MB
Total Virtual = 2047 MB
Available Virtual = 980 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0029.txt
*
****************************************
************************************
*
**
*
* BEGIN STACK DUMP:
* 09/20/05 22:11:16 spid 0
*
* Exception Address = 004093CA
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000807
This error with differing memory address happens twice a day at various
times of the day, I had hoped it was linked to server usage however this
doesnt seem the case.
This SQL Server is used as the backend of an Online Web Browser game, and
typically its most active in the evenings however it seems to produce these
errors at any time of the day.
Although if it errors in the morning it wont do it in the evening ... but
then if it errors in the evening it doesnt do it in the morning.
Its almost like SQL is playing up for attention but its not speaking a
language I know.
Does anyone have any Ideas, any help greatly appreciated.
ThanksHi
We had this for a while on one of our servers.
We rebuilt all the indexes on all the databases and the problem went away.
Regards-
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Daniel Paull" <DanielPaull@.discussions.microsoft.com> wrote in message
news:7B5217FF-4635-47DC-8F0C-4208A21E99C4@.microsoft.com...
> Hi folks,
> It seems for the last few days my SQL Server has been producing memory
> dumps
> (with errors) about three times a day.
> The main error seems to be the following :-
> Computer type is AT/AT COMPATIBLE.
> Current time is 22:11:16 09/20/05.
> 1 Intel x86 level 15, 3 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> MemoryLoad = 75%
> Total Physical = 991 MB
> Available Physical = 240 MB
> Total Page File = 2926 MB
> Available Page File = 2269 MB
> Total Virtual = 2047 MB
> Available Virtual = 980 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0029.txt
> *
> ****************************************
**********************************
***
> **
> *
> * BEGIN STACK DUMP:
> * 09/20/05 22:11:16 spid 0
> *
> * Exception Address = 004093CA
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000807
> This error with differing memory address happens twice a day at various
> times of the day, I had hoped it was linked to server usage however this
> doesnt seem the case.
> This SQL Server is used as the backend of an Online Web Browser game, and
> typically its most active in the evenings however it seems to produce
> these
> errors at any time of the day.
> Although if it errors in the morning it wont do it in the evening ... but
> then if it errors in the evening it doesnt do it in the morning.
> Its almost like SQL is playing up for attention but its not speaking a
> language I know.
> Does anyone have any Ideas, any help greatly appreciated.
> Thanks|||Hi thanks for the reply,
Did you use DBCC DBREINDEX or physically drop and recreate the indexes ?

Access Violation occurred writing address

Hi folks,
It seems for the last few days my SQL Server has been producing memory dumps
(with errors) about three times a day.
The main error seems to be the following :-
Computer type is AT/AT COMPATIBLE.
Current time is 22:11:16 09/20/05.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 75%
Total Physical = 991 MB
Available Physical = 240 MB
Total Page File = 2926 MB
Available Page File = 2269 MB
Total Virtual = 2047 MB
Available Virtual = 980 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0029.txt
*
*****************************************************************************
**
*
* BEGIN STACK DUMP:
* 09/20/05 22:11:16 spid 0
*
* Exception Address = 004093CA
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000807
This error with differing memory address happens twice a day at various
times of the day, I had hoped it was linked to server usage however this
doesnt seem the case.
This SQL Server is used as the backend of an Online Web Browser game, and
typically its most active in the evenings however it seems to produce these
errors at any time of the day.
Although if it errors in the morning it wont do it in the evening ... but
then if it errors in the evening it doesnt do it in the morning.
Its almost like SQL is playing up for attention but its not speaking a
language I know.
Does anyone have any Ideas, any help greatly appreciated.
ThanksHi
We had this for a while on one of our servers.
We rebuilt all the indexes on all the databases and the problem went away.
Regards-
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Daniel Paull" <DanielPaull@.discussions.microsoft.com> wrote in message
news:7B5217FF-4635-47DC-8F0C-4208A21E99C4@.microsoft.com...
> Hi folks,
> It seems for the last few days my SQL Server has been producing memory
> dumps
> (with errors) about three times a day.
> The main error seems to be the following :-
> Computer type is AT/AT COMPATIBLE.
> Current time is 22:11:16 09/20/05.
> 1 Intel x86 level 15, 3 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> MemoryLoad = 75%
> Total Physical = 991 MB
> Available Physical = 240 MB
> Total Page File = 2926 MB
> Available Page File = 2269 MB
> Total Virtual = 2047 MB
> Available Virtual = 980 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0029.txt
> *
> *****************************************************************************
> **
> *
> * BEGIN STACK DUMP:
> * 09/20/05 22:11:16 spid 0
> *
> * Exception Address = 004093CA
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000807
> This error with differing memory address happens twice a day at various
> times of the day, I had hoped it was linked to server usage however this
> doesnt seem the case.
> This SQL Server is used as the backend of an Online Web Browser game, and
> typically its most active in the evenings however it seems to produce
> these
> errors at any time of the day.
> Although if it errors in the morning it wont do it in the evening ... but
> then if it errors in the evening it doesnt do it in the morning.
> Its almost like SQL is playing up for attention but its not speaking a
> language I know.
> Does anyone have any Ideas, any help greatly appreciated.
> Thanks|||Hi thanks for the reply,
Did you use DBCC DBREINDEX or physically drop and recreate the indexes ?

Access Violation in SQL Server Native Client

Hi,
has anybody found a similar Error or could this verified?
When I run the following Statement:
insert into FACFG_SML_IDX
(FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX) values
(?,?,0)
I got an access violation here is the German error message:
"Es wurde versucht, im geschtzten Speicher zu lesen oder zu schreiben. Dies
ist hufig ein Hinweis darauf, dass anderer Speicher beschdigt ist."
This happens when I call SQLDescribeParam for the first parameter.
Here's the stack-trace from Visual Studio Debugger:
> msvcr80.dll!memcpy(unsigned char * dst=0x11080042, unsigned char *
> src=0x00000000, unsigned long count=158431312) Zeile 257 Asm
sqlncli.dll!337fbb90()
[Unten angegebene Rahmen sind mglicherweise nicht korrekt und/oder
fehlen, keine Symbole geladen fr sqlncli.dll]
sqlncli.dll!3387c7eb()
ntdll.dll!_RtlFreeHeapSlowly@.12() + 0x207 Bytes
ntdll.dll!_RtlFreeHeap@.12() + 0x16470 Bytes
ntdll.dll!_RtlLookupFunctionTable@.12() + 0x7d Bytes
ntdll.dll!ExecuteHandler2@.20() + 0x26 Bytes
ntdll.dll!ExecuteHandler@.20() + 0x24 Bytes
[Externer Code]
...
When the statement is change in the following way or the SQL Server driver
from XPSP2 is used there is no error:
insert into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)
I found this error in native client version 2005.90.3042.00 (SP2) and
2005.90.2047 (SP1).
Greetings
Dieter PelzHi, Dieter,
I understand that you encountered the Geman error message when you usd the
statement:
" insert into FACFG_SML_IDX
(FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX)
values
(?,?,0)" in SQLDescribeParam; however it succeeded when you used " insert
into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)".
If I have misunderstood, please let me know.
Appreciate your understanding that our MSDN Managed Newsgroups are focused
on English language support. It will be able to let us better understand
your issue clearly if you could do some translation in your future posts.
Anyway, after compare the following two articles:
http://msdn2.microsoft.com/de-de/library/33xdxt7x(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/33xdxt7x(vs.80).aspx
, I know that the corresponding english meaning is "Attempted to read or
write protected memory. This is often an indication that other memory has
been corrupted".
I will write a test project to see if I can reproduce your issue and I will
let you know as soon as possible. Also, since the error indicates that this
is a memory related issue, you may also carefully check if there are some
array overflow issues.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I have reproduced your issue at my side. Now I am consulting the product
team on this issue. I will let you know the response as soon as possible.
If it is convenient for your, I would like your sending me an email
response so that I can timely update you if I get the response from the
product team. My email address is changliw_at_microsoft_dot_com.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello,
sorry I thought an "Access Violation Exception" is clear enough, so I don't
translated the German error message.
Best Regards
Dieter Pelz
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> schrieb im Newsbeit
rag
news:0ojLLawXHHA.3856@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Dieter,
> I understand that you encountered the Geman error message when you usd the
> statement:
> " insert into FACFG_SML_IDX
> (FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX)
> values
> (?,?,0)" in SQLDescribeParam; however it succeeded when you used " insert
> into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)".
> If I have misunderstood, please let me know.
> Appreciate your understanding that our MSDN Managed Newsgroups are focused
> on English language support. It will be able to let us better understand
> your issue clearly if you could do some translation in your future posts.
> Anyway, after compare the following two articles:
> http://msdn2.microsoft.com/de-de/library/33xdxt7x(vs.80).aspx
> http://msdn2.microsoft.com/en-us/library/33xdxt7x(vs.80).aspx
> , I know that the corresponding english meaning is "Attempted to read or
> write protected memory. This is often an indication that other memory has
> been corrupted".
> I will write a test project to see if I can reproduce your issue and I
> will
> let you know as soon as possible. Also, since the error indicates that
> this
> is a memory related issue, you may also carefully check if there are some
> array overflow issues.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>|||Hi, Dieter,
Never mind! I have received your email response. Also, I just get a
response from a person; however he would like to know if my code was same
as yours. I would like to attach my code here and if there is anything
different from yours, please let me know.
========================================
====================================
==
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLPOINTER rgbValue=NULL;
SQLRETURN retcode;
SQLWCHAR ConnStrOut[MAXBUFLEN];
SQLSMALLINT cbConnStrOut = 0;
SQLSMALLINT NumParams, i, DataType, DecimalDigits, Nullable;
SQLUINTEGER ParamSize;
SQLINTEGER ParamLenArray[2];
SQLWCHAR Statement[1000];
WCHAR retstr[10];
SQLWCHAR* PtrArray[2];
/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set the ODBC version environment attribute */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3, 0);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT,(SQLPOINTER*)5, 0);
/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLWCHAR*) L"myWOW", SQL_NTS,
(SQLWCHAR*) L"sa", SQL_NTS,
(SQLWCHAR*) L"Password1!", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Allocate statement handle */
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
/* Process data */
WCHAR strStm[100]=L"INSERT INTO
ANT([ANT].[NAME],[ANT].[DESCRIPTION]) values (?,?)";
wcscpy(Statement,strStm);
retcode = SQLPrepare(hstmt, Statement,
SQL_NTS);
// Check to see if there are any parameters.
If so, process them.
SQLNumParams(hstmt, &NumParams);
if (NumParams) {
for (i = 0; i < NumParams; i++) {
SQLDescribeParam(hstmt, i+1, &DataType,
&ParamSize, &DecimalDigits, &Nullable);
PtrArray[i]=(SQLWCHAR *) malloc(50);
wcscpy(PtrArray[i],L"ABCDE");
SQLBindParameter(hstmt, i + 1,
SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 4,
0, PtrArray[i],
0,&ParamLenArray[i]);
}
// Execute the statement.
retcode = SQLExecute(hstmt);
free(PtrArray[0]);
free(PtrArray[1]);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}
========================================
====================================
================
If it is convenient for you, I recommend that you mail me a test project of
yours and I will forward to him.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Dieter,
I got another two responses from the product team. I would like to forward
them to you for your reference.
========================================
====================================
==
My book "A Guide to the SQL Standard" states this:
The general syntax is:
INSERT INTO table [ (column-commalist) ] source
Where "table" identifies the target table, the identifiers in parentheses
identify some or all of the columns of that table (by their unqualified
column names)
Italics are in the book to emphasize the names must be unqualified. So as
I was discussing with Warren the other day - I don't believe this is valid
syntax and probably should error. Reasonable consumers should not expect
this to work. However, if we have let them get away with it in the past we
may be stuck with some support.
========================================
====================================
==
There is some ambiguity here as to what is semantically correct. As an
example:
INSERT INTO [MyDB].[dbo].[Table1]
([Table1].[col1]
,[Table1].[col2]
,[SomeOtherTableThatMayOrMayNotExist].[col3]
,[Table1].[col4])
VALUES
(?, ?, ?, ?)
One wouldn't normally expect this query to work, yet it doesin fact, you
can put any n-part name on any of these columns in the query list and it
will still work so long as the column specifier identifies a column in the
table specified by the object name portion.
Which brings up some interesting secondary questionsfor instance, what
does OLEDB do with this query string when GetParameterInfo is called? Does
it know to remove the table portion from the column specifier? Should
ODBC/OLEDB error on SQLDescribeParam/GetParameterInfo with this query or
should they silently strip the table portion from the column specifier as
does the engine? I'm on a separate thread with some guys from the engine to
debate these pointswill follow up here once that's known.
========================================
====================================
==
Based on the two responses, I think that it is reasonable that column name
should not be used with table name in ODBC Driver.
Hope this helps.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support

Sunday, March 25, 2012

accessing database problem

Thursday, March 22, 2012

Accessing cubes parallely from 2 clients

accessing asp.net app on client

Accessing April CTP OLAP cube from Excel 2003

Accessing April CTP OLAP cube from Excel 2003

Accessing April CTP OLAP cube from Excel 2003

Accessing April CTP OLAP cube from Excel 2003

Accessing April CTP OLAP cube from Excel 2003

Tuesday, March 20, 2012

Accessing a DSN through a Sql job

Accessing #temp table in a proc as a user with minimal rights.

Monday, March 19, 2012

access violation on sql server 7

Access Violation occurred writing address

Access Violation occurred writing address

Access Violation occurred writing address

Access Violation in SQL Server Native Client

SQL Alert

Blog Archive

About Me