How odd, strange behaviour

Dear folks,
I've got the following issue and I can't work out:
This statement fails:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
Servidor: mensaje 7377, nivel 16, estado 1, l_nea 2
Cannot specify an index or locking hint for a remote data source.
But this one, ends successful and pull up the data:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Does anyone ever used or experienced any problem with that? are we talking
about the same or different goal with the different way? I mean, nolock, wit
h
nolock...
Thanks for any input and regards,You are defining just an alias here:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
HTH, Jens Suessmeyer.|||sorry Jens, I don't understand you, what do you mean with that?
"Jens" wrote:

> You are defining just an alias here:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
>
> Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
>
> HTH, Jens Suessmeyer.
>|||Defining aliases for the tables often helps to clear queries due to
long names or names where you don=B4t know what the data is all about,
something like:
SELECT Address.*
FROM T3455NBGA Address --Normally this is an Adress table but you
don=B4t know from the name, so we give it an alias in the query
INNER JOIN SomeOtherTable
ON SOmeotherTable.ID =3D Address.ID
HTH, Jens Suessmeyer.|||Thanks in advance, anyway it doesn't works.
"Jens" wrote:

> Defining aliases for the tables often helps to clear queries due to
> long names or names where you don′t know what the data is all about,
> something like:
> SELECT Address.*
> FROM T3455NBGA Address --Normally this is an Adress table but you
> don′t know from the name, so we give it an alias in the query
> INNER JOIN SomeOtherTable
> ON SOmeotherTable.ID = Address.ID
> HTH, Jens Suessmeyer.
>|||> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
So will this:

> SELECT NOLOCK.G61Num AS Curso,NOLOCK.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
And this:

> SELECT bozo.G61Num AS Curso,bozo.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET bozo
You would think that SQL Server would treat 'NOLOCK' as a reserved word, but
it doesn't.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:95D4CF5F-8E45-44E4-9F35-95F38A5286DA@.microsoft.com...
> Dear folks,
> I've got the following issue and I can't work out:
> This statement fails:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
>
> Servidor: mensaje 7377, nivel 16, estado 1, lnea 2
> Cannot specify an index or locking hint for a remote data source.
> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
> Does anyone ever used or experienced any problem with that? are we talking
> about the same or different goal with the different way? I mean, nolock,
> with
> nolock...
> Thanks for any input and regards,|||Of course not. Because of how you placed NOLOCK in your code, it is simply
being used as an alternative table name, instead of the NOLOCK action that
you are trying to produce. The query that is working is not using NOLOCK as
you intend, but rather is using the term as a reference to your table. The
query that is failing is using NOLOCK as you intended, but SQL Server does
not permit you to use it in that context.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:5A50B17C-2705-4027-A5F6-24E025F0CD4F@.microsoft.com...
> Thanks in advance, anyway it doesn't works.
> "Jens" wrote:
>|||I should add that this is not your fault (IMO) since it is more than
reasonable to expect NOLOCK to be a reserved word. It is a simple syntax
error, and an honest mistake.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23PJDYbnJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Of course not. Because of how you placed NOLOCK in your code, it is
simply
> being used as an alternative table name, instead of the NOLOCK action that
> you are trying to produce. The query that is working is not using NOLOCK
as
> you intend, but rather is using the term as a reference to your table.
The
> query that is failing is using NOLOCK as you intended, but SQL Server does
> not permit you to use it in that context.

how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

I am try to start with SQL BROKER service,

When I lunch from sql Management studio the following query, this don't finish never.

ALTER DATATABLE dbname SET ENABLE_BROKER

Where I am mistaking ?

I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx

Essentially:

-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER; SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name'; -- Where 'Database name' is the name of the database you want to query. |||

I assume you mean ALTER DATABASE.

ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.

HTH,

~ Remus

|||

Holas!

How can I close all the sessions using a query?

Saludos,

|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||

Thanks Ramus,

Saludos,

|||

thank you for your best guidance that was very useful for me to solve my problem.

regard you

mojgan

how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

I am try to start with SQL BROKER service,

When I lunch from sql Management studio the following query, this don't finish never.

ALTER DATATABLE dbname SET ENABLE_BROKER

Where I am mistaking ?

I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx

Essentially:

-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER; SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name'; -- Where 'Database name' is the name of the database you want to query. |||

I assume you mean ALTER DATABASE.

ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.

HTH,

~ Remus

|||

Holas!

How can I close all the sessions using a query?

Saludos,

|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||

Thanks Ramus,

Saludos,

|||

thank you for your best guidance that was very useful for me to solve my problem.

regard you

mojgan

how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

I am try to start with SQL BROKER service,

When I lunch from sql Management studio the following query, this don't finish never.

ALTER DATATABLE dbname SET ENABLE_BROKER

Where I am mistaking ?

I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx

Essentially:

-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER; SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name'; -- Where 'Database name' is the name of the database you want to query. |||

I assume you mean ALTER DATABASE.

ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.

HTH,

~ Remus

|||

Holas!

How can I close all the sessions using a query?

Saludos,

|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||

Thanks Ramus,

Saludos,

|||

thank you for your best guidance that was very useful for me to solve my problem.

regard you

mojgan

|||Hi there,

I faced the same problem you have mentioned.

Then, I found a simple solution. You need to use the following 2 statements together, and the query will finish in no time:

Use NorthWind
alter database NorthWind SET ENABLE_BROKER

How long is a SQL statement allowed to be?

I am trying to make the following SQL statement, but there seems to a limit
on how long a statement can be:

INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
addressB, postal_number, city, country, home_phone, mobile_phone,
work_phone, fax, email, sale_procentage, bank, account_number,
creation_initials, creation_date, creation_reason) values ("test", "test",
"test", etc...);

But I can only enter this much text:

INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
addressB, postal_number, city, country, home_phone, mobile_phone,
work_phone, fax, email, sale_procentage, bank, account_number,
creation_initials, creation_date, creation_reason) va

Is there some upper limit? And how do I make a long SQL statement like this?

JS"JS" <dsa.@.asdf.com> wrote in message news:d6ilga$rpj$1@.news.net.uni-c.dk...
>I am trying to make the following SQL statement, but there seems to a limit
> on how long a statement can be:
> INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
> addressB, postal_number, city, country, home_phone, mobile_phone,
> work_phone, fax, email, sale_procentage, bank, account_number,
> creation_initials, creation_date, creation_reason) values ("test", "test",
> "test", etc...);
> But I can only enter this much text:
> INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
> addressB, postal_number, city, country, home_phone, mobile_phone,
> work_phone, fax, email, sale_procentage, bank, account_number,
> creation_initials, creation_date, creation_reason) va
>
> Is there some upper limit? And how do I make a long SQL statement like
> this?
> JS

It sounds like you're using a window somewhere in Enterprise Manager? If so,
then just use Query Analyzer instead - it's a much better tool for
development and having precise control over what you're doing.

There is a maximum batch size for SQL commands, which depends on your
network packet size (see "Maximum Capacity Specifications" in Books Online),
but it's probably not a limit for most practical purposes.

Simon

How limiting is MS SQL?

I'm trying to decide whether MS SQL will allow me to accomplish the following objectives at no cost, or whether I'd eventually have to pay for an MS SQL upgrade to accompish my objectives.

I have big, unrealistic dreams. I want to create a humorous newsblog into which I would post more than a dozen times a day. Most of the posts would have large photographs. Presumably, I'd archive the posts by subject and ranking and 'most viewed," etc., using a database.

1) Will the space limitations of the MS SQL Express edition be an issue after a while?

2) Could I hire a web developer to help me from a remote location, once the website is large enough to warrant expansion? Or does the MS SQL Express edition allow only one user? I read something I didn't understand about CPU restrictions.

3) I'm confused because web hosts advertise the availability of MS SQL databases on their server...so does that mean I wouldn't have to buy an upgrade if it became neccessary? (I know, I'm shockingly uneducated.)

4) I'm going to buy Office 2007. Is it important to purchase a package that includes Microsoft Access, given my goals?

5) Any other thoughts in plain english on how the MS SQL express edition imposes limitations....basically, I don't understand how MS SQL Express might limit me down the road if the site were actually a success. What would have to happen before I would be forced to spend a lot of money on an upgrade later?

I'm almost completely new to computing. I've read a bunch of criticisms of MS SQL Express on internet forums that I didn't understand, but that really made me worried about my decision to go with Microsoft Products and Asp.net web hosts. (I understand some people have an irrational dislike of Microsoft, but there was A LOT of bashing.)Hi.

1. 4 GB is quite a lot of data but large photographs can quickly eat up database space. One option you might have if you're working with a web developer is to instead use disk space for the actual image rather than stuffing it into the database. You'd want to give each a random name and just store that name in the database.

2. Yes you could hire a web developer to work on it from a remote location. SQL Server 2005 Express is not limited in the amount of users that can interact with it. In fact for most things it differs very little from the full Enterprise edition. It does limit you to a single CPU instead of being able to utilize multiple CPUs. For a little more information on what you can do with this edition checkout:
http://www.sqlmag.com/Article/ArticleID/49736/49736.html

and

http://technet.microsoft.com/en-us/library/ms345154.aspx

3. If you're hosting somewhere that offers to host your SQL Server database that means you don't need to worry about SQL Server 2005 Express at all. You'll develop with that locally and then provide them with the database files. It will instead run on their shared server along side other customer databases.

4. As far as Access is concerned, if you're using SQL Server Express 2005 you really wouldn't have much use for Access at all. It certainly isn't something you'd want to build this site upon...

5. Like I said before SQL Server Express doesn't differ significantly from the full Enterprise product. There are some additional features missing that most non-Enterprise uses probably won't miss like Analysis, Notification, and Integration services. If you checkout SQL Server 2005 Express with Advanced Services (http://msdn2.microsoft.com/en-us/express/bb410792.aspx) you even get Reporting Services (which is cool). Other than that it is limited to a single CPU rather than multiple and yes the database size can only be a maximum of 4GB and it can only use 1 GB of ram for some things but overall it comes down to usage. Aside from storage space nothing you've described would seem to push Express beyond its limits.

Hope this helps you with your decision!
|||Wow! A lightening quick response on a holiday. Thanks!

I'm not as lightening quick as your response. I'd like to put your response in my own words, to see if I got it right.

1. Rather than storing the photo in the database, I could store a link to the photo. If I understand you correctly, that should allay my concerns regarding disk space.

2. I read the links you provided, and I understand that I could have a web developer work on the website from a different location. I wasn't sure I understood the following, however:

"It does limit you to a single CPU instead of being able to utilize multiple CPUs."

--How does this limit me as a practical matter? I looked at the resource you provided, and it said:

"SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. Internally, the engine limits the number of user scheduler threads to 1 so that only 1 CPU is used at a time. Features such as parallel query execution are not supported because of the single CPU limit."

I take it that the CPU limitation means that the web developer and I couldn't work on the database at the same time. But, umm..."user scheduler threads"... I'm not sure I've understood this correctly. Do I have it right?

3.1 So, here's a few excerpts from a web host service ad. I substituted the name WebHost.net in place of the actual host name.

"WebHost.NET offers Microsoft SQL Server 2005 database hosting, the next-generation of robust enterprise level database as an optional addon to our base hosting plan."

I understand that you don't represent the web host, but from what you can see, am I correct in understanding that if I were to buy the above package, I wouldn't have to worry about keeping within the 4 GB and 1 CPU limits? Or any other of the (relatively modest) MS SQL Express limitations?

3.2 Here's another excerpt:

"Connect to your MSSQL database with your choice of tools including Enterprise Manager, Query Analyzer, or Visual Studio.NET, directly through ASP or ASP.NET applications."

So, given the above, do some web host allow people to use Visual Web Developer's "Solutions Explorer" feature to make use of the MS SQL database stored on the Web Hosts' computer?

3.3 Is it reasonable to say that I could use the MS SQL Express edition for now, and then buy the MS SQL through my web host for a few extra bucks a month if the (relatively minor) limitations of MS SQL Express start to bother me? I wouldn't have to shell out $1000+ to buy a non-Express version of MS SQL?

I'm definitely willing to pay a little extra to be able to use Microsoft products front to back, since the idea seems to be that they're easy for a novice to use, stable, and integrated with one another.

4. The only answer my tiny little mind allowed me to grasp. Thanks!

5. "It can only use 1 GB of ram for some things but overall it comes down to usage."

5.1: I think I understand that the 1GB of ram limitation is not serious, but I don't understand why. I read in the material you referenced:

"The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, and so on is not counted toward the buffer pool limit."

Buffer pool? Connections? Locks?... Wookies? Chewbacca? Endor?

As a practical matter, does the 1GB limitation mean that I can't use all of my computer's RAM to work on the database with MS SQL? I obviously just don't grasp the significance of the 1GB ram limitation.

5.2: you wrote: "Overall it just comes down to usage."

I assume you meant that I can't use more than 4 GB of storage space? I just don't get what you meant. (My fault, not yours.)

6. Additional question: I (attempt) to use the 2008 Visual Web Developer Express edition, because somebody told me it writes far better code than the 2005 edition. Does the fact that I'm using the 2008 edition of VWD mean I can't download and use the MS SQ Express 2005 edition, since they are from different years? I haven't downloaded the database yet.

Thanks so much for your patience!

--Tim (randomasdfguy)
(in the preview pane, I saw that part of my answer was in a very small font, and I don't know why, and I couldn't manage to fix it. Sorry.)
|||Hello again Tim.

1: Yes storing the photo locally and including just a link in the database will not only save you space but can also improve overall performance of your site.

2: CPU Limitations: Well only using a single CPU basically limits performance but from the usage you described I seriously doubt this will impact the usability of your site. This CPU limitation really only relates to the processor installed on the server and in no way impacts how many users can connect to it so you and your web developer can definitely connect simultaneously along with all of your users.

3.1: Yes generally when a website says it offers SQL Hosting that means they own the license to the SQL Server and it is probably going to be Standard edition so the limitations imposed on the Express editions would not apply whatsoever. I would call their sales department to make sure but I've hosted several places and that was the case...

3.2: Yes thats what that means...that you can connect to your databases in Visual Studio or a variety of other host applications.

3.3: Absolutely. That is exactly how I would do it. Use the freebie for now and when and if performance degrades or you start running out of space upgrade. I doubt you'll hit either of those scenarios for a long time.

5.1: The 1 GB RAM limitation really just impacts some portions of performance and again given your usage I doubt you'll ever notice.

5.2: Ummm...by usage I mean how you intend to construct your website and how it is going to utilize SQL Server. A lot of it is up to the skill of the developer to make his site more efficient. There are tons of great techniques in ASP.NET to help you run in a very efficient manner. Here are a few performance related links to get you started:

http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/
http://msdn2.microsoft.com/en-us/library/ms998549.aspx

6: Oh you can definitely use SQL Server 2005 Express Edition with 2008 Visual Web Developer Express Edition no question about it.

Please let me know if you have any other questions and I'll do my best to answer them...

Christopher
|||Christopher,

Thanks a million for your help! It is sincerely appreciated!

How is the row filter clause and join filter should be....

Hi,
I am new to replication, I have the following tables in my
database, and I would like to replicate to other
subscriber using Merge Replication:
tblProduct (Product master)
ProdID (PK)
ProdGrp (FK to Product grouping)
ProdDesc
tblProdGrp (Product grouping)
ProdGrp (PK)
ProdGrpDesc
tblCustomer (Customer master)
CustID (PK)
BranchID (FK to branch master)
OfferGrp (FK to trade offer grouping)
CustName
tblBranch (Branch master)
BranchID (PK)
BranchName
tblTradeOfferGroup (Trade offer grouping)
OfferGrp (PK)
OfferGrpDesc
tblTradeOffer (Trade offer)
OfferID (PK)
OfferGrp (FK to Trade offer grouping)
StartEffDate
EndEffDate
tblTradeOfferProduct (Trade offer's product)
SeqID (PK)
ProdGrp (PK/FK)
OfferID (PK/FK)
OfferQty
*Note: SeqID + ProdGrp + OfferID is unique
I need all rows from tblProduct, tblProdGrp, tblBranch,
tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
replicate to subscriber, but only single branch's customer
in tblCustomer at subscriber. How should I configure the
row filter and join filter in my Merge Replication?
Currently, I configure as following:-
Row Filter:
tblCustomer row filter tblCustomer.BranchID = '001'
tblProduct <publish all rows>
tblProdGrp <publish all rows>
tblBranch <publish all rows>
tblTradeOfferGroup <publish all rows>
tblTradeOffer <publish all rows>
tblTradeOfferProduct <publish all rows>
Join Filter:
Filtered table Table to filter
tblTradeOfferGroup tblTradeOffer
tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
tblTradeOffer tblTradeOfferProduct
tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
But, tblTradeOfferProduct not replicated over. A conflict
occurs saying FOREIGN KEY constraint etc. The weird case
is, when I synchorise again, the rows publisher's
tblTradeOfferProduct are deleted.
Please advice.
Thank you.
HKM
I think, replicating tblTradeOfferProduct should fix your problem.
Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
tblProduct too ) it is better to replicate this table.
Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"
Since you dont have "NOT FOR REPLICATION" whenever that are constraint
violatins you will see merge failing with constraint violations.
And once some entries fail to propagate to the subscriber, in the next
merge, compensating actions (deletes for all the failed inserts) are made
and hence you will see that those rows vanish from the database.
You can either set the constraints to "NOT FOR REPLICATION" or replicate the
tblTradeOfferProduct table too. One of them should fix the problem
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> Hi,
> I am new to replication, I have the following tables in my
> database, and I would like to replicate to other
> subscriber using Merge Replication:
>
> tblProduct (Product master)
> --
> ProdID (PK)
> ProdGrp (FK to Product grouping)
> ProdDesc
> tblProdGrp (Product grouping)
> --
> ProdGrp (PK)
> ProdGrpDesc
> tblCustomer (Customer master)
> --
> CustID (PK)
> BranchID (FK to branch master)
> OfferGrp (FK to trade offer grouping)
> CustName
> tblBranch (Branch master)
> --
> BranchID (PK)
> BranchName
> tblTradeOfferGroup (Trade offer grouping)
> --
> OfferGrp (PK)
> OfferGrpDesc
> tblTradeOffer (Trade offer)
> --
> OfferID (PK)
> OfferGrp (FK to Trade offer grouping)
> StartEffDate
> EndEffDate
> tblTradeOfferProduct (Trade offer's product)
> --
> SeqID (PK)
> ProdGrp (PK/FK)
> OfferID (PK/FK)
> OfferQty
> *Note: SeqID + ProdGrp + OfferID is unique
> I need all rows from tblProduct, tblProdGrp, tblBranch,
> tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
> replicate to subscriber, but only single branch's customer
> in tblCustomer at subscriber. How should I configure the
> row filter and join filter in my Merge Replication?
> Currently, I configure as following:-
> Row Filter:
> tblCustomer row filter tblCustomer.BranchID = '001'
> tblProduct <publish all rows>
> tblProdGrp <publish all rows>
> tblBranch <publish all rows>
> tblTradeOfferGroup <publish all rows>
> tblTradeOffer <publish all rows>
> tblTradeOfferProduct <publish all rows>
> Join Filter:
> Filtered table Table to filter
> tblTradeOfferGroup tblTradeOffer
> tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
> tblTradeOffer tblTradeOfferProduct
> tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
> But, tblTradeOfferProduct not replicated over. A conflict
> occurs saying FOREIGN KEY constraint etc. The weird case
> is, when I synchorise again, the rows publisher's
> tblTradeOfferProduct are deleted.
> Please advice.
> Thank you.
> HKM
>

How is the metadata of the result of a UNION determined?

Hi,

A colleague and I have just found a slightly strange situation that we don't understand.

We had (effectively) the following query:

select cast(1 as decimal(38,10))
union all
select cast(1 as decimal(38,4))

And the result contained 2 rows, each with a a scale of 4. This surprised us, we expected that the metadata of the result would be determined by the topmost query.

So we reversed them and tried this:

select cast(1 as decimal(38,4))
union all
select cast(1 as decimal(38,10))

and got exactly the same result. 2 rows with a scale of 4.

We can't understand why the scale always gets determined to be 4 regardless of the order of the queries.

Any explanation would be much appreciated!

Thanks

Jamie

Doesn't matter. The answer's here: http://msdn2.microsoft.com/en-us/library/ms190476.aspx

-Jamie

|||

In addition to the "Precision, scale and length" topic, the UNION operator topic also documents how the data conversions happens if the various SELECT statements contain columns with different data types. See link below for more details:

http://msdn2.microsoft.com/en-us/library/ms180026(SQL.90).aspx

How I get the random row from the table?

When I execute the following query several times, I get the same row if there is no new data inserted:
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from table
where id = @.value|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think. You would have to make a while-loop and check if the ID exists I belive, and then loop for each ID that doesn't exist. have never done this myself so there might be a better way...|||The only issue with that is that you have to have an unique integer for your ID. Additionally your ID can not have gaps and must start at one (or is it zero).

Perhaps something like this??
----
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

executesql 'select top ' + @.value + ' from table order by XYZ'
----

then you need to select the first one or last one that is selected...

I don't know how you would do it though...

HTH|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think

Providing the id column is unique, then

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||Unfortunately I can't test this out on the machine I am on, for the above example wouldn't you need a group by clause in your select count(*) and a having instead of a where clause...

so something like...

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id group by t1.id) AS ID2
from table t1) v
having v.id2 = @.value

once again, sorry I can't check it.|||No aggregate functions have been computed on the set 'V', meaning that the group by and having clauses are not required.

Consider,

Select a, b, (select count(*) from table) AS COUNT
from table t1
group by a, b

This is invalid as COUNT is interpreted as a column as opposed to an aggregate function of t1.|||Okie cool. :) Like I said, I couldn't check so. ;)

It's an interesting problem though... personally I wouldn't try and get the database to do this...

I'd get the app to generate a random id to select and just do a standard query on that id...

Each to their own though. :)|||Thank you for your all helps.

Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

How do you think about it?|||Try this and see:
SELECT TOP 1 * FROM TableName
order by newid()|||Originally posted by gyuan
Thank you for your all helps.

Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

How do you think about it?

The code above will still encounter problems with the gaps and the not starting at zero...

this is the one you want

Originally posted by r123456

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||The problem you get with this solution

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

is say you have 5000 records and you delete 4000 records.

Your rand value will be between 1 and 4000 but your max UserId is 5000 anything with an Id over 4000 is pretty much unreachable...

I think you'd be better with this...

Declare @.value As Integer
SET @.value = (RAND() * (SELECT max(UserID) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

It would mean when gaps occur the row after the gap would be hit more often, but atleast you would cover your entire collection of rows.

Hope that makes sense.|||quote:
------------------------
Originally posted by r123456

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value

------------------------

What does columns stand for in the query?|||errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...|||the columns you want to select eg * or username, firstname, lastname etc...|||Hi Patrick,

I'm really not sure how your solution would work, what is the newid()?|||Its a build in command specifically to be use to "select random rows".
It is also use as a comand to auto gen Unique Identifier ids.

But when use in "order by newid()" it generates random rows.

Try it out and see...it works...unless..well theres something I'm missing in the whole discussion.|||Originally posted by Patrick Chua
errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...

It works too, but it takes 2 seconds, a little slower. Now I know the function NewID() and it helps. Thanks.|||rokslide,

That is a good idea to replace Count(*) to MAX(UserID). Thanks.|||Cool, I think I'll have to have a look into that one some time. :)

Thanks Patrick. :)|||No worries gyuan, please note though that (as I said above) it's not truely random.|||Correct.

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

If TOP * 1 is used in conjunction with max(id) then only the first value past a "gap" value will be returned, should @.value be equal to a "gap" value.|||I think Patrick's query is better:

SELECT TOP 1 *
FROM Users
ORDER BY NewID()

although the running time is a little longer.|||I've used Patrick's method in the past with success.|||Isn't anyone going to ask WHY do you want to do this?|||I use Patricks method daily and it works out great for me.
NewID() takes a little longer due to it generates a GUID but it is truely random.|||Originally posted by Brett Kaiser
Isn't anyone going to ask WHY do you want to do this?

Brett ...
You are always after the "why" instead of the "how"? I like you for the spirit coz I believe "Prevention is better than cure".|||How's easy.....

And thanks...

Seen to many rocket ships built...|||I have to admit to orginally thinking why would you want to, but I have seen a few "scuffles" break out on here over the "why" of things so I decided to leave it alone.

Then of course the curiosity took over and I started to think,... hey, how would you do that...

anyhow...

r123456

with this statement...

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

but if you use count and then compare count to the ID value you are going to completely miss some sections of the data entirely (eg. they will never have a chance to be selected). See the example that I noted eariler. The the max(id) option atleast you cover your entire span of data.

Of course Patrick's solution will cover everything perfectly so....|||Not true.

ID | ID2
1 1
2 2
3 3
4 4
5 5

Delete from table where id=2 OR id=3;

ID | ID2
1 1
4 2
5 3

You have two solutions. One of which requires very little code and an SQL Server function. The other requires a unique id, which for example can be the value of ROWID for an Oracle database.|||Ah yes, sorry, I forgot the second version of your solution with the new ID. :)

My fault entirely.

How I do this Query ?

Hello, Everyone

I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information

--

I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table

i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?

How I can copy object SMO between DB and servers?

Something following is necessary:

Column c1 = srv1.Databases[“db1”].Tables[“t1”].Columns[“c1”];

Column c2 = <something which copies c1>

srv2.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

srv2.ConnectionContext.CapturedSql.Clear();

srv2.Databases[“db1”].Tables[“t1”].Columns.Add( c2 );

foreach( string s in srv2.ConnectionContext.CapturedSql.Text )

{

Debug.WriteLine( s );

}

srv2.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

Really it is necessary to make copying all properties of c1 in {c2 = new Column()} ?

And the similar question: How to make so that the existing column in db1 would begin by the same as in db2? And to obtain the script of this.

I write the program of the comparison of DB structures. Comparison already works. But here with scripting arose problems.

I think you try to generate the change script. Setting all properties is needed in that case.

|||

Thanks, Michiel

I searched for the universal method of copying, for example the properties of column. Something like this:

Column c1 = db1.Tables[“t1”];

Column c2 = new Column();

c2.Parent = db2.Tables[“t2”];

c2.CopyPropertiesFrom( c1 );

or

c2.ToMakeSimilarOn( c1 );

Such is necessary in my case. It is to be regretted that it does not exist.

I tried to write something like:

Column c1 = srv.Databases["db1"].Tables["t1"].Columns["col"];

Column c2 = new Column( srv.Databases["db2"].Tables["t1"], c1.Name );

c2.DataType = c1.DataType;

foreach( Property p in c1.Properties )

{
if( !p.Dirty && p.Readable && p.Retrieved && p.Writable && !p.IsNull )

{

c2.Properties[p.Name].Value = p.Value;

}

}

But, as it proved to be - this is incorrect approach.

And I, enormous thanks for your Weblog.

How group column to show side-by-side?

This is nasty question but...

How can I take the following data from a table:

ID ItemNumber Type
1 9830302 CD
2 9830302 Cassette

And run a select statement to get me:

ID ItemNumber Type
1 9830302 CD/Cassette

Thanks,
Ron

Ron:

There is a pretty good discussion of this issue at this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1336558&SiteID=1

If you only ever have to worry about a couple of different types you can do something similar to this:

select min_id as id,
itemNumber,
min_type + '/' + max_type as Type
from ( select min(id) as min_id,
max(id) as max_id,
min(type) as min_type,
max(type) as max_type,
itemNumber,
from yourTable
where itemNumber = 983032
) x

|||

Using a trick that Arnie Rowland gave in: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1378712&SiteID=1

You can use an XML ELEMENTS subquery to collapse the multiple items into a single list.

Code Snippet

DECLARE @.ItemTable table
(
ItemID int,
ItemNumber int,
TypeDesc varchar(20)
)

INSERT INTO @.ItemTable Values ( 1, 9830302, 'CD' )
INSERT INTO @.ItemTable Values ( 2, 9830302, 'Cassette' )
INSERT INTO @.ItemTable Values ( 3, 9830303, 'CD' )
INSERT INTO @.ItemTable Values ( 4, 9830304, 'CD' )
INSERT INTO @.ItemTable Values ( 5, 9830304, 'Cassette' )
INSERT INTO @.ItemTable Values ( 6, 9830304, 'DVD' )
INSERT INTO @.ItemTable Values ( 7, 9830305, 'Cassette' )

-- Create Delimited list from multiple rows
-- From Tony Roberson
-- SQL 2005
SELECT DISTINCT ItemNumber, List = SUBSTRING(
(
SELECT '/' + TypeDesc as [text()]
FROM @.ItemTable Det
WHERE Det.ItemNumber = Itm.ItemNumber
FOR XML path(''), elements
), 2, 4096
)
FROM @.ItemTable Itm

I hope this is useful

|||PIVOT ?