Difference between Access and SQL Server

I have the following query in Access:

SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
interest_category_id, a.allergy_parent_id, a.allergy_element_id,
c.alr_category_id AS allergy_category_id
FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b ON
a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c ON
a.allergy_element_id = c.drug_id

and all is well!

When I create the same query in SQL sever (using the desinger), I get:

SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
interest_category_id, a.allergy_parent_id, a.allergy_element_id,
c.alr_category_id AS allergy_category_id
FROM dbo.AllergyDrugPermutation a INNER JOIN
dbo.alr_category_drug_map b ON a.interest_element_id =
b.drug_id INNER JOIN
dbo.alr_category_drug_map c ON a.allergy_element_id =
c.drug_id

The only structural difference I see here is that SQL removed the ( and )
from around the first join and created the double join.
When I run this query I get stuck in a loop and eventually the sever will
time out. If I only have the first join there is no time out issues but of
course the result is not what I desire.

Can someone help me out here and tell me what is wrong with the statement
used in SQL?

Thank you in advance,
EricI think I found the problem. AllergyDrugPermutation is a "view." If I
create a table the has the same data as that in the view I don't get the
time out error.
So the question becomes, why does this fail with view?
Thanks
Eric

"Beringer" <borden_eric@.invalid.com> wrote in message
news:mbSdd.56308$kz3.48760@.fed1read02...
>I have the following query in Access:
> SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
> interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> c.alr_category_id AS allergy_category_id
> FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b ON
> a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c
> ON a.allergy_element_id = c.drug_id
> and all is well!
> When I create the same query in SQL sever (using the desinger), I get:
> SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id
> AS interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> c.alr_category_id AS allergy_category_id
> FROM dbo.AllergyDrugPermutation a INNER JOIN
> dbo.alr_category_drug_map b ON a.interest_element_id
> = b.drug_id INNER JOIN
> dbo.alr_category_drug_map c ON a.allergy_element_id =
> c.drug_id
> The only structural difference I see here is that SQL removed the ( and )
> from around the first join and created the double join.
> When I run this query I get stuck in a loop and eventually the sever will
> time out. If I only have the first join there is no time out issues but
> of course the result is not what I desire.
> Can someone help me out here and tell me what is wrong with the statement
> used in SQL?
> Thank you in advance,
> Eric|||Did you get any error message when the query timeout occured?|||Hi

You would need to post DDL and example data (as insert statements) that
recreates the problem
http://www.aspfaq.com/etiquette.asp?id=5006

If you posted the access query (from the access designer) into query
analyser do you still have a problem?

John

"Beringer" <borden_eric@.invalid.com> wrote in message
news:1mSdd.56309$kz3.53477@.fed1read02...
> I think I found the problem. AllergyDrugPermutation is a "view." If I
> create a table the has the same data as that in the view I don't get the
> time out error.
> So the question becomes, why does this fail with view?
> Thanks
> Eric
> "Beringer" <borden_eric@.invalid.com> wrote in message
> news:mbSdd.56308$kz3.48760@.fed1read02...
> >I have the following query in Access:
> > SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS
> > interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> > c.alr_category_id AS allergy_category_id
> > FROM (AllergyDrugPermutation AS a INNER JOIN alr_category_drug_map AS b
ON
> > a.interest_element_id = b.drug_id) INNER JOIN alr_category_drug_map AS c
> > ON a.allergy_element_id = c.drug_id
> > and all is well!
> > When I create the same query in SQL sever (using the desinger), I get:
> > SELECT a.interest_parent_id, a.interest_element_id,
b.alr_category_id
> > AS interest_category_id, a.allergy_parent_id, a.allergy_element_id,
> > c.alr_category_id AS allergy_category_id
> > FROM dbo.AllergyDrugPermutation a INNER JOIN
> > dbo.alr_category_drug_map b ON
a.interest_element_id
> > = b.drug_id INNER JOIN
> > dbo.alr_category_drug_map c ON a.allergy_element_id
=
> > c.drug_id
> > The only structural difference I see here is that SQL removed the (
and )
> > from around the first join and created the double join.
> > When I run this query I get stuck in a loop and eventually the sever
will
> > time out. If I only have the first join there is no time out issues but
> > of course the result is not what I desire.
> > Can someone help me out here and tell me what is wrong with the
statement
> > used in SQL?
> > Thank you in advance,
> > Eric

Diff Result Between SQL 7 & SQL 2000

Hi.

When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.

The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.

Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff

Error message:

Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ...

SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.

Best regardsYou sure you just didn't execute all of the code in the QA window, and you're executing something you don't see?|||And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?|||Originally posted by Brett Kaiser
And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?

Hi,

Thank you for your email reply.

Those in [ ] were actual field names in my database table. I used them for display purposes so instead of showing Sales_organation, I display Sales Organisation, Value Out By instead of val_diff and finally Qty Out By instead of qty_diff (look more meanful to the users).

You are right that I didn't show all the script in my previous message because they worked ok until this line. I am just being curious why the same script work PERFECT in SQL 7 but not in SQL 2000. I have been running this same set of script for more than a year and still running fine as at this morning. Reason why I am trying it on SQL 2000 because we will be moving to SQL 2000 totally by end this month.|||No problems here...your doing something else wrong...

USE Northwind
GO

CREATE TABLE SAPvsSQL (
[Sales Organisation] varchar(10)
, [Value Out By] varchar(10)
, [Qty Out By] varchar(10)
)
GO

DECLARE @.Sales_Org varchar(10), @.val_diff varchar(10), @.qty_diff varchar(10)

SELECT @.Sales_Org = 'A', @.val_Diff = 'B', @.qty_diff = 'C'

INSERT INTO SAPvsSQL ([Sales Organisation], [Value Out By], [Qty Out By])
SELECT 'X', 'Y', 'Z'

SELECT * FROM SAPvsSQL

UPDATE SAPvsSQL
SET [Sales Organisation] = @.sales_org
, [Value Out By] = @.val_diff
, [Qty Out By] = @.qty_diff

SELECT * FROM SAPvsSQL
GO

DROP TABLE SAPvsSQL
GO

DialogError message type

Hi There

In my testing i have seen the following i just would like confirmation that i am correct.

When something happens to cause a Dialog Error say for example message validation fails.

I have noticed that the actual DialogError message always goes to the initiator queue, is this correct ?

If so then logic to handle the ErrorDialog message type only has to be in the initiator activated SP that logic is not required in the target queue activated SP since the ErrorDialog message always goes to the initiator queue, is that correct ?

Thanx

Both sides need to be prepared to deal with the error message. The same message validation can fail for a message sent on the other direction, or the initiator code can issue an explicit END CONVERSATION ... WITH ERROR (or even an ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS), or the initiator can drop the contract used on the conversation, or the service. All these result in an error message being sent to the target, and the list is not complete.

HTH,
~ Remus

Diagram Pane Add/Remove Tables Problem

Hi,

I am doing the AdventureworksDW Analysis Services Tutorial.

I am a the following place.

Analysis Services Tutorials > SQL Server 2005 Analysis Services Tutorial > Lesson 5: Defining Relationships Between Dimensions and Measure Groups >

I am at the following spot.

Adding Tables and Defining the Sales Quotas Measure Group

To add tables and define the Sales Quotas measure group

Switch to Data Source View Designer for the Adventure Works DW data source view.

Right-click anywhere in the Diagram Organizer pane, click New Diagram, and then specify Sales Quotas as the name for the new diagram. For more information, see Working with Diagrams in a Data Source View (Analysis Services).

Drag the Employee, Sales Territory, and Time tables from the Tables pane to the Diagram pane.

Add the FactSalesQuota table to the Diagram pane by right-clicking anywhere in the Diagram pane and selecting Add/Remove Tables.

I can do 1, 2, and 3.

When I try to do four, I bring up the dialog. On the right side of the dialog I see INCLUDED OBJECTS.

I select (highlight/make blue) FactSalesQuota and press O.K.

Nothing happens. What else do I need to do?

The link just below shows a screenshot.

AddRemoveTableProblem

Thanks,

Andre Mikulec

AIM

AIMDBA

Andre_Mikulec@.Hotmail.com

FactSalesQuota is included in your diagram already, but it has been given the friendly name "SalesQuotas". In your picture you can see it in the background in the list of tables as the fourth table up from the bottom.|||

Thanks,

I have been working 18 hours per day on an alternate project for a month.

I am finally getting some time to get back to this tutorial.

Andre Mikulec

AIM

AIMDBA

Andre_Mikulec@.Hotmail.com

Diagram Pane Add/Remove Tables Problem

Hi,

I am doing the AdventureworksDW Analysis Services Tutorial.

I am a the following place.

Analysis Services Tutorials > SQL Server 2005 Analysis Services Tutorial > Lesson 5: Defining Relationships Between Dimensions and Measure Groups >

I am at the following spot.

Adding Tables and Defining the Sales Quotas Measure Group

To add tables and define the Sales Quotas measure group

Switch to Data Source View Designer for the Adventure Works DW data source view.

Right-click anywhere in the Diagram Organizer pane, click New Diagram, and then specify Sales Quotas as the name for the new diagram. For more information, see Working with Diagrams in a Data Source View (Analysis Services).

Drag the Employee, Sales Territory, and Time tables from the Tables pane to the Diagram pane.

Add the FactSalesQuota table to the Diagram pane by right-clicking anywhere in the Diagram pane and selecting Add/Remove Tables.

I can do 1, 2, and 3.

When I try to do four, I bring up the dialog. On the right side of the dialog I see INCLUDED OBJECTS.

I select (highlight/make blue) FactSalesQuota and press O.K.

Nothing happens. What else do I need to do?

The link just below shows a screenshot.

AddRemoveTableProblem

Thanks,

Andre Mikulec

AIM

AIMDBA

Andre_Mikulec@.Hotmail.com

FactSalesQuota is included in your diagram already, but it has been given the friendly name "SalesQuotas". In your picture you can see it in the background in the list of tables as the fourth table up from the bottom.|||

Thanks,

I have been working 18 hours per day on an alternate project for a month.

I am finally getting some time to get back to this tutorial.

Andre Mikulec

AIM

AIMDBA

Andre_Mikulec@.Hotmail.com

Device error or device off-line. ?

Hi to All

when i m trying to execute following code

backup database web
to disk = 'c:\inetpub\wwwroot\backup\mybakup.bak'
with format

I m Getting Error like :

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\inetpub\wwwroot\backup\mybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!

but not for any subfolders of wwwroot.

Can Any One Help Me ?

Please follow the post in the other group, you don′t have to mulitpost as we the groups are all monitored.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Device error or device off-line ?

Hi to All

when i m trying to execute following code

backup database web
to disk = 'c:\inetpub\wwwroot\backup\mybakup.bak'
with format

I m Getting Error like :

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\inetpub\wwwroot\backup\mybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!

but not for any subfolders of wwwroot.

Can Any One Help Me ?

What does show

xp_cmdshell "dir c:\inetpub\wwwroot\backup"

?

|||

DO the subfolders inherit the permission from the wwwroot folder ? Make sure that this isn′t a permission problem. What does (like the message says the error log tell you about the problem?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

i m using Sql Server 2000 (sp3);

command

xp_cmdshell "dir c:\inetpub\wwwroot\backup"

gives following error:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.

|||

All Permission have been set properly.

but error is still as it is.

|||

the xp_cmdshell is located in the master database, so you have to prefix the database name or need to have your curretn context in the master database.

master.xp_cmdshell "dir c:\inetpub\wwwroot\backup"

or

Use master
GO
xp_cmdshell "dir c:\inetpub\wwwroot\backup"

HTH, JEns Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

sometime people think that the path within any TSQL commands can be also used locally, so if you are working in a client server scenario and you issue the command BACKUP DATABASE with some path and filename (with absolute drive letters), the drive letters apply to the server not the client. If you want to reach a destination on the client machine, you have to copy it via a UNC share.

Don′t know if that applies to you, just to let you know. If this is not the case, please see if the listing of the directory comes back with a listing of files (then it really HAS to be a permission problem) or it comes back with an error that the directory doesn′t exists.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Development server database refresh from production server

Hi
Can we use BCP AND/OR BULK INSERT to do development server databse refresh from production server . I have to do this by following the rules below:

- no truncation of source table
- update of changed or new records only.

I know we can use replication, dts and other methods for this but I need information about this one.

ThanksUsing BCP and/or Bulk insert to update a development server is possible, but would get complicated if you only want to update the changed records in the production server. If you want to only update the changed records you will need to somehow keep track of the records which changed in the production db, and this could lead to expanding your production db by adding extra columns or tables, which is probably not wanted. Overall I would not recommned this procedure.

Something like log shipping in SQL 2000 might be a better solution.

Good luck
Hope this helped

Developing a Search Case

hi all,
I am developing a search case...and i got to develop a stored procedure.

the procedure will be called in the following way...

exec search_text('t1@.c1%c2%c3%t2@.c1%c2%c3%t3@.c1%c2%,'he llo')

i.e. i want to search for 'hello' in the t1,t2,t3 tables and c1,c2,c3..are the corresponding columns in respective tables..

writing select statement is easy.,..but need a best solution to divide the tables and columns and then writing the procedure...

help me out...

thanks and regards
sai sreenivasHi,

it all depends on what database server you want to run the Stored Procedure...
So, what's the database and we try again...|||hii ,
My Database server is SQLSERVER on windows 2000 platform.....

looking forward for your reply...

thanks and regards
SAI SREENIVAS JEEDIGUNTA|||I'm sorry man, I'm not that familiar with Microsoft software.
If it were DB2 or Informix I could give you the solution...|||Will you always have the pattern 1 table and 3 columns ? Can you post the sql statement you have already designed that you want emulated in the sp ? Will you be running this sp in sql server or through a front-end like visual basic ... ? Will you always be searching for "hello" or some unknown string ?|||What results are you expecting back from your stored procedure ?

Developer Express & Database issues

I Am developing a simple app with a database. All has been going fine until now, the following is occuring:

In Visual Web Developer 2005 Express if I connect to the database and view some table data I can do that fine but then as soon as I try to view a page in the browser I get the error:
Login failed for user 'MachineName\ASPNET'
If in web developer I go to the database explorer and close the connection to the database then refresh the page it all works fine.

Anyone have anyideas on how to correct this?

Thanks
Scott

This is a frequently asked issue The login 'MachineName\ASPNET' is used by IIS when your website trying to connect to SQL with Window Authentication (Integrated Security=SSPI). So in this case just add the account to SQL Server Logins with proper database permissions, which can be easily done in Management Studio: go to Security->Logins->right clich and choose add login->add the Windows account 'MachineName\ASPNET' and give it permissions (or simply add it to sysadmin server role). You can download Management Studio Express from here:

http://msdn.microsoft.com/vstudio/express/sql/download/

Dev cube on 32-bit, production on 64?

Please help me confirm that the following scenario is reasonable.

I believe that I can develop an SSAS DB and cube on SQL 2005 "Dev/32 bit" and deploy dev builds locally using BIDS, and then deploy a production version, still using my local BIDS, straight to the "Production/64 bit" server (with same SP).

Am I correct that this will work and is a standard approach? Are there any obvious gotchas I should know about?

Thanks very much.

Daniel Upton

Yes this will work, and this approach is used by many users.

Determining what to input as server

I'm trying to connect to a sql database, but I don't know what myserver is in the following code.
Dim strConn As String = "server=myserver;database=Northwind"

I can't get the code to link up with my Northwind database.

I'm running everything locally if that helps.

Thanks!

JonYou mean what to use instead of "myserver"? It is the machine name on the network where the SQL Server is located. If it is on the same machine as where you are running the ASP.NET app, you can either use the machine name or "(local)"

Don|||Oh, and you can also use an IP address (such as 10.12.15.16) if you have it, or an internet address (theserver.mydomain.com).

Don

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy