Resolving DNS names from extended stored procedures

I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
mailto:rgenter "at" silverlink.com
Are you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>

Resolving DNS names from extended stored procedures

I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
--
mailto:rgenter "at" silverlink.comAre you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>

Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

Resolver Issues

I'm not able to resolve conflicts using the conflict viewer because I have
timestamp columns in my tables. Can I manually resolve these conflicts by
making the necessary changes to the data?
I'm using filters on my articles and I'm noticing that replication is
changing rows in the table that don't meet my filter clause. Well, all
the data didn't change in the row just the Last_Update_Date and
Last_Update_By columns changed. I have a trigger on my tables that
updates these columns. For some reason my trigger is getting fired.
These two columns get updated so replication thinks it needs to push these
changes along to the subscriber. I end up with conflicts if the
subscriber has also changed these rows.
The first thing to figure out is what is causing teh triggers to fire.
As far as manually doing this, you can. The conflict viewer is just another
application which sits over a set of data. You can easily do this yourself.
You can freely read/write to the conflict tables if you choose since the
only things which reads from them is the conflict viewer. If you choose,
you can write your own application to replace the conflict viewer in order
to fit it into your environment and tailor it to your needs.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I figured out my trigger issue.
I appreciate your help.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:O4wRnlLKEHA.620@.tk2msftngp13.phx.gbl...
> The first thing to figure out is what is causing teh triggers to fire.
> As far as manually doing this, you can. The conflict viewer is just
another
> application which sits over a set of data. You can easily do this
yourself.
> You can freely read/write to the conflict tables if you choose since the
> only things which reads from them is the conflict viewer. If you choose,
> you can write your own application to replace the conflict viewer in order
> to fit it into your environment and tailor it to your needs.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

resolve login problem after moving db

I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
Thanks
You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks
|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:

> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>

resolve login problem after moving db

I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first
.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:

> You need to run sp_help_revlogin AFTER you add the databases. Otherwise,
it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>sql

resolve login problem after moving db

I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> > I'm moving databases from one server to another server.
> >
> > Then I use the method in microsoft article 246133, to transfer logins.
> > 1. If I use transfer logins dts task, it creates orphaned users. but
> default
> > database goes to the right one.
> > 2. If I use the script that make use of the sp: sp_help_revlogin
> > It seems work OK, no orphaned users. But I found the default database all
> > goes to
> > master, instead of other databases as in the old server.
> > Then I need to reset the default database manually one by one.
> > Is that the way the scrip works? It seems it solves one problem but have
> > another?
> >
> > Any solutions
> > Thanks
>
>

Resolve Locking

i've a SQL2000 server and SQLserver CE installed, also i've about 100 reps having a palm with them, entering a data through a developed software, then make a daily connection to make synchronization and send their data to the server.
most of thes reps make the synchronization at the same time each day, so most of them get the error message of Deadlock on a resource of process ID...
how can i resolve this issue and what is the most appropriate type of locking i can use to prevent this issue?

thanks in advanceCarefully selecting transaction isolation level is important here. But what's more obvious is the presence of reverse object reference that is the main cause of deadlocks. You need to analyze data access order and see where you introduce something like this:

User1 holds a lock on TableA and tries to access (place a lock) on TableB
User2 holds a lock on TableB and tries to access (place a lock) on TableA

If changing data access order is not feasible, you need to introduce a more tight transactional control and take full advantage of atomicity, which will take care of deadlock situations.

Resolve conflict

Hi,

l've a raw data which contains list of device name and # of wires used, i.e.
DEVICE NAME # of Wires
-
A 10
B 11
C 17
D 5
A 0
E 0

So l would like to import these data into my table (with device name as primary key) which each device only appear once. If the device name appear twices, then l should ignore device with # of wires = 0. If device name appear twice and # of wires <> 0, then l should log it.

How should l do that in SSIS ?

1. Import the raw data

2. Put a MULTICAST into the pipeline.

3. Take Output1 from the MULTICAST and feed it into an AGGREGATE. Count the number of records per device name and sum the number of wires per device.

4. Us a conditional split to get all the devices where there is more than 1 record and the number of wires is greater than 0

5. Join that back to Output2 from the MULTICAST by using a MERGE JOIN, joining on device name.

6. Push the output from the MERGE JOIN into a flat file for later examination.

-Jamie

|||

I think there is an easier way to do this as long as there is no additional columns needed.

Source --> CS1 --> Agg --> CS2 --> Dest1

--> Dest 2

CS1 removes all items with # wires == 0 (e.g. expression on the output is # wires == 0 and then just don't attach this output and just attach the default output to Agg)

Agg then aggregates by name, does max (or min) on # wires and count

CS2 splits on count != 1, one output goes to the table (count == 1), and the other (count != 1) goes to either a script task that logs the info or a flat file that outputs the info.

This avoids MergeJoin and, therefore, a sort too.

HTH,

Matt