Not A Trusted Connection

I set up my SQLExpress server using Windows Authentication only. Now when I
try to create a user account using Username and Password, the connection
returns the error: Not a trusted connection. Does this have something to do
with my previous server setup or is there a general setting somewhere I need
to change?
Regards,
Fred Chateau
http://hotelmotelnow.com
Never mind... I found the setting.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u323BNenHHA.588@.TK2MSFTNGP06.phx.gbl...
>I set up my SQLExpress server using Windows Authentication only. Now when I
>try to create a user account using Username and Password, the connection
>returns the error: Not a trusted connection. Does this have something to do
>with my previous server setup or is there a general setting somewhere I
>need to change?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>

Not A Trusted Connection

I set up my SQLExpress server using Windows Authentication only. Now when I
try to create a user account using Username and Password, the connection
returns the error: Not a trusted connection. Does this have something to do
with my previous server setup or is there a general setting somewhere I need
to change?
Regards,
Fred Chateau
http://hotelmotelnow.comNever mind... I found the setting.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u323BNenHHA.588@.TK2MSFTNGP06.phx.gbl...
>I set up my SQLExpress server using Windows Authentication only. Now when I
>try to create a user account using Username and Password, the connection
>returns the error: Not a trusted connection. Does this have something to do
>with my previous server setup or is there a general setting somewhere I
>need to change?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>

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

Noob question: SELECT statement

Hi,
I'm very new to RS. I'm trying to find a single piece of data in a table. Do I need to create a seperate dataset for it or is there some way that I can insert a SELECT statement into a textbox? Maybe a sub query of an existing dataset?
Thanks in advance.
WOW!
To date this has had 111 views and ZERO posts. Can noone help me?
|||No you can't do this. Your select statement must populate a dataset and return the single value if thats what you want. You can also used a stored procedure to return the value into the dataset.

If its a value from an dataset that is already loaded you may beable to select which row in the dataset to take the value from. I am not 100% sure on how to do that one tough.

Hope that helps
Martin

noob question for msde2000

looks like a noob question but how can i manage msde2000...i can see sql is runnin but how can i create database , attach/detach database, write functions and so on...
thanx alot for the reply :)Originally posted by Schuldiner
looks like a noob question but how can i manage msde2000...i can see sql is runnin but how can i create database , attach/detach database, write functions and so on...

thanx alot for the reply :)

msde does not come with tools like EM or QA but there are some tools available for database management in msde to name a few by microsoft are SQL Server Web Data Administrator SQL Server Web Data Administrator (http://www.microsoft.com/downloads/details.aspx?familyid=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en) and OSQL

for more info in detail check:
http://www.aspfaq.com/show.asp?id=2442|||thank u very much :)

non-updatable views

can i create a non-updatable view?
i cannot use user level restrictions.Check out the "CREATE VIEW" page in SQL Server 2000 Books Online, which
outlines some rules to make views updatable, and see if you can use one of
those rules to make your views non-updatable. For example, if the view
definition contains a derived column, that view cannot be updated. Here's a
quick example:
CREATE VIEW MyView AS SELECT Col1, Col2, 'X' AS NotUpdatable FROM MyTable
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<mcourter@.mindspring.com> wrote in message
news:1122910718.612379.166500@.g44g2000cwa.googlegroups.com...
can i create a non-updatable view?
i cannot use user level restrictions.

non-required parameter

I using BI development studio to create some reports and one of the stored procedures that I have takes in 4 parameters.

ManagerID int =0
OfficeID int =0
StartDate datetime
EndDate datetime

In the report designer I use 2 other sql queries to populate ManagerID fields and OfficeID Fields.

The report will work if the customer selects both ManagerID and OfficeID fields but will not work if Manager OR OfficeID fields are select. They both have to be selected or an error message will be displayed stating that the non-selected field must have a value.

The the parameters properties section I have changed the type is integer and select allowed NULLs, I have even set the default value to 0 this didn't work. Then I tried changing the type from integer to string and selected 'allowed blanks'.

I still get the popup message stating that the non-select item must have a value.

Is there a way I can select 1 and not the other?

why not to try to add those drop down list to the web page not in the report and pass the selected parameters to the report file

Sample code

Private Sub SetReportParameters()
Dim userid As New ReportParameter("userid",
Security.GetUserID().ToString())
Dim p() As ReportParameter = {userid}
ReportViewer1.ServerReport.SetParameters(p)
End Sub

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

|||

Tthank you for the reply.

One thing I should have mentioned is that we are using ColdFusion 7 with SQL 2005.
I am still learning a few things about it.
If will try passing in those parameters but I am not sure how it will work with this structure.

|||

I don't think there is any problem but i cam through great web cast for this hope it helps you

SQL Server Reporting Services for Cold Fusion Developers
https://www119.livemeeting.com/cc/mseventsbmo/view?id=1032256029&role=attend&pw=7933C65D

|||

Thank you for the link, I have been looking for this video for awhile and all I got was a word document of the webcast.

I will be watching it soon.

I will mark this as answered.

Nonprintable / encrypted characters is not found

Hi All

I have a table with one column :

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

The column a is filled with encrypted data
(contains control and extended characters).
On my test, the select statement for one row
in table test2 does not always work sucessfully.

Below is my unsucessful statement for the second
row followed by 9 rows data. Each row is displayed
in 2 versions, text and VB ascii code.

select * from test2
where a = '\0<[\
|^[\]}{;\'

----
Row-1
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row-2
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
Row-3
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row-4
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row-5
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row-6
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row-7
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row-8
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row-9
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
----

I have 3 questions for all of you :
- Why SQL2000 can not find the second row (certain row)?
- Can SQL2000 handle character 0 to 255 ?
- Does my encrypted method produce bad data for SQL2000?

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Anita" <anonymous@.devdex.com> wrote in message
news:4029728d$0$196$75868355@.news.frii.net...
> Hi All
> I have a table with one column :
> CREATE TABLE test2
> ( a char(15)
> primary KEY CLUSTERED )
> The column a is filled with encrypted data
> (contains control and extended characters).
> On my test, the select statement for one row
> in table test2 does not always work sucessfully.
> Below is my unsucessful statement for the second
> row followed by 9 rows data. Each row is displayed
> in 2 versions, text and VB ascii code.
> select * from test2
> where a = '\0<[\
> |^[\]}{;\'
> ----
> Row-1
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row-2
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> Row-3
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row-4
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row-5
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row-6
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row-7
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row-8
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row-9
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> ----
> I have 3 questions for all of you :
> - Why SQL2000 can not find the second row (certain row)?
> - Can SQL2000 handle character 0 to 255 ?
> - Does my encrypted method produce bad data for SQL2000?
> Thanks in advance
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

1. I don't know, but the most obvious reason is that your search string
doesn't exactly match the encrypted string. Did you enter the SELECT
statement exactly as above? If so, then the issue could be that SQL ignores
whitespace, so you would need something like this to handle the newline:

select * from test2
where a = '\0<[\' + char(10) + '|^[\]}{;\'

2. Yes

3. As long as the encrypted string is in a character set supported by the
server (and you could use Unicode if necessary), then there shouldn't be any
specific issues - it's up to your application to do the
encrpytion/decryption.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:402a7e11$1_3@.news.bluewin.ch...
> "Anita" <anonymous@.devdex.com> wrote in message
> news:4029728d$0$196$75868355@.news.frii.net...
> > Hi All
> > I have a table with one column :
> > CREATE TABLE test2
> > ( a char(15)
> > primary KEY CLUSTERED )
> > The column a is filled with encrypted data
> > (contains control and extended characters).
> > On my test, the select statement for one row
> > in table test2 does not always work sucessfully.
> > Below is my unsucessful statement for the second
> > row followed by 9 rows data. Each row is displayed
> > in 2 versions, text and VB ascii code.
> > select * from test2
> > where a = '\0<[\
> > |^[\]}{;\'
> > ----
> > Row-1
> > %;>.[
> > ,)]/\-,=/
> > 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> > Row-2
> > \0<[\
> > |^[\]}{;\
> > 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> > Row-3
> > \0<[\ ,)? {=\?
> > 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> > Row-4
> > \0<[\ \%:_`- ]_
> > 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> > Row-5
> > \0<[\ \^:& }{;\
> > 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> > Row-6
> > \0[*_1>\_\ }{@.+
> > 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> > Row-7
> > ].>^/. ]-=}<)^&
> > 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> > Row-8
> > ]0_^{
> > }^~_|~{^_
> > 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> > Row-9
> > {
> > }{31{2|02~||
> > 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> > ----
> > I have 3 questions for all of you :
> > - Why SQL2000 can not find the second row (certain row)?
> > - Can SQL2000 handle character 0 to 255 ?
> > - Does my encrypted method produce bad data for SQL2000?
> > Thanks in advance
> > Anita Hery
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> 1. I don't know, but the most obvious reason is that your search string
> doesn't exactly match the encrypted string. Did you enter the SELECT
> statement exactly as above? If so, then the issue could be that SQL
ignores
> whitespace, so you would need something like this to handle the newline:
> select * from test2
> where a = '\0<[\' + char(10) + '|^[\]}{;\'
> 2. Yes
> 3. As long as the encrypted string is in a character set supported by the
> server (and you could use Unicode if necessary), then there shouldn't be
any
> specific issues - it's up to your application to do the
> encrpytion/decryption.
> Simon

Oops - it's not at all correct to say that SQL ignores whitespace; I was
thinking of trailing spaces there for some reason. What may have happened is
that Query Analyzer interpreted your newline as ASCII 13 (carriage return):

select ascii('
')

This gives 13, but you need 10, according to what you posted, so the select
query I suggested above should hopefully be what you need.

Simon|||Simon,

Thanks for your reply.

I show the exact data in ascii code. I am sure the source
of my problem is not about mistyping. I first found the
problem in my VB application. The following is a part of it.

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.OpenRecordset(s, dbOpenDynaset)
For i = 1 To rs.RecordCount
Debug.Print rs!a
X = ""
For j = 1 To Len(rs!a)
X = X & Asc(Mid(rs!a, j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" & rs!a & "'"
Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
If rs1.RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.movenext
Next
End Sub

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita,

I ran the following SQL Script and VBScript and it returned both rows. Does
this work in your environment? In any case, you might consider storing the
encrypted value as binary rather than character data.

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

DECLARE @.Value1 char(15)
SET @.Value1 =
CAST(0x253B3E2E5B0A2C295D2F5C2D2C3D2F AS char(15))
DECLARE @.Value2 char(15)
SET @.Value2 =
CAST(0x5C303C5B5C0A7C5E5B5C5D7D7B3B5C AS char(15))

INSERT INTO test2 VALUES(@.Value1)
INSERT INTO test2 VALUES(@.Value2)

SELECT
CAST(a AS binary(15)),
a FROM test2
WHERE a IN(@.Value1, @.Value2)

' test VBScript
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;" _ &
"Data Source=MyServer;" _ &
"Initial Catalog=MyDatabase;" _ &
"Integrated Security=SSPI"

dotest()
WScript.Echo "Done"

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
Do While rs.EOF = false
WScript.Echo rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
WScript.Echo X

s = "select * from test2 where a = '" & rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.RecordCount = 0 Then
WScript.Echo "==not found=="
End If
rs.movenext
Loop
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402aaa48$0$199$75868355@.news.frii.net...
> Simon,
> Thanks for your reply.
> I show the exact data in ascii code. I am sure the source
> of my problem is not about mistyping. I first found the
> problem in my VB application. The following is a part of it.
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.OpenRecordset(s, dbOpenDynaset)
> For i = 1 To rs.RecordCount
> Debug.Print rs!a
> X = ""
> For j = 1 To Len(rs!a)
> X = X & Asc(Mid(rs!a, j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" & rs!a & "'"
> Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
> If rs1.RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.movenext
> Next
> End Sub
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Thanks for your reply.

Currently I am working with DAO library / ODBC.
I am not familiar with VB script. So, I tried
using ADO library 2.7 and made a small modification
like this:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim qd As New ADODB.Command
Dim s As String

Private Sub Form_Load()
cn.ConnectionString = "PROVIDER=SQLOLEDB;
SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
cn.Open
dotest
End Sub

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
i = 0
Do While rs.EOF = False
i = i + 1
Debug.Print "Row " & i & " ----"
Debug.Print rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" &
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.EOF Then ' .RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.MoveNext
Loop
End Sub

If I use my original data, the above code will
produce the same problem on the same row (row 2).
Below is the output list:

====================
Row 1 ----
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row 2 ----
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
==not found==
Row 3 ----
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row 4 ----
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row 5 ----
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row 6 ----
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row 7 ----
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row 8 ----
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row 9 ----
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
=======================

The row 2 can be accessed by the query like this:

select * from test2
where a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

But, I will not use this way.

Anita

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi, Anita.

I was able to recreate your problem and narrowed it down to the
backslash/newline sequence in the character string. As a workaround, I used
a parameter rather than an embedded literal value. This technique also
eliminates the need to escape other special characters like single quotes.
Example below.

Dim cmd As New ADODB.Command
Dim parm1 As ADODB.parameter
cmd.CommandText = "SELECT * FROM test2 WHERE a = ?"
cmd.ActiveConnection = cn
Set parm1 = cmd.CreateParameter("@.Parm1", adChar, 1, 15, s)
cmd.Parameters.Append parm1
cmd.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402b81e0$0$202$75868355@.news.frii.net...
> Dan Guzman,
> Thanks for your reply.
> Currently I am working with DAO library / ODBC.
> I am not familiar with VB script. So, I tried
> using ADO library 2.7 and made a small modification
> like this:
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim qd As New ADODB.Command
> Dim s As String
> Private Sub Form_Load()
> cn.ConnectionString = "PROVIDER=SQLOLEDB;
> SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
> cn.Open
> dotest
> End Sub
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.Execute(s)
> i = 0
> Do While rs.EOF = False
> i = i + 1
> Debug.Print "Row " & i & " ----"
> Debug.Print rs.Fields("a")
> X = ""
> For j = 1 To Len(rs.Fields("a"))
> X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" &
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> If rs1.EOF Then ' .RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.MoveNext
> Loop
> End Sub
> If I use my original data, the above code will
> produce the same problem on the same row (row 2).
> Below is the output list:
> ====================
> Row 1 ----
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row 2 ----
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> ==not found==
> Row 3 ----
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row 4 ----
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row 5 ----
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row 6 ----
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row 7 ----
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row 8 ----
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row 9 ----
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> =======================
> The row 2 can be accessed by the query like this:
> select * from test2
> where a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> But, I will not use this way.
> Anita
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan,

You are absolutely right.

I think my problem is about query translation
on client side (VB). Perhaps, it is a bug
from Microsoft.

If the query is sent using direct method, like :
s = "select * from test2 where a = '" & _
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
then it is not guaranteed to work.

I made QA trial to prove that my problem was not
caused by SQL2000 :

CREATE TABLE test3
( a char(15)
primary KEY CLUSTERED,
b char(1) )

INSERT INTO test3
SELECT a, '2' as b FROM test2

--set b='1' on the second row
UPDATE test3 SET b = '1'
WHERE a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

DECLARE @.Value1 char(15)

SELECT @.value1 = a
FROM test3 where b = '1'

--successful second row access
SELECT * FROM test3 where a = @.Value1

Thanks for your reply

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL Server MVP Steve Kass pointed out to me that this behavior is described
in MSKB 164291
<http://support.microsoft.com/default.aspx?scid=kb;en-us;164291>.
Basically, SQL Server interprets a backslash followed a newline as a literal
continuation escape sequence so these characters are ignored in the literal
string. You can repro this in Query Analyzer with the following script:

SELECT 'Continu\
ed string'
GO

Although this behavior is also described in the Books Online in the Embedded
SQL for C section <esqlforc.chm::/ec_6_epr_02_101f.htm>, it is not mentioned
elsewhere as it probably should. A doc bug has been filed so the
documentation issue should be addressed in the future.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:403007ad$0$195$75868355@.news.frii.net...
> Dan,
> You are absolutely right.
> I think my problem is about query translation
> on client side (VB). Perhaps, it is a bug
> from Microsoft.
> If the query is sent using direct method, like :
> s = "select * from test2 where a = '" & _
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> then it is not guaranteed to work.
> I made QA trial to prove that my problem was not
> caused by SQL2000 :
> CREATE TABLE test3
> ( a char(15)
> primary KEY CLUSTERED,
> b char(1) )
> INSERT INTO test3
> SELECT a, '2' as b FROM test2
> --set b='1' on the second row
> UPDATE test3 SET b = '1'
> WHERE a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> DECLARE @.Value1 char(15)
> SELECT @.value1 = a
> FROM test3 where b = '1'
> --successful second row access
> SELECT * FROM test3 where a = @.Value1
> Thanks for your reply
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.

Non-deterministic Clustered Index for Indexed View

Hi all,
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
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|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:

> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:

> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:

> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> 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
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:

> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s

NONCLUSTERED?

CREATE NONCLUSTERED INDEX
Hi, What's diff between NONCLUSTERED and CLUSTERED? thanksClustered indexes physically order the data in the tables (think of a Yellow
pages, which is basically ordered by lastname, firstname)
Non-clustered is additional data with pointers to the actual data (think of
all your technical books, with those index pages at the back)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"js" <js@.someone@.hotmail.com> wrote in message
news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>|||See:
http://msdn.microsoft.com/library/d...asp?frame=true
And the subtopics...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl
..
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>

Non-clustered indexes on 1 separate fileGroup?

Hi Guys,

I am reviewing the storage structure of my growing db.

I am going to create multiple FileGroups and db files.
Most of the filegroups will be used to allow table partitioning and put the heavily used partitions on different storage than the read-only archives. TempDB will also have its own filegroup/storage.

For Indexes, I have a doubt. I am sure I can have all my non-clustered indexes on a separate fileGroup and storage but what about primary keys and/or clustered indexes?

Should I move them to the same filegroup than the non-clustered indexes or should I leave them with the data? What would be best?

I am running a 100GB reporting datamart on SQL2005 enterprise, There are almost no transactions, just a daily and weekly refresh. I will soon have more storage, SAN or NetApp, not sure yet.

Thanks,

Philippe

Hi

Moving your indexes to a seperate filegroup can improve the parallism of some querries that need to work with indexes and then do a bookmark lookup afterwards. If you have a VLDB like you said you should consider doing it, since the indexes cant be kept in memory all the time. So when you access the indexes it will not block the retrieval of your data from the other partition.

Non-Admin user can't create a linked report in My Reports...

A Non-Admin user can't create a linked report in his My Reports folder.
Reporting Services is set up with My Reports enabled with the default tasks
assigned to the My Reports role (this includes Create linked reports). This
same user can create sub-folders but not linked reports.
----
aspnet_wp!library!164!10/07/2004-17:42:44:: i INFO: Call to
CreateLinkedReport( '20041007 1742 SimpleReport', '/My
Reports/Submitted/Test', '/Standard Reports/Test/SimpleReport',
'Microsoft.ReportingServices.Library.Soap.Property[]' )
aspnet_wp!library!164!10/07/2004-17:42:44:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient for
performing this operation., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient for
performing this operation.Does the user have Read Properties permission to the report /Standard
Reports/Test/SimpleReport?
-Lukasz|||I discovered the problem... The Browser Role is not associated with the
"Create linked reports" task by default. Once I assigned the Create linked
reports task to the Browser Role the non-admin user could create linked
reports.
Thanks for the hint Lukasz.
Garry Lenz
"Garry Lenz" <glenz@.thoughtmill.com> wrote in message
news:%231hAadLrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> A Non-Admin user can't create a linked report in his My Reports folder.
> Reporting Services is set up with My Reports enabled with the default
tasks
> assigned to the My Reports role (this includes Create linked reports).
This
> same user can create sub-folders but not linked reports.
> ----
> aspnet_wp!library!164!10/07/2004-17:42:44:: i INFO: Call to
> CreateLinkedReport( '20041007 1742 SimpleReport', '/My
> Reports/Submitted/Test', '/Standard Reports/Test/SimpleReport',
> 'Microsoft.ReportingServices.Library.Soap.Property[]' )
> aspnet_wp!library!164!10/07/2004-17:42:44:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The
> permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient
for
> performing this operation., ;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The
> permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient
for
> performing this operation.
>

Non standard port - non default instance

Hi all,
I'm trying to create a subscription for a merge replication on a sql
server mobile 2005 based application.
The publisher is on a intranet, listening on port 9091 and with a named
instance.
The merge agent is in a DMZ with port 9091 open for inbound/outbound TCP
traffic to the intranet.
Problem:
when I try to synchronize I receive the "SQL Server does not exists" error.
My subscription has the following setup:
Publisher = "ServerName\InstanceName";
PublisherNetwork = NetworkType.TcpIpSockets;
PublisherAddress = "10.xxx.xxx.xxx,9091";
PublisherDatabase = "DatabaseName";
PublisherSecurityMode = SecurityType.DBAuthentication;
PublisherLogin = "name";
PublisherPassword = "pwd";
name/pwd user has correct priviledges non publisher database.
I can connect via SQL Client to the database from the machine in DMZ.
In my (many) tries, I also created an alias on the DMZ client and used
that for the Publisher property at the subscriber.
Nothing worked.
I'm finishing options...
Cl@. ha scritto:

> Problem:
> when I try to synchronize I receive the "SQL Server does not exists" error.
I solved opening 1434 udp port.
Question is: why sql client can connect without 1434 port open and the
merge agent can't?

Non Clustered Index

Hi,
Is it advisable to create a Non Clustered Index in "ALLow NULL" column?
Thanks,
Rahul JhaDepends on the requirement.

To turn it around - should a nullable column prohibit an index? No :)|||for that matter it's also possible to create a clustered index on a nullable column.|||but problems will arise if you make your index unique on a nullable column.|||but problems will arise if you make your index unique on a nullable column.

Not really, you will only be allowed on null "value"

which makes no sense

http://weblogs.sqlteam.com/brettk/archive/2005/04/20/4592.aspx

Non Clustered Effect

Hi ,
When I finish to create one non clustering index on of of my table , I
found out that the size of the hard disk increase amazingly.
I decide to drop the this index but the hard disk size did not back to
original size before that. What should I do next ? Please help
Travis Tan
Travis wrote:
> Hi ,
> When I finish to create one non clustering index on of of my table
> , I found out that the size of the hard disk increase amazingly.
> I decide to drop the this index but the hard disk size did not back
> to original size before that. What should I do next ? Please help
It's possible to configure automatic grow for database files and in this
way, when a db need space for an object (table or index) it take from os. If
you delete an object the space previously allocated won't be shrunk (unless
you set auto_shrink option but it's better to avoid this).
You may use DBCC SHRINKDATABASE or, better, DBCC SHRINKFILE to reduce the
space used by a database. See BOL for more details and for sintax of DBCC
commands.
Bye
PS: "clustering" in the name of this newsgroup means "Clustering technology"
and not as clustered (or not clustered) index...
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com

NOCACHE equivalent ?

Oracle query :
create table test(sno int, sno1 int) NOCACHE
Is there any equivalent in SQL Server for the above (specifically for the NOCAHE syntax)
Please advice,
Thanks,
SamNOCACHE is the default in SQLServer.

More interesting would be the CACHE statement in a create.
In SQLServer it is not possible directly afaik (correct me if I am wrong)
You can however use DBCC PINTABLE(database_id, table_id)
Look in the documentation for the use and working.|||Hai..

If u really want a table to be placed in cache then u can make the table as pintable so that in remains in the cache itslef. Surely this will increase the performance of that particular table provided the table is small in size. If the size of the table is more, then it will require so much memory, more over it will adversely affect the performace of other tables.

So analyse ur requirement and if u really want to place the table in cache then u can use the syntax as specfied by Woosterom

with regards
Sudar

No way out Please help second post

I am trying to write a query which when run can tell what courses a
department require and what courses were taken by the employee
/*
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
select * from #temp
select * from #temp1
*/
select coalesce (people_id,null),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
right join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
--group by people_id,dept_taken,coursecode,course_t
aken,deptid
union
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
left outer join #temp1 t1
on t1.deptid=t.dept_taken
and t1.coursecode=t.course_taken
) apple
This is the result set I am getting
How I can show peole id 3224in the first lin so it can show me which
courses was
required and were not taken or courses were taken which were not required.
peopleid Course Dept Required Taken
NULL FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
I like to see the outpurt come as this
peopleid Course Dept Required Taken
3224 FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
Thanks
TanweeHi
Thanks for posting DDL. But actaully your tables don't have primary key and
store redundant data, you are really need to normalize your database
I'm not certain that understand your requirements. It seems you don't need
to use UNION instead FULL join is giving you the data.
select isnull((select max(people_id) from #temp where
deptid=4),people_id),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
FULL join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
) AS APPLE
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> I am trying to write a query which when run can tell what courses a
> department require and what courses were taken by the employee
>
> /*
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp
> values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
> insert into #temp
values(3223,'VB','11/20/ 1996','Doney,Vicke',6738,'3','7')darkred">
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
>
> select * from #temp
> select * from #temp1
> */
> select coalesce (people_id,null),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> right join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> --group by people_id,dept_taken,coursecode,course_t
aken,deptid
> union
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> left outer join #temp1 t1
> on t1.deptid=t.dept_taken
> and t1.coursecode=t.course_taken
> ) apple
>
> This is the result set I am getting
> How I can show peole id 3224in the first lin so it can show me which
> courses was
> required and were not taken or courses were taken which were not required.
>
> peopleid Course Dept Required Taken
> NULL FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> I like to see the outpurt come as this
> peopleid Course Dept Required Taken
> 3224 FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> Thanks
> Tanwee
>|||This work great as long as another employee don't have the same department.
By adding this statement it will through the previous query
insert into #temp
values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
"Uri Dimant" wrote:

> Hi
> Thanks for posting DDL. But actaully your tables don't have primary key an
d
> store redundant data, you are really need to normalize your database
> I'm not certain that understand your requirements. It seems you don't need
> to use UNION instead FULL join is giving you the data.
>
> select isnull((select max(people_id) from #temp where
> deptid=4),people_id),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> FULL join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> ) AS APPLE
>
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
> Yo',6790,'4','8')
>
>|||On Mon, 21 Mar 2005 16:31:08 -0800, Tanweer wrote:

>This work great as long as another employee don't have the same department.
>By adding this statement it will through the previous query
> insert into #temp
>values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
Hi Tanweer,
Upon inspection of your data, I've come to the conclusion that your
problems are caused by lack of normalization. Unless I misunderstand
your data, you should split the #temp table in two parts:
Part 1: people_id, fullname, dept_taken (peoople_id is PK)
Part 2: people_id, course_taken, date_taken (people_id is FK to part 1;
people_id + course_taken is PK)
I didn't want to change your data, so I renamed the temp tables to
permanent tables and created views to mimic how it would look with
normalized data:
create table temp1(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into temp1
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into temp1 values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into temp1 values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
insert into temp1 values(3224,'VB','11/20/1999','Ricky
Yo',6790,'4','8')
-- Extra insert statement - uncomment for seecond test case
-- insert into temp1
--values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
create table temp2(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into temp2 values(3,'SQL DBA','7')
insert into temp2 values(3,'FoxPro','7')
insert into temp2 values(4,'FoxPro','8')
go
-- Create two views as placeholders for normalized tables
create view people as
select distinct people_id, full_name, assign_id, dept_taken
from temp1
go
create view coursetaken as
select people_id, course_taken, date_taken
from temp1
go
-- Show normalized data
select * from people
select * from coursetaken
go
-- And here's the query
select coalesce(p.people_id, ct.people_id) as people_id,
coalesce(c.coursecode, ct.course_taken) as coursecode,
coalesce(p.dept_taken, (select dept_taken from people where
people_id = coalesce(p.people_id, ct.people_id))) as deptid,
case when c.deptid is null then 0 else 1 end as required,
case when ct.people_id is null then 0 else 1 end as taken
from people as p
inner join temp2 as c
on c.deptid = p.dept_taken
full join coursetaken as ct
on ct.people_id = p.people_id
and ct.course_taken = c.coursecode
go
drop view people
drop view coursetaken
drop table temp1
drop table temp2
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)