Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help - using sp_executesql with dynamic query...dynamic parameters?

[posted and mailed, please reply in news]
Lisa Forde (1forde@.caribsurf.com) writes:
> Hi. I'm attempting to write a Stored Procedure that creates an SELECT
> statement. Belwo is an example of what I am tryign except that my actually
> query have many more parameters most of which can be eliminated.
>...
> EXEC sp_executesql @.sQuery,@.sParams, <WHAT DO I PUT HERE'>
> Can it even work?
You put the parameters. I have an example of a dynamic search routine
at http://www.sommarskog.se/dyn-search.html#sp_executesql which hopefully
can be of help for you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspPerfect!
Thanks.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F7A82091A39Yazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Lisa Forde (1forde@.caribsurf.com) writes:
actually
> You put the parameters. I have an example of a dynamic search routine
> at http://www.sommarskog.se/dyn-search.html#sp_executesql which hopefully
> can be of help for you.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

Help - Update Field in SQL Server

I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work

Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)

This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")

Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"

Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"

With this new code the password is not being changed. However, I am not receiving any errors.

BDyou can check your db if the pwd is being changed, to see if the sp is working properly.

(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.

or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.

apparently, this one requires an xtra trip to the db.

HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)
'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)
' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()
return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close

and have your SP defined like this :

CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...

HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.

It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValue
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.text
Dim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text

|||That worked! Thanks for your help!

Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.

Code

Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)

Result.Direction = ParameterDirection.ReturnValue

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

if Result.Value>0 then MSG.text= "Your password has been changed"

myCommand.Connection.Close()

End Code

In my html I have the following:

<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?

Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:

if Result.Value>0 then MSG.text= "Your password has been changed"

should be this:

if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"

Terri|||That's perfect. Thanks again for everybody's help.

Brian

Help - I need a book on SQL

Im trying to write a complicated SQL in analyser using several tables and su
b
selects to do some inserts.
I have to do this very often and I only have a limited knowledge of SQL.
Can you recommend a book I can go out and buy that will teach me how to
write involved SQLs for a beginner please.
(The name and author would be great).
Thanks, and frustrated!If there is one book that I would get this would be it
The Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
and BOL of course
http://sqlservercode.blogspot.com/|||Very good book for writing queries, specifically (as opposed to db design):
"SQL Queries For Mere Mortals" by Hernandez and Viescas
http://www.amazon.com/gp/product/02...glance&n=283155
NOTE: Celko wrote the Forward to this book - AND the book refers to columns
as fields!!! LOL!!!
"JD" <JD@.discussions.microsoft.com> wrote in message
news:ACA8DD1C-E62E-4AA3-910F-82EEEA6CC2F7@.microsoft.com...
> Im trying to write a complicated SQL in analyser using several tables and
> sub
> selects to do some inserts.
> I have to do this very often and I only have a limited knowledge of SQL.
> Can you recommend a book I can go out and buy that will teach me how to
> write involved SQLs for a beginner please.
> (The name and author would be great).
> Thanks, and frustrated!
>

Help

I am a student,come from china.now i will write a article about sql server and oricle 9i,i want know how to write ,i want to find the key word about it.but i do not know finding which words .i hope somebody help me .thank u.A little google'll do ya...

http://www.databasejournal.com/features/mssql/article.php/2170201

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})
) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime
})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentnumber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}')
And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})
) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}
')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') A
nd
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentnumber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}'
) And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime
})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') A
nd
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCod
e}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}')
And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) An
d
...for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written

Help

Hi
I am using Visual studio 2005 to write a web service. I am not able to
connect to database. I get the usual Named pipes provider error:could
not open a connection to the server. So I read up some material and
found out that I have to start the sqlbrowser service(using surface
area configuration manager) and create exceptions for the firewall for
the sqlexpress and sqlbrowser. I did all the things, still I get the
same error. However , when I add the datasource from the datasource
configuration wizard and test the connection, the test was succesfull.
Thanks in advance for the helpIt's best not to hijack a thread with a new topic -there is no certainty
anyone will see your post since they may think that the thread is complete.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"sunil" <sairaj.sunil@.gmail.com> wrote in message
news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
> Hi
> I am using Visual studio 2005 to write a web service. I am not able to
> connect to database. I get the usual Named pipes provider error:could
> not open a connection to the server. So I read up some material and
> found out that I have to start the sqlbrowser service(using surface
> area configuration manager) and create exceptions for the firewall for
> the sqlexpress and sqlbrowser. I did all the things, still I get the
> same error. However , when I add the datasource from the datasource
> configuration wizard and test the connection, the test was succesfull.
> Thanks in advance for the help
>|||This may not actually be a case of hijacking. It may just be a case of using
a totally meaningless subject that the newsreader assumes is part of the
same thread with the same less-than-useful subject.
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uKT6kAmuGHA.4748@.TK2MSFTNGP03.phx.gbl...
> It's best not to hijack a thread with a new topic -there is no certainty
> anyone will see your post since they may think that the thread is
> complete.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "sunil" <sairaj.sunil@.gmail.com> wrote in message
> news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
>

Help

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this queryYou can get the total number of people who logged in by doing something like:
SELECT COUNT(*) FROM LoginTable WHERE LoginDate = '2003/09/15'

I'm not sure what you mean by (2), (3), and (4). Do you mean the mininum number of people who were logged in simultaneously at a given moment in time during the day in question? (etc)

Cheers
Ken|||Thanks mate .
I will try to explain

DataBase fields are

id
date

So when a user is logged in I add a line to this table (id is identity field).

I am creating a report to show how many users logged on
between two dates (start date and end date (between date) ).

I will provide two Dates text boxes for the report i.e
Start Date and End Date and then search button
so for example if

say on these dates the logged users are as
On Date 01/08/2003 Logged users are 10
On Date 02/08/2003 Logged users are 8
On Date 03/08/2003 Logged users are 3
On Date 04/08/2003 Logged users are 5

So query should return
Count of user-->26 on these dates
Minimum users-->3 on these dates
Maximum users-->10
Average users-->sum of users/Count of users

I hope this makes some sense.

help

hi

i have table with folowing columns (subid,itemid)

i want to write select stmt to get subid where itemid =all group of value('1','2')

like this

Select subid from subscriptionItem where itemId in all('1','2')

but this stmt not work

help me

Somthing like this ?

Select SubID From SubscriptionItem Where ItemID in ('1','2')|||

Hi,

If you mean you want those SubID that have both ItemID 1 and 2, then the above will not work.

what you should is..

Select SubID From subscriptionItem Where ItemID in (1,2) group by SubID Having count(0) =2

Assuming that the combination SubID and ItemID is primary (unique).

If they are not unique, which I doubt, then try:

Select SubID FROM (Select distinct SubID, ItemID from SubscriptionItem where ItemID in (1,2)) si Group by SubID having count(0) = 2

Hope this helps

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!
I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>
|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.
|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>
|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentn umber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName
|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time
|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentn umber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>
|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>
|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
....for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentnumber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentnumber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
...for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written|||Have you tried running the SQL outside of Crystal just to look and verify
that they look like you want?
I'm not sure if this is SQL passed to the db or in a stored procedure. But,
either way, one way to do this would be to use sql-profiler to profile either
the sp execution or TSQL execution. You can then use the TextData to see the
exact call being made on the SQL server.
You can then run this in query analyzer to view the result set that is being
returned.
From what I know, you can not regroup you data to redisplay on separate
pages of the reports. Since, Crystal would have already walked through your
data to bind to the report using the first grouping.
I definitely am not a Crystal expert. But, I would recommend running the
SQL outside of Crystal so you have a good idea of the Record Set that you are
ultimately binding to your report and to verify that your joins and WHERE
clause is working as you expect.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> 1)DateTime
> 2)there are only 2 now...BeginDateTime and EndDateTime
> 3)it will be a date/time like 1/4/06 14:30
>
> Thank you for all of you help.....I am trying this right now...
> Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
> ....for the line of code that is erroring....I am not getting an
> error...but page 2 of my report where the data is grouped by
> departments is all blank...I don't know if this is supposed to
> happen...This is the first real Crystal Report I have ever written
>|||Thank you so much for your help. You have been very helpful. I
finally got it all to work out with that last line of code...I just
had to check the dates on the db...that is why i was getting a blank
report. Right now I am just writting the formula fields and I will be
done with this report. Couldn't have done it without your help!!!!
Thanks again!!!!|||Cool. Glad I could help.
Best of luck.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thank you so much for your help. You have been very helpful. I
> finally got it all to work out with that last line of code...I just
> had to check the dates on the db...that is why i was getting a blank
> report. Right now I am just writting the formula fields and I will be
> done with this report. Couldn't have done it without your help!!!!
> Thanks again!!!!
>

Help

Hi
I am using Visual studio 2005 to write a web service. I am not able to
connect to database. I get the usual Named pipes provider error:could
not open a connection to the server. So I read up some material and
found out that I have to start the sqlbrowser service(using surface
area configuration manager) and create exceptions for the firewall for
the sqlexpress and sqlbrowser. I did all the things, still I get the
same error. However , when I add the datasource from the datasource
configuration wizard and test the connection, the test was succesfull.
Thanks in advance for the helpIt's best not to hijack a thread with a new topic -there is no certainty
anyone will see your post since they may think that the thread is complete.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"sunil" <sairaj.sunil@.gmail.com> wrote in message
news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
> Hi
> I am using Visual studio 2005 to write a web service. I am not able to
> connect to database. I get the usual Named pipes provider error:could
> not open a connection to the server. So I read up some material and
> found out that I have to start the sqlbrowser service(using surface
> area configuration manager) and create exceptions for the firewall for
> the sqlexpress and sqlbrowser. I did all the things, still I get the
> same error. However , when I add the datasource from the datasource
> configuration wizard and test the connection, the test was succesfull.
> Thanks in advance for the help
>|||This may not actually be a case of hijacking. It may just be a case of using
a totally meaningless subject that the newsreader assumes is part of the
same thread with the same less-than-useful subject.
--
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uKT6kAmuGHA.4748@.TK2MSFTNGP03.phx.gbl...
> It's best not to hijack a thread with a new topic -there is no certainty
> anyone will see your post since they may think that the thread is
> complete.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "sunil" <sairaj.sunil@.gmail.com> wrote in message
> news:1154953948.775714.18740@.p79g2000cwp.googlegroups.com...
>> Hi
>> I am using Visual studio 2005 to write a web service. I am not able to
>> connect to database. I get the usual Named pipes provider error:could
>> not open a connection to the server. So I read up some material and
>> found out that I have to start the sqlbrowser service(using surface
>> area configuration manager) and create exceptions for the firewall for
>> the sqlexpress and sqlbrowser. I did all the things, still I get the
>> same error. However , when I add the datasource from the datasource
>> configuration wizard and test the connection, the test was succesfull.
>> Thanks in advance for the help
>