Sql Stop Logging: method

Showing posts with label method. Show all posts

Friday, March 23, 2012

How to tell which port

I am having remote server which I cannot log through term
server or any other method so i need to depend on sql
query analyzer to do most things.
I need to determine the port that server is using ?
is there any method to do so within sql query analyzer?
Any thoughts?apok wrote:
> I am having remote server which I cannot log through term
> server or any other method so i need to depend on sql
> query analyzer to do most things.
> I need to determine the port that server is using ?
> is there any method to do so within sql query analyzer?
> Any thoughts?
If you're using TCP-IP, then the default port is 1433.
--
David Gugick
Imceda Software
www.imceda.com|||No but with mutiple instances you dont know which one is
being used so in that case how do know which port is used.
>--Original Message--
>apok wrote:
>> I am having remote server which I cannot log through
term
>> server or any other method so i need to depend on sql
>> query analyzer to do most things.
>> I need to determine the port that server is using ?
>> is there any method to do so within sql query analyzer?
>> Any thoughts?
>If you're using TCP-IP, then the default port is 1433.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Hi
Take a look at SQLPing
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John|||This is a multi-part message in MIME format.
--070705010300050106000600
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Assuming you haven't cycled your errorlog since the last time you
started SQL server you could parse the errorlog into a tmp table and
grab it from there. Like this:
create table #tmp
(
line varchar(4000),
cont int
)
go
insert into #tmp (line, cont)
exec master.dbo.sp_readerrorlog
go
select line from #tmp
where line like '%SQL Server listening on%'
But that wouldn't work if you'd cycled your errorlog. Another way you
could do it would be to read the reg key it's stored in. Like this:
exec master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketNetLib\Tcp',
N'TcpPort'
For a default instance the reg key is different. Don't remember what
exactly, but it's something like this:
exec master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL
Server\MSSQL\MSSQLServer\SuperSocketNetLib\Tcp',
N'TcpPort'
However, the xp_regread xproc is undocumented and therefore likely to
changed (and therefore not recommended). In fact Microsoft changed its
behavior slightly in SQL 2000 SP4. Now, you can only read from certain
reg keys (it's a security thing), although these particular ones are OK
(by default). Also, the reg path for the TCP port is different for SQL
2005 so I wouldn't rely on that method too long.
There's a SQLDMO property (Registry2.TcpPort) for getting the port
number a SQL server is listening on. So you could write the query from
a DOS shell in VBScript or JScript or something like that.
HTH.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
apok wrote:
>No but with mutiple instances you dont know which one is
>being used so in that case how do know which port is used.
>
>
>>--Original Message--
>>apok wrote:
>>
>>I am having remote server which I cannot log through
>>
>term
>
>>server or any other method so i need to depend on sql
>>query analyzer to do most things.
>>I need to determine the port that server is using ?
>>is there any method to do so within sql query analyzer?
>>Any thoughts?
>>
>>If you're using TCP-IP, then the default port is 1433.
>>--
>>David Gugick
>>Imceda Software
>>www.imceda.com
>>.
>>
--070705010300050106000600
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Assuming you haven't cycled your errorlog since the last time you
started SQL server you could parse the errorlog into a tmp table and
grab it from there. Like this: 
 
create table #tmp 
( 
line varchar(4000), 
cont int 
) 
go 
 
insert into #tmp (line, cont) 
exec master.dbo.sp_readerrorlog 
go 
 
select line from #tmp 
where line like '%SQL Server listening on%' 
 
But that wouldn't work if you'd cycled your errorlog. Another way you
could do it would be to read the reg key it's stored in. Like this: 
 
exec master.dbo.xp_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketNetLib\Tcp', 
N'TcpPort' 
 
For a default instance the reg key is different. Don't remember what
exactly, but it's something like this: 
 
exec master.dbo.xp_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\Microsoft SQL
Server\MSSQL\MSSQLServer\SuperSocketNetLib\Tcp', 
N'TcpPort' 
 
However, the xp_regread xproc is undocumented and therefore likely to
changed (and therefore not recommended). In fact Microsoft changed its
behavior slightly in SQL 2000 SP4. Now, you can only read from certain
reg keys (it's a security thing), although these particular ones are OK
(by default). Also, the reg path for the TCP port is different for SQL
2005 so I wouldn't rely on that method too long. 
 
There's a SQLDMO property (Registry2.TcpPort) for getting the port
number a SQL server is listening on. So you could write the query from
a DOS shell in VBScript or JScript or something like that. 
 
HTH. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
apok wrote:
<blockquote cite="mid14e901c5196f$2e60d790$a501280a@.phx.gbl" type="cite">
<pre wrap="">No but with mutiple instances you dont know which one is
being used so in that case how do know which port is used.
</pre>
<blockquote type="cite">
<pre wrap="">--Original Message--
apok wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am having remote server which I cannot log through
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->term
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">server or any other method so i need to depend on sql
query analyzer to do most things.
I need to determine the port that server is using ?
is there any method to do so within sql query analyzer?
Any thoughts?
</pre>
</blockquote>
<pre wrap="">If you're using TCP-IP, then the default port is 1433.
--
David Gugick
Imceda Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
.
</pre>
</blockquote>
</blockquote>
</body>
</html>
--070705010300050106000600--

How to tell which port

Friday, February 24, 2012

How to store and use xqueries in sql2005?

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.

How to store and use xqueries in sql2005?

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:

but this doesn't:

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

|||

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,

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.

Sunday, February 19, 2012

How to stop a SSIS package execution from code?

Hi everyone,

After a Execute method I would need to stop a package but I don't know why:

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

I have a Events class (EventSSIS) which implements IDTSEvents and have an OnQueryCancel event but without parameters, such so:

Function OnQueryCancel() AsBooleanImplements IDTSEvents.OnQueryCancel

ReturnFalse

EndFunction

Let me know how to pass a boolean parameter because of I can't overloaded OnQueryCancel method

TIA-

I'm sorry, issue is solved.

happy coding,

Friday, March 23, 2012

How to tell which port

How to tell which port

How to tell which port

Friday, February 24, 2012

How to store and use xqueries in sql2005?

How to store and use xqueries in sql2005?

Sunday, February 19, 2012

How to stop a SSIS package execution from code?

Sql Stop Logging

Blog Archive

About Me