Case Statement in Stored Procedure ?

I am converting IIF statements from an Access Query into a Stored Procedure
and was doing ok until I got to this one which is a bit more complicated:
CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] / [Field5]
END
All fields are valid boleen fields
I have tried putting parens around the equation part but still get errors.
How can I get the data into this field? Thanks.CAST as the bit columns as INT such as CAST([Field2] AS INT)
I think that [Field2] + [Field3] -[Field4] / [Field5]
should be ([Field2] + [Field3] -[Field4]) / [Field5]
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:F23EB696-E2B4-43EA-AC88-664FB8B2221E@.microsoft.com...
>I am converting IIF statements from an Access Query into a Stored
>Procedure
> and was doing ok until I got to this one which is a bit more complicated:
> CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] /
> [Field5]
> END
> All fields are valid boleen fields
> I have tried putting parens around the equation part but still get errors.
> How can I get the data into this field? Thanks.
>|||Please explain what "All fields are valid boleen fields" means.
Also post the error message that you get.
ML|||The Fields I'm trying to perform math on are boolean fields and are already
in place in the stored procedure.
The error says:
ADO Error:Invalid operator for data type. Operator equals add, type equals
bit.
Thanks for helping.
"ML" wrote:

> Please explain what "All fields are valid boleen fields" means.
> Also post the error message that you get.
>
> ML|||>> All fields are valid Boolean fields <<
UNH' Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files.
Where is the DDL? SQL has no Boolean data types and good programemrs
do not use the proprietary BIT data type.
There is no CASE statement in SQL, either; there is a CASE expression.
So what "field" are you trying to assign this exprsssion to?|||First of all, bit is not boolean, and second - the error message is pretty
much self-explanatory. Why are you adding, subtracting and dividing values
that can either be 1 or 0?
E.g.:
what does this mean to you:
1 + 0 - 1 / 1
or:
1 + 1 - 0 / 1
or worse:
1 + 1 -1 / 0
Please describe what you're trying to achieve. There must be some reason...?
ML|||
"ML" wrote:

> First of all, bit is not boolean, and second - the error message is pretty
> much self-explanatory. Why are you adding, subtracting and dividing values
> that can either be 1 or 0?
> E.g.:
> what does this mean to you:
> 1 + 0 - 1 / 1...this means 0%
> 1 + 0 / 1 + 1 ...this would be 50%
> 1 + 1 / 1 + 1 ...100%
> or:
> 1 + 1 - 0 / 1
> or worse:
> 1 + 1 -1 / 0...checked for and not allowed through business rules
> Please describe what you're trying to achieve. There must be some reason..
.?
>
> ML
I needed to be able to do math on the fields like I can in MS
Access...Changing the bits to integers worked.|||> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
Good programmers make proper use of approriate dtaa types in any given
situation.
Similary, good programmers don't just follow along with the comment "all
GOTO statements or CURSORs are bad", but take the time to understand why
other programmers prefer to use better alternatives. Cursors can also be
very powerful, but only when used in an appropriate situation where the same
result cannot be equally-well achieved via set-based operations.
If you were defining a table ApplicationUsers, and needed to store (in a
SQL2000 database) a field indicating whether the user was enabled, ( a field
that could only ever have a value of Yes/No), what field would YOU use ...

> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
Whilst I agree with you that the snippet is technically an expression, not a
statement, this value doesn't necessarily need to be assigned to a field.|||I guess if I were an SQL expert I wouldn't need to use this web site and
expose my ignorance of SQL terminology to arrogant know-it-alls who find it
easier to berate my lack of knowledge than just simply give me the answer I
was looking for like Raymond D'Anjou so kindly did. I would appreciate you
not anwering any of my posts in the future.
"--CELKO--" wrote:

> UNH' Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files.
> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
>|||AkAlan,
Don't let Celko intimidate you, and as for him being a SQL expert, well,
perhaps an ANSI 92 expert and thats about as far as it goes.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:0A58D8F7-6E39-4072-9634-2082745BA421@.microsoft.com...
>I guess if I were an SQL expert I wouldn't need to use this web site and
> expose my ignorance of SQL terminology to arrogant know-it-alls who find
> it
> easier to berate my lack of knowledge than just simply give me the answer
> I
> was looking for like Raymond D'Anjou so kindly did. I would appreciate you
> not anwering any of my posts in the future.
> "--CELKO--" wrote:
>

CASE in T-SQL

I'm upsizing Access to SQL Server 2005.
I need to convert the following query:
SELECT
tblHistory.AutoNumber,
tblHistory.InputDate,
IIf([tblHistory].[TaxType]="111" Or [tblHistory].[taxtype]="222","ABC",IIf([tblHistory].[taxtype]="AAA","AAA","BBB")) AS 1TaxType FROM tblHistory;

CASE in T SQL:
SELECT tblHistory.AutoNumber,
tblHistory.InputDate,
'1TaxType' =
CASE
WHEN [tblHistory].[TaxType] = '111' THEN 'ABC'
WHEN [tblHistory].[TaxType] = '222' THEN 'ABC'
ELSE
CASE
WHEN [tblHistory].[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END
END
FROM tblHistory;

Is this correct?

It is fine. But you can simplify it further like:

SELECT h.AutoNumber,
h.InputDate,
CASE
WHEN h.[TaxType] IN ('111', '222') THEN 'ABC'
WHEN h.[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END as [1TaxType]
FROM tblHistory as h;

I also modified the SELECT statement syntax to use table aliases which makes it easier to read and change the proprietary column alias syntax also.

Case / Switch function help

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.Use the CASE statement in SQL Server.|||Can I do this in the view design mode or do I need to make a user defined function?
Any sample code would be really appreciated.|||CASE WHEN [CategoryName] = 'Beverage' THEN 'Drink'
WHEN [CategoryName] = 'Cheese' THEN 'Dairy'
ELSE 'Unknown'
END AS DerivedColumnName

Cascading Parameters ValidValues via the Webservices?

I'd like to access validvalues in a report through the webservices. Anyone
know a way to get validvalues of a parameter that is dependent on a chosen
value of another param? Thanks.If the valid values query for the second parameter (B) references the value of the first parameter
(A), the valid values list of B should dynamically change based on A.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> I'd like to access validvalues in a report through the webservices. Anyone
> know a way to get validvalues of a parameter that is dependent on a chosen
> value of another param? Thanks.
>|||Can I do this via the webservices? I'm still not clear on how to do it.
The query for the second parameter (B) is parameterized using the first
parameter (A) and works fine using the report manager to render the report.
However, if I do
ReportParameter[] parameters = rs.GetReportParameters(myReportPath, null,
false, null, null);
then parameters[0] will have valid values, but parameters[1] won't yet.
This makes sense, because it doesn't have a value from the first parameter
to query with. Is there a way for me to now send a value picked out of the
first parameter's valid values back to the server and get the valid values
for the second parameter? I've tried recalling GetReportParameters with a
ParameterValue[], but that doesn't seem to work.
Thanks.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:OqkzusUWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> If the valid values query for the second parameter (B) references the
value of the first parameter
> (A), the valid values list of B should dynamically change based on A.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
> news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > I'd like to access validvalues in a report through the webservices.
Anyone
> > know a way to get validvalues of a parameter that is dependent on a
chosen
> > value of another param? Thanks.
> >
> >
>|||Hi Steve:
I have an example here: http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
<landiss@.bus.oregonstate.edu> wrote:
>I'd like to access validvalues in a report through the webservices. Anyone
>know a way to get validvalues of a parameter that is dependent on a chosen
>value of another param? Thanks.
>|||Thanks!
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:p3njd0t8smjh6kun5dnu1vocpu7qe24vui@.4ax.com...
> Hi Steve:
> I have an example here: http://odetocode.com/Articles/123.aspx
> HTH,
> --
> Scott
> http://www.OdeToCode.com
> On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
> <landiss@.bus.oregonstate.edu> wrote:
> >I'd like to access validvalues in a report through the webservices.
Anyone
> >know a way to get validvalues of a parameter that is dependent on a
chosen
> >value of another param? Thanks.
> >
>

Cascading Deletes

When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?

SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?

Has anyone scanned the landscape for a merry-go-round?

:D

Wholly disconnected ramblings bart man...

Seriously...be careful with cascading...should be no need...

I never liked messing with keys...

damn surrogates...

To me, if a key changes, then it's a new entity...or the key is defined improperly...

You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

Disconneted ramblings...

...many...non-sequiturs...

Wish I had a key for elipsis so I didn't have to hit the period key three times...

Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...

pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...

Cool...

You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication

maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?

WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE

Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

Again, do I need to write triggers to accomplish my goal here :confused:

I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:

and you dont have to create database devices anymore.. :D

Cascading Changes in SQL Server

I am converting a database from Access to SQL Server and am trying to
set up a relationship like the one that had been set in Access. This
relationship had cascading deletes and updates. When I try to set up
the relationship in SQL server, I get the message (as an example):
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_States-StateID' on table
'PersonnelInformation' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
I don't really understand why I am getting this message, unless
cascading updates are bidirectional (meaning that a change to the
foreign key would cascade over to the primary key:
I basically have the following setup (I am using an example that is a
bit easier to understand than my actual table setup:
TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
CompanyTaxID CompanyTaxID StateID CompanyTaxID
Company CEOName STATE SubsidiaryTaxID
StateID AddressLine1 StateID
...
StateID
So basically I set a primary key to foreign key relationship from
TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
and Updates).
I also set a primary to foreign Key relationship from TABLECOMPANY
(PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
and Updates).
Then I create a primary to foreign key relationship from TABLESTATE
(PK) to Each of the other three tables on StateID (Update only).
This configuration fails. I don't understand why, though clearly the
relationships with TABLESTATE are the problem. But since TABLE state
contains no foreign keys, nor does it specify any cascading deletes,
no changes made anywhere could create a loop. Unless a change in a
foreign key could affect the primary key, which doesn't make
sense...?
Does anyone know why this is happening. Is there a solution, or do I
HAVE to use triggers?
Thanks,
Ryan
Check the article at
http://support.microsoft.com/default...;en-us;321843, it is about
this error.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ryan" <ryan.d.rembaum@.kp.org> wrote in message
news:b5cda00e.0408271644.1466e73f@.posting.google.c om...
> I am converting a database from Access to SQL Server and am trying to
> set up a relationship like the one that had been set in Access. This
> relationship had cascading deletes and updates. When I try to set up
> the relationship in SQL server, I get the message (as an example):
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> FOREIGN KEY constraint 'FK_States-StateID' on table
> 'PersonnelInformation' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> constraint. See previous errors.
> I don't really understand why I am getting this message, unless
> cascading updates are bidirectional (meaning that a change to the
> foreign key would cascade over to the primary key:
> I basically have the following setup (I am using an example that is a
> bit easier to understand than my actual table setup:
> TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
> CompanyTaxID CompanyTaxID StateID CompanyTaxID
> Company CEOName STATE SubsidiaryTaxID
> StateID AddressLine1 StateID
> ...
> StateID
> So basically I set a primary key to foreign key relationship from
> TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
> and Updates).
> I also set a primary to foreign Key relationship from TABLECOMPANY
> (PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
> and Updates).
> Then I create a primary to foreign key relationship from TABLESTATE
> (PK) to Each of the other three tables on StateID (Update only).
> This configuration fails. I don't understand why, though clearly the
> relationships with TABLESTATE are the problem. But since TABLE state
> contains no foreign keys, nor does it specify any cascading deletes,
> no changes made anywhere could create a loop. Unless a change in a
> foreign key could affect the primary key, which doesn't make
> sense...?
> Does anyone know why this is happening. Is there a solution, or do I
> HAVE to use triggers?
> Thanks,
> Ryan
|||Hi Dejan,
Thanks. Just to clarify, is this article saying that SQL server
determines whether a key will might cause cycles or multiple cascade
paths based simply on the table in its entirety, as opposed to whether
the actual fields you will be updating could possibly cause such an
event?
In the example given, I can understand the problem, in that the setup
has two cascading updates to the same field on the many side of the
relationship. This is not the case in my example. There are not
multiple paths at the field level. A change to a state ID (in
TABLESTATE) should cause an update in the three tables it is related
to. Those tables have relationships with each other that do not
include the table TABLESTATE.
Again, though, from the TABLE level I could see how SQL might flag it.
Am I correct in what I am getting from this?
If so, this seems too bad since I think it would be easier to manage
if I could save triggers for things relationships can't do. Strange
that Access can establish the necessary relationships and SQL can't.
Oh well!
Thanks!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<uak74uZjEHA.704@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/default...;en-us;321843, it is about
> this error.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Ryan" <ryan.d.rembaum@.kp.org> wrote in message
> news:b5cda00e.0408271644.1466e73f@.posting.google.c om...

Cascading Changes in SQL Server

I am converting a database from Access to SQL Server and am trying to
set up a relationship like the one that had been set in Access. This
relationship had cascading deletes and updates. When I try to set up
the relationship in SQL server, I get the message (as an example):
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_States-StateID' on table
'PersonnelInformation' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
I don't really understand why I am getting this message, unless
cascading updates are bidirectional (meaning that a change to the
foreign key would cascade over to the primary key:
I basically have the following setup (I am using an example that is a
bit easier to understand than my actual table setup:
TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
CompanyTaxID CompanyTaxID StateID CompanyTaxID
Company CEOName STATE SubsidiaryTaxID
StateID AddressLine1 StateID
...
StateID
So basically I set a primary key to foreign key relationship from
TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
and Updates).
I also set a primary to foreign Key relationship from TABLECOMPANY
(PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
and Updates).
Then I create a primary to foreign key relationship from TABLESTATE
(PK) to Each of the other three tables on StateID (Update only).
This configuration fails. I don't understand why, though clearly the
relationships with TABLESTATE are the problem. But since TABLE state
contains no foreign keys, nor does it specify any cascading deletes,
no changes made anywhere could create a loop. Unless a change in a
foreign key could affect the primary key, which doesn't make
sense...'
Does anyone know why this is happening. Is there a solution, or do I
HAVE to use triggers?
Thanks,
RyanCheck the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;321843, it is about
this error.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ryan" <ryan.d.rembaum@.kp.org> wrote in message
news:b5cda00e.0408271644.1466e73f@.posting.google.com...
> I am converting a database from Access to SQL Server and am trying to
> set up a relationship like the one that had been set in Access. This
> relationship had cascading deletes and updates. When I try to set up
> the relationship in SQL server, I get the message (as an example):
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> FOREIGN KEY constraint 'FK_States-StateID' on table
> 'PersonnelInformation' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> constraint. See previous errors.
> I don't really understand why I am getting this message, unless
> cascading updates are bidirectional (meaning that a change to the
> foreign key would cascade over to the primary key:
> I basically have the following setup (I am using an example that is a
> bit easier to understand than my actual table setup:
> TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
> CompanyTaxID CompanyTaxID StateID CompanyTaxID
> Company CEOName STATE SubsidiaryTaxID
> StateID AddressLine1 StateID
> ...
> StateID
> So basically I set a primary key to foreign key relationship from
> TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
> and Updates).
> I also set a primary to foreign Key relationship from TABLECOMPANY
> (PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
> and Updates).
> Then I create a primary to foreign key relationship from TABLESTATE
> (PK) to Each of the other three tables on StateID (Update only).
> This configuration fails. I don't understand why, though clearly the
> relationships with TABLESTATE are the problem. But since TABLE state
> contains no foreign keys, nor does it specify any cascading deletes,
> no changes made anywhere could create a loop. Unless a change in a
> foreign key could affect the primary key, which doesn't make
> sense...'
> Does anyone know why this is happening. Is there a solution, or do I
> HAVE to use triggers?
> Thanks,
> Ryan|||Hi Dejan,
Thanks. Just to clarify, is this article saying that SQL server
determines whether a key will might cause cycles or multiple cascade
paths based simply on the table in its entirety, as opposed to whether
the actual fields you will be updating could possibly cause such an
event?
In the example given, I can understand the problem, in that the setup
has two cascading updates to the same field on the many side of the
relationship. This is not the case in my example. There are not
multiple paths at the field level. A change to a state ID (in
TABLESTATE) should cause an update in the three tables it is related
to. Those tables have relationships with each other that do not
include the table TABLESTATE.
Again, though, from the TABLE level I could see how SQL might flag it.
Am I correct in what I am getting from this?
If so, this seems too bad since I think it would be easier to manage
if I could save triggers for things relationships can't do. Strange
that Access can establish the necessary relationships and SQL can't.
Oh well!
Thanks!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<uak74uZjEHA.704@.TK2MSFTNGP09.phx.gbl>...
> Check the article at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;321843, it is about
> this error.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Ryan" <ryan.d.rembaum@.kp.org> wrote in message
> news:b5cda00e.0408271644.1466e73f@.posting.google.com...
> > I am converting a database from Access to SQL Server and am trying to
> > set up a relationship like the one that had been set in Access. This
> > relationship had cascading deletes and updates. When I try to set up
> > the relationship in SQL server, I get the message (as an example):
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> > FOREIGN KEY constraint 'FK_States-StateID' on table
> > 'PersonnelInformation' may cause cycles or multiple cascade paths.
> > Specify ON DELETE NO ACTION or ON
> > UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> > constraint. See previous errors.
> >
> > I don't really understand why I am getting this message, unless
> > cascading updates are bidirectional (meaning that a change to the
> > foreign key would cascade over to the primary key:
> >
> > I basically have the following setup (I am using an example that is a
> > bit easier to understand than my actual table setup:
> >
> > TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
> > CompanyTaxID CompanyTaxID StateID CompanyTaxID
> > Company CEOName STATE SubsidiaryTaxID
> > StateID AddressLine1 StateID
> > ...
> > StateID
> >
> > So basically I set a primary key to foreign key relationship from
> > TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
> > and Updates).
> >
> > I also set a primary to foreign Key relationship from TABLECOMPANY
> > (PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
> > and Updates).
> >
> > Then I create a primary to foreign key relationship from TABLESTATE
> > (PK) to Each of the other three tables on StateID (Update only).
> >
> > This configuration fails. I don't understand why, though clearly the
> > relationships with TABLESTATE are the problem. But since TABLE state
> > contains no foreign keys, nor does it specify any cascading deletes,
> > no changes made anywhere could create a loop. Unless a change in a
> > foreign key could affect the primary key, which doesn't make
> > sense...'
> >
> > Does anyone know why this is happening. Is there a solution, or do I
> > HAVE to use triggers?
> >
> > Thanks,
> > Ryan

Cascading Changes in SQL Server

I am converting a database from Access to SQL Server and am trying to
set up a relationship like the one that had been set in Access. This
relationship had cascading deletes and updates. When I try to set up
the relationship in SQL server, I get the message (as an example):
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Intro
ducing
FOREIGN KEY constraint 'FK_States-StateID' on table
'PersonnelInformation' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
I don't really understand why I am getting this message, unless
cascading updates are bidirectional (meaning that a change to the
foreign key would cascade over to the primary key:
I basically have the following setup (I am using an example that is a
bit easier to understand than my actual table setup:
TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
CompanyTaxID CompanyTaxID StateID CompanyTaxID
Company CEOName STATE SubsidiaryTaxID
StateID AddressLine1 StateID
..
StateID
So basically I set a primary key to foreign key relationship from
TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
and Updates).
I also set a primary to foreign Key relationship from TABLECOMPANY
(PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
and Updates).
Then I create a primary to foreign key relationship from TABLESTATE
(PK) to Each of the other three tables on StateID (Update only).
This configuration fails. I don't understand why, though clearly the
relationships with TABLESTATE are the problem. But since TABLE state
contains no foreign keys, nor does it specify any cascading deletes,
no changes made anywhere could create a loop. Unless a change in a
foreign key could affect the primary key, which doesn't make
sense...'
Does anyone know why this is happening. Is there a solution, or do I
HAVE to use triggers?
Thanks,
RyanCheck the article at
http://support.microsoft.com/defaul...b;en-us;321843, it is about
this error.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ryan" <ryan.d.rembaum@.kp.org> wrote in message
news:b5cda00e.0408271644.1466e73f@.posting.google.com...
> I am converting a database from Access to SQL Server and am trying to
> set up a relationship like the one that had been set in Access. This
> relationship had cascading deletes and updates. When I try to set up
> the relationship in SQL server, I get the message (as an example):
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Int
roducing
> FOREIGN KEY constraint 'FK_States-StateID' on table
> 'PersonnelInformation' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e
> constraint. See previous errors.
> I don't really understand why I am getting this message, unless
> cascading updates are bidirectional (meaning that a change to the
> foreign key would cascade over to the primary key:
> I basically have the following setup (I am using an example that is a
> bit easier to understand than my actual table setup:
> TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
> CompanyTaxID CompanyTaxID StateID CompanyTaxID
> Company CEOName STATE SubsidiaryTaxID
> StateID AddressLine1 StateID
> ...
> StateID
> So basically I set a primary key to foreign key relationship from
> TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
> and Updates).
> I also set a primary to foreign Key relationship from TABLECOMPANY
> (PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
> and Updates).
> Then I create a primary to foreign key relationship from TABLESTATE
> (PK) to Each of the other three tables on StateID (Update only).
> This configuration fails. I don't understand why, though clearly the
> relationships with TABLESTATE are the problem. But since TABLE state
> contains no foreign keys, nor does it specify any cascading deletes,
> no changes made anywhere could create a loop. Unless a change in a
> foreign key could affect the primary key, which doesn't make
> sense...'
> Does anyone know why this is happening. Is there a solution, or do I
> HAVE to use triggers?
> Thanks,
> Ryan|||Hi Dejan,
Thanks. Just to clarify, is this article saying that SQL server
determines whether a key will might cause cycles or multiple cascade
paths based simply on the table in its entirety, as opposed to whether
the actual fields you will be updating could possibly cause such an
event?
In the example given, I can understand the problem, in that the setup
has two cascading updates to the same field on the many side of the
relationship. This is not the case in my example. There are not
multiple paths at the field level. A change to a state ID (in
TABLESTATE) should cause an update in the three tables it is related
to. Those tables have relationships with each other that do not
include the table TABLESTATE.
Again, though, from the TABLE level I could see how SQL might flag it.
Am I correct in what I am getting from this?
If so, this seems too bad since I think it would be easier to manage
if I could save triggers for things relationships can't do. Strange
that Access can establish the necessary relationships and SQL can't.
Oh well!
Thanks!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<uak
74uZjEHA.704@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/defaul...b;en-us;321843, it is abou
t
> this error.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Ryan" <ryan.d.rembaum@.kp.org> wrote in message
> news:b5cda00e.0408271644.1466e73f@.posting.google.com...

CAS

I'm having a difficult time wrapping my head around Code Access Security.
We have just installed Sharepoint Portal Server 2003. I have a web part that
uses the obc.net dataprovider that is causing me a security error when I try
to upload the web part.
I'm just not understanding what I need to change on the server to allow this
code to run. Since very few people will be able to add web parts does it
make sense just to take the security down to a lower level? This is on our
corporate intranet. If so, can someone explain to me how to lower the
security for the whole site?
Thanks,
JasonSorry - wrong newsgroup
"Jason MacKenzie" <jmackenzie_nospamallowed@.formet.com> wrote in message
news:uunGCpJpEHA.896@.TK2MSFTNGP12.phx.gbl...
> I'm having a difficult time wrapping my head around Code Access Security.
> We have just installed Sharepoint Portal Server 2003. I have a web part
> that uses the obc.net dataprovider that is causing me a security error
> when I try to upload the web part.
> I'm just not understanding what I need to change on the server to allow
> this code to run. Since very few people will be able to add web parts
> does it make sense just to take the security down to a lower level? This
> is on our corporate intranet. If so, can someone explain to me how to
> lower the security for the whole site?
> Thanks,
> Jason
>

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid
<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.
Hi
Run SQL Server Profiler on the target server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:
> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideall
y
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capture NT User ID

Access front-end, SQL Server 2005 backend.

I have users connected to SQL Server via a Microsoft Access user-
interface.
Connection is via NT login.

I want to log users' activities to the database with their userid.

How can I capture their NT User ID (via VBA in Access)?

Thanks,
BubblesYou could create a view in SQL Server, I guess, like:

CREATE VIEW dbo.NTUsername
AS
SELECT [username] = SUSER_SNAME();

Now VBA should be able to query from that view in SQL Server just like it
would any other table or view...

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"bubbles" <bubbles.one@.hotmail.comwrote in message
news:1175737278.694527.78540@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

Access front-end, SQL Server 2005 backend.
>
I have users connected to SQL Server via a Microsoft Access user-
interface.
Connection is via NT login.
>
I want to log users' activities to the database with their userid.
>
How can I capture their NT User ID (via VBA in Access)?
>
Thanks,
Bubbles
>

Capture Error In DTS Package

I have a SQL Server Package that is dumping a text file into an Access
Database. However, due to update timing every now and then it errors
out due to a primary key violation. How do I capture that error
number in my next Active Script Task and determine whether or not I
want to continue (since if it's a duplicate I just want to skip it and
finish the DTS package). If it's a different error I want to log
it.
Thanks!Hi
If these are identity values you may want to check out
http://www.sqldts.com/293.aspx
if not try
http://www.sqldts.com/282.aspx
John
"Creative" wrote:
> I have a SQL Server Package that is dumping a text file into an Access
> Database. However, due to update timing every now and then it errors
> out due to a primary key violation. How do I capture that error
> number in my next Active Script Task and determine whether or not I
> want to continue (since if it's a duplicate I just want to skip it and
> finish the DTS package). If it's a different error I want to log
> it.
> Thanks!
>|||On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> If these are identity values you may want to check outhttp://www.sqldts.com/293.aspx
> if not tryhttp://www.sqldts.com/282.aspx
> John
>
> "Creative" wrote:
> > I have a SQL Server Package that is dumping a text file into an Access
> > Database. However, due to update timing every now and then it errors
> > out due to a primary key violation. How do I capture that error
> > number in my next Active Script Task and determine whether or not I
> > want to continue (since if it's a duplicate I just want to skip it and
> > finish the DTS package). If it's a different error I want to log
> > it.
> > Thanks!- Hide quoted text -
> - Show quoted text -
Thanks for the reply but it doesn't look like that answers my
question. I'm actually using a File Transformation from a text file
to an access database. I have a connection to the text file and a
connection to an access database with a transformation connecting the
two.|||Hi
There was no mention of the destination database being an access database,
in your original post. I suggest that you therefore load into a staging table
and either do an update if the PK exists or an insert if it doesn't.
John
"Creative" wrote:
> On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > If these are identity values you may want to check outhttp://www.sqldts.com/293.aspx
> > if not tryhttp://www.sqldts.com/282.aspx
> >
> > John
> >
> >
> >
> > "Creative" wrote:
> > > I have a SQL Server Package that is dumping a text file into an Access
> > > Database. However, due to update timing every now and then it errors
> > > out due to a primary key violation. How do I capture that error
> > > number in my next Active Script Task and determine whether or not I
> > > want to continue (since if it's a duplicate I just want to skip it and
> > > finish the DTS package). If it's a different error I want to log
> > > it.
> >
> > > Thanks!- Hide quoted text -
> >
> > - Show quoted text -
> Thanks for the reply but it doesn't look like that answers my
> question. I'm actually using a File Transformation from a text file
> to an access database. I have a connection to the text file and a
> connection to an access database with a transformation connecting the
> two.
>|||I don't know the answer, but it's a good question, and I'm in a
similar situation in SSIS - except I want to test for a warning code!
Googling found this crufty answer:
http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
There may be a better answer if you open up the multiphase data pump
business, but I have small experience with that.
Note there is also a DTS group, I'll copy this to.
Josh
On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
wrote:
>I have a SQL Server Package that is dumping a text file into an Access
>Database. However, due to update timing every now and then it errors
>out due to a primary key violation. How do I capture that error
>number in my next Active Script Task and determine whether or not I
>want to continue (since if it's a duplicate I just want to skip it and
>finish the DTS package). If it's a different error I want to log
>it.
>Thanks!|||Hi Josh
I pointed the OP to the tutorial on Multiphase datapump on SQL DTS. In SSIS
you have a standarderrorvariable which may be of some use to you, and there
is the configure error output dialog. you may want to check out Professional
SQL Server 2005 Integration Services by Brian Knight et al ISBN 0764584359 or
the short videos on http://www.jumpstarttv.com such as
http://www.jumpstarttv.com/Media.aspx?vid=34 where Brian shows how an
application similar to the examples in the book work.
John
"JXStern" wrote:
> I don't know the answer, but it's a good question, and I'm in a
> similar situation in SSIS - except I want to test for a warning code!
> Googling found this crufty answer:
> http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
> There may be a better answer if you open up the multiphase data pump
> business, but I have small experience with that.
> Note there is also a DTS group, I'll copy this to.
> Josh
>
> On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
> wrote:
> >I have a SQL Server Package that is dumping a text file into an Access
> >Database. However, due to update timing every now and then it errors
> >out due to a primary key violation. How do I capture that error
> >number in my next Active Script Task and determine whether or not I
> >want to continue (since if it's a duplicate I just want to skip it and
> >finish the DTS package). If it's a different error I want to log
> >it.
> >
> >Thanks!
>|||John,
Thanks for several good hints, I'll be following them all. Configure error
output dialog?!?! Oh, in a data flow. Righto.
I keep waiting for some other, newer SSIS books to come out.
I've got the basics grinding along now, anyway.
Thanks again.
Josh
"John Bell" wrote:
> Hi Josh
> I pointed the OP to the tutorial on Multiphase datapump on SQL DTS. In SSIS
> you have a standarderrorvariable which may be of some use to you, and there
> is the configure error output dialog. you may want to check out Professional
> SQL Server 2005 Integration Services by Brian Knight et al ISBN 0764584359 or
> the short videos on http://www.jumpstarttv.com such as
> http://www.jumpstarttv.com/Media.aspx?vid=34 where Brian shows how an
> application similar to the examples in the book work.
> John
> "JXStern" wrote:
> > I don't know the answer, but it's a good question, and I'm in a
> > similar situation in SSIS - except I want to test for a warning code!
> >
> > Googling found this crufty answer:
> > http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
> >
> > There may be a better answer if you open up the multiphase data pump
> > business, but I have small experience with that.
> >
> > Note there is also a DTS group, I'll copy this to.
> >
> > Josh
> >
> >
> > On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
> > wrote:
> >
> > >I have a SQL Server Package that is dumping a text file into an Access
> > >Database. However, due to update timing every now and then it errors
> > >out due to a primary key violation. How do I capture that error
> > >number in my next Active Script Task and determine whether or not I
> > >want to continue (since if it's a duplicate I just want to skip it and
> > >finish the DTS package). If it's a different error I want to log
> > >it.
> > >
> > >Thanks!
> >
> >

Cant Use SQL Server with Firewall

My ISP recently had me reset my TCP/IP stack. After that, Norton Personal
Firewall prompted me twice that SQL Server was trying to access the
Internet. Both times I responded to allow it to and to always use that
action. Now I am not able to use SQL Server with NPF enabled. If I disable
NPF, SQL Server works fine.

I am using the desktop edition of SQL Server 7, on a standalone PC, not
connected to a server. I have been using SQL Server and NPF together for
over a year. Now, since my TCP/IP stack was reset, NPF interferes with SQL
Server.

Anyone have any experience with this?

Thanks,

NeilNot sure how to do that. Can allow an IP to be trusted, but don't see how
one specifies a port.

"Michael Culley" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:e7EaQbtZDHA.2932@.tk2msftngp13.phx.gbl...
> You probably need to open port 1433.
> --
> Michael Culley
>
> "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
news:gzC0b.560$lw4.409@.newsread3.news.pas.earthlin k.net...
> > My ISP recently had me reset my TCP/IP stack. After that, Norton
Personal
> > Firewall prompted me twice that SQL Server was trying to access the
> > Internet. Both times I responded to allow it to and to always use that
> > action. Now I am not able to use SQL Server with NPF enabled. If I
disable
> > NPF, SQL Server works fine.
> > I am using the desktop edition of SQL Server 7, on a standalone PC, not
> > connected to a server. I have been using SQL Server and NPF together for
> > over a year. Now, since my TCP/IP stack was reset, NPF interferes with
SQL
> > Server.
> > Anyone have any experience with this?
> > Thanks,
> > Neil|||Here is a response from John Gose MS PSS

For SQL and a firewall, the general rule is 1433 in ( or whatever port your
SQL Server is listening on) and any out. See first reference. As far as
DTC, if you are using it, the second article should help

287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932

250367 INFO: Configuring Microsoft Distributed Transaction Coordinator
(DTC) to
http://support.microsoft.com/?id=250367

--

---------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
news:EHG0b.824$lw4.154@.newsread3.news.pas.earthlin k.net...
> Not sure how to do that. Can allow an IP to be trusted, but don't see how
> one specifies a port.
> "Michael Culley" <mculley@.NOSPAMoptushome.com.au> wrote in message
> news:e7EaQbtZDHA.2932@.tk2msftngp13.phx.gbl...
> > You probably need to open port 1433.
> > --
> > Michael Culley
> > "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> news:gzC0b.560$lw4.409@.newsread3.news.pas.earthlin k.net...
> > > My ISP recently had me reset my TCP/IP stack. After that, Norton
> Personal
> > > Firewall prompted me twice that SQL Server was trying to access the
> > > Internet. Both times I responded to allow it to and to always use that
> > > action. Now I am not able to use SQL Server with NPF enabled. If I
> disable
> > > NPF, SQL Server works fine.
> > > > I am using the desktop edition of SQL Server 7, on a standalone PC,
not
> > > connected to a server. I have been using SQL Server and NPF together
for
> > > over a year. Now, since my TCP/IP stack was reset, NPF interferes with
> SQL
> > > Server.
> > > > Anyone have any experience with this?
> > > > Thanks,
> > > > Neil
> >|||Thanks.

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OtsXhqvZDHA.1640@.TK2MSFTNGP10.phx.gbl...
> Here is a response from John Gose MS PSS
> For SQL and a firewall, the general rule is 1433 in ( or whatever port
your
> SQL Server is listening on) and any out. See first reference. As far as
> DTC, if you are using it, the second article should help
> 287932 INF: TCP Ports Needed for Communication to SQL Server Through a
> Firewall
> http://support.microsoft.com/?id=287932
> 250367 INFO: Configuring Microsoft Distributed Transaction Coordinator
> (DTC) to
> http://support.microsoft.com/?id=250367
>
> --
> ---------
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> news:EHG0b.824$lw4.154@.newsread3.news.pas.earthlin k.net...
> > Not sure how to do that. Can allow an IP to be trusted, but don't see
how
> > one specifies a port.
> > "Michael Culley" <mculley@.NOSPAMoptushome.com.au> wrote in message
> > news:e7EaQbtZDHA.2932@.tk2msftngp13.phx.gbl...
> > > You probably need to open port 1433.
> > > > --
> > > Michael Culley
> > > > > "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> > news:gzC0b.560$lw4.409@.newsread3.news.pas.earthlin k.net...
> > > > My ISP recently had me reset my TCP/IP stack. After that, Norton
> > Personal
> > > > Firewall prompted me twice that SQL Server was trying to access the
> > > > Internet. Both times I responded to allow it to and to always use
that
> > > > action. Now I am not able to use SQL Server with NPF enabled. If I
> > disable
> > > > NPF, SQL Server works fine.
> > > > > > I am using the desktop edition of SQL Server 7, on a standalone PC,
> not
> > > > connected to a server. I have been using SQL Server and NPF together
> for
> > > > over a year. Now, since my TCP/IP stack was reset, NPF interferes
with
> > SQL
> > > > Server.
> > > > > > Anyone have any experience with this?
> > > > > > Thanks,
> > > > > > Neil
> > > > > >

cant upsize from access to MSDE 2000 (urgentish :p)

Hi guys
Having a problem with MSDE. I'm trying to upsize an Access database to MSDE.
I'm fairly new to all this SQLServer malarkey, so bear with me.
I've setup MSDE with the default Instance name (what is the default instance
name?) and a good SAPWD. Used to the /qb+ switch to install from the command
line - if I don't, the installation just seems to die half way through.
Now then, when I try and upsize my access db, i tell it to "create a new
database" and then it gives me a new dialog with Which server, login,
password etc. I'm not entirely sure what to put in here though. What's the
LoginID? I haven't created one (as far as i know).
I've tried MSSQL and 80 (both directories in Prog Files\SQL Server), but i
keep getting the same error (18452), telling me that login failed for user.
Reason: not associated with a trusted SQL server connection.
What am I doing wrong? Have I missed something? Any help greatly appreciated!
Cheers
Dan
hi Dan,
"Dan Nash" <dan@.musoswire.co.uk> ha scritto nel messaggio
news:83F5ED5F-0F4A-43BC-AE40-1A57B907F9F1@.microsoft.com...
> Hi guys
> Having a problem with MSDE. I'm trying to upsize an Access database to
MSDE.
> I'm fairly new to all this SQLServer malarkey, so bear with me.
> I've setup MSDE with the default Instance name (what is the default
instance
> name?) and a good SAPWD. Used to the /qb+ switch to install from the
command
> line - if I don't, the installation just seems to die half way through.
please add the
/L*v "c:\...\Log.txt"
commandline parameter to the setup.exe bootstrap installer, in order to log
the installation (quite 2mb textual log) for troubleshooting...
did nt understand if you succesfully installed MSDE or not.. =;-D

> Now then, when I try and upsize my access db, i tell it to "create a new
> database" and then it gives me a new dialog with Which server, login,
> password etc. I'm not entirely sure what to put in here though. What's the
> LoginID? I haven't created one (as far as i know).
> I've tried MSSQL and 80 (both directories in Prog Files\SQL Server), but i
> keep getting the same error (18452), telling me that login failed for
user.
> Reason: not associated with a trusted SQL server connection.
> What am I doing wrong? Have I missed something? Any help greatly
appreciated!
here seems you succesfully got it... MSDE is installed..
MSDE installs by default only granting WindowsNT (trusted) authentication...
you have to manually change this setting..
please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info on how to modify this behaviour both at install time and later...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hiya
Thanks for, I had a look at the article and did what it suggested with the
Registry. That's prevented it from asking me for a username and password.
However, when I do it now.. I'm getting an "overflow" error. Any ideas on
that one?
Thanks again for your help so far!
Cheers
Dan
"Andrea Montanari" wrote:

> hi Dan,
> "Dan Nash" <dan@.musoswire.co.uk> ha scritto nel messaggio
> news:83F5ED5F-0F4A-43BC-AE40-1A57B907F9F1@.microsoft.com...
> MSDE.
> instance
> command
> please add the
> /L*v "c:\...\Log.txt"
> commandline parameter to the setup.exe bootstrap installer, in order to log
> the installation (quite 2mb textual log) for troubleshooting...
> did nt understand if you succesfully installed MSDE or not.. =;-D
> user.
> appreciated!
> here seems you succesfully got it... MSDE is installed..
> MSDE installs by default only granting WindowsNT (trusted) authentication...
> you have to manually change this setting..
> please have a look at
> http://support.microsoft.com/default...b;en-us;285097 for further
> info on how to modify this behaviour both at install time and later...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Dan,
"Dan Nash" <dan@.musoswire.co.uk> ha scritto nel messaggio
news:855B1269-ED3F-4E70-B734-476F38DA4307@.microsoft.com...
> Hiya
> Thanks for, I had a look at the article and did what it suggested with the
> Registry. That's prevented it from asking me for a username and password.
> However, when I do it now.. I'm getting an "overflow" error. Any ideas on
> that one?
please have a look at
http://support.microsoft.com/default...;EN-US;Q237980
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply