Collation problem: object

Showing posts with label object. Show all posts

Sunday, March 11, 2012

COM objects under 64-bit?

Hi everyone,

Primary platform is Framework 2.0 under 32-bit

Production platform will be Framework 2.0 under 64-bit.

I'm concerned about what object should I use in order to launch DTS 2000/7.0 from our .Net service.

Currently it tested fine from a 32-bit environment (sql server 2005 as backend).

Reference: "Microsoft DTSPackage Object Library"

Physical file: dtspkg.dll

My wonder, is there any limitation when it's gonna to work on 64-bit?

Generally speaking, any limitation/issue/drawback for COM objects under 64 bit?

In my project properties appears Any CPU, x86, x64. I've got now Any CPU.

Thanks for your advices,

Well, anyway, in any case. I assume that it'd provoke lots of pitfalls, drawbacks and so on.

So then, could I use assemblies in order to launch DTS 2000?

(Microsoft.SqlServer.Dts.Runtime.Application)

Thanks again,

|||Do you mean Microsoft.SqlServer.Dts.Runtime namespace from SQL Server 2005? If yes, it can't run DTS 2000 packages, it can only run SSIS packages.

The DTS 2000 only exists in 32-bit version, you can use it on 64-bit OS from 32-bit processes run in WOW64.|||

Hi Michael,

Thanks a lot for your information.

So that, could I deduce that such COM component will work fine on our Window Server 2003 Enterprise x64 Edition?

Thanks again.

|||

hi,

is it so?

|||They will work on x64, but your application has to be 32-bit.

So instead of Any CPU, compile it for x86 - it will then run in WOW64 and be able to access DTS COM objects (provided they are installed of course).|||

No, my project got a "ANYCPU". Which is the problem?

Upload dtspkg.dll to the cluster. Or anything else?

|||If you compile for AnyCPU, the executable will run as 64-bit executable on x64 machine. You can only access 32-bit DLLs from 32-bit processes, so you would not have access to DTS objects if executable is compiled for AnyCPU. Your executable has to be compiled for x86 - then it will run in WOW64 on x64 machine (i.e. as 32-bit process, with access to 32-bit COM objects).
I think it is more than one DLL, see redistributable docs in SQL 2000 docs for list of files that are might and should be copied, and proper installation instructions.|||

Thanks for that. I'll keep on mind.

So that, leaving as 'Any CPU' it'd caused a pitfall..

But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...

What a pity!! What could I do in order to fix that?

AFAIK is just a dtspkg.dll, at least when I run my project from VS. VS take dtspkg.dll and it's created (suppose) a RCW called

as "Interop.DTS.dll"

Thanks again.

|||

enric vives wrote:

But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...
What a pity!! What could I do in order to fix that?

Move to SSIS of course!

|||

Ok, Michael, I'm totally agree with you, but listen to me:

We've got around 500 dts "live" up and just ten SSIS running on-production in order to cover our business as usual. As time goes by dts will be transformed into .DTSX, of course!

Currently have two services: one which throws exclusively dts 2000 and the new one which is that we're discussing at (both ssis and dts 2000)

But, thanks indeed.

|||

DTS comming from SQL Server 2000, never mind 7.0, has never been supported on 64-bit. The initial engine support for 64-bit (Liberty?) only came along after the SQL Server 2000 had been relased, and clearly decsion was made that only certain parts of the product would be ported. Forward compatability on hardware is rather a challenge I'd imagine, and going back to add new hardware support for old components like DTS would clearly have impacted the delivery of SQL 2005. MS have been clear about this strategy, so that seems fair to me, you cannot expect old products to be constantly updated to keep pace with new hardware or operating systems. I like my Windows 2003 64-bit over NT4 thanks.

Also think about what adding 64-bit support means to something like a data engine, you need to do some fairly major work around memory managent to allow it to scale over all that nice memory 64-bit gives us. They did that work, and found there was no realistic way forward to make the orginal code base scale, so they wrote the SSIS pipeline. You will often hear variations on that point when speaking to team members at conferences and the like.

Every few genartions in software refresh cycles you get a step change like this. The best thing that has happend is that DTS is still there and supported, so DTS just keeps on working. If your DTS solutions no longer scale, then it seems fair to accept that some re-work will be required, although I would agree that this step change is quite significant, but then so are the benefits and that seems like the trade off.

|||

hi Darren,

No, no I'm not complaining about Microsoft's philosophy or regarding ssis at all. I'm complaining about tight dates and about the fact that I'll have to run my service as 32 bit only for keep in mind old ETL.

SSIS is a very good tool!

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
I just answered my own question about 5 minutes after posting. The sp_OAxxx stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
|||Look up OLE Automation in BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?
|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison|||Look up OLE Automation in BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored
procedure and call methods, etc, but now I can't remember how I did it. Can
someone point me in the right direction?I just answered my own question about 5 minutes after posting. The sp_OAxxx
stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored proced
ure and call methods, etc, but now I can't remember how I did it. Can someone point
me in the right direction?|||Look up OLE Automation in BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not re

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server (where SQL Server is not installed), I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

I tried every thing I could find on the web but with no luck.

Can someone please help me?
Many thanks in advance for your help.

A COM error means that something was not registered, so I am guessing the product SQL 2005 or DTS part was not installed in that computer, did you try to reinstall SQL 2005?|||

Your error is explained in the link below and I know the plain way to deploy DTS so if you decide to change your means of deployment post again so I can help. Hope this helps.

http://www.velocityreviews.com/forums/t93159-com-object-with-clsid-10020200eb1c11cfae6e00aa004a34d5-is-either-not-valid-or-not-registered.html

|||

Hiya guys.

Sorry for the late update!!!

Special thanks to Caddre!

Just want to give an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job (or use impersonation).

Hope this helps.

COM object from Store Procedure

Hello there
Can i use a COM object from Store Procedure?
Thanks-
--
<Harvey Triana />Harvey Triana wrote:

> Hello there
> Can i use a COM object from Store Procedure?
> Thanks-
> --
> <Harvey Triana />
In SQL Server 2000 take a look at the sp_OA procs in Books Online:
sp_OACreate, sp_OAMethod, etc.
In SQL Server 2005 the preferred alternative would be to call a .NET
class from a proc.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Dont forget to use sp_OADestroy to destroy the object when you are
done. We have seen memory leak issues when we didnt use sp_OADestroy.
SAI|||Thank a lot-
yes, i know kindnesses of 2005 version.
But, i want testing SQL Server 200/COM vs SQL Server 2005/VB.NET in this
plan (I s

the best efficiency)
<Harvey Triana />
Drilling View Developer (52 oil wells in 2005)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1138724769.911891.36830@.o13g2000cwo.googlegroups.com...
> Harvey Triana wrote:
>
> In SQL Server 2000 take a look at the sp_OA procs in Books Online:
> sp_OACreate, sp_OAMethod, etc.
> In SQL Server 2005 the preferred alternative would be to call a .NET
> class from a proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

COM Interops on MTA's...

Greetings!
I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
application... I'm currently getting an error when I try to use this object
saying:
System.InvalidCastException: Unable to cast COM object of type
'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error:
No such interface supported (Exception from HRESULT: 0x80004002
(E_NOINTERFACE)).
at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
pbstrConnectionString)
at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
62
at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
Now I'm guessing that this COM object does support multiple threads.
So the question is how to get around this. Would wrapping this function in a
singleton, for example, do the trick? Or maybe having a static function in
an object?
What would be the best approach?
thanks for your time.
Daniel."Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OC2INWwwHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Greetings!
> I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
> application... I'm currently getting an error when I try to use this
> object saying:
> System.InvalidCastException: Unable to cast COM object of type
> 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
> 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
> QueryInterface call on the COM component for the interface with IID
> '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following
> error: No such interface supported (Exception from HRESULT: 0x80004002
> (E_NOINTERFACE)).
> at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
> pbstrConnectionString)
> at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
> in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
> 62
> at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
> C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
>
> Now I'm guessing that this COM object does support multiple threads.
> So the question is how to get around this. Would wrapping this function in
> a singleton, for example, do the trick? Or maybe having a static function
> in an object?
> What would be the best approach?
> thanks for your time.
> Daniel.
Make sure your thread is initilaize to enter a STA before starting (see
SetApartmentState(ApartmentState.STA) in System.Threading).
Willy.

>|||Willy,
Thanks for your reply.
In my application I dynamically create multiple threads according to some
configuration. At this point in the application the knowledge doesn't exist
as to whether the thread that is created will use the COM object... Is it
recommended that I just mark each thread as STA?
For Each myObj As MyObjectType In myObjects
' launch a seperate thread for each configured message flow
Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
t.Name = myObj.myName
t.SetApartmentState(ApartmentState.STA) // <-- ' Is
this okay '
MyThreadHandler.Instance.AddThread(t)
t.Start(myParameter)
Next
Cheers.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Willy,
> Thanks for your reply.
> In my application I dynamically create multiple threads according to some
> configuration. At this point in the application the knowledge doesn't
> exist as to whether the thread that is created will use the COM object...
> Is it recommended that I just mark each thread as STA?
> For Each myObj As MyObjectType In myObjects
> ' launch a seperate thread for each configured message flow
> Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
> t.Name = myObj.myName
> t.SetApartmentState(ApartmentState.STA) // <-- ' Is
> this okay '
> MyThreadHandler.Instance.AddThread(t)
> t.Start(myParameter)
> Next
> Cheers.
> Dan.
>
Dan, The apartment state is a COM only thing, it doesn't affect .NET. An STA
apartment is valid for single and apartment threaded COM object types, so in
your case it is safe to initialize your threads to enter an STA .
Willy.|||"Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan, The apartment state is a COM only thing, it doesn't affect .NET. An
> STA apartment is valid for single and apartment threaded COM object types,
> so in your case it is safe to initialize your threads to enter an STA .
> Willy.
>
Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
like a bogus message. My process spends a lot of time looping around in
other parts of the code while polling which suggests the reason for this
error coming up.
Thanks for your help.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OiEYjv7wHHA.3444@.TK2MSFTNGP05.phx.gbl...
> "Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
> news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> like a bogus message. My process spends a lot of time looping around in
> other parts of the code while polling which suggests the reason for this
> error coming up.
> Thanks for your help.
> Dan.
>
This is not necessarily bogus message, don't take this too lightly. One
possible reason for the MDA is that your thread fails to pump the message
queue, if that's the case you will have problems whenever the finalizer
needs to release the COM object references you are holding in this thread,
this will block the finalizer thread with as a result that finalizable
objects stay in memory, finally leading into OOM and other kind of failures.
Willy.|||>> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> This is not necessarily bogus message, don't take this too lightly. One
> possible reason for the MDA is that your thread fails to pump the message
> queue, if that's the case you will have problems whenever the finalizer
> needs to release the COM object references you are holding in this thread,
> this will block the finalizer thread with as a result that finalizable
> objects stay in memory, finally leading into OOM and other kind of
> failures.
> Willy.
>
I've kept the process running, keeping an eye on memory and handles, and it
appears to destory and release the COM object correctly as there's no
increase in the resources used.
How would I check whether I'm "failing to pump the message queue"? This is
not a GUI app, but a backend service. Is there any way in .Net to check the
COM reference count?
I do explicitly disgard the object after creating and using...
' clear up
System.Runtime.InteropServices.Marshal.ReleaseComObject(bulkLoadComObject)
bulkLoadComObject = Nothing
Thanks.
Daniel.

Saturday, February 25, 2012

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
SteenAre you on May CTP? When I drag the columns folder to the query window, I get the columns listed in
the same order as in my CREATE TABLE statement...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to just click the "Column"
> header in the Object browser window and then drag it over to the "code pane". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in the object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns in alphabetically order
> when I drag them over, eventhough they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can
>> be changed somewhere?
>> Regards
>> Steen|||Strange. I don't have a 2000 to test against. Perhaps there is a difference. I would report this to
the beta forums if I were you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the columns are listed in the
> "correct" order which is the order they have been typed in and not alphabetically.
> Regards
> Steen
> Tibor Karaszi wrote:
>> Are you on May CTP? When I drag the columns folder to the query
>> window, I get the columns listed in the same order as in my CREATE
>> TABLE statement...
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can be changed somewhere?
>> Regards
>> Steen
>|||This is a multi-part message in MIME format.
--040307040208010607010408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:
>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have been
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>>Are you on May CTP? When I drag the columns folder to the query
>>window, I get the columns listed in the same order as in my CREATE
>>TABLE statement...
>>"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>>news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>>
>>Hi
>>When I want to use all (or at least many) columns in a table, I'm
>>used to just click the "Column" header in the Object browser window
>>and then drag it over to the "code pane". In Query Analyzer I was
>>use to get all the columns in the same order as they where showed in
>>the object browser to the left. When using MicroSoft SQL Server
>>Mamagement Studio, I get the columns in alphabetically order when I
>>drag them over, eventhough they are shown in another order in the
>>Object Browser window. Does anybody know if this is a setting that can
>>be changed somewhere?
>>Regards
>>Steen
>>
>
>
--040307040208010607010408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've got the June CTP and I see the same behaviour with SSMS &
a SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions. 
</tt>
<div class="moz-signature">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Steen Persson (DK) wrote:
<blockquote
cite="miduMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com"
type="cite">
<pre wrap="">Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Are you on May CTP? When I drag the columns folder to the query
window, I get the columns listed in the same order as in my CREATE
TABLE statement...
"Steen Persson (DK)" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:spe@.REMOVEdatea.dk"><spe@.REMOVEdatea.dk></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com">news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi
When I want to use all (or at least many) columns in a table, I'm
used to just click the "Column" header in the Object browser window
and then drag it over to the "code pane". In Query Analyzer I was
use to get all the columns in the same order as they where showed in
the object browser to the left. When using MicroSoft SQL Server
Mamagement Studio, I get the columns in alphabetically order when I
drag them over, eventhough they are shown in another order in the
Object Browser window. Does anybody know if this is a setting that can
be changed somewhere?
Regards
Steen
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040307040208010607010408--|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Friday, February 24, 2012

Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!

Thursday, February 16, 2012

column description in tables design

Using SS2000. When we design a new table in EM, we often put text in the
description field for each column. We tried copying the table as an object
via DTS to a new server/database and everything seems to transfer except the
descriptions.
Is there a way to transfer the descriptions? Where are they stored? I can
run this query to see what the descriptions are but I can't look inside the
function to see where it's pulling the info from.
SELECT objname, value
FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
'tblleads_branch', 'column', null)
Thanks,
--
Dan D.I have not tested it but the DTS Import/Export Wizard, when you select Copy
objects and data between SQL Server databases, has the choice to include
extended properties, unchecked by default. Just check that box and that
should work.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dan D." wrote:
> Using SS2000. When we design a new table in EM, we often put text in the
> description field for each column. We tried copying the table as an object
> via DTS to a new server/database and everything seems to transfer except the
> descriptions.
> Is there a way to transfer the descriptions? Where are they stored? I can
> run this query to see what the descriptions are but I can't look inside the
> function to see where it's pulling the info from.
> SELECT objname, value
> FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> 'tblleads_branch', 'column', null)
> Thanks,
>
> --
> Dan D.|||It did. Thanks.
--
Dan D.
"Ben Nevarez" wrote:
> I have not tested it but the DTS Import/Export Wizard, when you select Copy
> objects and data between SQL Server databases, has the choice to include
> extended properties, unchecked by default. Just check that box and that
> should work.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Dan D." wrote:
> > Using SS2000. When we design a new table in EM, we often put text in the
> > description field for each column. We tried copying the table as an object
> > via DTS to a new server/database and everything seems to transfer except the
> > descriptions.
> >
> > Is there a way to transfer the descriptions? Where are they stored? I can
> > run this query to see what the descriptions are but I can't look inside the
> > function to see where it's pulling the info from.
> >
> > SELECT objname, value
> > FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> > 'tblleads_branch', 'column', null)
> >
> > Thanks,
> >
> >
> > --
> > Dan D.

Sunday, March 11, 2012

COM objects under 64-bit?

COM Objects in Stored Procedures?

COM Objects in Stored Procedures?

COM Objects in Stored Procedures?

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not re

COM object from Store Procedure

COM Interops on MTA's...

Saturday, February 25, 2012

Column order issue

Column order issue

Column order issue

Friday, February 24, 2012

Column index

Thursday, February 16, 2012

column description in tables design

Collation problem

Blog Archive

About Me