OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....
What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.
|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?
|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....
What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.
|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?
|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

sql

OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....
What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.
|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?
|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....
What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.
|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?
|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

OK button not working...

I am trying to get MSSQL Reporting Services working. I have used it before, but now I have the opportunity to get the software entrenched in my organization. I installed the app and it opens up, but when I try to create a new folder the software will not allow the OK choice. I can cancel, but I need to choose OK.

I have installed the app, SP2, and a hotfix that is on the website. Help....
What happens when you try to click the OK button, or is it disabled?|||Thanks. The button presses, but doesn't "click". There aren't any tool tips when I mouse over or hover, and the page doesn't change.|||I have tried to reinstall the Reoporting Services app on the server, but after the install was completed, the functions are the same. I cannot use the web page GUI to do anything. I can deploy reports and data connections via Visual Studio, though. I need the web GUI to work too. HELP!|||This sounds like some sort of network issue. Can you access it while logged in on the server? Does http://localhost/reports work? Do you have any special security set in the browser? Does it work from other machines?
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am having the same "Ok button" issue. Was this ever resolved?|||I just solved this problem.

For some reason, my aspnet_client/system_web/1_1_4322 directory was missing. In that directory is WebUIValidation.js, a file required by Reporting Services for validation.

To fix it, I just copied the folder from my local IIS installation and now it works perfectly.|||hmm... it could just be the browser you are using. the first time i used reporting services (without reading the requirements specs - who wants to waste time on that kinda ***? ) i was working on mozilla and most of the functionality got disabled. the safest browser to use with reporting services is ie.
|||osubvr

Did you reinstall .net framework or copy file? Has anyone else had this issue?
|||

I have the same issue. Nothing seems to work:

the file is there

reinstalling .Net doesn't bring any good news either.

I even put the physical name in machine.conf in the root of .Net and still nothing

Please Help!

|||I reinstalled .net framework and now the buttons are working again.....weird|||

I deleted even the aspnet_client directory and then simply used aspnet_regiis -i. No error messages. the aspnet_client directory was copied back to inetpub\wwwroot\ but my SQLReportingServices still not working (web interface) ..always asking for his WebUiValidation.js. I'm exasperated.

I have an SBS Premium...I don't know if this makes any change to a normal Server 2003.

|||Miracle..but it works now. The mysteries of computer science...|||

I only have the D:\InetPub\WWWRoot\aspnet_client\system_web\2_0_50727 folder...it does not include any files...should it? I just installed this on a fresh server...

BTW, the other issue is that reports have "X" for the controls...assume its tied to the same issue...

|||

I've run aspnet_regiss -i (and -c) , I still get the same error. The complication might be that the default website points to d:/websites/wwwroot instead of c:/inetpub.

Is there any way to get it to work?

Regards Bert

odbc, linux, and mssql

I'm not really experenced with databases or odbc but I have a need to connect to a mssql 2000 server for work. I have installed openlink odbc client on my Red Hat 7.3 box. The docs are very confusing and I'm really stumped on what I need here to connect. Any help here will keep me from pulling anymore of my hair out!

I hope I am providing enough information.

I'm using the the " OpenLink Generic ODBC Driver" and when I try to connect I get the following:

Failed
SQLDriverConnect on Client failed.
DB_Connect Error
[OpenLink][ODBC]RPC: Remote system error, SQLSTATE=08004
[OpenLink][ODBC]Connection rejected by data source, SQLSTATE=08004

my odbc.inc file looks like this:

; portfolio, please check out our web site at:
;
; http://www.openlinksw.com
;
; or contact us at:
;
; general.information@.openlinksw.com
;
; If you have any technical questions, please contact our support
; staff at:
;
; technical.support@.openlinksw.com
;
[ODBC Data Sources]
OpenLink = OpenLink Generic ODBC Driver
trackit = OpenLink Generic ODBC Driver

[Default]
Driver = /usr/openlink/lib/oplodbc.so.1

[OpenLink]
Driver = /usr/openlink/lib/oplodbc.so.1
ServerType = Oracle 8.1.x
Username =
Password =
Database =
Options =
FetchBufferSize = 99
ReadOnly = no
DeferLongFetch = no
Host = localhost
Protocol = TCP/IP
Description = Sample Oracle 8.1.x Connection

[trackit]
Description = Sample DSN
Options = trackit
Port = 5000
Host = trackit.premdor.com
UserName = JR
ServerType = SQLServer 2000
Protocol = TCP/IP
Driver = /usr/openlink/lib/oplodbc.so.1
Database = trackit
ReadOnly = Yes
NoLoginBox = No
FetchBufferSize = 99Hi,

I haven't tried using this "open link ODBC driver" yet but i guess you need to use the 'SQL Server ODBC driver for Linux".

hth!|||Your odbc.inc file does not include an entry for SQL server ....

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred
Try updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||ITFred wrote:
> *
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment that this is a behavioral issue with
> SQL2K that needs to be addressed.
> - ITFred *
I've been running into the exact same issue recently with my database.
My configuration is a MS Access/VBA front end with MSSQL as the
backend, and I see similar behavior when I allow ODBC to pick the
indices to use.
One workaround I've found for this problem is to encapsulate the query
into a stored procedure, then call the stored procedure via ODBC in
pass-through mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems
to resolve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with
queries which are dynamically generated, and it also places queries in
a second location outside of your client code where you might have a
debugging error.
Its not the most elegant solution, and may not work for you at all if
you are constructing the SQL string dynamically in VB, but maybe it can
give you some inspiration on what else you can try (i.e. dynamically
call CREATE and DROP PROCEDURE statements to construct a customized
stored procedure, run it, and kill it when you're done, et. al.).
Hope this helps.
Jim
Jim Tran
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message163387.html
|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.
|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......
|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFredTry updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||

quote:

---

Originally posted by ITFred

So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred

---

I've been running into the exact same issue recently with my database. My c
onfiguration is a MS Access/VBA front end with MSSQL as the backend, and I s
ee similar behavior when I allow ODBC to pick the indices to use.
One workaround I've found for this problem is to encapsulate the query into
a stored procedure, then call the stored procedure via ODBC in pass-through
mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems to res
olve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with queries w
hich are dynamically generated, and it also places queries in a second locat
ion outside of your client code where you might have a debugging error.
Its not the most elegant solution, and may not work for you at all if you ar
e constructing the SQL string dynamically in VB, but maybe it can give you s
ome inspiration on what else you can try (i.e. dynamically call CREATE and D
ROP PROCEDURE statements to construct a customized stored procedure, run it,
and kill it when you're done, et. al.).
Hope this helps.
Jim|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.|||With respect to the issue brought up by A. Finkler above, that's actually a
separate cause of index failure through ODBC (though still noteworthy).
In my most recent situation, my query had the following construction:
UPDATE [Table A]
SET [Table A].[Column 1] = X
FROM
[Table A] INNER JOIN
([Table B] INNER JOIN
[Table C] ON [Table B].[Column G] = [Table C].[Column H])
ON [Table A].[Column P1] = [Table B].[Column P2] AND
[Table A].[Column Q1] = [Table B].[Column Q2] AND
[Table A].[Column R1] = [Table B].[Column R2] AND
[Table A].[Column S1] = [Table C].[Column S2]
Running this query through the Query Analyzer always produced an appropriate
indexing scheme that resulted in fast performance, but launching the exact
same query string through ODBC would randomly cause a table scan.
Jim|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

ODBC login problem

This is possibly related to my restoration of the MS SQL DB from MSSQL 2000
to MSSQL 2005:
I backed up a database from MSSQL 2000 to move to a new server running MSSQL
2005.
The DB appeared to restore without a problem (although not yet confirmed);
however, my ODBC login is now not accepted?
In setting up the ODBC DSN configuration, I use my old SQL Server
Authentication login and password, which I get the following error message:
"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".
My ODBC login was what I used on my MSSQL 2000 setup and I presume the
backup and restore would copy these Authentication securities across to the
MSSQL 2005 setup?
Please let me know what I have overlooked.
Thanks> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
First, make sure the target SQL 2005 server is configured to allow both
Windows and SQL authentication. The trusted connection message indicates
that you are attempting Windows authentication rather than SQL
authentication.
Also, logins are not restored when you restore a database so you'll need to
create the SQL login on the target server if it doesn't already exists.
You'll also need to execute sp_change_users_login to remap the existing
database user to the SQL login on the target server. Example below. See
the Books Online for details.
USE MyDatabase
GO
--create a new login
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO
--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@.Action = 'Update_One',
@.UserNamePattern = 'MyLogin',
@.LoginName = 'MyLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
> This is possibly related to my restoration of the MS SQL DB from MSSQL
> 2000
> to MSSQL 2005:
> I backed up a database from MSSQL 2000 to move to a new server running
> MSSQL
> 2005.
> The DB appeared to restore without a problem (although not yet confirmed);
> however, my ODBC login is now not accepted?
> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
> My ODBC login was what I used on my MSSQL 2000 setup and I presume the
> backup and restore would copy these Authentication securities across to
> the
> MSSQL 2005 setup?
> Please let me know what I have overlooked.
> Thanks|||Is the new server setup for both Windows and SQL
Authentication? Did you add the login to the SQL Server 2005
server?
You can also check for users not mapped to a login as well
as map users and SQL logins using sp_change_users_login. To
list the users, execute the following in the database:
EXEC sp_change_users_login 'Report'
See books online for more information on
sp_change_users_login
-Sue
On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
<Calculator@.discussions.microsoft.com> wrote:

>This is possibly related to my restoration of the MS SQL DB from MSSQL 2000
>to MSSQL 2005:
>I backed up a database from MSSQL 2000 to move to a new server running MSSQ
L
>2005.
>The DB appeared to restore without a problem (although not yet confirmed);
>however, my ODBC login is now not accepted?
>In setting up the ODBC DSN configuration, I use my old SQL Server
>Authentication login and password, which I get the following error message:
>"Login failed for user 'XXXXX'. The user is not associated with a Trusted
>SQL Server connection".
>My ODBC login was what I used on my MSSQL 2000 setup and I presume the
>backup and restore would copy these Authentication securities across to the
>MSSQL 2005 setup?
>Please let me know what I have overlooked.
>Thanks|||Thanks Dan
I followed your advice and ran the queries which ran well until I got to:
--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@.Action = 'Update_One',
@.UserNamePattern = 'MyLogin',
@.LoginName = 'MyLogin'
which gave me the following error:
"Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
Terminating this procedure. The Username 'XXXXX' is absent or invalid."
The Username in sp_change_users_login is the same 'MyLogin' that I set up in
CREATE LOGIN MyLogin?
Thanks
"Dan Guzman" wrote:

> First, make sure the target SQL 2005 server is configured to allow both
> Windows and SQL authentication. The trusted connection message indicates
> that you are attempting Windows authentication rather than SQL
> authentication.
> Also, logins are not restored when you restore a database so you'll need t
o
> create the SQL login on the target server if it doesn't already exists.
> You'll also need to execute sp_change_users_login to remap the existing
> database user to the SQL login on the target server. Example below. See
> the Books Online for details.
> USE MyDatabase
> GO
> --create a new login
> CREATE LOGIN MyLogin
> WITH PASSWORD = 'asecrtpasw0rd',
> DEFAULT_DATABASE = MyDatabase
> GO
> --remap existing database user to new or exisitng login
> EXEC sp_change_users_login
> @.Action = 'Update_One',
> @.UserNamePattern = 'MyLogin',
> @.LoginName = 'MyLogin'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Calculator" <Calculator@.discussions.microsoft.com> wrote in message
> news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
>|||Thanks Sue
1) Your Question: > Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
I am using the built-in account on the local system as shown in the "SQL
Server properties" in the SQL Configuration Manager.
From there I perform the following:
under "Administrative Tools" I select the "ODBC Data Source Administrator",
where I enter data in the "Microsoft SQL Server DSN Configuation".
Here I select the second option button: "With SQL Server Authentication
using a login ID and password entered by the user."
I also tick "Connect to SQL Server ..." and enter my Login ID and my Passwor
d.
My new Login ID and Password do not work with the newly created login and
Password using:
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO
The same error results:
"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".
2) The results from your procedure: EXEC sp_change_users_login 'Report'
gave me 2 x UserName and 2 x UserSID
The UserSIDs I have never seen or used before: Should they be familar?
Thanks
"Sue Hoegemeier" wrote:

> Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
> You can also check for users not mapped to a login as well
> as map users and SQL logins using sp_change_users_login. To
> list the users, execute the following in the database:
> EXEC sp_change_users_login 'Report'
> See books online for more information on
> sp_change_users_login
> -Sue
> On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
> <Calculator@.discussions.microsoft.com> wrote:
>
>|||> which gave me the following error:
> "Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
> Terminating this procedure. The Username 'XXXXX' is absent or invalid."
I would expect this error if the 'MyLogin' user does not exist in the target
database. My understanding from your initial post is that the user already
exists so only steps needed were to add the login and remap.
If the user does not exist in the database, simply run:
CREATE USER MyLogin;
This will create new user 'MyLogin' and map it to the existing login
'MyLogin' you previously created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:C20F8BC2-18A8-4F83-9BAD-527100D5F585@.microsoft.com...[vbcol=seagreen]
> Thanks Dan
> I followed your advice and ran the queries which ran well until I got to:
> --remap existing database user to new or exisitng login
> EXEC sp_change_users_login
> @.Action = 'Update_One',
> @.UserNamePattern = 'MyLogin',
> @.LoginName = 'MyLogin'
> which gave me the following error:
> "Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
> Terminating this procedure. The Username 'XXXXX' is absent or invalid."
> The Username in sp_change_users_login is the same 'MyLogin' that I set up
> in
> CREATE LOGIN MyLogin?
> Thanks
>
> "Dan Guzman" wrote:
>|||As Sue and I mentioned earlier, make sure you SQL Server is configured to
allow both SQL Server and Windows authentication. You can do this from SQL
Server Management Studio. Right-click on the server in object explorer,
select Properties-->Security and then select 'SQL Server and Windows
Authentication mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:F1F02198-D32F-481F-9E23-4F17BA62AECC@.microsoft.com...[vbcol=seagreen]
> Thanks Sue
> 1) Your Question: > Is the new server setup for both Windows and SQL
> I am using the built-in account on the local system as shown in the "SQL
> Server properties" in the SQL Configuration Manager.
> From there I perform the following:
> under "Administrative Tools" I select the "ODBC Data Source
> Administrator",
> where I enter data in the "Microsoft SQL Server DSN Configuation".
> Here I select the second option button: "With SQL Server Authentication
> using a login ID and password entered by the user."
> I also tick "Connect to SQL Server ..." and enter my Login ID and my
> Password.
> My new Login ID and Password do not work with the newly created login and
> Password using:
> CREATE LOGIN MyLogin
> WITH PASSWORD = 'asecrtpasw0rd',
> DEFAULT_DATABASE = MyDatabase
> GO
> The same error results:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
> 2) The results from your procedure: EXEC sp_change_users_login 'Report'
> gave me 2 x UserName and 2 x UserSID
> The UserSIDs I have never seen or used before: Should they be familar?
> Thanks
> "Sue Hoegemeier" wrote:
>

ODBC in SQL Query Analyzer

Is there any way to use an ODBC data source in MS SQL Query Analyzer? I
need to join an MS SQL table with a table from a non MSSQL database.
DTS would allow me to import the alient table but I want to avoid that,
if possible.
thanks"EoRaptor013" <rchrismon@.patmedia.net> wrote in message
news:1107369347.205441.278230@.g14g2000cwa.googlegr oups.com...
> Is there any way to use an ODBC data source in MS SQL Query Analyzer? I
> need to join an MS SQL table with a table from a non MSSQL database.
> DTS would allow me to import the alient table but I want to avoid that,
> if possible.
> thanks

Check out sp_addlinkedserver and "Configuring Linked Servers" in Books
Online - you can define a link to a data source on the MSSQL server, then
query it directly with TSQL.

Simon|||Check out the OPENROWSET function if your external datasource is also
OLE DB compatible. (There is, I think, is a OLE DB provider for ODBC
so you should be able to use this).

--
David Rowland
For a good monitor for Users and Server usage, try DBMonitor
http://dbmonitor.tripod.com

ODBC from unix client to MSSQL server?

Anyone support a config where you have an unix/linux (AIX here) ODBC
client connection to SQL Server database? I am looking for a simple,
supportable configuration that does not require me to learn perl or
instal a bunch of crap gnu shareware on enterprise class machines.
Please help!
You can try the JDBC driver for SQL Server 2000 - watch out
for line wrap on the link:
http://www.microsoft.com/downloads/d...displaylang=en
You can find more information on other alternatives at:
http://www.sommarskog.se/mssql/unix.html
-Sue
On Thu, 29 Jul 2004 12:34:38 -0700, "sumGirl"
<sumGirl@.discussions.microsoft.com> wrote:

>Anyone support a config where you have an unix/linux (AIX here) ODBC
>client connection to SQL Server database? I am looking for a simple,
>supportable configuration that does not require me to learn perl or
>instal a bunch of crap gnu shareware on enterprise class machines.
>Please help!
|||Have you checked out FreeTDS at www.freetds.org.
"sumGirl" <sumGirl@.discussions.microsoft.com> wrote in message
news:8F5C9D39-C780-4A29-B305-DA598CB760EC@.microsoft.com...
> Anyone support a config where you have an unix/linux (AIX here) ODBC
> client connection to SQL Server database? I am looking for a simple,
> supportable configuration that does not require me to learn perl or
> instal a bunch of crap gnu shareware on enterprise class machines.
> Please help!
>

ODBC from unix client to MSSQL server?

Anyone support a config where you have an unix/linux (AIX here) ODBC
client connection to SQL Server database? I am looking for a simple,
supportable configuration that does not require me to learn PERL or
instal a bunch of crap gnu shareware on enterprise class machines.
Please help!You can try the JDBC driver for SQL Server 2000 - watch out
for line wrap on the link:
AA54BFDC03A&displaylang=en" target="_blank">http://www.microsoft.com/downloads/...&displaylang=en
You can find more information on other alternatives at:
http://www.sommarskog.se/mssql/unix.html
-Sue
On Thu, 29 Jul 2004 12:34:38 -0700, "sumGirl"
<sumGirl@.discussions.microsoft.com> wrote:

>Anyone support a config where you have an unix/linux (AIX here) ODBC
>client connection to SQL Server database? I am looking for a simple,
>supportable configuration that does not require me to learn PERL or
>instal a bunch of crap gnu shareware on enterprise class machines.
>Please help!|||Have you checked out FreeTDS at www.freetds.org.
"sumGirl" <sumGirl@.discussions.microsoft.com> wrote in message
news:8F5C9D39-C780-4A29-B305-DA598CB760EC@.microsoft.com...
> Anyone support a config where you have an unix/linux (AIX here) ODBC
> client connection to SQL Server database? I am looking for a simple,
> supportable configuration that does not require me to learn PERL or
> instal a bunch of crap gnu shareware on enterprise class machines.
> Please help!
>