LIMIT problems: hii

Showing posts with label hii. Show all posts

Friday, March 30, 2012

Link two internal sql-servers

Hi!
I have a network with two sql-servers.
One which we can call LOCAL1 is a sql-server with a non public ip-adress.
Another one which we can call PUBLIC1 which is a sql-server with a
public ip-adress.
I have setup one account on each server for connection to the pubs-database.
I add linked server with sp_addlinkedserver on booth servers and also
adds logins with sp_addlinkedsrvlogin.
Evrything seems fine.
When I login with my account that I just setup on the local server with
QA and the query like this
select * from PUBLIC1.pubs.dbo.authors
everything is fine and it works perfect.
But when I login with my account on the public server with QA and query
like this
select * from LOCAL1.pubs.dbo.authors
I get this response after 21 seconds:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I am using booth named pipes and TCP/IP on booth the client and the server.
I have tried to add the local server to the public servers hosts file.
But it didnt help.
Any suggestions?
Thanks!
Start here:
Potential causes of the "SQL Server Does Not Exist or Access Denied" error
message
http://support.microsoft.com/default...b;en-us;328306
Firewall in between maybe?
HTH
Dylan
"dotNet" <dotnet@.brimba.nu> wrote in message
news:eqmmDXbXEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi!
> I have a network with two sql-servers.
> One which we can call LOCAL1 is a sql-server with a non public ip-adress.
> Another one which we can call PUBLIC1 which is a sql-server with a
> public ip-adress.
> I have setup one account on each server for connection to the
pubs-database.
> I add linked server with sp_addlinkedserver on booth servers and also
> adds logins with sp_addlinkedsrvlogin.
> Evrything seems fine.
> When I login with my account that I just setup on the local server with
> QA and the query like this
> select * from PUBLIC1.pubs.dbo.authors
> everything is fine and it works perfect.
> But when I login with my account on the public server with QA and query
> like this
> select * from LOCAL1.pubs.dbo.authors
> I get this response after 21 seconds:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I am using booth named pipes and TCP/IP on booth the client and the
server.
> I have tried to add the local server to the public servers hosts file.
> But it didnt help.
> Any suggestions?
> Thanks!
|||Start here:
Potential causes of the "SQL Server Does Not Exist or Access Denied" error
message
http://support.microsoft.com/default...b;en-us;328306
Firewall in between maybe?
HTH
Dylan
"dotNet" <dotnet@.brimba.nu> wrote in message
news:eqmmDXbXEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi!
> I have a network with two sql-servers.
> One which we can call LOCAL1 is a sql-server with a non public ip-adress.
> Another one which we can call PUBLIC1 which is a sql-server with a
> public ip-adress.
> I have setup one account on each server for connection to the
pubs-database.
> I add linked server with sp_addlinkedserver on booth servers and also
> adds logins with sp_addlinkedsrvlogin.
> Evrything seems fine.
> When I login with my account that I just setup on the local server with
> QA and the query like this
> select * from PUBLIC1.pubs.dbo.authors
> everything is fine and it works perfect.
> But when I login with my account on the public server with QA and query
> like this
> select * from LOCAL1.pubs.dbo.authors
> I get this response after 21 seconds:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I am using booth named pipes and TCP/IP on booth the client and the
server.
> I have tried to add the local server to the public servers hosts file.
> But it didnt help.
> Any suggestions?
> Thanks!
|||Hi!
I have looked there.
I found that I cannot even telnet from the local1 to localhost
telnet localhost 1433
this works perfect on the public server...
I can ping localhost and the local ip as well.
But I can connect with QA to local1 and run queries...
What can this be?
I dont have any firewall on the local1-server.
Thanks
Dylan Kruger[MS] wrote:

> Start here:
> Potential causes of the "SQL Server Does Not Exist or Access Denied" error
> message
> http://support.microsoft.com/default...b;en-us;328306
> Firewall in between maybe?
> HTH
> Dylan
> "dotNet" <dotnet@.brimba.nu> wrote in message
> news:eqmmDXbXEHA.4032@.TK2MSFTNGP11.phx.gbl...
>
> pubs-database.
>
> server.
>
>
|||Hi!
I have looked there.
I found that I cannot even telnet from the local1 to localhost
telnet localhost 1433
this works perfect on the public server...
I can ping localhost and the local ip as well.
But I can connect with QA to local1 and run queries...
What can this be?
I dont have any firewall on the local1-server.
Thanks
Dylan Kruger[MS] wrote:

> Start here:
> Potential causes of the "SQL Server Does Not Exist or Access Denied" error
> message
> http://support.microsoft.com/default...b;en-us;328306
> Firewall in between maybe?
> HTH
> Dylan
> "dotNet" <dotnet@.brimba.nu> wrote in message
> news:eqmmDXbXEHA.4032@.TK2MSFTNGP11.phx.gbl...
>
> pubs-database.
>
> server.
>
>

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default...b;en-us;280106
http://support.microsoft.com/default...b;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error mess
age
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

Monday, March 12, 2012

limiting the number of child rows

I have got two related tables (tblMembers and tblTickets) that have a 1 to 2 relationship i.e a member can have 1 or 2 tickets, but not more.

How do I enforce this condition in SQL?.

Please help

Hello,

There are several ways to implement this. I assume you have a table similar to tblMemberTicket in which you have a 1-n from tblMemebers and a 1-n from tblTickets...

1. Triggers

2. Check constraints

3. Application login in stored procedures.

If you need enforcement at the schema level, then only options 1 or 2 are applicable.

Cheers,

Rob

|||

Thanks Rob, I've solved it with a check constraint.

(Isn't that triggers are executed after the INSERT?..... i'm a newbie)

|||

Hi GM,

An INSTEAD OF INSERT trigger for example, fires when any action attempts an insert - not "after" the data has been inserted (so to speak).

Cheers,

Rob

|||

How did you do this with a check constraint? The only way I can think is to it in a check constraint is to have a domain of two value:

columnName tinyint check (column between 1 and 2)

would do it, but it would seem clumsy to work with.

You couldn't really do it in a check constraint that uses a user-defined function easily since the value isn't in the table yet, you would have to see if two already exist.

Best to use an AFTER trigger for that. Basically you check once the data in in the table (inside your transaction of course) to see if an invalid situation exists. Then you rollback as needed:

create table cardinalityTest
(
cardinalityTest int identity(1,1),
groupColumn char(10),
value int
) --I would expect a natural key to be available in a real table
go

create trigger cardinalityTest$insertTrigger
on cardinalityTest
after insert, update
as
begin
begin try
if exists (select groupColumn
from cardinalityTest
--only check groupColumn values that have changed
where exists ( select *
from inserted
where inserted.groupColumn = cardinalityTest.groupColumn)
group by groupColumn
having count(*) > 2 --two is allowed
)
raiserror ('Greater than 2 values for a groupColumn value',16,1)
end try
begin catch
if @.@.trancount > 1
rollback transaction

--you can do something more interesting
declare @.message varchar(4000)
set @.message = error_message()
raiserror (@.message, 16, 1)

end catch
end
go
insert into cardinalityTest(groupColumn, value)
select 'first',1

insert into cardinalityTest(groupColumn, value)
select 'first',1

insert into cardinalityTest(groupColumn, value)
select 'first',2

Msg 50000, Level 16, State 1, Procedure cardinalityTest$insertTrigger, Line 26
Greater than 2 values for a groupColumn value
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.

insert into cardinalityTest(groupColumn, value)
select 'second',1
union all
select 'second',1

insert into cardinalityTest(groupColumn, value)
select 'nope',1
union all
select 'nope',1
union all
select 'nope',1

insert into cardinalityTest(groupColumn, value)
select 'third',1
union all
select 'third',1
union all
select 'fourth',1
union all
select 'fourth',1

insert into cardinalityTest(groupColumn, value)
select 'fifth',1
union all
select 'fifth',1
union all
select 'fifth',1
union all
select 'sixth',1

|||

CHECK constraints are evaluated at the end of the DML action so you could use a UDF like:

create function CT2 ()
returns bit
as
begin
return(select top 1 1 from CardinalityTest group by groupColumn having count(*) > 2)
end

-- Table-level CHECK constraint

alter table CardinalityTest
add check ( dbo.CT2() is null )

|||

Wow. I sure had that wrong. I was thinking that checks happened before the data was in the table.

Thanks for the info.

|||

Does this apply to SQL Server 2000 too ?

Because right now, I'm experiencing quite different behavior....

I have temporal table where each row hase ActiveFrom, ActiveTo column.

There is "primary key", but there can be more than one row with same primary key value as long as rows has different, not overlaping time interval specified by ActiveFrom and ActiveTo columns...

There is no way to create real primary key here so i'm trying to create table level constraint using UDF which checks this.....

My function:

create function TestFinancialIndicatorBusinessKey()

returns int

begin

declare @.retVal int

SELECT @.retVal = ISNULL(COUNT(*), 0)

FROM FinancialIndicator AS A

INNER JOIN FinancialIndicator AS B

ON (A.Id != B.Id) AND (A.IdFSFormat = B.IdFSFormat) AND (A.IdFIType = B.IdFIType) AND (A.MainSegment = B.MainSegment)

WHERE dbo.IsIntervalIntersectionNonEmpty(A.ActiveFrom, A.ActiveTo, B.ActiveFrom, B.ActiveTo) > 0

PRINT ISNULL(@.retVal, 0)

return @.retVal

end

(Id is syntetic primary key - identity, (IdFSFormat,IdFIType, MainSegment) is "primary key"

I've created constraint like this:

ALTER TABLE dbo.FinancialIndicator

WITH CHECK ADD CONSTRAINT CHK_BUSINESS_KEY

CHECK (dbo.TestFinancialIndicatorBusinessKey() = 0);

Everything seems fine, but doesn't work ! Firts update to table, which break this rule is executed without problem. Next update which should repair the incorect row fails....

It seems like this table level constraint is executed only before my update....

Any idea

Thanx, Michal