How to Change the Host Computer Name on W2K3R2 SQL 2K5 Server?

I am having some application issues installing an Ultipro Server on W2K3R2 w/
SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
I saw a post about this and thought there should be a good way to check and
fix?
Hi
If SELECT @.@.SERVERNAME returns the old server name see:
http://support.microsoft.com/kb/303774/en-us
John
"Geoff Hughes" wrote:

> I am having some application issues installing an Ultipro Server on W2K3R2 w/
> SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
> I saw a post about this and thought there should be a good way to check and
> fix?

How to Change the Host Computer Name on W2K3R2 SQL 2K5 Server?

I am having some application issues installing an Ultipro Server on W2K3R2 w/
SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
I saw a post about this and thought there should be a good way to check and
fix?Hi
If SELECT @.@.SERVERNAME returns the old server name see:
http://support.microsoft.com/kb/303774/en-us
John
"Geoff Hughes" wrote:
> I am having some application issues installing an Ultipro Server on W2K3R2 w/
> SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
> I saw a post about this and thought there should be a good way to check and
> fix?

How to change Query Timeout?

Greetings

I have encountered the following problem:

I currently develop an application for my company that actually uses rather long queries, with many records.

I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):

The first one is a test database that we use when in developing time quota to test our data

The second one is the real thing a data base that contains lots of records.

When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...

I tried to change the Query time out time from inside the SQL Server from

Tools/Options/Advanced

but it doesn't seem to work out... it still times out after 30 secs

Any Ideas?

Thanx in advance :DCould it be that this is not a SQL problem but an ADO-ADO.NET problem, i'm sure the default commandtimeout for command objects in ADO-ADO.NET is 30 seconds if not specified, specify 0 for unlimited timeouts and a value in seconds if you want different from the default.

I'd go for a 45 seconds property on the commandtimeout property...

regards,
J.

P.S. If i'm off base here I appologise, this is my first post, total newb...|||Thanks a lot for the advise Dreamweaver, but I need to specify that I tested the queries from inside the SQL Server enterprise Manager creating two new Views (One in each separate Data Base) and pasting in their SQL section the SQL string that I "Pinched off/Copied " from the debugger of the .NET just before it is fed to the adapter...

So I have side by side two new viewes with the exactly same SQL, but belonging to two different data bases...

So the problem arises through the SQL server...

Any more Ideas guys?...

Thanks!. :D|||I've just been looking at the settings on a server I am looking after. Surprisingly the timeout properties in Tools/Options/Advanced differ from right clicking the server and selecting properties and then looking at the bottom part of the connection tab, on this server it has;

0 in Tools/Options/Advanced

and

600 in Server Properties/Connection Tab

I'd have a look in right click Server/Properties/Connection Tab and see if that says 30,

regards,
J.|||first set sp_configure to display advanced options
execute reconfigure with override to force a "LIVE" change
then set remote query timeout to an appropriate value.
run reconfigure with override again.

USE master
EXEC sp_configure 'show advanced option', '1'
/*
Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.
*/

RECONFIGURE with override
EXEC sp_configure

--XXXX change to query timeout value (example '800')
sp_configure 'remote Query Timeout', 'XXXX'

:eek: I was so tired at the time i wrote this that i lost myself in coloring the code in the message posting areasql

How to Change Graph Type in a .rpt File

Hi all,

I'm working with Crystal report and a java Swing application. The interaction between the two is made up by a simple wrapper that starts the .rpt file when a button is pressed, passing just a few parameters.

i'd like to know if it's possible (and how :D ) to pass the type of chart to be displayed to the .rpt file as a parameter, letting the java application user to choose the chart type inside the Swing environment.

thanks for the help

EnricoI think you need to create as many reports as the number of charts and show corresponding reports as what the user chooses

How to change from SQL Server Express to Database.mdf connection

Hi,

I've created a SQL Server Express Database that is used by an ASP.NET 2.0 application (developed using VWD Express).

I would like to be able to:

Make changes to stored procedures, views and table structures while developing/testing ASP.NET application - which, ideally, needs a SQL Server Manager Studio Express connection and Upload my application and data to a database on a network - which, ideally, needs a direct Database.mdf connection.

I know that I need to change my connection string - and am able to achieve that with ease - but I'm not sure what the best way of switching between the two ways of attaching on the database side. I keep getting logon type problems etc.

Thanks very much.

Regards

Gary

Hi,

that should be normally straight forward using on of the user instances string for "attaching" the database to a instance temporarly (www.connectionstrings.com) Which error do you get while attaching ?

HTH, JEns Suessmeyer.

http://www.sqlserver2005.de

How to change Default port in MS SQL server (2005) and Use it with Pro* C

Hi,
My application is using Pro* c to connect MS SQL SERVER. Now i want
to change default port for SQL SERVER. How can i do that? and does this
make any impact on my Pro*c application connection?(there's no
parameter for PORT in Pro*c connect statement). How can i configure for
this? Please reply.I'm Using MS SQL SERVER 2005. Please specify whether i need any
Registry editing for that.
Thanks in Advance
Umesh|||These may help.
http://support.microsoft.com/kb/823938/
and in BOL;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2276a5ed-ae3f-4855-96d8-f5bf
01890640.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<umeshcg@.gmail.com> wrote:
| Hi,
|
| My application is using Pro* c to connect MS SQL SERVER. Now i want
| to change default port for SQL SERVER. How can i do that? and does this
| make any impact on my Pro*c application connection?(there's no
| parameter for PORT in Pro*c connect statement). How can i configure for
| this? Please reply.
||||Dear Dave,
Thanks for ur reply. But i'm facing a peculiar problem when i
change tcp port of SQL server 2005.
I have changed the default port of 1433 to 1844 through "SQL SERVER
CONFIGURATION MANAGER" and it is visible in registry too As,
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL
Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\IPAl
l]
Display Name = Any IP Address
TcpDynamicPorts=[Blank]
TcpPort=1844
Then i restarted the service. when i tried to netstat it does
not show 1844 port. Then i checked errorLOG(both Manually and through
Query) it says:
"Server is listening on [ 127.0.0.1 <ipv4> 1434]"
and its true too. i can see it when i connect with telnet
localhost 1434. How can ii solve this? one more thing.. how can i
enable SQL SERVER 2005 for remote connection? Default is disabled.
Please Reply.
Thanks in Advance,
Umesh.C.G
Dave Patrick wrote:
> These may help.
> http://support.microsoft.com/kb/823938/
> and in BOL;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2276a5ed-ae3f-4855-96d8-f5
bf01890640.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <umeshcg@.gmail.com> wrote:
> | Hi,
> |
> | My application is using Pro* c to connect MS SQL SERVER. Now i want
> | to change default port for SQL SERVER. How can i do that? and does this
> | make any impact on my Pro*c application connection?(there's no
> | parameter for PORT in Pro*c connect statement). How can i configure for
> | this? Please reply.
> ||||It works for me. Be sure the dynamic port is blank for IP1, IP2, IPAll
Before;
Starting portqry.exe -n 127.0.0.1 -e 1433 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1433 (ms-sql-s service): LISTENING
portqry.exe -n 127.0.0.1 -e 1433 -p TCP exits with return code 0x00000000.
----
Starting portqry.exe -n 127.0.0.1 -e 1966 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1966 (unknown service): NOT LISTENING
portqry.exe -n 127.0.0.1 -e 1966 -p TCP exits with return code 0x00000001.
----
After;
Starting portqry.exe -n 127.0.0.1 -e 1966 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1966 (unknown service): LISTENING
portqry.exe -n 127.0.0.1 -e 1966 -p TCP exits with return code 0x00000000.
Also this article may help.
http://support.microsoft.com/defaul...scid=kb;[LN];914277
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"invy" wrote:
| Dear Dave,
| Thanks for ur reply. But i'm facing a peculiar problem when i
| change tcp port of SQL server 2005.
|
| I have changed the default port of 1433 to 1844 through "SQL SERVER
| CONFIGURATION MANAGER" and it is visible in registry too As,
|
| & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL
| Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\IPAl
l]
|
| Display Name = Any IP Address
| TcpDynamicPorts=[Blank]
| TcpPort=1844
|
| Then i restarted the service. when i tried to netstat it does
| not show 1844 port. Then i checked errorLOG(both Manually and through
| Query) it says:
|
| "Server is listening on [ 127.0.0.1 <ipv4> 1434]"
|
| and its true too. i can see it when i connect with telnet
| localhost 1434. How can ii solve this? one more thing.. how can i
| enable SQL SERVER 2005 for remote connection? Default is disabled.
| Please Reply.
|
|
| Thanks in Advance,
| Umesh.C.G

How to change Default port in MS SQL server (2005) and Use it with Pro* C

Hi,
My application is using Pro* c to connect MS SQL SERVER. Now i want
to change default port for SQL SERVER. How can i do that? and does this
make any impact on my Pro*c application connection?(there's no
parameter for PORT in Pro*c connect statement). How can i configure for
this? Please reply.I'm Using MS SQL SERVER 2005. Please specify whether i need any
Registry editing for that.
Thanks in Advance
Umesh|||These may help.
http://support.microsoft.com/kb/823938/
and in BOL;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2276a5ed-ae3f-4855-96d8-f5bf01890640.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<umeshcg@.gmail.com> wrote:
| Hi,
|
| My application is using Pro* c to connect MS SQL SERVER. Now i want
| to change default port for SQL SERVER. How can i do that? and does this
| make any impact on my Pro*c application connection?(there's no
| parameter for PORT in Pro*c connect statement). How can i configure for
| this? Please reply.
||||Dear Dave,
Thanks for ur reply. But i'm facing a peculiar problem when i
change tcp port of SQL server 2005.
I have changed the default port of 1433 to 1844 through "SQL SERVER
CONFIGURATION MANAGER" and it is visible in registry too As,
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
Display Name = Any IP Address
TcpDynamicPorts=[Blank]
TcpPort=1844
Then i restarted the service. when i tried to netstat it does
not show 1844 port. Then i checked errorLOG(both Manually and through
Query) it says:
"Server is listening on [ 127.0.0.1 <ipv4> 1434]"
and its true too. i can see it when i connect with telnet
localhost 1434. How can ii solve this? one more thing.. how can i
enable SQL SERVER 2005 for remote connection? Default is disabled.
Please Reply.
Thanks in Advance,
Umesh.C.G
Dave Patrick wrote:
> These may help.
> http://support.microsoft.com/kb/823938/
> and in BOL;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2276a5ed-ae3f-4855-96d8-f5bf01890640.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <umeshcg@.gmail.com> wrote:
> | Hi,
> |
> | My application is using Pro* c to connect MS SQL SERVER. Now i want
> | to change default port for SQL SERVER. How can i do that? and does this
> | make any impact on my Pro*c application connection?(there's no
> | parameter for PORT in Pro*c connect statement). How can i configure for
> | this? Please reply.
> ||||It works for me. Be sure the dynamic port is blank for IP1, IP2, IPAll
Before;
Starting portqry.exe -n 127.0.0.1 -e 1433 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1433 (ms-sql-s service): LISTENING
portqry.exe -n 127.0.0.1 -e 1433 -p TCP exits with return code 0x00000000.
----
Starting portqry.exe -n 127.0.0.1 -e 1966 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1966 (unknown service): NOT LISTENING
portqry.exe -n 127.0.0.1 -e 1966 -p TCP exits with return code 0x00000001.
----
After;
Starting portqry.exe -n 127.0.0.1 -e 1966 -p TCP ...
Querying target system called:
127.0.0.1
Attempting to resolve IP address to a name...
IP address resolved to localhost
querying...
TCP port 1966 (unknown service): LISTENING
portqry.exe -n 127.0.0.1 -e 1966 -p TCP exits with return code 0x00000000.
Also this article may help.
http://support.microsoft.com/default.aspx?scid=kb;[LN];914277
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"invy" wrote:
| Dear Dave,
| Thanks for ur reply. But i'm facing a peculiar problem when i
| change tcp port of SQL server 2005.
|
| I have changed the default port of 1433 to 1844 through "SQL SERVER
| CONFIGURATION MANAGER" and it is visible in registry too As,
|
| [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
| Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
|
| Display Name = Any IP Address
| TcpDynamicPorts=[Blank]
| TcpPort=1844
|
| Then i restarted the service. when i tried to netstat it does
| not show 1844 port. Then i checked errorLOG(both Manually and through
| Query) it says:
|
| "Server is listening on [ 127.0.0.1 <ipv4> 1434]"
|
| and its true too. i can see it when i connect with telnet
| localhost 1434. How can ii solve this? one more thing.. how can i
| enable SQL SERVER 2005 for remote connection? Default is disabled.
| Please Reply.
|
|
| Thanks in Advance,
| Umesh.C.G

how to Catalog Deploy

i created catalog in my local folder to do full text search.now iam going to
deploy our application in the website.so can u let me know how we can
deploy the catalog from my local machine to web server.
Thanks and Regards,
K.Mohan
Mohan,
Actually, the steps are the same for deploying SQL Server based Full Text
Search (FTS) on your local server as it is on your server's website.
Although, you may want to use following T-SQL stored procedures vs. the FT
Indexing wizard:
use pubs
go
sp_fulltext_service 'clean_up'
go
sp_fulltext_database 'enable' -- --> NOTE: Only run this ONCE per database
!!!
go
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
For more details, see Full Text Indexing using T-SQL from a Profiler Trace
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!304.entry
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mohan" <mohananu@.yahoo.com> wrote in message
news:%23CW$edRuFHA.1264@.TK2MSFTNGP12.phx.gbl...
>i created catalog in my local folder to do full text search.now iam going
>to deploy our application in the website.so can u let me know how we can
>deploy the catalog from my local machine to web server.
> Thanks and Regards,
> K.Mohan
>
|||You may be able to copy a built catalog to the web server. Have a look at
this kb article.
http://support.microsoft.com/default...b;en-us;240867
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mohan" <mohananu@.yahoo.com> wrote in message
news:%23CW$edRuFHA.1264@.TK2MSFTNGP12.phx.gbl...
> i created catalog in my local folder to do full text search.now iam going
to
> deploy our application in the website.so can u let me know how we can
> deploy the catalog from my local machine to web server.
> Thanks and Regards,
> K.Mohan
>

How to capture Error Messages in script

I know how to capture the error number using @.@.ERROR, but I don't know how
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>

How to cancel report?

Hi,
I have an application with RS reports. When user runs a report, I want to
have a button Cancel Report. I can find the jobID for this report and cancel
it, but cursor on IE is still an hourglass and green circle with the text
"Report is being generated" are still in the center of the browser. Is there
any way to let RS know that it should stop to do everything for this session
because current job is already cancelled?
Maybe I even can get some feedback from RS that it is cancelled and I can
suppress my Cancel button?
Any suggestions?
Thanks.
Victor
P.S. If Microsoft guys will read this: Are we going to have in the future
Cancel button?
Thanks.Anybody... Anything...
"Victor" wrote:
> Hi,
> I have an application with RS reports. When user runs a report, I want to
> have a button Cancel Report. I can find the jobID for this report and cancel
> it, but cursor on IE is still an hourglass and green circle with the text
> "Report is being generated" are still in the center of the browser. Is there
> any way to let RS know that it should stop to do everything for this session
> because current job is already cancelled?
> Maybe I even can get some feedback from RS that it is cancelled and I can
> suppress my Cancel button?
> Any suggestions?
> Thanks.
> Victor
> P.S. If Microsoft guys will read this: Are we going to have in the future
> Cancel button?
> Thanks.
>

How to call SQL server Reports in VB 6.0

Hi
Present we are using crystal reports 8.5 in VB application .
database is SQL server 2000.
How can convert existing Crystall reports into SQl Server reports and How to
call SQL server Reports in VB 6.0 as like Crystal reports.
Would appreciate if any one gives suggestion on this.
Thanks
VenuThere are two ways. One, implement SOAP in VB and use web services (not
trivial). The other is to embed an IE control in your app and use URL
integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"venu gopal" <venu gopal@.discussions.microsoft.com> wrote in message
news:910A2BBE-A706-4B5F-90B3-088B7CDDB7F8@.microsoft.com...
> Hi
> Present we are using crystal reports 8.5 in VB application .
> database is SQL server 2000.
> How can convert existing Crystall reports into SQl Server reports and How
> to
> call SQL server Reports in VB 6.0 as like Crystal reports.
> Would appreciate if any one gives suggestion on this.
> Thanks
> Venu
>

How to call SP of one SQL server from other SQL server

Hi,

In my application environment, we have data will be in available in different SQL servers.

I hava requirement to call the SP of on SQL server from the SP in the other SQL server.

Can any one help me out for a solution which will be faster?

Please note that I cant go for the linked servers as the other SQL severs credentials will be stored in a Database table and it has to be used in run time of the SPs.

Awaiting quick response from you all..

Thanks in advance.

-Thams

YOu can use the openrowset for that:

DECLARE @.USERNAME VARCHAR(50)

DECLARE @.Password VARCHAR(50)

DECLARE @.SQLString VARCHAR(500)

SET @.USERNAME = 'SomeUser'

SET @.Password = 'SomePassword'

SET @.SQLString = '

SELECT a.*

FROM OPENROWSET(''SQLNCLI'', ''Data Source=.;UserName=' + @.USERNAME + ';Pwd=' + @.Password + ';'',

''EXEC Northwind.dbo.[Ten Most Expensive Products]'') AS a;

'

SELECT @.SQLString

EXEC(@.SQLString)

Jens K. Suessmeyer

http://www.sqlserver2005.de

how to call a batch file from trigger

Hi,
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
Sunil
Hi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi,
I have a requirement to invoke a Java application and update the remote MySQ
L database, when a particular field in MSSQL Server 2000 is updated. my idea
is to write a trigger on AFTER update of field and from trigger invoke a ba
tch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger
or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote mysql instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to mysql is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100
,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a Java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run Java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run java application
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated
Thanks In advance
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to calculate bandwidth requirement for client-server applicati

I am writing a client-server desktop application.
Front end is developed in VB
Back end database is using SQL 2000
The project is being developed using Classes.
The application will be accessed by 1,000+ users using a Virtual Private
Network to connect to the SQL database.
How to I calculate the Bandwidth requirement per user per transaction for my
application?
Please advise on any tools that can be used to Find out the above
Information? Thanks.
kburito wrote:
> I am writing a client-server desktop application.
> Front end is developed in VB
> Back end database is using SQL 2000
> The project is being developed using Classes.
> The application will be accessed by 1,000+ users using a Virtual
> Private Network to connect to the SQL database.
> How to I calculate the Bandwidth requirement per user per transaction
> for my application?
> Please advise on any tools that can be used to Find out the above
> Information? Thanks.
A call to the database should be pretty small. Use stored procedures (no
server API cursors and the like), make sure result sets are as small as
possible. That means, don't include extraneous columns or unnecessary
rows in the result sets. Also, eliminate all Order By operations on
queries if the application is more than capable of sorting any needed
data itself.
Use read-only, forward-only result sets exclusively and
update/insert/delete data from other stored procedures.
Always fetch all result set data immediately and then process after
everything is fetched.
Never use Parameters.Refresh() type methods from ADO as they require a
lot of interrogation of the database and are slow. Create your
parameters programmatically.
You can use the Show Client Statistics option in Query Analyzer to see
client data for a particular call.
David Gugick
Imceda Software
www.imceda.com

How to build application

Dear Sir/ Madam,

I used to use the clients/server application in two tier, but now i thinks the two tier of application is cannot support for the user as well.

That's why we try to build the three tier system in SQL Server. In our first time, we cannot build it at all and we don't know how to do it.

Is it SQL Server support three tier system?

If SQL Server can support, How can we build it?

I hope you will reply me soon.

Ratana Ky
Cambodia.

Ratana,

Take a look at this web site and spend some time witching the following webcasts. The presenter shows how easy it become to create n-tier applications. He also gives you a link to source code.

http://www.microsoft.com/events/series/teched2005.mspx

MSDN Webcast: ASP.NET 2.0: Overview of ASP.NET 2.0 (Part 1 of 2) (Level 300)

MSDN Webcast: ASP.NET 2.0: Overview of ASP.NET 2.0 (Part 2 of 2) (Level 300)

HTH
-w|||This question is pretty generic, like how to build a car. The answer is yes. You can do a web search for terms "three tier architecture sql server" to find articles on the subject.