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
>

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Repost: Exporting Information

Hello NG
We have a production database that we have moved the tables to SQLServer and
I have been creating stored procedures to replicate some of the processes
done on the access side - the server does processing extremely faster than
access - Here is my problem I am currently pulling info from SQL SERVER to
Access once a w for wly stats I want to create a stored procedure to
process the wly stats and push them to an access table upon request - we
have multiple warehouses and I would like to have the process coded once in
sql and dump to the access table based off a warehouse variable passed to
the stored procedure - Can SQLServer from a stored procedure export a
recordset into a varible defined Access Table?
TIAFAH
RandyHi Randy,
Yes this is possible...You can do it by using a linked server. You can
find info about linked servers in SQL Server Books Online. I've given
an outline of the general steps you need to do below.
Hope this helps.
=================
1) Open Query Analyzer and log in using an account with SysAdmin
privileges (e.g. sa). Make sure you are using the "master" database.
2) Type in the following:
exec sp_addlinkedserver '<Linked Server Name>', '<MS Access Version>',
'Microsoft.Jet.OLEDB.4.0', '<Full Path To Access DB>'
<Linked Server Name> can be whatever you want it to be, you will use
this to reference the Access DB.
<MS Access Version> is something like 'Access 97' or 'Access 2003' etc
<Full Path To Access DB> is, well, yeah...
3) Execute what you entered in (2) and then clear the Query Analyzer
window of the command
4) Type in the folliwng into Query Analyzer:
sp_addlinkedsrvlogin '<Linked Server Name>', false, '<SQL Server Login
Name>', 'Admin', NULL
<SQL Server Login Name> is the SQL Server login that will access the
Access DB. Make it something like "sa" if you will be executing the
stored proc under your account.
5) Execute the command entered in (4)
OK, after doing that you can now access you MS Access DB from SQL
Server. To reference a table in Access you have to write:
<Linked Server Name>...<Table Name>
You can do Inserts and other things to this table. In your case you
would probably be wanting to Insert values into the table.
Hope that helps.