Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft Help! SqlServerAgent Won't Start after Reboot

Have posted previously under the subject 'Missing What To Start SQLServer Ag
ent in EM' but haven't received any suggestions.
Problem: The SQLServerAgent can be started from DOS Prompt but doesn't start
on reboot and cannot be started from within the Enterprise manager without
error 1060: (The specified service does not exist as an installed service)
occurred while performing t
his service operation on the SQLServerAgent service.
History: Installed Office service pack and it 'messed' up the Enterprise Man
ager. After reinstalling the client tools and the sp3a service pack all wor
ked but scheduled jobs. It turns out that the SQL Server Agent is not runni
ng. It doesn't automatical
ly start up but can be started from DOS whereby my jobs in turn can execute
with no problem.
There used to be an error 8344 SuperSocket Error but after the registers we
re cleaned and errors removed the event error no longer appears. When viewi
ng the registry for SQLServerAgent there was some mention in one of the prev
ious posts about "dependa
ncies" in the registry which is not present. Suspect that the registry is
not complete and would like to know what is missing so that so that SQLServ
erAgent can properly run without manual intervention.
Please advise.
dmatta59
Have posted previously under the subject 'Missing What To Start SQLServer Ag
ent in EM' but haven't received any suggestions.
Problem: The SQLServerAgent can be started from DOS Prompt but doesn't start
on reboot and cannot be started from within the Enterprise manager without
error 1060: (The specified service does not exist as an installed service)
occurred while performing t
his service operation on the SQLServerAgent service.
History: Installed Office service pack and it 'messed' up the Enterprise Man
ager. After reinstalling the client tools and the sp3a service pack all wor
ked but scheduled jobs. It turns out that the SQL Server Agent is not runni
ng. It doesn't automatical
ly start up but can be started from DOS whereby my jobs in turn can execute
with no problem.
There used to be an error 8344 SuperSocket Error but after the registers we
re cleaned and errors removed the event error no longer appears. When viewi
ng the registry for SQLServerAgent there was some mention in one of the prev
ious posts about "dependa
ncies" in the registry which is not present. Suspect that the registry is
not complete and would like to know what is missing so that so that SQLServ
erAgent can properly run without manual intervention.
Please advise.
dmatta59dmatta59,
You could try and install the latest SQL Server service pack. That might fix
it. If not, you may wish to raise a call with MS PSS.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Service pack 3a was installed after the client tools were installed. The on
ly glitch in the installation was that it had to be installed using the sa i
d and password b/c it couldn't verify a valid domain id and password. (I be
lieve related to the SQLSer
verAgent not working somehow). Otherwise installation was successful...|||Have resolved the problem. Used scm.exe to install the SQLServerAgent servi
ce using the instructions on the following article:
http://groups.google.com/groups?q=i...tngxa06&rnum=21
**Please note that the in this case the sqlagent -c -v was working from the
dos prompt and the jobs could run properly. The only problem was that the S
QLServerAgent was not automatically starting or couldn't be started within E
M.
Thank you,
dmatta59|||Status: SQLServerAgent Service Re-Installed
Problem: Missing SQLServerAgent
Symptoms: SQLServerAgent would not automatically start and could not
be started using Enterprise Manager. However, the SQLServerAgent could
be started from a dos command prompt using sqlagent -c -v from the
directory that sqlagent.exe resided.
Used scm.exe to INSTALL the SQLServerAgent service using the
instructions on the following article:
http://groups.google.com/groups?q=i...tngxa06&rnum=21
dmatta59
Mark Allison <marka@.no.tinned.meat.mvps.org> wrote in message news:<FC8661CC-BF2D-492E-93CE
-C73B040F7D17@.microsoft.com>...
> dmatta59,
> You could try and install the latest SQL Server service pack. That might fix it. I
f not, you may wish to raise a call with MS PSS.sql

Microsoft Help! SQLServerAgent Missing Something....

Have posted previously under the subject 'Missing What To Start SQLServer Ag
ent in EM' but haven't received any suggestions.
Problem: The SQLServerAgent can be started from DOS Prompt but doesn't start
on reboot and cannot be started from within the Enterprise manager without
error 1060: (The specified service does not exist as an installed service)
occurred while performing t
his service operation on the SQLServerAgent service.
History: Installed Office service pack and it 'messed' up the Enterprise Man
ager. After reinstalling the client tools and the sp3a service pack all wor
ked but scheduled jobs. It turns out that the SQL Server Agent is not runni
ng. It doesn't automatical
ly start up but can be started from DOS whereby my jobs in turn can execute
with no problem.
There used to be an error 8344 SuperSocket Error but after the registers we
re cleaned and errors removed the event error no longer appears. When viewi
ng the registry for SQLServerAgent there was some mention in one of the prev
ious posts about "dependa
ncies" in the registry which is not present. Suspect that the registry is
not complete and would like to know what is missing so that so that SQLServ
erAgent can properly run without manual intervention.
Please advise.
dmatta59Windows Service Dependancies are stored in the "DependOnService" value under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\SQLAgent$[Instance
name]. The value should be "MSSQL$[Instance name]. There is a
"DependOnGroup" value which should be empty as there are no dependant
service groups for SQL Agent.
I'm wondering how you think installing an Office Service Pack could have
anything to do with this SQL Agent's auto startup? I could understand it
mucking up SQL Enterprise Manager, but I'd be amazed if it actually messed
with the Winndows Service Control Manager registry values for services that
have nothing to do with Office..
What did you mean when you said "but after the registers were cleaned.."?
Did you manually alter the registry? I don't mean this to be antagonistic,
but I really suggest you learn to backup the registry before making changes.
Obviously you didn't do this or you wouldn't be asking for what the values
should be. I assure you this is a valuable lesson to learn.
Did you check the Windows Event Log?
Regards,
Greg Linwood
SQL Server MVP
"dmatta59" <anonymous@.discussions.microsoft.com> wrote in message
news:3B363F0E-C782-40A2-8B2F-BB0AB4E14FFF@.microsoft.com...
> Have posted previously under the subject 'Missing What To Start SQLServer
Agent in EM' but haven't received any suggestions.
> Problem: The SQLServerAgent can be started from DOS Prompt but doesn't
start on reboot and cannot be started from within the Enterprise manager
without error 1060: (The specified service does not exist as an installed
service) occurred while performing this service operation on the
SQLServerAgent service.
> History: Installed Office service pack and it 'messed' up the Enterprise
Manager. After reinstalling the client tools and the sp3a service pack all
worked but scheduled jobs. It turns out that the SQL Server Agent is not
running. It doesn't automatically start up but can be started from DOS
whereby my jobs in turn can execute with no problem.
> There used to be an error 8344 SuperSocket Error but after the registers
were cleaned and errors removed the event error no longer appears. When
viewing the registry for SQLServerAgent there was some mention in one of the
previous posts about "dependancies" in the registry which is not present.
Suspect that the registry is not complete and would like to know what is
missing so that so that SQLServerAgent can properly run without manual
intervention.
> Please advise.
> dmatta59
>
>|||1) Regarding Office Service Pack: It didn't dawn on me that an office servi
ce pack would have impacted the SQL Server EM either. However after the se
rvice pack installation, the EM was not accessable and the 'clean up' has be
en given to me.
"Article from MSDN: OSE: Error Messages When You Install Office Server Exten
sions on Computer with SQL Server 2000
http://support.microsoft.com/defaul...kb;en-us;281460"
2) I installed the client tools followed by the service pack 3a. The EM wor
ked great but not the SQLServer agent which in turn meant that the batch job
s would not execute. The message in the event id was 8344 and some of the o
ptions for 'fixing' were ut
ilized but didn't work. Meanwhile, it was determined that the SQLServerAgen
t wasn't automatically starting after reboot or from within EM. However, th
e SQLServerAgent could be manually started from a DOS window and the batch j
obs work fine.
3) The registers were not cleaned manually but with a tool developed by Micr
osoft. A copy of the registers before the clean routine was made and is sti
ll available. An error was detected and corrected. The event id of 8344 d
oesn't happen anymore but t
hen the SQLServerAgent still doesn't start except manually from a dos windo
w....
It seems that common to an automatic startup and to a manual startup within
the EM would be a dll and/or a registry that is corrupt and/or missing, resp
ectively. Any ideas?
Thanxs,
dmatta59|||Greg,
A few more bits of information ......
The Sql database and EM was working fine on the 15th am. Then the service p
ack was installed on the 15th pm. The EM not working was noticed immediatel
y so client tools and 3a service pack were installed -- all was working exce
pt SQLServer Agent which wa
s not detected until a few days later. Viewing the history reveals that th
e 5am download worked fine on the 15th but no other downloads were attempted
after the 15th and no errors were generated. For this reason, I suspect t
hat the office service pack
affected the SQLServer agent ... Maybe it is a coincidence and something el
se 'hit' at the same time but still need to fix the problem of the SQLServer
Agent not starting automatically or from within the EM...
As the registry reference,
Windows Service Dependancies are stored in the "DependOnService" value under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\SQLAgent$[Instance
name]. The value should be "MSSQL$[Instance name]. There is a
"DependOnGroup" value which should be empty as there are no dependant
service groups for SQL Agent.
Here is what I see in the registry for my default named instance is under:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services--SQLAgent
--Enum
--(Default) REG_SZ (value not set)
--0 REG_SZ Root\LEGACY_SQLSERV
ERAGENT\0000
--Count REG_DWORD (1)
--NextInstance REG_DWORD (1)
There is nothing else.
Would appreciate your input...
dmatta59|||Status: SQLServerAgent Service Re-Installed
Problem: Missing SQLServerAgent
Symptoms: SQLServerAgent would not automatically start and could not
be started using Enterprise Manager. However, the SQLServerAgent could
be started from a dos command prompt using sqlagent -c -v from the
directory that sqlagent.exe resided.
Used scm.exe to INSTALL the SQLServerAgent service using the
instructions on the following article:
http://groups.google.com/groups?q=i...tngxa06&rnum=21
dmatta59
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:<ucDcvxkFEHA.3880@.TK2MSFTNGP10
.phx.gbl>...
> Windows Service Dependancies are stored in the "DependOnService" value und
er
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\SQLAgent$[Instanc
e
> name]. The value should be "MSSQL$[Instance name]. There is a
> "DependOnGroup" value which should be empty as there are no dependant
> service groups for SQL Agent.
> I'm wondering how you think installing an Office Service Pack could have
> anything to do with this SQL Agent's auto startup? I could understand it
> mucking up SQL Enterprise Manager, but I'd be amazed if it actually messed
> with the Winndows Service Control Manager registry values for services tha
t
> have nothing to do with Office..
> What did you mean when you said "but after the registers were cleaned.."?
> Did you manually alter the registry? I don't mean this to be antagonistic,
> but I really suggest you learn to backup the registry before making change
s.
> Obviously you didn't do this or you wouldn't be asking for what the values
> should be. I assure you this is a valuable lesson to learn.
> Did you check the Windows Event Log?
> Regards,
> Greg Linwood
> SQL Server MVP
> "dmatta59" <anonymous@.discussions.microsoft.com> wrote in message
> news:3B363F0E-C782-40A2-8B2F-BB0AB4E14FFF@.microsoft.com...
> Agent in EM' but haven't received any suggestions.
> start on reboot and cannot be started from within the Enterprise manager
> without error 1060: (The specified service does not exist as an installed
> service) occurred while performing this service operation on the
> SQLServerAgent service.
> Manager. After reinstalling the client tools and the sp3a service pack al
l
> worked but scheduled jobs. It turns out that the SQL Server Agent is not
> running. It doesn't automatically start up but can be started from DOS
> whereby my jobs in turn can execute with no problem.
> were cleaned and errors removed the event error no longer appears. When
> viewing the registry for SQLServerAgent there was some mention in one of t
he
> previous posts about "dependancies" in the registry which is not presen
t.
> Suspect that the registry is not complete and would like to know what is
> missing so that so that SQLServerAgent can properly run without manual
> intervention.

Microsoft Access 2000 missing ODBC Links

I have a Microsoft Access 2000 with several ODBC linked tables.
The back end DB is SQL Server 2000.
At the beginning everything was running OK.
Apparently randomly, Access returns the following error:
"ODBC -- connection to SQL Server failed" when I try to open a linked table that previously work.
I must delete the linked table and create the link again to let the system work again.
Please Help !
Thanks
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
That happens whenever you make a schema change to the table in SQL
Server. One way to avoid these errors is to write VBA code that runs
on startup. It deletes any old links and re-creates them when the
Access app opens, guaranteeing that the links are always valid.
--Mary
On Fri, 22 Oct 2004 02:40:27 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>I have a Microsoft Access 2000 with several ODBC linked tables.
>The back end DB is SQL Server 2000.
>At the beginning everything was running OK.
>Apparently randomly, Access returns the following error:
>"ODBC -- connection to SQL Server failed" when I try to open a linked table that previously work.
>I must delete the linked table and create the link again to let the system work again.
>Please Help !
>Thanks
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Thanks for the help, but the base SQL Server tables are always the same !
No schema update have been made to them.
PS: Sorry for my POOR english...
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||It might be a network error if you're not making any schema changes.
I'd still recommend creating a procedure to deletelink/relink tables.
This will save you from having to do it manually every time there's an
error and the connection gets dropped.
--Mary
On Fri, 22 Oct 2004 08:33:12 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for the help, but the base SQL Server tables are always the same !
>No schema update have been made to them.
>PS: Sorry for my POOR english...
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Thanks for answering me !
I'll keep on trying.
Table relinking solution is OK but it's too slow...
Thanks anyway.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||It shouldn't be slow at all if you have a valid SQL Server connection
over a LAN. It should only take a couple of seconds for the code to
execute.
--Mary
On Mon, 25 Oct 2004 01:38:45 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for answering me !
>I'll keep on trying.
>Table relinking solution is OK but it's too slow...
>Thanks anyway.
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Relinking one or two tables is OK, but I have 110 tables linked :-O !
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||I'm not talking about doing it manually, I'm talking about running VBA
code that uses DAO to delete and recreate tabledef objects. This
should run very quickly since all it's doing is creating tabledef
objects in the local .mdb and setting their connection properties. You
don't access the actual tables on the server until you open them. The
only way this code takes longer than a few seconds is if you're
linking thousands of tables, which no one in their right mind is going
to do anyway.
--Mary
On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Relinking one or two tables is OK, but I have 110 tables linked :-O !
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||You shouldn't need to delete and recreate the TableDef objects Umberto. Just
call the RefreshLink method. Mary is correct it should go very fast -
seconds not minutes. Use this code:
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
tdf.RefreshLink
End If
Next
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ivlsn0p3bhju561t3i46vu8g01j2a73d37@.4ax.com... [vbcol=seagreen]
> I'm not talking about doing it manually, I'm talking about running VBA
> code that uses DAO to delete and recreate tabledef objects. This
> should run very quickly since all it's doing is creating tabledef
> objects in the local .mdb and setting their connection properties. You
> don't access the actual tables on the server until you open them. The
> only way this code takes longer than a few seconds is if you're
> linking thousands of tables, which no one in their right mind is going
> to do anyway.
> --Mary
> On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
> wrote:
ASP.NET resources...
>
|||I have been having the same trouble lately, but I think I just fixed it.
When linking the table there is a check box to save password (I swear I
have never seen it before). I reestablished one table from each
database I am linked to and it seems to work. Previously every time I
closed and reopened Access I had to relink the tables.
Ed21
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message443464.html

Microsoft Access 2000 missing ODBC Links

I have a Microsoft Access 2000 with several ODBC linked tables.
The back end DB is SQL Server 2000.
At the beginning everything was running OK.
Apparently randomly, Access returns the following error:
"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
I must delete the linked table and create the link again to let the system w
ork again.
Please Help !
Thanks
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...That happens whenever you make a schema change to the table in SQL
Server. One way to avoid these errors is to write VBA code that runs
on startup. It deletes any old links and re-creates them when the
Access app opens, guaranteeing that the links are always valid.
--Mary
On Fri, 22 Oct 2004 02:40:27 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>I have a Microsoft Access 2000 with several ODBC linked tables.
>The back end DB is SQL Server 2000.
>At the beginning everything was running OK.
>Apparently randomly, Access returns the following error:
>"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
>I must delete the linked table and create the link again to let the system
work again.
>Please Help !
>Thanks
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for the help, but the base SQL Server tables are always the same !
No schema update have been made to them.
PS: Sorry for my POOR english...
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It might be a network error if you're not making any schema changes.
I'd still recommend creating a procedure to deletelink/relink tables.
This will save you from having to do it manually every time there's an
error and the connection gets dropped.
--Mary
On Fri, 22 Oct 2004 08:33:12 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for the help, but the base SQL Server tables are always the same !
>No schema update have been made to them.
>PS: Sorry for my POOR english...
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for answering me !
I'll keep on trying.
Table relinking solution is OK but it's too slow...
Thanks anyway.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It shouldn't be slow at all if you have a valid SQL Server connection
over a LAN. It should only take a couple of seconds for the code to
execute.
--Mary
On Mon, 25 Oct 2004 01:38:45 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for answering me !
>I'll keep on trying.
>Table relinking solution is OK but it's too slow...
>Thanks anyway.
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Relinking one or two tables is OK, but I have 110 tables linked :-O !
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||I'm not talking about doing it manually, I'm talking about running VBA
code that uses DAO to delete and recreate tabledef objects. This
should run very quickly since all it's doing is creating tabledef
objects in the local .mdb and setting their connection properties. You
don't access the actual tables on the server until you open them. The
only way this code takes longer than a few seconds is if you're
linking thousands of tables, which no one in their right mind is going
to do anyway.
--Mary
On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Relinking one or two tables is OK, but I have 110 tables linked :-O !
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||You shouldn't need to delete and recreate the TableDef objects Umberto. Just
call the RefreshLink method. Mary is correct it should go very fast -
seconds not minutes. Use this code:
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
tdf.RefreshLink
End If
Next
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ivlsn0p3bhju561t3i46vu8g01j2a73d37@.
4ax.com...
> I'm not talking about doing it manually, I'm talking about running VBA
> code that uses DAO to delete and recreate tabledef objects. This
> should run very quickly since all it's doing is creating tabledef
> objects in the local .mdb and setting their connection properties. You
> don't access the actual tables on the server until you open them. The
> only way this code takes longer than a few seconds is if you're
> linking thousands of tables, which no one in their right mind is going
> to do anyway.
> --Mary
> On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
> wrote:
>
ASP.NET resources...[vbcol=seagreen]
>|||I have been having the same trouble lately, but I think I just fixed it. Whe
n linking the table there is a check box to save password (I swear I have ne
ver seen it before). I reestablished one table from each database I am linke
d to and it seems to work. Previously every time I closed and reopened Acces
s I had to relink the tables.

Microsft Development Environment - Missing Toolbox Items

Hi,
I had some problems with the MDE application used to create reporting services reports, and rectified the problem by running the program with the command switch /resetskippkgs. Since doing this, my toolbox has lost all the controls used to design a reporting services report (e.g. Matrix, Table, Image etc).
Anyone know how I can get these back? I've tried doing a repair on the installation of the application, but no joy.
Thanks.
SimonRight-click in the toolbox, choose Add/Remove Items, and click the Reset
button.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:F7117049-4270-49A4-A9D0-08CDAB6D70A9@.microsoft.com...
> Hi,
> I had some problems with the MDE application used to create reporting
services reports, and rectified the problem by running the program with the
command switch /resetskippkgs. Since doing this, my toolbox has lost all
the controls used to design a reporting services report (e.g. Matrix, Table,
Image etc).
> Anyone know how I can get these back? I've tried doing a repair on the
installation of the application, but no joy.
> Thanks.
> Simon

MgmtStudio: Use current connection/window when opening file

Is there a setting that I am missing, or is it just not possible to do.

When I have a "QueryAnalyzer" Connection/Window open in SQL Server Management Studio, and I open a script file... it wan't to make another connection.

Is there a way that I can set it to NOT do this, and use the existing Window/Connection.

As we have to SQL Logins and thus need to type our passwords each time a new connection is made...

Also, is CTRL-N not in use anymore to open a new connection/window ?

This behavior has been changed in our next service pack (Service Pack 2). There were many Microsoft Connect items filed around this. You can check them out here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

MgmtStudio: Use current connection/window when opening file

Is there a setting that I am missing, or is it just not possible to do.

When I have a "QueryAnalyzer" Connection/Window open in SQL Server Management Studio, and I open a script file... it wan't to make another connection.

Is there a way that I can set it to NOT do this, and use the existing Window/Connection.

As we have to SQL Logins and thus need to type our passwords each time a new connection is made...

Also, is CTRL-N not in use anymore to open a new connection/window ?

This behavior has been changed in our next service pack (Service Pack 2). There were many Microsoft Connect items filed around this. You can check them out here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Message: Missing end comment mark '*/'.

Hi,

I created a web syncronization merge replication ,both servers and clients are sql server 2005 and everything is fine with the settings , but while synchronization and after i received some sch files i got the following message at the subscribers on the initial snapshot operation:

2006-06-27 07:50:14.471 The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Microsoft SQL Native Client
Number: 113
Message: Missing end comment mark '*/'.

Any idea ?

Tarek Ghazali

SQL Server MVP

My guess is that myfile_311.sch contains a stored procedure|view|function with something like

/*

...

go

*/

There is a longstanding limitation in our batch parsing logic that will erroroneously recognize 'go's embedded in comments to be batch delimiters. The only workaround for this is to edit the script generated by the snapshot agent to remove the offending comment. We really hope that we can address this issue in the near future.

-Raymond