Not a valid identifier while executing an Stored procedure

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma

Northwind database extra SQL needs

I have asked for the following questions and I need your advises.
Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.
1=2E I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2=2E Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3=2E Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4=2E We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.
5=2E Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than =BD of their orders
are being shipped to a region other than their home region.
Please advise ...thanks a lotsoalvajavab1@.yahoo.com wrote:
> I have asked for the following questions and I need your advises.
> Utilizing the Northwind database suppied with SQL Server, create SQL to
> solve each of the exercises listed.
> 1. I want to contact all customers who have received over $1,000 in
> discounts on orders this year. Give me the name and phone number of the
> person to contact at the customers site. Also, list the orders where
> the total discount was greater than $100. Remember, discount is a
> percentage of the price.
> 2. Give me a list of suppliers and products where we do not have the
> stock on hand to fill the orders to be shipped. List out the customer
> and order information for each of the products involved.
> 3. Give me a list of all orders that were shipped after the required
> date for the week of Jan 7, 2001. I want to know the name of the
> employees that were responsible for the orders.
> 4. We are having a golf tournament and I need some prizes. Give me a
> list of the top 5 shippers by dollar amount in the last year.
> 5. Some customers are taking us for a ride on shipping. Give me a list
> of customers and the orders involved where more than ½ of their orders
> are being shipped to a region other than their home region.
> Please advise ...thanks a lot
>
Homework assignment for the weekend?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I recommend start with something like: www.sqlcourse.com
It sounds like you are asking us to do your class work for you. If you
tried, and was not successful, and then asked for assistance, I would
happily assist.
But I won't do it for you.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
<soalvajavab1@.yahoo.com> wrote in message
news:1152300594.681810.103240@.75g2000cwc.googlegroups.com...
I have asked for the following questions and I need your advises.
Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.
1. I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2. Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3. Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4. We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.
5. Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than ½ of their orders
are being shipped to a region other than their home region.
Please advise ...thanks a lot|||This is a multi-part message in MIME format.
--080504070700040905040602
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
soalvajavab1@.yahoo.com wrote:
> I have asked for the following questions and I need your advises.
> Utilizing the Northwind database suppied with SQL Server, create SQL to
> solve each of the exercises listed.
> 1. I want to contact all customers who have received over $1,000 in
> discounts on orders this year. Give me the name and phone number of the
> person to contact at the customers site. Also, list the orders where
> the total discount was greater than $100. Remember, discount is a
> percentage of the price.
> 2. Give me a list of suppliers and products where we do not have the
> stock on hand to fill the orders to be shipped. List out the customer
> and order information for each of the products involved.
> 3. Give me a list of all orders that were shipped after the required
> date for the week of Jan 7, 2001. I want to know the name of the
> employees that were responsible for the orders.
> 4. We are having a golf tournament and I need some prizes. Give me a
> list of the top 5 shippers by dollar amount in the last year.
> 5. Some customers are taking us for a ride on shipping. Give me a list
> of customers and the orders involved where more than ½ of their orders
> are being shipped to a region other than their home region.
> Please advise ...thanks a lot
>
I think you should start with looking up the SELECT statement in Books
On Line. I think that will get you started doing your homework...:-).
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--080504070700040905040602
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:soalvajavab1@.yahoo.com">soalvajavab1@.yahoo.com</a> wrote:
<blockquote
cite="mid1152300594.681810.103240@.75g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">I have asked for the following questions and I need your advises.
Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.
1. I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2. Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3. Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4. We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.
5. Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than ½ of their orders
are being shipped to a region other than their home region.
Please advise ...thanks a lot
</pre>
</blockquote>
<font size="-1"><font face="Arial">I think you should start with
looking up the SELECT statement in Books On Line. I think that will get
you started doing your homework...:-).<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--080504070700040905040602--

Northwind database extra SQL needs

I have asked for the following questions and I need your advises.
Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.
1=2E I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2=2E Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3=2E Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4=2E We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.
5=2E Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than =BD of their orders
are being shipped to a region other than their home region.
Please advise ...thanks a lotsoalvajavab1@.yahoo.com wrote:
> I have asked for the following questions and I need your advises.
> Utilizing the Northwind database suppied with SQL Server, create SQL to
> solve each of the exercises listed.
> 1. I want to contact all customers who have received over $1,000 in
> discounts on orders this year. Give me the name and phone number of the
> person to contact at the customers site. Also, list the orders where
> the total discount was greater than $100. Remember, discount is a
> percentage of the price.
> 2. Give me a list of suppliers and products where we do not have the
> stock on hand to fill the orders to be shipped. List out the customer
> and order information for each of the products involved.
> 3. Give me a list of all orders that were shipped after the required
> date for the week of Jan 7, 2001. I want to know the name of the
> employees that were responsible for the orders.
> 4. We are having a golf tournament and I need some prizes. Give me a
> list of the top 5 shippers by dollar amount in the last year.
> 5. Some customers are taking us for a ride on shipping. Give me a list
> of customers and the orders involved where more than of their orders
> are being shipped to a region other than their home region.
> Please advise ...thanks a lot
>
Homework assignment for the weekend?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I recommend start with something like: www.sqlcourse.com
It sounds like you are asking us to do your class work for you. If you
tried, and was not successful, and then asked for assistance, I would
happily assist.
But I won't do it for you.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
<soalvajavab1@.yahoo.com> wrote in message
news:1152300594.681810.103240@.75g2000cwc.googlegroups.com...
I have asked for the following questions and I need your advises.
Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.
1. I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2. Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3. Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4. We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.
5. Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than of their orders
are being shipped to a region other than their home region.
Please advise ...thanks a lot|||soalvajavab1@.yahoo.com wrote:
> I have asked for the following questions and I need your advises.
> Utilizing the Northwind database suppied with SQL Server, create SQL to
> solve each of the exercises listed.
> 1. I want to contact all customers who have received over $1,000 in
> discounts on orders this year. Give me the name and phone number of the
> person to contact at the customers site. Also, list the orders where
> the total discount was greater than $100. Remember, discount is a
> percentage of the price.
> 2. Give me a list of suppliers and products where we do not have the
> stock on hand to fill the orders to be shipped. List out the customer
> and order information for each of the products involved.
> 3. Give me a list of all orders that were shipped after the required
> date for the week of Jan 7, 2001. I want to know the name of the
> employees that were responsible for the orders.
> 4. We are having a golf tournament and I need some prizes. Give me a
> list of the top 5 shippers by dollar amount in the last year.
> 5. Some customers are taking us for a ride on shipping. Give me a list
> of customers and the orders involved where more than of their orders
> are being shipped to a region other than their home region.
> Please advise ...thanks a lot
>
I think you should start with looking up the SELECT statement in Books
On Line. I think that will get you started doing your homework...:-).
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministratorsql

Northwind database extra SQL needs

I have asked for the following questions and I need your advises.

Utilizing the Northwind database suppied with SQL Server, create SQL to
solve each of the exercises listed.

1.I want to contact all customers who have received over $1,000 in
discounts on orders this year. Give me the name and phone number of the
person to contact at the customers site. Also, list the orders where
the total discount was greater than $100. Remember, discount is a
percentage of the price.
2.Give me a list of suppliers and products where we do not have the
stock on hand to fill the orders to be shipped. List out the customer
and order information for each of the products involved.
3.Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.
4.We are having a golf tournament and I need some prizes. Give me a
list of the top 5 shippers by dollar amount in the last year.

5.Some customers are taking us for a ride on shipping. Give me a list
of customers and the orders involved where more than of their orders
are being shipped to a region other than their home region.

Please advise ...thanks a lotOn 7 Jul 2006 12:29:00 -0700, soalvajavab1@.yahoo.com wrote:

Quote:

Originally Posted by

>I have asked for the following questions and I need your advises.
>
>Utilizing the Northwind database suppied with SQL Server, create SQL to
>solve each of the exercises listed.

(snip)

Quote:

Originally Posted by

>Please advise ...thanks a lot

Hi soalvajavab1,

If you're following a SQL course, my advise is to review the lessons
yoou have trouble with. If you then still have trouble solving these
puzzles, ask your teacher for more explanation. And if you need help on
some specific issues, come back here - just don't ask us to provide
complete solutions for your homework.

If I would provide the answers for you to copy straight away, you
wouldn't learn to solve these issues by yourself. That will bite you
when you sit an exam without internet access. Or when you're in your
first job and it turns out that your graduation certificate isn't worth
the paper it's printed on.

--
Hugo Kornelis, SQL Server MVP|||(soalvajavab1@.yahoo.com) writes:

Quote:

Originally Posted by

3. Give me a list of all orders that were shipped after the required
date for the week of Jan 7, 2001. I want to know the name of the
employees that were responsible for the orders.

Hey, that's an easy one! There are no orders for 1999 or later in
Northwind...

For the rest, I echo Hugo's reply. We don't mind helping people here, but
we don't do your homework.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

normalizing data warehouse

Here’s a story. I’m in a warehouse and I’m to normalize 8M records
The employee dimesion is composed of the following fields
Employee_key, employee_no, center_id, date_hired and other fields
What I want is to
1. Select disctint employee_no, centerid, datehired
Plus
2. the “top 1 employee_key” per group if grouped by
(employee_no,centerid,datehired)
3. no cursor pls.
the resultset
employee_no, centerid, datehired, employee_key --<--top 1
thank you, thank you…
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787does this help? You could consider using min() function instead...
select
max(employee_key) as EmpKey,
employee_no,
centerid,
datehired
from
table
group by
employee_no, centerid, datehired
MC
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:AF491592-2324-42A0-B397-FBA301F3DD6F@.microsoft.com...
> Here's a story. I'm in a warehouse and I'm to normalize 8M records
> The employee dimesion is composed of the following fields
> Employee_key, employee_no, center_id, date_hired and other fields
> What I want is to
> 1. Select disctint employee_no, centerid, datehired
> Plus
> 2. the "top 1 employee_key" per group if grouped by
> (employee_no,centerid,datehired)
> 3. no cursor pls.
> the resultset
> employee_no, centerid, datehired, employee_key --<--top 1
> thank you, thank you.
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787

Normalization Question.

I have few doubts whether the following DB structure is good or rubbish.

Suppose you are an organization has many cinemas,Theatres,Venues etc:
Cinemas
Theatres
Venues
etc

and you want to store-retrieve about each one of this item

would you normalize like this

Organization Table
==================
orgID PK
OrgName

CinemaInOrganization
==================
OrgID PK
CinID PK

TheatreInOrganization
==================
OrgID PK
CinID PK

VenueInOrganization
====================
OrgID PK
VenID PK

Do you envisage any problem with querying tables with this structure?

Thanks a lot in advance

Not really, it largely depends on how your systems handle the Theatres, Cinemas and Venues.

Personally they are all types of venues, what commonality of data and function is shared across them all?

|||

Thanks a lot for your quick reply.
That was just a fictious example that strongly reflect a scenario I have to implement.

The commonality among them is that all these Items (Venues,Theatres etc) they all belong to an Organization.

What I wanted to establish is that if you have the following Tables:

Venue VeniD -VenName etc
Theatre ThID -THName etc
Cinema CinID -CinName

and then this organization has a one to many toabove tables should you create Link table for each one of them as I mentioned in the original post?

Thanks again

|||

My confusion.

I would only create link tables where you can have many to many relationships. It really complicates the issue to have those intermediate tables when you don't need them (in my view)

Normalization

I am a bit confused with normalization, hopefully someone here can help me out.
I got the following unnormalized list:

UNNORMALIZED
R1 = (ward_no, ward_name, patient_no, first_name, last_name, drug_card_no (drug_code, drug_name, date_dispensed, dosage))
Now i want to normalize it to the 3NF.
This is what i have tried:

1NF
R11 = (ward_no, ward_name (patient_no, first_name, last_name, drug_card_no))
R12 = (drug_code, drug_name, date_dispensed, dosage)

2NF
R111 = (ward_no, ward_name)
R112 = (ward_no, patient_no, first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

3NF
R111 = (ward_no, ward_name)
R1121 = (ward_no, patient_no, first_name)
R1122 = (first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

But now am not sure if its right. I think i may be missing somethings. could someone advise?
Thanks1NF means no repeating groups

it looks like R11 repeats patients in a ward, so that fails 1NF

what are you using as your reference for normalization? a textbook or the internet? if a textbook, please give its title and author, if the internet, please give urls of the sites you're using|||Well im just using the lecture slide note we got in class.
now for that 1NF i know what you mean so i was thinking like this:
1NF
R11 = (ward_no, ward_name patient_no, first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

without that new group? anything else|||here are two good resources:
Relational Data Architecture (PDF) (http://www.oreilly.com/catalog/javadtabp/chapter/ch02.pdf)
3 Normal Forms Database Tutorial (http://www.phlonx.com/resources/nf3/)|||i understand the rules but then once i try doin on i jus get confused i u can see in the above.|||Can you give us a sample of your data. Then we can show you the transition to 1NF, 2NF, 3NF. The problem is that you have a represented your initial data in a column related format so we can't actually see where data repeats itself.

The two rules to follow for 1NF are :
1. A row of data cannot contain repeating groups of similar data (atomicity); and
2. Each row of data must have a unique identifier (or Primary Key).

You initial line actually looks like 1NF already to me, however it doesn't identify primary keys.|||ummm well this is what i was given:

R1 = (ward_no, ward_name, patient_no, first_name, last_name, drug_card_no (drug_code, drug_name, date_dispensed, dosage))

thats all i can give u sorry.

Noob Query Problem -This will be easy for you

This is driving me crazy. I am using a simple ASP table editor to make changes to an Access table. The following command is causing me to get an error:

UPDATE rates Set IRItype='A2', IRIProductName='Stylish', SQfeet='803', Bathrooms=1, IRIunitprice='600',

This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The SQL command is automaticly generated by the ASP script. I do not know if that extra comma is the cause of the error.is that all there was to the UPDATE statement? if so, it produces an error because it ends with a dangling comma

typically, you would have a WHERE clause, unless your intention is to update all rows to those values

so yeah, look into the ASP script|||The extra comma is certainly a problem. Also, make sure SQfeet and IRIunitprice are text values in the table. If they are numeric, then you wouldn't want the single quote surrounding the values.

Noob need help with recursion

I created a table called MyTable with the following fields :

Id int
Name char(200)
Parent int

I also created the following stored procedure named Test, which is supposed to list all records and sub-records.

CREATE PROCEDURE Test
@.Id int
AS

DECLARE @.Name char(200)
DECLARE @.Parent int

DECLARE curLevel CURSOR LOCAL FOR
SELECT * FROM MyTable WHERE Parent = @.Id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Id AS Id, @.Name AS Name, @.Parent AS Parent
EXEC Test @.Id
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
END

CLOSE curLevel
DEALLOCATE curLevel
GO

I added a MxDataGrid and DataSourceControl.
SelectCommand property of the DataSourceControl = EXEC Test 0

When I run the aspx page, it only shows 1 record. I tried to change the parameter to 1, 2, 3 but it always shows only 1 record (not the same tho).

Is there something wrong with the stored procedure ?Ok, I made some modifications to make it work properly but there is some limitations. I have to store the results in a temp table. Can I do something similar but without the temp table ?

(Note : I changed some field/table names)

-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
AS
DELETE FROM TmpNodesAndSubNodes
EXEC GetNodesAndSubNodesRecursive @.NodeId
SELECT * FROM TmpNodesAndSubNodes
GO
-- ----------------------
-- Fill the temp table with the id's of the nodes and it's sub nodes
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodesRecursive
@.NodeId INT
AS
-- Store the node id into the temp table
INSERT INTO TmpNodesAndSubNodes VALUES(@.NodeId)
-- Declare a local cursor to seek in the nodes table
-- and some variables to retreive the values
DECLARE @.CurrentNodeId INT
DECLARE @.CurrentParentNodeId INT
DECLARE curLevel CURSOR LOCAL FOR
 SELECT Id FROM Nodes WHERE ParentNodeId=@.NodeId
-- Fetchs the records and call the recursive method for each node id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
WHILE @.@.FETCH_STATUS = 0
BEGIN
 EXEC GetNodesAndSubNodesRecursive @.CurrentNodeId
 FETCH NEXT FROM curLevel INTO @.CurrentNodeId
END
-- Clean up
CLOSE curLevel
DEALLOCATE curLevel
GO

|||Aaarg :)

I decided to work on a non recursive solution.

Non-Yielding on Scheduler 1

I'm receiving the following error on SQL Server 2000 SP4 (no additional
hotfixes after SP4 have been installed.)
Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
Scheduler 1.
Following this message I receive the follow message
Error: 17883, Severity: 1, State: 0
Then logins to the server fail and SQL stop responding. Any assistance in
solving this problem will be appreciated.Check the section titled Error 17881 and Error 17883
in the following article:
http://support.microsoft.com/?id=319892
You would want to search support.microsoft.com on 17883 for
related articles but most of the issues have been addressed
in SP4 (most, not all)
For a good understanding of UMS, check the following
article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_02252004.asp
-Sue
On Fri, 8 Sep 2006 08:27:01 -0700, Sid
<Sid@.discussions.microsoft.com> wrote:
>I'm receiving the following error on SQL Server 2000 SP4 (no additional
>hotfixes after SP4 have been installed.)
>Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
>Scheduler 1.
>Following this message I receive the follow message
>Error: 17883, Severity: 1, State: 0
>Then logins to the server fail and SQL stop responding. Any assistance in
>solving this problem will be appreciated.

Non-Yielding on Scheduler 1

I'm receiving the following error on SQL Server 2000 SP4 (no additional
hotfixes after SP4 have been installed.)
Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
Scheduler 1.
Following this message I receive the follow message
Error: 17883, Severity: 1, State: 0
Then logins to the server fail and SQL stop responding. Any assistance in
solving this problem will be appreciated.Check the section titled Error 17881 and Error 17883
in the following article:
http://support.microsoft.com/?id=319892
You would want to search support.microsoft.com on 17883 for
related articles but most of the issues have been addressed
in SP4 (most, not all)
For a good understanding of UMS, check the following
article:
http://msdn.microsoft.com/library/d...ev_02252004.asp
-Sue
On Fri, 8 Sep 2006 08:27:01 -0700, Sid
<Sid@.discussions.microsoft.com> wrote:

>I'm receiving the following error on SQL Server 2000 SP4 (no additional
>hotfixes after SP4 have been installed.)
>Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
>Scheduler 1.
>Following this message I receive the follow message
>Error: 17883, Severity: 1, State: 0
>Then logins to the server fail and SQL stop responding. Any assistance in
>solving this problem will be appreciated.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Hi all,

Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

After the first error all subsequent queries would run perfectly.

What am I missing?

Avi

The first message indicates that there were users found in the principal when you issued the failover. These users have to be killed and their transactions rolled back.

The second exception message tends to indicate that the connection getting the error was one of the users found in that database and they were killed.

|||

Thanks for the reply!

Could you please elaborate some more why the connection sometimes get killed and the action rolled back. Does not mirroring suppose to move the connection to the active database without killing it?

Thanks,

Avi

|||No, that is not how it works. It can reconnect to the new mirror, but the existing connection will get killed and its transaction rolled back.|||Standard database projection requires that if a connection is killed prior to the transaction being completed that the transaction must be rolled back. By initiating a failover all connections are severed and must therefor be rolled back.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Hi all,

Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

After the first error all subsequent queries would run perfectly.

What am I missing?

Avi

The first message indicates that there were users found in the principal when you issued the failover. These users have to be killed and their transactions rolled back.

The second exception message tends to indicate that the connection getting the error was one of the users found in that database and they were killed.

|||

Thanks for the reply!

Could you please elaborate some more why the connection sometimes get killed and the action rolled back. Does not mirroring suppose to move the connection to the active database without killing it?

Thanks,

Avi

|||No, that is not how it works. It can reconnect to the new mirror, but the existing connection will get killed and its transaction rolled back.|||Standard database projection requires that if a connection is killed prior to the transaction being completed that the transaction must be rolled back. By initiating a failover all connections are severed and must therefor be rolled back.

Non-equi Joins using <>

Can we replace the where clause operator of '<>' with a join syntax condition.

Could someone explain me the dynamics behind the following query:

select a.b, c.d from a inner join c on a.b <> c.d

This could effectively replace the NOT IN and EXCEPT operators

No.. <> wont work as you expected. You query is similar to the following query,

select * from a cross join cWhere a.b <> c.d

Since it is set opertation your query will guide you to make a cross join and it only filter those values which are present on both the tables.

You have to use NOT IN or NOT EXISTS on your query. Join will be compared on both tables row by row. The IN & EXISTS will be verified with the one table value with other tables rows of value.

|||

Since, I am avoiding the use of NOT IN & NOT EXISTS due to its obvious perofrmance degradations, I also wouldn't use a Cross Join (which I think for tables with more rows would be terribly slow). Hence, would I get a Join query to have a performance upgrade over them.

|||

Hi there,

NOT IN and NOT EXISTS are no the same when it comes to performance. NOT EXISTS is much better in terms of performance especially if you're checking for a specific value as the query would return a TRUE value if a match is found and therefore avoid further iterations.

|||

I tend to use NOT EXISTS in these situations to take advantage of the semi-joins. Also, beware that the EXCEPT operator is typically slower.

Here are some previous threads that discuss NOT IN versus NOT EXISTS:

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

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726903&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087482&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=637335&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654087&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

Here is a thread that discusses NOT IN versus EXCEPT:

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

Nonempty Problem

following query is nt working properly. need a solution (need to filter the non empty rows)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
non empty CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]NonEmpty({filter(CROSSJOIN([CustomTimeSet],[GeneralLedgerSet]),[Measures].[MdaCodeTotal] <> 0 )})on rows

I am a bit suspicious about the [Measures].[Description] measure. Is this a calculated measure? It could be what is causing the non empty clause not to work. If you are using SSAS 2005, something like the following might work:

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
nonempty( CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) , {Measures.MdaCodeTotal})on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]

Note I am using the second parameter in the NonEmpty() function to set the measure context for the non empty evaluation.

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
--
David Portas
SQL Server MVP
--

non_empty_behavior advice

Hello!

I'm new in the SSAS2005 world and was wondering if the following MDX script was good, especialy the non_empty_behavior part:

SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
Descendants([ENTITIES].CurrentMember, , LEAVES) * Descendants([PRODUCTS].CurrentMember, , LEAVES)
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

As you can see, I want to display Averages for a special member of my ACCOUNTS dim and the same member is used in the non_empty_behavior property (if there is no data at an agregated level, there is not data on the leaves levels and so no need to calculate averages, at least I think so . Is it right ?

I also tried to code this with the new EXISTING function but it doesn't seem to work
SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
EXISTING Leaves([ENTITIES]) * EXISTING Leaves([PRODUCTS])
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

Should I use EXISTING that way ?

Thx!

Unless you plan to support scenarios where there is multiselect on Entities or Products - leave EXISTING out of it - the Descendants function will work just fine. If you do need to support multiselect, than you can use EXISTING, but please don't use Leaves() function in the right-hand side of assignment. You can replace it with the key level of appropriate dimension. It would also help if Entities and Products were not parent-child dimensions.

Use of Non_Empty_Behavior here is probably OK, but only if you don't have some other calculations (or even unary operators) which can interfere with how PU_MOI is computed.

|||

Thanks for your answer Mosha!

In fact, there may be a multi-select on Entities but the Average value wouldn't be of interest in that way so.. I also forgot to mention but Entities and Products are parent-child dims, so if I use the EXISTING formula, I have to use Leaves too, or it there another way ?

By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ? Maybe a Filter([Entities].Members) against the currently selected Entity ? Or is there a 'IsCalculatedMember' like function I could use ?

|||

The fact that Entities and Products are parent-child will cause issues, but you still don't need to use Leaves function. Descendants with LEAVES flag is good enough here. For more differences between the two, check out this blog: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

> By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ?

Sorry, but I didn't understand the question.

non paged pool is empty error

I have received the following error
The server was unable to allocate from the system nonpaged pool because the
pool was empty.
Any idea on how to trap which process is causing the memory to leak or be
used up. This has caused SQL Server to be unavailable. I had to reboot as a
fix
I would like to trap it before it happens again. Any way to do so ?
Using Win2K3 and SQL 2KHi Hassan
I would expect this to be a gradual therefore monitoring the memory usage
over time would possibly indicate which process is not releasing memory.
Check out
http://www.microsoft.com/technet/prodtechnol/exchange/Guides/TrblshtE2k3Perf/7a44b064-8872-4edf-aac7-36b2a17f662a.mspx?mfr=true
and the perfmon counters in http://ask.support.microsoft.com/kb/133384
Also see if this applies:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272568&sd=ee
John
"Hassan" wrote:
> I have received the following error
> The server was unable to allocate from the system nonpaged pool because the
> pool was empty.
> Any idea on how to trap which process is causing the memory to leak or be
> used up. This has caused SQL Server to be unavailable. I had to reboot as a
> fix
> I would like to trap it before it happens again. Any way to do so ?
>
> Using Win2K3 and SQL 2K
>
>
>