Configure HTTPS Access to SQL Server 2005 Analysis Services

Hi All,

There's material for configure HTTP to access SSAS 2005. http://www.microsoft.com/technet/prodtechnol/sql/2005/httpssas.mspx
It's applied and success with basic authentication.
but i have no idea about using HTTPS to access it. can't find related doc in microsoft website too.
could you kindly share your experience or related material?

Thanks!
Best regards,
Tommy
can anyone help?

Configuration of SQL Server 2000 Instance

OK.

What I ask about is

how to configure a SQL Server 2000 Instance that allow different copies of my Application to access it

Simply (" my Application with users on LAN and ONLY ONE SQL Server 2000 is installed on a machine that connected with this LAN ")

How I can connect to this SQL Server AND What is needed about any specific configuration options

and If So , How I can deploy these configurations when distributing my Application

- I mean SQL Server configuration-

Thanks more for help

please any answer

on the server check "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe" to make sure network protocols are enabled.

on the workstations check: C:\WINDOWS\system32\cliconfg.exe

Configuration for SQL 2000

Hi,

What configuration steps should I do to enable my .NET web application to access my SQL 2000 database? I have SQL Server 2000 SP3, SQL 2005 Express, SQL 2005 and VS 2005 installed on the same machine. Is it anything to do with the NT Authority\Network Service or ASPNET account that I need to allow access to or what? Kinda lost here... been banging my server for the past week.

Cheers!

There are several options. Do you want to run the SQL Server in Mixed Mode ? DO you want to use impersonation ? DO you want to impersoante the user at the SQL Server ?

-Jens Suessmeyer.

|||

That is also my next question. What I would like is for annonymous access to view the data taken from the database. Only authorized personnel can log in through Forms Authentication to Insert, Update or Delete the database.

I believe this is a simple implementation but I can't seem to get the configuration right. The database works if I put it as a local database but that defeats the purpose of the implementation.

I got this error which I believe is very common

What can I set in IIS 6.0, Compiter Management and SQL 2000?:

Server Error in '/' Application.

---

Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/> </system.web> </configuration>

|||

Anyway after some tweaking here and there, I managed to get it to work properly. Added NT Authority\Local Service account to SQL 2000 User Login, which will categorize it as the public grp. After that checking the tables this group can operate Select instruction on.

This is probably not the conventional way, so I would like to know what the generic workable implementation should be (otherwise called the industrial standard).

Cheers!

Configuing SQLServer Express for internet access

Could someone point me to an article that shows how to configure SQLServer express so I can connect to it over the lan and Internet?

Thanks

These articles should help guide you.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Configuration - Guideline for Connectivity Question Posting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Conect via IP not server name - why?

Hi
My colleague has set up MSDE on his machine. I am only able to connect to his machine via IP and not servername
Why is this? I want to use ACCESS as a fornt end and can't useing IP address
Jerry

If you are on the same network just use the register server option in Enterprise manager and it becomes local to you. Hope this helps.

conditions in where clause

Hi I am upsizing the access database to SQL 2005.
I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue.

In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work.

in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Any one know how i can best replicate this behavior?

Dagz

In place of IIF, use CASE. Using your example:

Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Code Snippet

WHERE CASE Table1.Column1
WHEN 0 THEN Table2.Column1
ELSE Table3.Column1
END

|||Here you go

Code Snippet

Case WHEN table1.Column1 = 0 THEN Table2.Column1
else Table3.Column1
END|||

when i try this i get the error message, where is of none boolean type?

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional query criteria in Access 2003 with SQL Server BE

Hello:
I just upsized an Access 2003 application to a SQL Server 2000 back end.
There is a query that worked fine before the upsize, but does not work now.
The purpose of the query is to give me a list of employees that are
scheduled to work today. The employee master row has 7 checkboxes for the
days of the week.
Here is the SQL for the query. Can someone tell me how to make this work
with the SQL Server BE?
SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
FROM EmployeeQry
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
(((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
(((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
(((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
(((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
(((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
Thanks
Steve
John:
Thanks for the reply. Sorry ... the query returns no rows, when in fact
there are many for each day of the week. I tried your suggestions ...
comments under each suggestion.
Steve
"John Spencer" wrote:

> I would try:
> Adding to the IIF statement.
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True,False)))
>
I tried this with no difference

> Or even simpler, drop the IIF statement
> EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')
>
This returned all rows of the table.

> IF that still fails then try reversing the logic of the test. Just in
> case SQL server is storing the data as 1 (True) and 0 (False).
> EmployeeQry.Tuesday =IIf(Format(Date(),"dddd")<>'Tuesday',False, True)
>
Didn't work either.

> By the way "..., but does not work now." is not very descriptive of the
> problem. Does that mean, you get the wrong results, no results, an
> error, different results than expected at times, a syntax error or ...?
> Please try to be a bit more specific in describing the problem.
> '================================================= ===
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '================================================= ===
>
> Steve Happ wrote:
>
|||What type is EmployeeQry.Monday on SQL Server?
It could be as John alluded to that
for SQL Server
True = 1
False = 0
for Access
True = -1
False = 0
I might misunderstand, but you try "1"
instead of "True"
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
"Steve Happ" wrote:
> I just upsized an Access 2003 application to a SQL Server 2000 back end.
> There is a query that worked fine before the upsize, but does not work
> now.
> The purpose of the query is to give me a list of employees that are
> scheduled to work today. The employee master row has 7 checkboxes for the
> days of the week.
> Here is the SQL for the query. Can someone tell me how to make this work
> with the SQL Server BE?
> SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
> EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
> EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
> EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
> FROM EmployeeQry
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
> (((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
> (((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
> (((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
> (((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
> (((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
>
> Thanks
> Steve
|||or
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,Null))) OR
the point being that if Monday = "True"
on SQL, then its value is 1
which will never be equal to Access "True"
which is equal to -1
"Gary Walter" wrote:
> What type is EmployeeQry.Monday on SQL Server?
> It could be as John alluded to that
> for SQL Server
> True = 1
> False = 0
> for Access
> True = -1
> False = 0
> I might misunderstand, but you try "1"
> instead of "True"
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
>
> "Steve Happ" wrote:
>

Conditional Formatting?

Is there the equivalent of MS Access 'conditional formatting' for fields in report services, if so how do I access it?

Thanks

You will need to use expressions - check the following links:

* http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

* http://defdeveloper.blogspot.com/2005/10/conditional-row-formatting-in.html

* http://msdn2.microsoft.com/en-us/library/ms159238.aspx

-- Robert

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password
,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional Expression - i.e., IIF in Access

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);

But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END|||Originally posted by Brett Kaiser
SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END

Well, when I do that, I get
"The Query Designer does not support the CASE SQL construct."

Can you even use CASE in a view?|||Also, I need to assign an alias to that column.|||What are you using?

Aren't you using query analyzer?

If you're using Access you may need to make it a PASS THRU query

SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol|||I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help|||What do you mean in VIEW?

Are you doing this in Enterprise Manager?

I would recommend against that.|||Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql ;)|||What s/he said...

Use QA though for SQL Server development...

You'll have a lot less headaches...

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Concurrent access problem...

Hi All
I am developing portfolio management application. In this application i
am fetching data from database and populate to dataset and that dataset
is getting binded to the datagrid... it is multiuse application and one
portfolio is getting accessed by more than one user. somehow i want to
make sure that one transaction can be updated by only one person. no
more than one person should be allowed to update one transaction... can
anyone suggest me good approach to solve this problem
thanks
Deep OceanOne simple approach would be to have a timestamp or datetime column in the
table .
Between the Retrieval and Save of the data - check for the datetime column
to have same value, which mean no other user has updated the record. If the
column values are not same between retrieval and save then return an error
message to the user that the record has already been updated by some other
user and user need to refresh the screen to do a frech update.
"Deep Silent Ocean" wrote:

> Hi All
>
> I am developing portfolio management application. In this application i
> am fetching data from database and populate to dataset and that dataset
> is getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no
> more than one person should be allowed to update one transaction... can
> anyone suggest me good approach to solve this problem
> thanks
> Deep Ocean
>|||Is this a web application using a disconnected ADO.NET dataset or a client
server type application using a connected ADO recordset?
For ADO recordsets bound to a grid (ex: Visual Basic or MS Office), it is a
matter of specifying the LockType property of the recordset to optimistic or
pessimistic.
http://msdn.microsoft.com/library/d.../>
ocktype.asp
http://msdn.microsoft.com/library/d...ocktypeenum.asp
http://msdn.microsoft.com/library/d...
ursortypex.asp
In web application, data changes are made to an ADO.NET database indirectly
via a disconnected dataset, so the issues are different. Here is an
excellent article on handling concurrency issues in a web application.
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
Pessimistic locking can sometimes inadvertently prevent users from
performing otherwise harmless operations, so I favor the method of using a
timestamp column and then giving the user the flexibility of choosing
whether or not to save their changes over another edit. However, this should
be rarely needed. It's also more of a business process issue than a
technical issue. Generally speaking, an account, invoice, portfolio, etc.
should be assigned to a specific account manager. It would seem confusing to
have multiple people working the same customer, but perhaps you mean
something different by "portfolio".
"Deep Silent Ocean" <ocean.indian@.gmail.com> wrote in message
news:O2tvKf9vFHA.908@.tk2msftngp13.phx.gbl...
> Hi All
>
> I am developing portfolio management application. In this application i am
> fetching data from database and populate to dataset and that dataset is
> getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no more
> than one person should be allowed to update one transaction... can anyone
> suggest me good approach to solve this problem
> thanks
> Deep Ocean

concurrent access during snapshot generation

We are using SQL 2K with sp4 and a push transaction subscription.
In the Publication properties, under the snapshot tab, there is a check box
for 'Concurrent access during snapshot gneration', the default is not
checked.
1. I am thinking about checking the box because the benefit is great, but
what is the consequence? I am curious of why it isn't default to 'checked' box
2. If leaving it unchecked, say I have 100 tables in the snapshot, the
system only locks a table one at a time during the process, they shouldn't
lock all the tables at once, am I right?
wingman
For the first part, the methodology is different. Transactions made during
the snapshot time are sent to the distribution database and will be
synchronised after the snapshot. This is possible in transactional because
it is transaction-based, but not in merge. However, in some cases it might
be desirable to quiesce the articles while they are being snapshotted, so as
to limit the transactions getting queued up. I'm not too sure if your
assumption is correct about the second part and will check it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the explanation. Just so I understand it correctly because I
don't quite know the word 'quiesce' means.
If the box is unchecked, the snaplock process will stop any transactions
being sent to the distribution database until the snapshot is done.
If the box is checked, any transactions will be sent to distribution
database, which in turns sends them to the subscriber database while snapshot
is going on.
Your answer prompts me a new question or a clarification of what I thought I
understand. When that check box indicates "Do Not lock tables during
snapshot generation.....'. What does it mean? Does it lock the tables in
publisher database or in subscriber database?
"Paul Ibison" wrote:

> For the first part, the methodology is different. Transactions made during
> the snapshot time are sent to the distribution database and will be
> synchronised after the snapshot. This is possible in transactional because
> it is transaction-based, but not in merge. However, in some cases it might
> be desirable to quiesce the articles while they are being snapshotted, so as
> to limit the transactions getting queued up. I'm not too sure if your
> assumption is correct about the second part and will check it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Wingman,
it's referring to a table-lock on the publisher. The tables usually don't
exist on the subscriber, or if they do, the default is to drop them anyway.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Sorry I am still not quite clear. Please pattern me and clarify the following
So if I unchecked the box(tables will be locked), when the snapshot is
happening, which of the following scenario is happening:
1. the snapshot process will stop any transactions being sent to the
distribution database until the snapshot is done. This means any changes to
any tables are sitting in the queue of the distribution database. During the
snapshot process, the tables involved are locked.
2. the snapshot process will only stop transactions related to the tables
involved in the snapshot and these transactions will be held in the queue of
the distribution database. This means changes are still allowed to be made
to other tables except the tables involved in the snapshot. During the
snapshot process, the tables involved are locked.
If the box is checked, are the above situations reversed? For example,
there will be no queueing in the distribution database and the tables
involved in the snapshot are not locked.
Again, thank for your time and patience very much.
"Paul Ibison" wrote:

> Wingman,
> it's referring to a table-lock on the publisher. The tables usually don't
> exist on the subscriber, or if they do, the default is to drop them anyway.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||With the box unchecked, it is the tables on the Publisher that are locked.
Other processes trying to update these tables will be blocked until the lock
is removed. This should be verifiable by testing and simultaneously
profiling or using the current activity window.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

Concurrent Access

Hi,
I have been asked to give a realistic number of concurrent access to do the
stress test to the sql server for our application. I used sp_who2, and
there are about 187 rows in the result pane. However, most of them has"Sleep"
status. 187 concurrent access is a lot. Am I using the right method? Have you
done stress test on your sql server? What is the number of concurrent access
that you put on the server during the test?
Thanks in advance!
K
We have a couple hundred workstations and just over 1000 database
connections.
Keith
"K" <K@.discussions.microsoft.com> wrote in message
news:562243D0-BF68-45BB-A7FA-F4760A79DEB7@.microsoft.com...
> Hi,
> I have been asked to give a realistic number of concurrent access to do
> the
> stress test to the sql server for our application. I used sp_who2, and
> there are about 187 rows in the result pane. However, most of them
> has"Sleep"
> status. 187 concurrent access is a lot. Am I using the right method? Have
> you
> done stress test on your sql server? What is the number of concurrent
> access
> that you put on the server during the test?
> Thanks in advance!
> K
>