Accessing FTP site from sql server

How to create DTD compatible XML file from result set returned from a query.

Can we access FTP site and upload this xml ?

Is it possible to do from Tsql?

I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.

Hope this helps,

John

|||

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.

Accessing FTP site from sql server

How to create DTD compatible XML file from result set returned from a query.

Can we access FTP site and upload this xml ?

Is it possible to do from Tsql?

I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.

Hope this helps,

John

|||

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.

accessing fake data inside an INSTEAD OF trigger

Hello,
Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?
For example :
UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.
Is there a way to access the value 'abc' in such a situation?
thank you
sNo, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>Hello,
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>For example :
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>Is there a way to access the value 'abc' in such a situation?
>thank you
>s|||OK ... but this data should exist somewhere.
I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?
thank you
Roy Harvey wrote:
> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
> Roy Harvey
> Beacon Falls, CT
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>|||On 3 Mar 2006 07:30:03 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>OK ... but this data should exist somewhere.
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>thank you
Nothing like that I've ever heard of. Or imagined, for that matter.
Roy Harvey
Beacon Falls, CT

Accessing Estimated Query Execution Plan (QEP) statisitics

Hi,

I have a question about estimated query execution plans that are
generated in QA of MSSQL.

If I point at an icon/physical operator in the estimated QEP, it shows
me
some statistics about the operator.
Is there a way to retrieve these statistics through a query, i.e., can
these statistics be available to the user?

Also, is there a way to generate these statistics on my own?

thanks in advance

-TC.T Chaudhary (replysoon_04@.yahoo.com) writes:
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?

Yes. It's all done through SQL statements. Rather than saying the exact
statements to use(*), I will tell you how find out: Use the SQL Server
Profiler to see what commands that Query Analyzer submits to SQL Server
when you have enabled these options.

(*) Since to tell which they are, *I* would have to run Profiler.

> Also, is there a way to generate these statistics on my own?

I'm not sure that I understand the questions, but if I'm guessing right
the answer is: no, you can't.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"T Chaudhary" :
> Hi,
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
> Also, is there a way to generate these statistics on my own?
> thanks in advance
> -TC.

TC,

See SET SHOWPLAN_ALL IN BOL for getting the data you're asking about. To
get the results of your query *and* the plan, see SET STATISTICS PROFILE.

Craig

P.S. Playing with these for a while will give you some real appreciation for
QA! :)

Accessing Different Servers

If I have 2 registered servers, what syntax allows me to access both servers
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"

> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.
|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>
|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt
|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
MattBlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight,
I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid obj
ect name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your quest
ions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

accessing data in different database

Hello Everyone,

Is it possible to write a query that can access data in differnt database so I have "trainee" and "training" databases. In want to access one training table in trainee database through a query. How can I do this.

Thanks.

Hi,

Yes it is possible just use database and scheme name in table name for example:

Database.scheme.tableName

select * from [trainee].[dbo].[training]

select * from [training].[dbo].[training]

both select statement should work from both databases. Be sure that user which run query have rights to access tables you use in your queries in both databases. It is also good to note in code that procedure use another database which has to exists.

Thanks

JPazgier

accessing data from a web page

Hi all,
I need to create a web application.
I will probably need to web pages in this application. On the first
page, I would like to query a Sql table and list the rows returned by the
query in a List.
When user will click on any of the listed rows and click on Edit, I
would like to display the second page which will allow the user to edit the
selected record and save it back into the Sql table.
I know, I can easily create this app using ASP.Net. But the problem is
that the web pages will need to be accessed locally from laptops. IIS will
not be available to serve the pages. Each laptop will have Sql server
installed locally. So my app probably would have to use only
HTML,javascript/vbscript. It can't use ASP/ASP.Net.
I will appreciate it if you can provide me suggestions or links to
tutorials/samples that can help me with this application.
Thanks.Nikhil Patel wrote:
> Hi all,
> I need to create a web application.
> I will probably need to web pages in this application. On the first
> page, I would like to query a Sql table and list the rows returned by
> the query in a List.
> When user will click on any of the listed rows and click on Edit, I
> would like to display the second page which will allow the user to
> edit the selected record and save it back into the Sql table.
> I know, I can easily create this app using ASP.Net. But the
> problem is that the web pages will need to be accessed locally from
> laptops. IIS will not be available to serve the pages. Each laptop
> will have Sql server installed locally. So my app probably would have
> to use only HTML,javascript/vbscript. It can't use ASP/ASP.Net.
> I will appreciate it if you can provide me suggestions or links to
> tutorials/samples that can help me with this application.
> Thanks.
How about writing a fat client app to do this, instead of a web app.
David Gugick
Imceda Software
www.imceda.com|||Nikhil,
If you can't use IIS, then it kills the idea of creating a web solution.
Do you have access to any other tools such as VB/.Net or VC++/.Net? Are the
local laptops on the same domain as the web server? If so, you could add a
header for the website in IIS and build/serve the pages as you normally woul
d.
"Nikhil Patel" wrote:

> Hi all,
> I need to create a web application.
> I will probably need to web pages in this application. On the first
> page, I would like to query a Sql table and list the rows returned by the
> query in a List.
> When user will click on any of the listed rows and click on Edit, I
> would like to display the second page which will allow the user to edit th
e
> selected record and save it back into the Sql table.
> I know, I can easily create this app using ASP.Net. But the problem is
> that the web pages will need to be accessed locally from laptops. IIS will
> not be available to serve the pages. Each laptop will have Sql server
> installed locally. So my app probably would have to use only
> HTML,javascript/vbscript. It can't use ASP/ASP.Net.
> I will appreciate it if you can provide me suggestions or links to
> tutorials/samples that can help me with this application.
> Thanks.
>
>|||Thanks. But this application has to be a web app. because it is inteded to
be used in a browser of a third party app.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eLjje1gCFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Nikhil Patel wrote:
> How about writing a fat client app to do this, instead of a web app.
> --
> David Gugick
> Imceda Software
> www.imceda.com

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?
Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/de..._adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible
?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
to
> execute a query on SERVER_A referencing a table on SERVER_B is that possib
le?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/d... />
a_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/d...>
dda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:
> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> > If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> > to
> > execute a query on SERVER_A referencing a table on SERVER_B is that
> > possible?
> >
> > Example
> >
> > First Server: SERVER_A, Database: Nuts, Table: Goodness
> >
> > Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> >
> > From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> > where I need to get some data from SERVER_A , database 'Nuts', table
> > 'Goodness'.
> >
> > Is that possible? How can I do it? Would I use a linked server? If so can
> > anyone show me a good resource on how to set up a linked server?
>
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
John
"ipramod@.gmail.com" wrote:
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.
Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default...b;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>
|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegro ups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/defaul...kb;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing columns using fully qualified name from a Join

1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
JDBC, Service Pack 2, Version 2.2.0037
2) The query I'm executing is:
select * from employees, dept, city where dept.dpid=employees.dept and " +
"employees.cityofresidence=city.cid order by dept.dpid, empid
3) city and dept tables have a field called 'name'
4) rs.getString("name") gives dept.name (city.name if city appears before
dept in the FROM clause of the query)
5) rs.getString("city.name") throws exception - 'Invalid column name'
How can I use the fully qualified column name to retrieve correct values
irrespective of the order of the appearance of the table names in the FROM
clause of the query
Note: rs.getString(int col_num) always retrieves correct values.
praskam wrote:

> 1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
> JDBC, Service Pack 2, Version 2.2.0037
> 2) The query I'm executing is:
> select * from employees, dept, city where dept.dpid=employees.dept and " +
> "employees.cityofresidence=city.cid order by dept.dpid, empid
> 3) city and dept tables have a field called 'name'
> 4) rs.getString("name") gives dept.name (city.name if city appears before
> dept in the FROM clause of the query)
That is correct, per JDBC spec.

> 5) rs.getString("city.name") throws exception - 'Invalid column name'
The data/metadata that come back from the DBMS do not contain any reference to the
table from which a column came.

> How can I use the fully qualified column name to retrieve correct values
> irrespective of the order of the appearance of the table names in the FROM
> clause of the query.
You can't. Unless you explicitly define a label for each column in the SQL.
That would involve replacing "select *" with select t1.col1 'table1.col1', t1.col2 'table1.col2' ...
t2.col1 'table2.col1' etc. from table1 t1, table2 t2 etc.
Note that you can use any string for a column label. Using "table1.col1" would suit your
request, but you could tailor it as you see fit.
Joe Weinstein at BEA

> Note: rs.getString(int col_num) always retrieves correct values.
As expected, and is the most reliable.
>

accessing another database in a stored procedure

hi

i have stored procedure and i need to access another database in my stored procedure

I'm going to query a table which is located in another database

as you know it is impossible to use the USE database keyword in stored procedures

so what should I do?

thanks.

create myProc
as

select someStuff from otherDb.dbo.otherTable
.....

just qualify the table in the other db fully with the databasename.

/Kenneth

|||Not to mention permissions...the user executing the stored procedure should have permissions to the other database|||Hi

You specify a different database on the same sql server within a stored procedure using the format:
[databasename].dbo.TableName

for example

select [Users].dbo.Addresses

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
>