clarification needed on sqlxml installation

I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
In most cases you just need to install it on the IIS Server.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
|||Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>
|||It depends what you are doing, but SQLXML is primarily a client-side
technology. Install it on the IIS server and use the "Configure IIS Support"
tool to create a VDir that connects to your SQL Server to retrieve the data
you need to expose through IIS (either via a template, a schema, a SOAP
vName).
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Jrgen" <jorgen_persson78@.hotmail.com> wrote in message
news:%23pNo3H8yEHA.3376@.TK2MSFTNGP12.phx.gbl...
Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>

Checking Jobs Status through Query

I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.

Thanks in Advance

Jacx

you can use opennrowset or openquery to

query other servers

job information are stored in msdb and you can invoke the

following to query job information

use msdb
select * from sysjobs
select * from sysjobhistory

|||

tually i wanted a code to find the urrent job status of a particular job which i am inerested in. Kindly help me with that.

Thanks

Jacx

|||

use msdb
select * from sysjobs sj join --<change the * to get only the columns you need
sysjobhistory sjh
on sj.job_id=sjh.job_id
where name like 'W%' <modify this for the job name

check the run status

check this link

http://msdn2.microsoft.com/en-gb/library/ms174997.aspx

checking agents

I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your helpI have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.

thanks for your help

These can help you
http://www.databasejournal.com/features/mssql/article.php/3491201

http://www.sqlservercentral.com/articles/Administering/longrunningjobs/1897/|||Try these:

exec master..xp_servicecontrol querystate, MSSQLSERVER
exec master..xp_servicecontrol querystate, SQLServerAgent

Checking a Column Data Type

Here is the issue,

I was given the task of creating a datamart by combining the information from several different database servers. While doing this I ran into an interesting issue that I can't seem to figure out.

I have two tables, one table has a code value in it pointing to the corresponding lookup table. The lookup table for whatever reason (I didn't build the application or the database, yay legacy support! ) has two lookup columns, one is the standard incrementing numbers and the other is a series of letters. The problem lies in the fact that the code in the first table is a char(1) and can either be a letter or a number. The look up has type int and char so I run into a simple conversion problem. Here is my datamart select statement that fails because it can not convert a char to an int. My question is what would be an easy way to test which value type the char(1) is and use the appropriate left outer join.. I tried several different approaches but just can't seem to figure it out.

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON

(

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM) OR

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)

)

the above of course fails due to type conversion. status_cd is the char(1), status_nm is the char and pslu_cd is the int. I have no clue why they set it up this way, but I get to figure out how to deal with it.

Confused,

-Andrew

Try to use ISNUMERIC:

declare @.c char(1)

set @.c='5'

select isnumeric(@.c)

Result: 1

declare @.c2 char(1)

set @.c2='a'

select isnumeric(@.c2)

Result: 0

|||

yeah I thought about that but then the question is, how do you use IF ELSE Logic inside a JOIN statement

Can you even do something like this? ( I just tried it and got parse errors) So I guess the question is now how do you put conditional logic inside a JOIN ON ?

LEFT OUTER JOIN PERMIT_STATUS_LU

ON

(

IF ( isnumeric(PERMIT.endorse_status_cd) = 1)

BEGIN

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM)

END

ELSE
BEGIN

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)

END

)

|||

Use CASE instead.

Also, isnumeric, under some situations, can be unreliable. Refer to this article for details.

isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390

|||

Good idea using the CASE. I am still getting errors though, I am still unsure if its even possible to put a CASE statement inside the JOIN on clause. For example I tried

....

FROM PERMIT

CASE isnumeric(PERMIT.endorse_status_cd)

WHEN 0 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHEN 1 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

END

That didnt work so I tried

....

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU ON

(

CASE isnumeric(PERMIT.endorse_status_cd)

WHEN 0 THEN PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHEN 1 THEN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

END

)

that failed as well

Does anyone have a definative answer to the above question?

|||

I created 2 separate queries: One that contains only the rows that have a numeric value for endorse_status_cd and another for the non numeric values. I used a union to merge them into one singe result set. Here is the query:

Code Snippet

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHERE IsNumeric(PERMIT.endorse_status_cd) = 0

UNION

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

WHERE IsNumeric(PERMIT.endorse_status_cd) = 1

Based on the article that Arnie included in his reply, it might be better to create your own function (check the article) instead of IsNumeric.

I hope this answers your question.

Best regards,

Sami Samir

Check Sysobjects Error during Snapshot

During the snapshot generation, I get this eror while the snapshot agent is
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:

> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:

> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>