Failed to enable constraints

Hello there,

I have a table under SQL2005 express with 8 fields with a primaryKey defined.

In my BLL in the table adapter there is one "SELECT DISTINCT prodCAT from prods"

The prodCAT is a nvarchar(50) and it is not part of an index or primaryKey.

When I preview the data from my BLL I get the correct data.

I defined a new web form (aspx) and put a ddl (dropDownList) with an objectdataSource that relates to the above sql function/command

and I get a

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

BTW when I defined the above SQL in the BLL I got a "The new command text makes the function return data with schema different from the schema of the main query"

Any suggestions?

Does your BLL return a dataset? If so you shoudl not get the behaviour described.|||

yes, it should return a dataset.

|||

sorry,

it return a data table

|||Try changing it to return a dataset.|||How do I do that??|||Look athttp://www.dotnetjunkies.com/QuickStartv20/howto/doc/adoplus/GetDataFromDB.aspx ADO.NET: Populate a DataSet from a Database|||

First thanx a lot

If I populate the dataset using this technique by-pass the BLL. Is it the correct way to do things??

best regards

|||There are times when a Business Logic Layer is useful - this does not look like one! You could of course put an entry into the BLL that calls the layer below to get a dataset and pass it up to the GUI.

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>>I'm trying to copy objects and data between 2 SQL Server databases using
> the
>>import/export wizard in SQL Server 2000.
>>I select the source and destination database and leave everything as
>>default. When I try to run the DTS Package, it stalls at 22% for a few
>>minutes, then goes up to 96% or so very fast and gives the following
> message
>>quoted in the subject. ("Failed to copy objects...")
>>When I double-click the error, it reads the following:
>>"Need to run the object to perform this operation
>>[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
>>I'm running the package as "sa", so I don't think I don't have privileges
> to
>>anything.
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> > Are the two databases on the same machine? The same instance?
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>sql

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using t
he
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following messa
ge
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using
a
> Domain Admin account and also use Windows authentication when promted by t
he
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come thisHi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,Claim
ID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:

> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come thisHi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,ClaimID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:
> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come this
Hi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,ClaimID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:

> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

Extremely weird SQL problem

Figure this...
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
says ...
648842 rows affected
however...
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
only returns 267564
in the Query Analyzer window.
And to top it all off... about 30 minutes after I
noticed it and investigated... it stopped happening.
Now both queries above affect the same number of rows.
This is the 4th time this has happened.
I haven't found any patters in log files or memory
settings or data types of fields.. .etc.
It's a brand new SQL Server 2000 Enterprise Edition.
6gb ram. Dual 3.06 ghz 1mb cache, Perc4/Dc Raid 10,
on 5x145gb scsi drives.
This problem wasn't happening on standard edition
that we migrated from about a month ago.
Any help would be greatly appreciated."Nick" <anonymous@.discussions.microsoft.com> wrote:
> Figure this...
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> says ...
> 648842 rows affected
> however...
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> only returns 267564
> in the Query Analyzer window.
> And to top it all off... about 30 minutes after I
> noticed it and investigated... it stopped happening.
--
Hi Nick,
I would update statistics, free procedure cache and rebuild indexes if I see
inconsistent results like this. I will also review execution plans for any
discrepancies.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Extremely complex Select statement

Hello,
I'm having real problems defining a SELECT statement for
the following scenario:
I have a Requirements table, each row in this table has
at least 1 row in the RequirementsWeeks table which
defines values for N weeks for each requirement.
Also, each requirement has a product value and a tool
value and each product belongs to a prodCategory and each
tool belongs to a ToolCategory (these associations are
stored in separate tables)
Here's an example of the records I have:
Requirements: RequirementsWeeks:
Product Tool Id ReqId Week Value
A TA 1 1 8 0.20
C TB 2 1 9 0.35
2 8 2.56
2 9 3.52
Products: Tools:
Id ProductName ProCategoryID Id ToolName ToolCatID
1 A 45 1 TA 68
2 B 53 2 TB 68
3 C 45
What i want to have is:
For all pairs product-tool in requirements that matches
ToolCatID And ProdCatID return the sum for week N
For example:
ToolCatID:68 And ProdCatID:45 the sum will return 0.20 +
2.56
And I need this to be done for All posible combinations
of ToolCatID and ProdCatID.
Any thoughts? I don't want to loop through a single SQL
statement that receives toolCat and prodCat as parameters,
I thought there's a way for SQL to manage this.
Thank you a lot for your time! I'm really desperate!Yes, exactly. Thanks a lot for your time, I've already
found a solution, which is to divide the problem in two
parts: The statement to select each row, and the one to
select that for all possible combinations of toolcat and
product cat(which was a very easy select). This seems to
work well but if you have a better idea please let me know.
Thank you so much for your time!! I really appreciate it!
Marcela
>--Original Message--
>So based on your example is this what you want the output
to be
>Toolcatid ProDCatID Sum Week
>68 45 0.2 + 2.56 8
>68 45 0.35+3.52 9
>68 53 0.2 + 2.56 8
>68 53 0.35+3.52 9
>
>"Marcela" <marcela.villalobos@.yahoo.com> wrote in message
>news:057b01c35bb1$af9e9310$a401280a@.phx.gbl...
>> Hello,
>> I'm having real problems defining a SELECT statement
for
>> the following scenario:
>> I have a Requirements table, each row in this table has
>> at least 1 row in the RequirementsWeeks table which
>> defines values for N weeks for each requirement.
>> Also, each requirement has a product value and a tool
>> value and each product belongs to a prodCategory and
each
>> tool belongs to a ToolCategory (these associations are
>> stored in separate tables)
>> Here's an example of the records I have:
>> Requirements: RequirementsWeeks:
>> Product Tool Id ReqId Week Value
>> A TA 1 1 8 0.20
>> C TB 2 1 9 0.35
>> 2 8 2.56
>> 2 9 3.52
>> Products: Tools:
>> Id ProductName ProCategoryID Id ToolName
ToolCatID
>> 1 A 45 1 TA 68
>> 2 B 53 2 TB 68
>> 3 C 45
>>
>> What i want to have is:
>> For all pairs product-tool in requirements that matches
>> ToolCatID And ProdCatID return the sum for week N
>> For example:
>> ToolCatID:68 And ProdCatID:45 the sum will return
0.20 +
>> 2.56
>> And I need this to be done for All posible combinations
>> of ToolCatID and ProdCatID.
>> Any thoughts? I don't want to loop through a single SQL
>> statement that receives toolCat and prodCat as
parameters,
>> I thought there's a way for SQL to manage this.
>> Thank you a lot for your time! I'm really desperate!
>>
>
>.
>

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id
|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

Extracting month, day, year from dates

I need help in building a Select statement for an ODBC connection to an AS400 DB2 system. I am using this Select statement for SQL Reporting Services.

Unfortunately 15 years ago one of the main datatables on the AS400 was set up splitting the transaction date into THREE FIELDS! One for Month, one for Day and then one for Year.

I need to take yesterday's date and somehow use this in my WHERE statement to connect the transaction records that are in this AS400 table. This is what I have now that is not working:

WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(GETDATE() ) AND
(QS36F.DOGOWNR.TDAY = "DAY"(GETDATE()-1 ) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(GETDATE())
GROUP BY QS36F.BRDMST.BRDESC

How do I extract each day, month, and year from the current date?

Thanks for the information

SqlServer has a DatePart function. Is that what you're looking for?

Mike

|||

Mike,

I did try the DATEPART function and was getting an AS400 ODBC Client Express error. I do believe I solved the problem, however I just had this query running over two hours and just killed it. Here is the SELECT statement:

SELECT COUNT(QS36F.BRDMST.BRDESC) AS BreedCount, QS36F.BRDMST.BRDESC
FROM QS36F.BRDMST, QS36F.DOGOWNR
WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(NOW())) AND
(QS36F.DOGOWNR.TDAY = "DAY"(NOW()) - 1) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(NOW()))
GROUP BY QS36F.BRDMST.BRDESC

The DOGOWNR table has about 1,500,000 records.

I need this to be a lot quicker. Might you have any suggestions? My thought is to use SSIS to extract all of the data into SQL from the AS400 and then build the reports through that, except that SSIS will have to run nightly to copy over any differential data.

Thanks for the information.

|||

Wish I could help, but I'm no DBA. I wonder where the bottlenecks are, guessing it's the ODBC business and not the query? Would be interesting to run that query right on the AS400 and see how fast it is.

I did a quick google trying to see if there's a .NET driver available. I found this - http://cc.borland.com/Item.aspx?id=23616 - not sure if it will help or not.

Mike

|||

Mike,

Our AS400 contractor happens to be here today and I asked her. She said that for the AS400 itself to query this datatable it takes about 1/2 an hour. I do believe too it has something to do with ODBC which is why I might go the SSIS route and replicate the data that is needed.

I will also check out that .Net driver. Thanks for the help.

|||You are going to have a problem with that if it is the 1st of the month or of the year. Make sure you are using Now() or Now() -1 in all instances, not a combination of the two.

If your query is running much longer than expected you might want to make sure the data types match up. If your TYear field and Year(Now()) are different data types it is possible that your TYear is being converted rather than the result of Year(Now()). If this was the case I don't think your index would be used. In any case check your query plan to be sure.

When you check the plan, make sure that the expected table is driving the query. I am not familiar with AS400 but in some database implementation this is critical.

count(*) is often more effecient than count(column). Since you are grouping on the column being counted you don't really need count to filter out counting of the null columns.

You might also experiment with a view that does the counting for you, then join the view back tot he breed table.

Create View OwerCountByBreed As
Select brdcod, count(*) "TheCount"
From dogownr
Where
TYear = Year(Now()-1) And
TMonth = Month(Now()-1) And
TDay = Day(Now-1)
Group By brdcod

Select brddesc, TheCount
From mrdmst, ownercountbybreed
Where brdmst.breed# = ownercountbybreed.brdcod

Just some thoughts. Sorry if this was all obvious.
|||

Some of that was obvious but not thought of by me so thank you for the reminders (like having be the first day of the month). I think maybe an easier solution may be to do a DATEADD. I don't know, I will figure it out today.

I also am going to try using the IBM AS400 OleDB adapter as opposed to using ODBC and see if that helps any.

I am mainly a developer, not a DB Admin. I am very familiar with simple SELECT statements. I am learning more and more about advanced SELECT statements.

|||

Hi,

I have some experience in AS400/DB2 - it look to me like you are running this on system 36 flat files (system 36 environment on the AS400) am I correct?

This may be a mute point - but have you checked the indexing on the DB2 database? Adding one index may well speed things up.

Also, what anti-virus do you have on your PC? Strange question? I have know Norton realtime scan to try and check all data as it was returned via ODBC - just a thought.

|||I was able to use SSIS useing Derived Colums to pull the data into a Flat File. I then copied the flat file into SQL. The initial pull from the AS400 took about two hours for almost 2.5 million records, but the flat file to SQL took about 30 seconds.

Extracting image column to the file

Hello all,
how can I extract image strored in DB in to file?
Will something like this work?
DECLARE @.cmd VARCHAR(2048)
SET @.cmd = 'bcp select history_image from recipient where recipient_id = 1
queryout c:\test\625954.jpg '
EXEC master..xp_cmdshell @.cmd, NO_OUTPUTYou want to use TextCopy.
e.g.
--OJ: TEXTCOPY example
-- Loading txt files into db
--create tb to hold data
create table tmp(fname varchar(100),txt text default '')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50)
--specify desired folder
set @.path='c:\test'
set @.sql='dir ' + @.path + '*.txt /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"dev" /d"tempdb" /t"tmp" /c"txt" /w"where
fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i'
print @.sql
exec master..xp_cmdshell @.sql,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
drop table tmp
go
-oj
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:DDDCCF4D-23B1-4348-9740-47A7738C9E36@.microsoft.com...
> Hello all,
> how can I extract image strored in DB in to file?
> Will something like this work?
> DECLARE @.cmd VARCHAR(2048)
> SET @.cmd = 'bcp select history_image from recipient where recipient_id = 1
> queryout c:\test\625954.jpg '
> EXEC master..xp_cmdshell @.cmd, NO_OUTPUT

extracting dates

hi
I want to select all the dates between 2 dates.ie if i'm passing 17th June and 19th june then it shud give 17,18 and 19.
Like wise if i'm giving 28th june and 3rd july it shud return 28,29,30,1,2,3.
Pls help me.
Thnx in advance
Nebubetween '20040101' and ''20040102