Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to connect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald Wheeler
What error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I get the standard server not found or access denied error - both via ASP calls and the SQL Server Query analyzer. I don't think that it's an authentication problem because I've tried using both valid and invalid passwords, as well as trying to the connec
t to the Northwind database, and received the same error
Donald
|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 database on a machine that already has a set of active IIS directories for webmail. I think the firewall is ok but the connections are getting 'swallowed' by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
sql

Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to c
onnect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald WheelerWhat error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I get the standard server not found or access denied error - both via ASP ca
lls and the SQL Server Query analyzer. I don't think that it's an authentica
tion problem because I've tried using both valid and invalid passwords, as w
ell as trying to the connec
t to the Northwind database, and received the same error
Donald|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 dat
abase on a machine that already has a set of active IIS directories for webm
ail. I think the firewall is ok but the connections are getting 'swallowed'
by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler

Problems Accessing SQL after going from SQL 2000 to Mgmt Studio SQL 2005

I installed Mgmt Studio and SQL 2005 on a new server - I imported my complete old SQL 2000 databases - then shut down my Original SQL 2000 server and used that Idenitical IP as the Mgmt Studio SQL 2005's IP

I am using Linux Jakarta Tomcat v5.0.27 and all SQL references are referring only to the IP number of ther server and not using DNS -

Originally I was getting an error when my system tried to access the SQL stating that it was an untrusted connection - I then realized that the IP number referred to was not the primary IP and I changed that so the it was.

Both the Linux and SQL 2005 are now using network IPs and then I got a message that the password that I was using was not strong enough so I changed that as well - then all my logging dissappeared

When I try to connect now there is no indication in any log (SQL nor Windows) that I am attempting to connect - the website just hangs for like 3 minutes and fails the login stating that the username/password is incorrect (or it could not verify it) even though I changed nothing on the Tomcat side and it was logging before the IP werent matching.

Is there a significance difference between SQL 2000 and SQL 2005 that will not allow my Tomcat to connect?

Few Addl Notes
I can ping to and from each server
I can telnet from Linux to SQL 2005
Linux of course is not part of my Windows 2003 Domain but it is on the same network

Any and all help would be greatly appreciated

Did you already enable remote connections on the SQL Server 2005 ? See more information in my site in the Screencast section.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes - I have SQL 2005 Standard Edition which listens over TCPIP but I changed it to Use both TCPIP and pipes just in case - no luck - I also have already made sure that both Authentications are allowed (Windows and SQL)

Here is an error I am getting
Date 11/14/2006 11:58:00 AM
Log Windows NT (Application)

Source MSSQLSERVER
Category Logon
Event 3221243928
Computer SLATE2

Message
Login failed for user 'swamy'. [CLIENT: 207.145.184.148]

Now I can get ODBC connections with this response from an XP Client
Date 11/14/2006 12:47:14 PM
Log SQL Server (Current - 11/14/2006 12:47:00 PM)

Source Logon

Message
Login succeeded for user 'BESTREG\Swamy'. Connection: trusted. [CLIENT: 207.145.184.93]

|||Could you please post your connectstring here ?

-Jens.

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmaz
themarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Problems accessing data from recordset (SQL Server 2000)

I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
Lee
CrazyAtlantaGuy wrote:

> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096

> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee

Problems accessing data from recordset (SQL Server 2000)

I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee

Problems accessing data from recordset (SQL Server 2000)

I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:

> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096

> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee

Problems accessing CreateSubscription via API

Hi,

I'm trying to create a subscription via API and am getting an error. BTW, creating it through ReportManager works great, meaning the Report, the DataSource and its credientials are all working as planned.

I'm following the sample for CreateSubscription() on the books online, http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

I'm hosting the script on a web page and when running, am getting an error:

"One of the extension parameters is not valid for the following reason: The account you are using does not have administrator privileges. A subscription cannot be created fordank@.adventure-works.com"

Well, obvisouly it's not working. Moreover, I don't understand the error, what does the 'TO' email to do with it?

Please help,

Edmund

Oops, I fogot to mention that this occurs only when authoenticated under ASP.NET 2.0's membership forms authentication.

Edmund