activeX step never finishes.

I have an activex script which executes a query that never finishes. If I recompile the query in Management Studio before I run this step, then the step will *usually* work fine. This is the last step in a very long update process so the data has changed a great deal when this step executes.

The query always works fine in Management studio btw.

I am using BI studio to run the package.

I have tried placing a step before this that marks the query for recompilation but that doesnt seem to work. Any ideas how to resolve this?

Why ActiveX?|||it loops through the result set and sends out an email for each record. The package was originally a dts 2000 package so I just kept it as is. If theres a better way to do this in SSIS I would love to know.|||Use an Execute SQL task to populate an object-typed variable. Hook the SQL task to a Foreach loop which "shreds" the variable. Inside that foreach loop you could use a Send Mail task.

See if this helps: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

active physical node

Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the cluadmin
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharath
I remember a tip, probably from Tom, about this. If you keep a text file at
the same location on each of the nodes, and store the name of the node in
those files, then you can connect to the server and run something like below
to get the server name:
EXEC master..xp_cmdshell 'Type C:\NodeName.txt'
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:E2F171C0-4591-48AF-AFB3-10A60801134A@.microsoft.com...
Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the
cluadmin
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharath

active physical node

Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the cluadmi
n
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharathI remember a tip, probably from Tom, about this. If you keep a text file at
the same location on each of the nodes, and store the name of the node in
those files, then you can connect to the server and run something like below
to get the server name:
EXEC master..xp_cmdshell 'Type C:\NodeName.txt'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:E2F171C0-4591-48AF-AFB3-10A60801134A@.microsoft.com...
Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the
cluadmin
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharath

active physical node

Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the cluadmin
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharathI remember a tip, probably from Tom, about this. If you keep a text file at
the same location on each of the nodes, and store the name of the node in
those files, then you can connect to the server and run something like below
to get the server name:
EXEC master..xp_cmdshell 'Type C:\NodeName.txt'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:E2F171C0-4591-48AF-AFB3-10A60801134A@.microsoft.com...
Is there any way to find out using a sql query or from registry the current
active node (physical server) of a cluster? I do not want to use the
cluadmin
gui.
if i can find out the node on which sql resources are active using a query -
it will be helpful.
cheers,
bharath

Active directory query 1000 page size limitaion

Hi.

We need to create a view of our active directory users (we have 2500).

I found out that there is max page size of 1000, so we cannot get more
data.

Anyone found a solution to that problem?

ThanksI don't really understand what your question has to do with MSSQL - are
you talking about Reporting Services, perhaps? If so, you can try
microsoft.public.sqlserver.reportingsvcs to see if you get a better
response.

Simon

Active Directory OpenQuery 1000 row limit

Hello. When doing an OPENQUERY against Active Directory,
I hit the 1000 row limit. THat's all this query below
will return. We can get this to work, by writing in
VBSCRIPT and then reading multiple sets of 1000. Would
anyone know a way to use the SQL format below to get
beyond the 1000 row limit? I know there's a RANGE
parameter in ADO, but not sure IF or HOW to code for that
in an OPENQUERY... THanks, Bruce
SELECT givenName, sn, cn, mail, samAccountName
FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
(&(objectCategory=person)(objectClass=User));
givenName,sn,cn,mail,samAccountName;subtree')Bruce,
You can do something like this:
1. Order the data returning from ADSI.
2. Find the max value returned.
3. Query again, starting after the last max value.
4. Loop until done.
I don't know of a way to get the entire result set back with TSQL.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> Hello. When doing an OPENQUERY against Active Directory,
> I hit the 1000 row limit. THat's all this query below
> will return. We can get this to work, by writing in
> VBSCRIPT and then reading multiple sets of 1000. Would
> anyone know a way to use the SQL format below to get
> beyond the 1000 row limit? I know there's a RANGE
> parameter in ADO, but not sure IF or HOW to code for that
> in an OPENQUERY... THanks, Bruce
>
> SELECT givenName, sn, cn, mail, samAccountName
> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> (&(objectCategory=person)(objectClass=User));
> givenName,sn,cn,mail,samAccountName;subtree')
>
>|||Thanks Russell. Yes, it doesn't appear that you can get
ALL rows returned in one SQL statement, and I'm fine with
the loop method via SQL. But I can't seem to get that
working either. Seems like howeer I feliter in my query,
it's filtering within the 1000 max rows... Any way you
know to get more then the 1000 rows? Is there a range
parameter in LDAP, that will do the NEXT 1000 rows,
something like that' THanks, Bruce
>--Original Message--
>Bruce,
>You can do something like this:
>1. Order the data returning from ADSI.
>2. Find the max value returned.
>3. Query again, starting after the last max value.
>4. Loop until done.
>I don't know of a way to get the entire result set back
with TSQL.
>Russell Fields
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:090301c3bdaa$70026140$a501280a@.phx.gbl...
>> Hello. When doing an OPENQUERY against Active
Directory,
>> I hit the 1000 row limit. THat's all this query below
>> will return. We can get this to work, by writing in
>> VBSCRIPT and then reading multiple sets of 1000. Would
>> anyone know a way to use the SQL format below to get
>> beyond the 1000 row limit? I know there's a RANGE
>> parameter in ADO, but not sure IF or HOW to code for
that
>> in an OPENQUERY... THanks, Bruce
>>
>> SELECT givenName, sn, cn, mail, samAccountName
>> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
>> (&(objectCategory=person)(objectClass=User));
>> givenName,sn,cn,mail,samAccountName;subtree')
>>
>
>.
>|||Bruce,
I don't see my earlier reply, so I will try again.
Actually you need to plan your queries to bring back less that 1000 rows.
(NOT as clean as getting 1000, then the next 1000, etc.) For example,
within the query you might have:
x = 'smtp:Pager1*' -- brings back everything that matches this pattern
(in SQL terms "Like 'Pager1%'")
x = 'smtp:Pager2*' etc.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:078801c3be5b$44f05b90$a001280a@.phx.gbl...
> Thanks Russell. Yes, it doesn't appear that you can get
> ALL rows returned in one SQL statement, and I'm fine with
> the loop method via SQL. But I can't seem to get that
> working either. Seems like howeer I feliter in my query,
> it's filtering within the 1000 max rows... Any way you
> know to get more then the 1000 rows? Is there a range
> parameter in LDAP, that will do the NEXT 1000 rows,
> something like that' THanks, Bruce
>
> >--Original Message--
> >Bruce,
> >
> >You can do something like this:
> >1. Order the data returning from ADSI.
> >2. Find the max value returned.
> >3. Query again, starting after the last max value.
> >4. Loop until done.
> >
> >I don't know of a way to get the entire result set back
> with TSQL.
> >
> >Russell Fields
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> >> Hello. When doing an OPENQUERY against Active
> Directory,
> >> I hit the 1000 row limit. THat's all this query below
> >> will return. We can get this to work, by writing in
> >> VBSCRIPT and then reading multiple sets of 1000. Would
> >> anyone know a way to use the SQL format below to get
> >> beyond the 1000 row limit? I know there's a RANGE
> >> parameter in ADO, but not sure IF or HOW to code for
> that
> >> in an OPENQUERY... THanks, Bruce
> >>
> >>
> >> SELECT givenName, sn, cn, mail, samAccountName
> >> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> >> (&(objectCategory=person)(objectClass=User));
> >> givenName,sn,cn,mail,samAccountName;subtree')
> >>
> >>
> >>
> >
> >
> >.
> >

Active Directory Linked Server Fails After Upgrade to SQL Server 2

We had an SQL Server 2000 instance running on Windows 2003. This was
querying a linked AD server running on Window 2000.
The query we run is as follows:
SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
sAMAccountName, givenName, telephoneNumber,
facsimileTelephoneNumber,
sn, mail,department, physicalDeliveryOfficeName FROM
''LDAP://DC=mycompany,DC=com''
where sn<''k''
and objectClass = ''User''')
The SQL Server instance was upgraded to SQL Server 2005. No other changes
were made.
We now get an error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "python" reported an
error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "ADSDSOObject" for
linked server "python".
Any suggestions?
--
Jonathan PalmerJonathan Palmer wrote:
> We had an SQL Server 2000 instance running on Windows 2003. This was
> querying a linked AD server running on Window 2000.
> The query we run is as follows:
> SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
> sAMAccountName, givenName, telephoneNumber,
> facsimileTelephoneNumber,
> sn, mail,department, physicalDeliveryOfficeName FROM
> ''LDAP://DC=mycompany,DC=com''
> where sn<''k''
> and objectClass = ''User''')
> The SQL Server instance was upgraded to SQL Server 2005. No other changes
> were made.
> We now get an error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "ADSDSOObject" for linked server "python" reported an
> error. The provider did not give any information about the error.
> Msg 7350, Level 16, State 2, Line 1
> Cannot get the column information from OLE DB provider "ADSDSOObject" for
> linked server "python".
> Any suggestions?
>
Hi
I've just tried you statement on a SQL2005 server, and it works fine for
me. The only thing I've changed, is the part where you define your LDAP
server. Instead of ...''LDAP:/DC=nycompany, DC=com''... i've changed
it to ...''LDAP://DC-1''... where DC-1 is the name of my domain
controller. I don't know if that will solve your problem because I don't
think this should be any different from SQL2000 to SQL2005. Only
specifying the domain controller name has worked for me in SQL2000 as
well... actually I've never got it working with anything else...:-).
--
Regards
Steen Schlüter Persson
DBA|||The error message is the same if I use the FQDN for the server, the server
name alone or DC=.. etc approach.
If I type in the wrong FQDN, I do not get the error message about "cannot
get the column information"|||Jonathan Palmer wrote:
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"
That's a bit strange. Have you tried to remove the linked server and
recreate it?
What happens if you use OPENROWSET like in my example below -Does that
make any difference?
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
Servername Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://YourDomainCOntrollerName''
WHERE objectClass = ''organizationalPerson''
'
)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||try recreate the linked server and rerun sp_addlinkedsrvlogin. SIDs might be
wrong after upgrade.
--
-oj
"Jonathan Palmer" <nospam@.nospamnospam.nospamnospam> wrote in message
news:92564888-4415-4C84-8475-5DA44BFE655C@.microsoft.com...
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"|||If I use OpenRowSet, I do get results, e.g.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,
''
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://MYSERVER''
WHERE objectClass = ''organizationalPerson''
')
I tried recreating the linked server and running the query:
SELECT * FROM OpenQuery(ADSI, 'SELECT mail
,SamAccountName FROM
''LDAP://myserver'
where
objectClass = ''User''')
This query returns no results if it is made without a security context and
rows if I enter a username and password. How can the first query run without
any loggin details and the second only run with loggin details?
--
Jonathan Palmer
"Steen Persson (DK)" wrote:
> That's a bit strange. Have you tried to remove the linked server and
> recreate it?
> What happens if you use OPENROWSET like in my example below -Does that
> make any difference?
>
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
> Servername Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://YourDomainCOntrollerName''
> WHERE objectClass = ''organizationalPerson''
> '
> )
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator
>

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

Active / Active Cluster SQL Query

I have a SQL Server 2000 with SP3 in a Active / Active
Cluster. I would like to write a SQL query to receive
database table data from the other side of the Active
cluster.
Node A - Virtual Server Name: HGFOPKLD\ERT - Database
Name: NewOrleans_Sales - Table - June_2004
Node B - Virtual Server Name: JKLOPGHF\ERT - Database
Name: NewOrleans_Capital - Table - June_2004
I would like to write a query to select data from
NewOrleans_Sales..June2004 to NewOrleans_Capital..Jun2004.
Listed below is an outline of time intervals for this
failure.
Select Sold_date, Value from NewOrleans_Capital..Jun2004
FROM NewOrleans_Sales..June2004.
Please help me with this query, do I need create a linked
server between two servers?
Thanks!
Mark
You need to create a linked server to access remote data. Use either the
OpenQuery function or the four-part name convention. Accessing a clustered
instance from another clustered instance is no different than accessing any
remote SQL server from a local SQL server.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:0c1001c48b81$98b494f0$a401280a@.phx.gbl...
> I have a SQL Server 2000 with SP3 in a Active / Active
> Cluster. I would like to write a SQL query to receive
> database table data from the other side of the Active
> cluster.
> Node A - Virtual Server Name: HGFOPKLD\ERT - Database
> Name: NewOrleans_Sales - Table - June_2004
> Node B - Virtual Server Name: JKLOPGHF\ERT - Database
> Name: NewOrleans_Capital - Table - June_2004
> I would like to write a query to select data from
> NewOrleans_Sales..June2004 to NewOrleans_Capital..Jun2004.
> Listed below is an outline of time intervals for this
> failure.
> Select Sold_date, Value from NewOrleans_Capital..Jun2004
> FROM NewOrleans_Sales..June2004.
> Please help me with this query, do I need create a linked
> server between two servers?
> Thanks!
> Mark
>
>
>

Action Queries

Two questions.

1. How do I create action queries in SQL Server? From Enterprise Manager -> view I can create, run but NOT save the query.

2. How do I access an SQL server query from access like i would an Access query? Do i have to make a call to a stored procedure or something like that?If you like to reuse a query you can create it in Query Analyzer and then save it to a file. Using Query Analyzer you can then open the file and run the query. You can use any text editor to create a query and save it. Also by using a stored procedure you are saving the SQL to the database so it can be used at any time just by calling it. Depending on how you setup permission you can let other users call your stored procedure.|||I'm a little new at this so bear with me... OK.. I follow you up to the point of the stored procedure. Say i save that file as Test.. Is test.sql the stored procedure? I thought stored procedures were only available inside Enterprise manager. Lastly Once the file containing the sql statement is created, how do i reference the file from enterprise manager? i can figure out how to call a stored procedure in the enterprise manager from Access VBA code.|||Access_Dude,
Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

select Name, Postcode
from Customer
where CustomerID = 123

... can become a stored proc ('action query')...

create proc spGetCustomerNameAndPostcode
as
select Name, Postcode
from Customer
where CustomerID = 123

... it can then be simply called from an app, or from within SQL Server, using:
exec spGetCustomerNameAndPostcode

(Note that the exec isn't always needed, depends on what you're doing)

Hope this helps :)
Jon.|||Access_Dude,
Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

select Name, Postcode
from Customer
where CustomerID = 123

... can become a stored proc ('action query')...

create proc spGetCustomerNameAndPostcode
as
select Name, Postcode
from Customer
where CustomerID = 123

... it can then be simply called from an app, or from within SQL Server, using:
exec spGetCustomerNameAndPostcode

(Note that the exec isn't always needed, depends on what you're doing)

Hope this helps :)
Jon.

accumulation column

If my query returns the following result:

NAME1 10
NAME2 20
NAME3 15
NAME4 5
NAME5 25

is there any way (without using cursors) to add a column that adds the numbers so that I get the following result:

NAME1 10 10
NAME2 20 30
NAME3 15 45
NAME4 5 50
NAME5 25 75

? thanks in advanceyes, with a theta joinselect t1.name
, t1.num
, sum(t2.num) as accum
from yourtable as t1
inner
join yourtable as t2
on t1.name >= t2.name
group
by t1.name
, t1.num
order by t1.namenotice the inequality in the join condition

Accessing UDF on a linked server

Hi all-

I need to invoke a UDF on a linked server. Is this possible?

eg:
I need to execute the following query from Server1

select *
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId

Thanks for your timeNever mind. I got it. I could do this using OPNEQUERY

Accessing the underlying sql query from the rdl file

Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group).

Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd).

I can then manually drill down and retrieve the SematicQuery xml from the commandText field.

The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of.

Is this possible?

Thanks,

Arjay

P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0.

After attempting to autogenerate serialization classes on the SemanticQuery xml with Xsd and XsdObjectGen, I ended up hand coding some classes that handled recursion. From there, I was able to regenerate the sql query by walking through the filter and grouping sections. While this isn't a generic solution, it works well for my needs because the model I need this for is a single datasource derived from a view.

As a feature request, it sure would be nice to be able get the query string from the reporting engine directly.

accessing the database remotely from non-microsoft platform

I have a query of the kind brains of this group.

Is it possible to access a microsoft sqlserver database (for select, insert,
update, delete, and for miscellaneous administrative actions such as table
and index creation and deletion) remotely from a compiled C program (and/or,
from Perl) running on a NON microsoft platform, such as Sun's Solaris and/or
Linux?

What toolset(s) could be used (on the UNIX/Linux end) for such a purpose?
Would it require a corresponding special toolset to be installed on the
Windows server end as well? (I'd prefer not to have to do that if
possible.)

If you are not now rolling on the floor laughing at the "heresy" of such a
notion, thanks in advance.

Dan
drlevy@.glowing.com (replace 'glowing' with 'lucent')Some suggestions here:

http://www.sommarskog.se/mssqlperl/unix.html

Simon

Accessing Second Record Within Query

Hello,
I'm attempting to use a query to add up the charges in a record from a
table and two corresponding records in another table and then return a
result if there is a difference between the known total and these
calculations. My query below works fine for adding up charges from a
record in the H_MAIN table and one record from the H_Main_Extra table.
What would I add into this query to access the second corresponding
record in
the H_Main_Extra table?
Thanks in advance for any advice.
Jon
SELECT [TotalSub].Tif, [TotalSub].c1, [TotalSub].c2, [TotalSub].c3,
[TotalSub].c4, [TotalSub].CalcTotal, [TotalSub].ExtractedTotal,
[TotalSub].AmtDiff FROM (SELECT H_MAIN.Tif, H_MAIN.Changes,
IIf(IsNull(H_MAIN.[24f_Charges1]),"0",H_MAIN.[24f_Charges1]) AS c1,
IIf(IsNull(H_MAIN.[24f_Charges2]),"0",H_MAIN.[24f_Charges2]) AS c2,
IIf(IsNull(H_MAIN_Extra.[24f_Charges1]),"0",H_MAIN_Extra.[24f_Charges1])
AS c3,
IIf(IsNull(H_MAIN_Extra.[24f_Charges2]),"0",H_MAIN_Extra.[24f_Charges2])
AS c4,Val([c1])+Val([c2])+Val([c3])+Val([c4
]) AS CalcTotal,
IIf(IsNull(H_MAIN.[28_Total_Charges]),"0",Val(H_MAIN.[28_Total_Charges]))
AS ExtractedTotal, Abs([CalcTotal]-[ExtractedTotal]) AS AmtDiff
FROM H_MAIN LEFT JOIN H_Main_Extra ON H_MAIN.Id =
H_Main_Extra.ParentId) AS TotalSub WHERE ((([TotalSub].[AmtDiff])>0));On 5 Dec 2005 12:12:51 -0800, jon.tjemsland@.gmail.com wrote:

>Hello,
>I'm attempting to use a query to add up the charges in a record from a
>table and two corresponding records in another table and then return a
>result if there is a difference between the known total and these
>calculations. My query below works fine for adding up charges from a
>record in the H_MAIN table and one record from the H_Main_Extra table.
>What would I add into this query to access the second corresponding
>record in
>the H_Main_Extra table?
>Thanks in advance for any advice.
Hi Jon,
The code you posted uses the IIf function. This function does not exist
in SQL Server. I suspect that you're using Access. If I'm right, then
you'll probably get better help in an Access group. (This group is for
SQL Server).
Your question is also not completely clear. You say you want to add a
"second corresponding record", but you don't indicate how I would know
which rows are "corresponding". And if there are more than 2, then how
to determine which are first and seceond?
If the IIf's were an error and this question does concern SQL Server,
then please check www.aspfaq.com/5006 to get more information on the
details you need to include in your post to enable us to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing query execution plan results programmatically

Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample

SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;

but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.

Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.

I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.

Thanks -- MT

C# sample

SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
connString.ApplicationName = "TryIt";
connString.DataSource = @."(local)";
connString.IntegratedSecurity = true;
connString.InitialCatalog = "tempdb";
using (SqlConnection conn = new SqlConnection(connString.ToString()))
{
conn.Open();
SqlCommand showplan_on_cmd = new SqlCommand("set showplan_xml on", conn);
showplan_on_cmd.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("Select count(*) From sys.tables", conn);
using (SqlDataReader showplan_results = cmd.ExecuteReader())
{
if (showplan_results.Read())
{
Console.WriteLine(showplan_results[0].ToString());
}
}
SqlCommand showplan_off_cmd = new SqlCommand("set showplan_xml off", conn);
showplan_on_cmd.ExecuteNonQuery();
}

Hope that helps.

PS: You can use this sample inside a .NET assembly with slight modifications (use a context-connection) to do the same thing inside a stored procedure.

|||That worked great. I had tried an ExecuteDataSet operation, but I had not thought to try a data reader. Thanks!

Accessing Query Execution Plan (QEP) Statistics

Hi,
I have some questions about estimated QEPs that can be generated in MSSQL QA.
If I point at an icon/physical operator in the estimated QEP, it shows me
some statistics about the operator.
1. Is there a way to retrieve these statistics through a query, i.e., can
these statistics be available to the user?
2. Are these statistics generated at the server or at the client?
3. Also, is there a way to generate these statistics on my own?
Thanks in advance,
-TC.
T Chaudhary wrote:
> Hi,
> I have some questions about estimated QEPs that can be generated in
> MSSQL QA.
> If I point at an icon/physical operator in the estimated QEP, it
> shows me some statistics about the operator.
> 1. Is there a way to retrieve these statistics through a query, i.e.,
> can these statistics be available to the user?
> 2. Are these statistics generated at the server or at the client?
> 3. Also, is there a way to generate these statistics on my own?
> Thanks in advance,
> -TC.
Generated on the server. You can use SET SHOWPLAN_ALL ON from QA to get
results back in result set format.
Try This:
Set showplan_all on
go
select * from pubs..publishers
go
Set showplan_all off
go
David G.
|||If you are also interested in index statistics try
DBCC show_statistics(object_id, indid)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"T Chaudhary" <replysoon_04@.yahoo.com> wrote in message
news:e8149671.0409091256.56631cf@.posting.google.co m...
> Hi,
> I have some questions about estimated QEPs that can be generated in MSSQL
QA.
> If I point at an icon/physical operator in the estimated QEP, it shows me
> some statistics about the operator.
> 1. Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
> 2. Are these statistics generated at the server or at the client?
> 3. Also, is there a way to generate these statistics on my own?
> Thanks in advance,
> -TC.

Accessing Query Execution Plan (QEP) Statistics

Hi,
I have some questions about estimated QEPs that can be generated in MSSQL QA.
If I point at an icon/physical operator in the estimated QEP, it shows me
some statistics about the operator.
1. Is there a way to retrieve these statistics through a query, i.e., can
these statistics be available to the user?
2. Are these statistics generated at the server or at the client?
3. Also, is there a way to generate these statistics on my own?
Thanks in advance,
-TC.T Chaudhary wrote:
> Hi,
> I have some questions about estimated QEPs that can be generated in
> MSSQL QA.
> If I point at an icon/physical operator in the estimated QEP, it
> shows me some statistics about the operator.
> 1. Is there a way to retrieve these statistics through a query, i.e.,
> can these statistics be available to the user?
> 2. Are these statistics generated at the server or at the client?
> 3. Also, is there a way to generate these statistics on my own?
> Thanks in advance,
> -TC.
Generated on the server. You can use SET SHOWPLAN_ALL ON from QA to get
results back in result set format.
Try This:
Set showplan_all on
go
select * from pubs..publishers
go
Set showplan_all off
go
David G.|||If you are also interested in index statistics try
DBCC show_statistics(object_id, indid)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"T Chaudhary" <replysoon_04@.yahoo.com> wrote in message
news:e8149671.0409091256.56631cf@.posting.google.com...
> Hi,
> I have some questions about estimated QEPs that can be generated in MSSQL
QA.
> If I point at an icon/physical operator in the estimated QEP, it shows me
> some statistics about the operator.
> 1. Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
> 2. Are these statistics generated at the server or at the client?
> 3. Also, is there a way to generate these statistics on my own?
> Thanks in advance,
> -TC.

Accessing Query analyzer Messages programmatically

I would like to access the results posted to the Messages tab of Query Analyzer via code. I had assumed this was possible using ADO, but I am not having any luck.

Here is the example that I would like to use:

-- This T-SQL returns a simple report
DECLARE @.Foo Datetime

SELECT @.Foo = GetDate()

Print @.Foo
Print ''
Print 'Another line of text'
Print ''
Print 'End of report'

When I try opening this as an ADODB recordset, the recordset come back closed.

I'm scratching my head trying to think of other ways to do this, and I keep coming up blank.

Any ideas?see ADODB.Connection.Errors collection

?ADODB.Connection.Errors.Item(0).Description