How to change SQL Server Browser service back to port 1434

Gurus,
Running SQL Server 2005 SP2. The SQL Server Browser service but since I am
running on dynamic ports it is not listening on 1434. In fact, looking thru
Configuration Manager I am not able to tell what port it is listening on or
how to change it back to 1434. Any ideas?
--
SpinSQL Browser always listens to 1434. It is the database engine that might be static or dynamic.
Browser is only there to translate between instance name and port number.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Spin" <Spin@.invalid.com> wrote in message news:67rgfkF2or7t6U1@.mid.individual.net...
> Gurus,
> Running SQL Server 2005 SP2. The SQL Server Browser service but since I am running on dynamic
> ports it is not listening on 1434. In fact, looking thru Configuration Manager I am not able to
> tell what port it is listening on or how to change it back to 1434. Any ideas?
> --
> Spin
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:89DD7ACA-DAB4-40D4-987F-5C6CA9B329B1@.microsoft.com...
> SQL Browser always listens to 1434. It is the database engine that might
> be static or dynamic. Browser is only there to translate between instance
> name and port number.
On my SQL Server I opened a CMP prompt a 'Netsat' command returned nothing
listening on port 1434. SQL Browser service IS running.|||"Spin" <Spin@.invalid.com> wrote in message
news:67ro9qF2q0vlvU1@.mid.individual.net...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:89DD7ACA-DAB4-40D4-987F-5C6CA9B329B1@.microsoft.com...
> On my SQL Server I opened a CMP prompt a 'Netsat' command returned nothing
> listening on port 1434. SQL Browser service IS running.
Disregard. I've done some reading and the SQL Browser service listens on a
UDP port, netstat only shows TCP ports on which connections are either
established or the server is listening. I have to figure out what UDP ports
are being listened. This may be a bit more difficult.|||"Spin" <Spin@.invalid.com> wrote in message
news:67rrf3F2q34siU1@.mid.individual.net...
> "Spin" <Spin@.invalid.com> wrote in message
> news:67ro9qF2q0vlvU1@.mid.individual.net...
> Disregard. I've done some reading and the SQL Browser service listens on
> a UDP port, netstat only shows TCP ports on which connections are either
> established or the server is listening. I have to figure out what UDP
> ports are being listened. This may be a bit more difficult.
Using TCPView.exe from Microsoft, I can clearly see now on at least one SQL
Server that the SQL Broswer service is listening on UDP 1434!
Tibor, am I correct in believing that the SQL Browser listening port CANNOT
be changed?|||> Tibor, am I correct in believing that the SQL Browser listening port CANNOT be changed?
Yes, that is the way I understand it. The whole purpose of SQL Browser is to be a fixed port. This
is so that outsiders can query it (that specific port) for the port number for an instance name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Spin" <Spin@.invalid.com> wrote in message news:67rtnuF2qnt8iU1@.mid.individual.net...
> "Spin" <Spin@.invalid.com> wrote in message news:67rrf3F2q34siU1@.mid.individual.net...
>> "Spin" <Spin@.invalid.com> wrote in message news:67ro9qF2q0vlvU1@.mid.individual.net...
>> Disregard. I've done some reading and the SQL Browser service listens on a UDP port, netstat
>> only shows TCP ports on which connections are either established or the server is listening. I
>> have to figure out what UDP ports are being listened. This may be a bit more difficult.
> Using TCPView.exe from Microsoft, I can clearly see now on at least one SQL Server that the SQL
> Broswer service is listening on UDP 1434!
> Tibor, am I correct in believing that the SQL Browser listening port CANNOT be changed?
>
>|||You can use netstat to show UDP ports that are being listened on. Run the
following:
cmd>netstat -ano
One of UDP rows in the result will have port 1434. Find the the PID, and run
tasklist to confirm that the PID is sql browser.
Linchi
"Spin" wrote:
> "Spin" <Spin@.invalid.com> wrote in message
> news:67ro9qF2q0vlvU1@.mid.individual.net...
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in message news:89DD7ACA-DAB4-40D4-987F-5C6CA9B329B1@.microsoft.com...
> > On my SQL Server I opened a CMP prompt a 'Netsat' command returned nothing
> > listening on port 1434. SQL Browser service IS running.
> Disregard. I've done some reading and the SQL Browser service listens on a
> UDP port, netstat only shows TCP ports on which connections are either
> established or the server is listening. I have to figure out what UDP ports
> are being listened. This may be a bit more difficult.
>
>sql

how to change language code of client executing the report dynamic?

Our customer want to use a different language type of his Regional option.
So the datetime type at the Parameter Bar of reporting service could
no show as stander code. (For example short date of his Regional is
2000.1.1 but he want to show as 1.1.2000 when he click the calendor
@.Parameter Bars and he refuse to change his regional options)He shouldn't have to change his regional params.
On the report just set the 'language' to '=User!Language' (this is available
in the Globals in the expression editor)
If you wish to add specific formatting on a cell or other item you can still
do this.
See the follow for this and other best practices:
scroll down to "93" to see my poin
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
"lieshengren" wrote:
> Our customer want to use a different language type of his Regional option.
> So the datetime type at the Parameter Bar of reporting service could
> no show as stander code. (For example short date of his Regional is
> 2000.1.1 but he want to show as 1.1.2000 when he click the calendor
> @.Parameter Bars and he refuse to change his regional options)
>
>
>

how to build such a report with include unkown amount of subreports?

I have spent much time but still have no idea.
I need to dynamic create the report.especially the report (A) is made up of unknown amount of one composite report(B,which include serveral subreports).
An idea is to place subreports of B directly in A.

And the subreports include some tables(not the real table in database) which columns is not fixed.|||Put the SubReport under a Group instead of under a Page header, that way you will get a subreport each time you change groups.|||Would you please explain more detail?
And the other problem is that some tables(not the real table in database) which columns is not fixed.|||I'll try to explain in more detail, but I need to know more about what you're trying to do. What version of Crystal are you using, how are you building the report (using rpt files or RDC, etc...). Give me as much details as you can about what you have already done, what's not working, and what you're trying to do.|||My crystal reports' version is 9.2.

I dont mind using RDC or RPT.

For example there are 4 database tables name (The order information of garment)OrderStyle,ColorGroup,ClothSizeCode,OrderDetails
the sub reports is
sub report A
--------------
OrderCode | StyleCode | StyleName//In OrderStyle
--------------

sub report B
-------------------
|ColorGroupCode | S | M | L | XL | XXL | ...| Summary
|-----------------
|001 |1 |5 |1 | 5 | 9 | ...|21
|002 |1 |6 |2 | 6 |1 | ...|16
|.....................................................
-------------------
|summary |2 |11|3 |11 |10 | ...|37
------------------

The S,M,L,XL,XXL are come from table ClothSizeCode and which size is selected is decided by customer. I think that to use crosstab may be a good idea.
My problem is that how to fixed these 2 subreports in one report.
In power builder it is simple but the efficiency is so poor that it may take 2 minutes to get such a report with only 6 subreport A and 6 subreport B with a few rows.|||OK, here's my guess, tell me if it's close to what you need...

Group Header #1: Customer

... SubReport1:

... ... OrderCode | StyleCode | StyleName//In OrderStyle

... SubReport2:

... ... CrossTab with ClothSizeCode as column, ColorGroupCode as row, If you choose to display Totals, it will automatically summarize it for you

Group Footer #1

Remember that each Subreport is like a whole separate report, it doesn't have to be based on the exact same criteria as your main report. Put both SubReport1 and SubReport2 in the Group Header #1 section.

How to build dynamic Xquery

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.