Getting the place number for a specifik ID, from a select list

Heres the thing

Im making a booking application where it is possible to be put on standby. When you book more than there is room fore, you will be put on standby. At the same time there should be a field in the databaserow that will be set to true if you start out with being put on standby. The order of the bookings is being set by the logdate, the one who books first gets the higher place.

When you book, my idea was to first insert the data, then check to see if the place of the booking has exceeded the maximum number

Im coding in vb2005 and can easily make it by coding with the sql:

Sql= "Select nr from booking where date=@.date and ClassID=" & theID

And then make a loop with a reader ( I havent inserted any of basic code in this example!)

while reader.read

X+= 1

If reader.item("Nr") = Thenumber then exit while

End while

If x> Max number then do the Update where Standby=true

But i would like to make it simpler with just making a simple call to database and not making a list to read. Ive put in the vb example to just explain what i would like to do..

If anybody have a better idea it is very welcome!!

Dan, you might try something like this...

insert into Booking
select flight_num, reservation_num,
case
when
(
Select Count(*)
From Booking
Where New.flight_num = Booking.flight_num
) >= MaximumSeats then 'Standby'
else 'Reserved'
end as reservation_status
From NewReservations New

The subquery in the case-when-else construct will determine the number of seats already on the Booking table, and insert the record by placing Standy or Reserved in the reservation_status column . The MaximumSeats for the flight needs to be known, also...

Not sure if you store the new apps in a separate table before inserting into your reservation table, but should give you an idea...

|||

You can use the following Logic...

Update/Insert query .. Where @.RequestedNumber <= (Select Count(Room) From booking Where date=@.date and ClassID=@.ClassID);

Select Case When @.@.RowCount <> 0 Then 'Updated' Else 'StandBy' End as Status

sql

Getting Started

Hi
I have created a basic report.
Now I am looking for options to send in some parameters to the report as
in a url to call from a web application, which can be applied on the query.
Also rendering in pdf format.
Any help would be appreciable.
Thanks!
SandyEverything you need to know:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_urlaccess_374y.asp?frame=true
Kulgan.

Getting SQL database structure from here to there

I'm sure there is a simple way to do this but I cannot think of it.
I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.You might be able to ftp the actual raw mdf/ldf files up to the server, then attach these files to your db.
|||Thanks a ton. Thatwould be the perfect solution. Unfortunately, I don't see any kind of access to the attach command on their control panel. I will check again though. What about running scripts to create the tables, stored procedures, views etc.? Do you think that could work?|||Ok I think I misunderstood you...if you only need the schema (and notthe data itself) then you can script out the database an an .sql fileand then execute the resulting sql code on your hosting server and theschema will be created.
|||If you need to script the Data you can try my tool SQL Inserter. Its on GotDotnet
http://www.gotdotnet.com/workspaces/workspace.aspx?id=17ec0d2c-c29a-4eb3-83d5-b1c58bb32a78
Mathias|||

Thanks to everyone for responding. The script method seems to be the best one for me and is working so far.

Getting rid of unused indexes - sql server 2000

Hi,
I'm analyzing index configuration in one large database (800GB). I am not
familiar that much with the application. Since I suspected that bunch of
indexes are not being used at all (for most of "suspicious" composite
indexes, less selective column is listed first - one of the things that makes
me think what I think), I ran the trace, and included only "execution plan"
event in it. I imported all these trace files into table, and I ran couple of
reports against it. I believe that I got confirmation for my initial thought
but first I want to double check with you guys:
For most of "suspicious" indexes (I observed only large tables) I found only
"index delete" operations. For example, for one of these indexes, in all
execution plans for that day, there was only this (as part of one of many
execution plans):
|--Index
Delete(OBJECT[products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
So no "Index Scan", no "Index Seek" operations for the the given index
(IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
suspect) part of every day purging process. So when they run purging script
for the table, all indexes are listed within execution plan with "index
delete" operation.
I was just wondering if I missed something? Does this idea sound reasonable?
Thanks,
Pedja
P.S. I do take into account weekly&monthly tasks which were not included
into execution plans that day...
Pedja
So what is the question?
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that
> makes
> me think what I think), I ran the trace, and included only "execution
> plan"
> event in it. I imported all these trace files into table, and I ran couple
> of
> reports against it. I believe that I got confirmation for my initial
> thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found
> only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT[products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> (I
> suspect) part of every day purging process. So when they run purging
> script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound
> reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...
|||In SQL Server 2005 you can find unused or used indexes and type of
index use in details.
take a look at
http://shahamishm.tripod.com/id1.html
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:[vbcol=seagreen]
> Pedja
> So what is the question?
> http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
|||amish
SS2005? He are asking about SS2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1166513986.026176.182400@.j72g2000cwa.googlegr oups.com...
> In SQL Server 2005 you can find unused or used indexes and type of
> index use in details.
> take a look at
> http://shahamishm.tripod.com/id1.html
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>

Getting rid of unused indexes - sql server 2000

Hi,
I'm analyzing index configuration in one large database (800GB). I am not
familiar that much with the application. Since I suspected that bunch of
indexes are not being used at all (for most of "suspicious" composite
indexes, less selective column is listed first - one of the things that make
s
me think what I think), I ran the trace, and included only "execution plan"
event in it. I imported all these trace files into table, and I ran couple o
f
reports against it. I believe that I got confirmation for my initial thought
but first I want to double check with you guys:
For most of "suspicious" indexes (I observed only large tables) I found only
"index delete" operations. For example, for one of these indexes, in all
execution plans for that day, there was only this (as part of one of many
execution plans):
|--Index
Delete(OBJECT[products].[dbo].[ProdTrans].[IX_ProdTrans_Da
ys_Type]))
So no "Index Scan", no "Index Seek" operations for the the given index
(IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
suspect) part of every day purging process. So when they run purging script
for the table, all indexes are listed within execution plan with "index
delete" operation.
I was just wondering if I missed something? Does this idea sound reasonable?
Thanks,
Pedja
P.S. I do take into account weekly&monthly tasks which were not included
into execution plans that day...Pedja
So what is the question?
http://www.sql-server-performance.c...ate_indexes.asp
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that
> makes
> me think what I think), I ran the trace, and included only "execution
> plan"
> event in it. I imported all these trace files into table, and I ran couple
> of
> reports against it. I believe that I got confirmation for my initial
> thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found
> only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT[products].[dbo].[ProdTrans].[IX_ProdTrans_
Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> (I
> suspect) part of every day purging process. So when they run purging
> script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound
> reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...|||In SQL Server 2005 you can find unused or used indexes and type of
index use in details.
take a look at
http://shahamishm.tripod.com/id1.html
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:[vbcol=seagreen]
> Pedja
> So what is the question?
> http://www.sql-server-performance.c...ate_indexes.asp
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...|||amish
SS2005? He are asking about SS2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1166513986.026176.182400@.j72g2000cwa.googlegroups.com...
> In SQL Server 2005 you can find unused or used indexes and type of
> index use in details.
> take a look at
> http://shahamishm.tripod.com/id1.html
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>|||> I was just wondering if I missed something? Does this idea sound reasonabl
e?
Yes, your reasoning seems reasonable to me.
Just to be sure, do some analysis on some index that *is* used and make sure
you *do* find scan
and/or seek operations against that index. Just so you don't miss out all in
dex usage in your
analysis (you never know...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that ma
kes
> me think what I think), I ran the trace, and included only "execution plan
"
> event in it. I imported all these trace files into table, and I ran couple
of
> reports against it. I believe that I got confirmation for my initial thoug
ht
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found on
ly
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT[products].[dbo].[ProdTrans].[IX_ProdTrans_
Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (
I
> suspect) part of every day purging process. So when they run purging scrip
t
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound reasonabl
e?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...|||Thanks Tibor,
I did check other indexes, lots of index seeks in ex plans...
Thanks again for your opinion.
Pedja
"Tibor Karaszi" wrote:

> Yes, your reasoning seems reasonable to me.
> Just to be sure, do some analysis on some index that *is* used and make su
re you *do* find scan
> and/or seek operations against that index. Just so you don't miss out all
index usage in your
> analysis (you never know...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
>sql

Getting rid of unused indexes - sql server 2000

Hi,
I'm analyzing index configuration in one large database (800GB). I am not
familiar that much with the application. Since I suspected that bunch of
indexes are not being used at all (for most of "suspicious" composite
indexes, less selective column is listed first - one of the things that makes
me think what I think), I ran the trace, and included only "execution plan"
event in it. I imported all these trace files into table, and I ran couple of
reports against it. I believe that I got confirmation for my initial thought
but first I want to double check with you guys:
For most of "suspicious" indexes (I observed only large tables) I found only
"index delete" operations. For example, for one of these indexes, in all
execution plans for that day, there was only this (as part of one of many
execution plans):
|--Index
Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
So no "Index Scan", no "Index Seek" operations for the the given index
(IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
suspect) part of every day purging process. So when they run purging script
for the table, all indexes are listed within execution plan with "index
delete" operation.
I was just wondering if I missed something? Does this idea sound reasonable?
Thanks,
Pedja
P.S. I do take into account weekly&monthly tasks which were not included
into execution plans that day...Pedja
So what is the question?
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that
> makes
> me think what I think), I ran the trace, and included only "execution
> plan"
> event in it. I imported all these trace files into table, and I ran couple
> of
> reports against it. I believe that I got confirmation for my initial
> thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found
> only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> (I
> suspect) part of every day purging process. So when they run purging
> script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound
> reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...|||In SQL Server 2005 you can find unused or used indexes and type of
index use in details.
take a look at
http://shahamishm.tripod.com/id1.html
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:
> Pedja
> So what is the question?
> http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> > Hi,
> > I'm analyzing index configuration in one large database (800GB). I am not
> > familiar that much with the application. Since I suspected that bunch of
> > indexes are not being used at all (for most of "suspicious" composite
> > indexes, less selective column is listed first - one of the things that
> > makes
> > me think what I think), I ran the trace, and included only "execution
> > plan"
> > event in it. I imported all these trace files into table, and I ran couple
> > of
> > reports against it. I believe that I got confirmation for my initial
> > thought
> > but first I want to double check with you guys:
> > For most of "suspicious" indexes (I observed only large tables) I found
> > only
> > "index delete" operations. For example, for one of these indexes, in all
> > execution plans for that day, there was only this (as part of one of many
> > execution plans):
> >
> > |--Index
> > Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> >
> > So no "Index Scan", no "Index Seek" operations for the the given index
> > (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> > (I
> > suspect) part of every day purging process. So when they run purging
> > script
> > for the table, all indexes are listed within execution plan with "index
> > delete" operation.
> >
> > I was just wondering if I missed something? Does this idea sound
> > reasonable?
> >
> > Thanks,
> > Pedja
> >
> > P.S. I do take into account weekly&monthly tasks which were not included
> > into execution plans that day...|||amish
SS2005? He are asking about SS2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1166513986.026176.182400@.j72g2000cwa.googlegroups.com...
> In SQL Server 2005 you can find unused or used indexes and type of
> index use in details.
> take a look at
> http://shahamishm.tripod.com/id1.html
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>> Pedja
>> So what is the question?
>> http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
>>
>> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
>> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
>> > Hi,
>> > I'm analyzing index configuration in one large database (800GB). I am
>> > not
>> > familiar that much with the application. Since I suspected that bunch
>> > of
>> > indexes are not being used at all (for most of "suspicious" composite
>> > indexes, less selective column is listed first - one of the things that
>> > makes
>> > me think what I think), I ran the trace, and included only "execution
>> > plan"
>> > event in it. I imported all these trace files into table, and I ran
>> > couple
>> > of
>> > reports against it. I believe that I got confirmation for my initial
>> > thought
>> > but first I want to double check with you guys:
>> > For most of "suspicious" indexes (I observed only large tables) I found
>> > only
>> > "index delete" operations. For example, for one of these indexes, in
>> > all
>> > execution plans for that day, there was only this (as part of one of
>> > many
>> > execution plans):
>> >
>> > |--Index
>> > Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
>> >
>> > So no "Index Scan", no "Index Seek" operations for the the given index
>> > (IX_ProdTrans_Days_Type) at all - whole day! These delete operations
>> > are
>> > (I
>> > suspect) part of every day purging process. So when they run purging
>> > script
>> > for the table, all indexes are listed within execution plan with "index
>> > delete" operation.
>> >
>> > I was just wondering if I missed something? Does this idea sound
>> > reasonable?
>> >
>> > Thanks,
>> > Pedja
>> >
>> > P.S. I do take into account weekly&monthly tasks which were not
>> > included
>> > into execution plans that day...
>|||> I was just wondering if I missed something? Does this idea sound reasonable?
Yes, your reasoning seems reasonable to me.
Just to be sure, do some analysis on some index that *is* used and make sure you *do* find scan
and/or seek operations against that index. Just so you don't miss out all index usage in your
analysis (you never know...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that makes
> me think what I think), I ran the trace, and included only "execution plan"
> event in it. I imported all these trace files into table, and I ran couple of
> reports against it. I believe that I got confirmation for my initial thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
> suspect) part of every day purging process. So when they run purging script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...

Getting Report page Count

Hi All
We have an ASP.NET application that lists all the reports hosted in our SQL
RS by using RS webservices. When user selects on a particular report from the
list the corresponding parameters get shown again using web service calls.
After the selection of parameters, the user gets to view the report in the
report viewer control. We have also designed a custom tool bar complete with
export options and with prev/next functionalities. In order to get the
pagecount we are calling the render method and get the streamid count. We are
passing the device info for Image i.e.
<DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
however is very random and does not match the actual total page count of the
report.
Can anyone please help or guide here...
Thanks !
PRI just wanted to add that the report gets shown in the reportviewer by URL.
The render method is used to purely generate the pagecount. Shouldn't there
be an easier way...
"PR" wrote:
> Hi All
> We have an ASP.NET application that lists all the reports hosted in our SQL
> RS by using RS webservices. When user selects on a particular report from the
> list the corresponding parameters get shown again using web service calls.
> After the selection of parameters, the user gets to view the report in the
> report viewer control. We have also designed a custom tool bar complete with
> export options and with prev/next functionalities. In order to get the
> pagecount we are calling the render method and get the streamid count. We are
> passing the device info for Image i.e.
> <DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
> however is very random and does not match the actual total page count of the
> report.
> Can anyone please help or guide here...
> Thanks !
> PR

Getting replication to work on Windows 2003 Server X64 environment using SQL 2000

I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!

I'm having issues getting this to work under Windows Server 2003 X64.

I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....

My question is: Were do I get the X64 version of these tools?

sqlce30setupen.msi
sql2Ken@.P4.msi

The SQL environment is X86 as follows: SQL2000 SP4

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Any help would be much appreciated!

I am sorry here. Please read http://support.microsoft.com/?kbid=912430 for more details. To conclude, SQL Mobile server tools do not work on 64-bit. So, the solution for you in this case is that have IIS box and SQL Server box separately. SQL Server in your case would be 64-bit and IIS would be 32-bit. Let me know if you need more details.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Is there any prospect of Microsoft re-compiling these toools to work under x64 environment?|||

We are really sorry for that. It is not just compiling but a lot more when comes to making the code work for both 32-bit and 64-bit. Is there any problem in the proposed solution. I am really interested to know and solve any issue you may face.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||The only other option open to me at this point is placing the tools on a x86 machine running sharepoint server, I believe that this may also be a challenge?|||

I am wondering why you suddenly require share point here! May be I did not understand your question. But, from replication perspective you donot need Microsoft Share Point software.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

Getting replication to work on Windows 2003 Server X64 environment using SQL 2000

I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!

I'm having issues getting this to work under Windows Server 2003 X64.

I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....

My question is: Were do I get the X64 version of these tools?

sqlce30setupen.msi
sql2Ken@.P4.msi

The SQL environment is X86 as follows: SQL2000 SP4

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Any help would be much appreciated!

I am sorry here. Please read http://support.microsoft.com/?kbid=912430 for more details. To conclude, SQL Mobile server tools do not work on 64-bit. So, the solution for you in this case is that have IIS box and SQL Server box separately. SQL Server in your case would be 64-bit and IIS would be 32-bit. Let me know if you need more details.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Is there any prospect of Microsoft re-compiling these toools to work under x64 environment?|||

We are really sorry for that. It is not just compiling but a lot more when comes to making the code work for both 32-bit and 64-bit. Is there any problem in the proposed solution. I am really interested to know and solve any issue you may face.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||The only other option open to me at this point is placing the tools on a x86 machine running sharepoint server, I believe that this may also be a challenge?|||

I am wondering why you suddenly require share point here! May be I did not understand your question. But, from replication perspective you donot need Microsoft Share Point software.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

Getting meta-data for Linked Servers

My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.

Does SQL Server expose Linked Server meta data?

How would one retrieve this meta data if it is exposed?

Thanks,

Ben

Hi Ben,

No SQL Server does not store metadata about linked servers (excluding connection information stored in sys.servers/sysservers). You would need to interrogate the actual linked server for this. So, if it were MSAccess, you would need to refer to the system tables (such as MSysObjects) etc.

Cheers,

Rob

|||

I was afraid that was the answer.

Thanks

getting login id in trigger

I have a web application which connects to Microsoft SQL Server 2000 through
JDBC-ODBC Driver. The application server is JBoss and I am using connection
pooling.
When the application connects to the database it provides userid and
password which are 'sa' and 'password' respectively. They are constants for
all users. The user also type in his/her own login id which I stored in the
HTTPSession.
Problem is my triggers wants to get that login id. Is it possible?
Thanks
RizwanHi
You should not be using 'sa' as the login to SQL Server as this may be too
privileged.
The users login id is only used as authentication mechanism, therefore you
will either need to pass it as part of each call to the query/stored
procedures or possibly generate some kind of session token and pass that and
then use the session token as a link to the login.
John
"Rizwan" <hussains@.pendylum.com> wrote in message
news:5Ozce.14173$gA5.818174@.news20.bellglobal.com...
>I have a web application which connects to Microsoft SQL Server 2000
>through
> JDBC-ODBC Driver. The application server is JBoss and I am using
> connection
> pooling.
> When the application connects to the database it provides userid and
> password which are 'sa' and 'password' respectively. They are constants
> for
> all users. The user also type in his/her own login id which I stored in
> the
> HTTPSession.
> Problem is my triggers wants to get that login id. Is it possible?
>
> Thanks
> Rizwan
>|||> possibly generate some kind of session token and pass that and
> then use the session token as a link to the login.
can you explain this solution a bit more about what is session token?
thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uNfEUWWTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi
> You should not be using 'sa' as the login to SQL Server as this may be too
> privileged.
> The users login id is only used as authentication mechanism, therefore you
> will either need to pass it as part of each call to the query/stored
> procedures or possibly generate some kind of session token and pass that
and
> then use the session token as a link to the login.
> John
>
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:5Ozce.14173$gA5.818174@.news20.bellglobal.com...
>|||Hi
The easiest way is to store and pass the user_id that the person
authenticated with within you code. You than pass this value to each
procedure that is called e.g.
EXEC myProc @.user_id = 'John'
If you want to access the user_id in a trigger you would have to add a
user_id column to each table (say last_modified_by) and set 'John' as the
value. This way you can see who changed it by accessing the last_modified_by
in the inserted table in your trigger. Alternatively you can do the work
that the trigger would have done in the stored procedure and you would not
need the extra column.
A token would be a means of relating the session to the user, if you have a
users table it may be stored in there. That way you are not passing
something that is clearly a username, but you can get the username by
selecting the appropriate record from the users table.
HTH
John
"Rizwan" <hussains@.pendylum.com> wrote in message
news:9srde.1176$3U.240756@.news20.bellglobal.com...
> can you explain this solution a bit more about what is session token?
> thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uNfEUWWTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> and
>

getting list of SQL Instances / Databases on network

I am using C#.NET and I am writing an application where I need to display to
the user in a comboBox all the SQL Server instances that can be detected and
dis. I have seen many applications like Enterprise Manager that can detect
them all. Once the user selects the instance, I would also like to get a lis
t
of all the databases stored into another combo.
How do I do this in C#?
Thanks for the help in advance.
David
Message posted via http://www.webservertalk.comhi
probably this can hekp you:
http://chanduas.blogspot.com/2005/0...-databases.html
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David C via webservertalk.com" wrote:

> I am using C#.NET and I am writing an application where I need to display
to
> the user in a comboBox all the SQL Server instances that can be detected a
nd
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a l
ist
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com
>|||This just lists the databases on a *known* single instance, which is also
treated here in more depth:
http://www.aspfaq.com/2456
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3EAACA02-A627-4CE6-8A6E-E9CCFF6208D2@.microsoft.com...
> hi
> probably this can hekp you:
> http://chanduas.blogspot.com/2005/0...-databases.html
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "David C via webservertalk.com" wrote:
>|||If you are familiar with SQL-DMO, you can use ListAvailableServers()
You can also use the SQLPing utility; one version has C# source code
included.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
In addition, Gert has produced some tools:
http://www.sqldev.net/misc/EnumSQLSvr.htm
http://www.sqldev.net/misc/ListSQLSvr.htm
http://www.sqldev.net/misc/OleDbEnum.htm
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Here's a very quick way to do it in C#
Create a new console application, and add this reference:
Project | Add Reference | COM | Microsoft SQLDMO Object Library
using System;
using System.Collections.Generic;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
}
}
}
That's it... now, as others might mention, this isn't 100% accurate, because
some servers in your network may be "hidden," and the service also has to be
started to be detected this way.
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Aaron,
If I could give you 1000 points for giving the perfect answer I would.
This was an outstanding piece of code that worked perfectly. We have an SQL
expert here and he did not think of this. So props to you.
Thanks,
David
Aaron Bertrand [SQL Server MVP] wrote:
>Here's a very quick way to do it in C#
>Create a new console application, and add this reference:
>Project | Add Reference | COM | Microsoft SQLDMO Object Library
>using System;
>using System.Collections.Generic;
>using System.Text;
>namespace ConsoleApplication1
>{
> class Program
> {
> static void Main(string[] args)
> {
> SQLDMO.Application sqlDmoApplication = new SQLDMO.Application()
;
> SQLDMO.NameList serverList;
> serverList = sqlDmoApplication.ListAvailableSQLServers();
> foreach(string serverName in serverList)
> {
> Console.WriteLine(serverName);
> }
> }
> }
>}
>That's it... now, as others might mention, this isn't 100% accurate, becaus
e
>some servers in your network may be "hidden," and the service also has to b
e
>started to be detected this way.
>
>[quoted text clipped - 10 lines]
Message posted via http://www.webservertalk.com|||The best way to do it is using SQLBrowseConnect function from ODBC (no
SQLDMO dependency). Check the sample here:
http://www.codeproject.com/cs/database/LocatingSql.asp
Note: SQLBrowseConnect does not work if LAN is not available (cable
unplugged, etc.) while local instances are still accessible :-))
cheers,
</wqw>|||I too am looking for this same type of information. I read through the
response's and none gave me any information I could use. I've already tried
the one David said returned the information he needed. Here's what I am
looking for.
I've got SQL Server 2005 Express installed twice with 2 instances. The
first is the default SQLExpress and the second I named. We'll say
TestExpress. I know if I go into the registry under SQL Server it list both
of these instance names and I can probably retrieve this information from
there. But is there anyway to retrieve it via SQLDMO or any of the .NET SQL
references.
This code Aaron listed and David said it worked for him
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
This only retrieved 2 items
{local}
the other was my computer name
Any help would be appreciated.
Joe
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com

Getting intermittent error with SQLDMO objects

In an application that uses SQLDMO object to BCP data into a empty table in a database I get the following error only occassionally on one or two machines out of hundreds. I would like to understand why and resolve the issue.

From the application log file.

Bulk Copying data
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error Description
-2147220299 Error Error
IDispatch error #693 Error Message
An error occurred while processing [RMS_EDM_TUTORIAL] Batch Copy:[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.RmsDbManager::Process

The Code involved.

The error is thrown at the the following line in the code below.

dmoTable = dmoTables->Item((LPCTSTR)sTableName); // Error thrown I think by ODBC driver but not sure. The error indicates locked locked resources but which resources (no query involved that I see) and on the machine where the error occurrs it happens every time. Help would be greatly appreciated.

bool

MyClassSqlServer::BatchCopy( const char *sDBName, const char *sDir, const char *sFileSpec, MyClassFile& logfile)
{
MyClass_ASSERT_VALID;
MyClassBool bRet = MyClass_FALSE;
ASSERT(sDBName);
ASSERT(sDir);
ASSERT(sFileSpec);
ASSERT(m_dmoServer.GetInterfacePtr());
CString sPath(sDir), sDataFile, sTableName;

try
{
sPath += PATH_SEP;
sPath += CString(sFileSpec);

CFileFind fileFind;
BOOL bContinue = fileFind.FindFile(sPath);

SQLDMO::DatabasesPtr dmoDbs;
SQLDMO::_DatabasePtr dmoDb;
SQLDMO::TablesPtr dmoTables;
SQLDMO::_TablePtr dmoTable;
dmoDbs = m_dmoServer->GetDatabases();
dmoDb = dmoDbs->Item(sDBName);
dmoTables = dmoDb->GetTables();

while ( bContinue )
{

bContinue = fileFind.FindNextFile();
sDataFile = fileFind.GetFilePath();
sTableName = fileFind.GetFileTitle();
sTableName = "[" + sTableName + "]";
CString msg;

if (!fileFind.IsDirectory())
{
dmoTable = dmoTables->Item((LPCTSTR)sTableName);
msg.Format("BCP file is: %s\n", sTableName);
logfile << msg;
BulkCopy ( dmoTable, sDataFile);
}
}
fileFind.Close();
bRet = MyClass_TRUE;
}
catch(_com_error &err)
{
TRACE0("An error occurred in MyClassSqlServer::BatchCopy()");
logfile << err.Description() << " Error Description \n";
logfile << err.Error() << " Error Error\n";
logfile << err.ErrorMessage() << " Error Message\n";
throw Except(Except::se_BatchCopy, err.Description(), err.ErrorMessage());
}

I moved this thread to data access as this issue does not seem to be rooted in DMO. I hope that someone in this group can help out.

Getting ID after Insert from AutoIncrement column in MS Access

I am inserting new records stored in SQL Server into a legacy MS Access application using SSIS. During the transformation, I need to get the ID MS Access assigned to the autoincrement column in the MS Access table I am inserting the row into. Is this possible? Can someone give me an example?

Thanks,

Steve

Not possible in a batch type insert like SSIS does.

Why not make your own "AutoIncrement" column inside SSIS? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Thanks Phil. I did not think so, but thought I would ask. I need the actual ID from the Access table so I can update the record in SQL server. The solutions is migrating to SQL server but in the meantime, information can be updated in either Access or a web interface to SQL Server. We have to sync the data between the two.

|||It may be possible if you insert one row at a time, but I'm not sure how to return the last AutoIncrement value in Access. In SQL Server it's @.@.identity, but not sure in Access.

You can still calculate your own autoincrement number. If nothing else is inserting into that Access table, you can turn auto-increment off. Then using the page I listed earlier, you calculate the max value which seeds the starting number for your upcoming inserts. Just a thought.|||

How can I force the transaction to complete within a dataflow? To solve this problem of retrieving the primary key assigned by Access, I added another column to the Access table to write the SQL key. When I add the record from SQL Server to Access, I write the SQL Server key to this column. The next step of the data flow is to re-read the record using the SQL key and retrieve the Access key assigned to the autonumber column. The problem seems to be that when I get to this step of the dataflow, the record hasn't actually been written so it does not complete the Lookup transformation.

Is there a way to force the transaction or do I need to move this step to a new Control Flow?

Thanks,

Steve

|||You'll have to move it to a new data flow. While the data flow does process "row by row", rows are processed in buffers. So one buffer (of ~10000 rows by default) has to be processed through the lookup before the same buffer can be written to the destination.

Getting Exception while installing SQL Express 2005 in Windows Vista OS

Hi All,

I have developed an windows application using C#. I am using SQL Express as back-end.

I have created an installer class which first installs .NET Framework2.0, SQL Express2005 and then the

application. It is woking fine with WindowsNT and XP oprating systems.

If i try to install this on Windows Vista OS, I am getting expection while installing SQL Express(Creating SQL

Instance).

Can anybody help me in this scenario.

Regards,
Doppalapudi.

Can you supply the exact error message you are seeing? Are you seeing it on multiple Vista machines or just one? Can you also check your log directory for the text string "value 3" and supply the 10-15 lines above it?

Thanks,
Sam Lester (MSFT)

|||

Hi

Thanks for giving quick reply. I got the problem and solved it. I was able to install the application.

Thanks

Doppalapudi.

Getting error while using dbo in sql query

Dear All,

I am using a query as it is in SQL Server 2000 in my C# windows application.

The query is having name with "dbo" in all database object. If i remove that dbo from the name it is working fine. Why it is not working .net application ? What is the theory behind it. Please explain me..Thanks in advance.My query is given below.

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

Thanks,

Saji

1. Are the database objects actually in the dbo schema?

2. What do you mean by "not working" - an error? no results? incorrect results?

|||I noticed a posible problem in this lines:
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
and
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
Clients_1 don't exists as a database object, but instead is an alias for Clients table, so you can't use this sintax when using in select statement. Just remove dbo. before Clients_1 and probably will be Ok:

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

|||

Hi Boban,

Thank you so much..It is working well.

Regards,

Saji

Getting error while using dbo in sql query

Dear All,

I am using a query as it is in SQL Server 2000 in my C# windows application.

The query is having name with "dbo" in all database object. If i remove that dbo from the name it is working fine. Why it is not working .net application ? What is the theory behind it. Please explain me..Thanks in advance.My query is given below.

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

Thanks,

Saji

1. Are the database objects actually in the dbo schema?

2. What do you mean by "not working" - an error? no results? incorrect results?

|||I noticed a posible problem in this lines:
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
and
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
Clients_1 don't exists as a database object, but instead is an alias for Clients table, so you can't use this sintax when using in select statement. Just remove dbo. before Clients_1 and probably will be Ok:

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

|||

Hi Boban,

Thank you so much..It is working well.

Regards,

Saji

Getting error while using dbo in sql query

Dear All,

I am using a query as it is in SQL Server 2000 in my C# windows application.

The query is having name with "dbo" in all database object. If i remove that dbo from the name it is working fine. Why it is not working .net application ? What is the theory behind it. Please explain me..Thanks in advance.My query is given below.

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

Thanks,

Saji

1. Are the database objects actually in the dbo schema?

2. What do you mean by "not working" - an error? no results? incorrect results?

|||I noticed a posible problem in this lines:
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
and
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
Clients_1 don't exists as a database object, but instead is an alias for Clients table, so you can't use this sintax when using in select statement. Just remove dbo. before Clients_1 and probably will be Ok:

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

|||

Hi Boban,

Thank you so much..It is working well.

Regards,

Saji

Getting error while using dbo in sql query

Dear All,

I am using a query as it is in SQL Server 2000 in my C# windows application.

The query is having name with "dbo" in all database object. If i remove that dbo from the name it is working fine. Why it is not working .net application ? What is the theory behind it. Please explain me..Thanks in advance.My query is given below.

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

Thanks,

Saji

1. Are the database objects actually in the dbo schema?

2. What do you mean by "not working" - an error? no results? incorrect results?

|||I noticed a posible problem in this lines:
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
and
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
Clients_1 don't exists as a database object, but instead is an alias for Clients table, so you can't use this sintax when using in select statement. Just remove dbo. before Clients_1 and probably will be Ok:

strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");

string sqlOrder = strsql.ToString();

|||

Hi Boban,

Thank you so much..It is working well.

Regards,

Saji

Getting Error code from SqlServer to FrontEnd Application

Hi,

Frndz, Asume that we have some error in stored procedure. We shall control that error by using transactions in Backend(Sql Server). But how z t possible to give the information to the front end application that error has occured in backend ? Will anyone plz help me.

If u cant able to understand plz mail me at mneduu@.gmail.com

Thankz in Advance.

Thanks & Regards
(M. Nedu)You could have a variable as an integer, you can set the varaible with @.@.ERROR. The variable will assign a number in the varaible if an error occurs.

Then before you commit your transaction you can have an if stment, so

If @.Variable <> 0
Begin
@.Reason = 'what ever you want to put in'
End
Else
Commit

Somthing like that