Hello,
Setting parameter ranges for a query is very useful.
How can I specify : begins with ..... and make that a parameter (in this
case first 5 digits of a project name)?
Liek '%[parameter3]%' does not work.
What should it be?
Help much appreciated,
An 'over-his-head' Texas Tonie
Texas Tonie,
If you want a range of values, look at the BETWEEN operator. For character
strings, however, the whole issue of collation and sort order needs to be
considered when defining what is between.
For your example problem, assuming that the 'number' is actually a character
string (not some sort of numeric datatype) and that the number may have
leading spaces, then you could use:
DECLARE @.Value = ' 12345'
SELECT @.Value WHERE LTRIM(@.Value) LIKE '12345%'
FWIW,
RLF
"Texas Tonie" <TexasTonie@.discussions.microsoft.com> wrote in message
news:72C622EC-3C49-41C7-8259-2274E9396CD3@.microsoft.com...
> Hello,
> Setting parameter ranges for a query is very useful.
> How can I specify : begins with ..... and make that a parameter (in this
> case first 5 digits of a project name)?
> Liek '%[parameter3]%' does not work.
> What should it be?
> Help much appreciated,
> An 'over-his-head' Texas Tonie
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Adding a new table to a complex join statement
All help appreciated.
I currently use the following select statement as a custom query.
SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)
Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].
I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards
here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql
I currently use the following select statement as a custom query.
SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)
Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].
I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards
here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql
Sunday, March 25, 2012
Adding a line number to SQL Output
Guess I am going brain dead at the momnet!
I want to add a line number to a simple SQL query so the output would look something like:
1 Output of the select statement
2 Output of the select statement
3 Output of the select statement
My current statement is basically:
Select field1, field2, field3, field4
From table1
where field1 = somevalue
Like I typed, very simple. I am just not finding how I should add a line number to the output.
Any help would be appreciated!Originally posted by jhimes
Select field1, field2, field3, field4
From table1
where field1 = somevalue
Try adding rownum to your select statement.
[code]
Select field1, field2, field3, field4, rownum as count
From table1
where field1 = somevalue
Oh - yea an update to this - I assume you are on Oracle - if its another database, that probably wont work.|||Thank you, that is just what I needed!|||And yes I am on Oracle & just learning real SQL (from Microsoft Access SQL).|||Originally posted by jhimes
And yes I am on Oracle & just learning real SQL (from Microsoft Access SQL).
You're welcome - Oracle is the place to be! Good luck and check back with questions because I am still always learning something new myself.
I want to add a line number to a simple SQL query so the output would look something like:
1 Output of the select statement
2 Output of the select statement
3 Output of the select statement
My current statement is basically:
Select field1, field2, field3, field4
From table1
where field1 = somevalue
Like I typed, very simple. I am just not finding how I should add a line number to the output.
Any help would be appreciated!Originally posted by jhimes
Select field1, field2, field3, field4
From table1
where field1 = somevalue
Try adding rownum to your select statement.
[code]
Select field1, field2, field3, field4, rownum as count
From table1
where field1 = somevalue
Oh - yea an update to this - I assume you are on Oracle - if its another database, that probably wont work.|||Thank you, that is just what I needed!|||And yes I am on Oracle & just learning real SQL (from Microsoft Access SQL).|||Originally posted by jhimes
And yes I am on Oracle & just learning real SQL (from Microsoft Access SQL).
You're welcome - Oracle is the place to be! Good luck and check back with questions because I am still always learning something new myself.
Adding a database user
Hi,
Could someone please tell me how to add a database user using a query? I
know how to set the appropriate permissions for tables of existing users,
but I can't figure out how to add a new user.
Thank you,
SteveSteve,
Do you mean a User or a Login or both? There is a difference and a User is
no good without an associated Login. You may want to check out sp_adduser
and sp_addlogin in BooksOnLine.
Andrew J. Kelly
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>|||Hi,
Also have a look into the system procedures which allocate fixed roles to
users/logins in books online.
sp_addsrvrolemember and sp_addrolemember
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORPqt3OREHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Do you mean a User or a Login or both? There is a difference and a User
is
> no good without an associated Login. You may want to check out sp_adduser
> and sp_addlogin in BooksOnLine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Steve Caliendo" <scaliendo@.epion.com> wrote in message
> news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
users,[vbcol=seagreen]
>|||To add to the other responses:
Use sp_addlogin to add a new SQL login.
Use sp_grantlogin to grant an existing Windows account access to SQL Server
Use sp_grantdbaccess to add a SQL login or Windows account as a database
user
For example:
EXEC sp_addlogin 'MySqlLogin', 'MyPassword'
EXEC sp_grantlogin 'MyDomain\MyAccount'
USE MyDatabase
EXEC sp_grantdbaccess 'MySqlLogin'
EXEC sp_grantdbaccess 'MyDomain\MyAccount'
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>
Could someone please tell me how to add a database user using a query? I
know how to set the appropriate permissions for tables of existing users,
but I can't figure out how to add a new user.
Thank you,
SteveSteve,
Do you mean a User or a Login or both? There is a difference and a User is
no good without an associated Login. You may want to check out sp_adduser
and sp_addlogin in BooksOnLine.
Andrew J. Kelly
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>|||Hi,
Also have a look into the system procedures which allocate fixed roles to
users/logins in books online.
sp_addsrvrolemember and sp_addrolemember
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORPqt3OREHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Do you mean a User or a Login or both? There is a difference and a User
is
> no good without an associated Login. You may want to check out sp_adduser
> and sp_addlogin in BooksOnLine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Steve Caliendo" <scaliendo@.epion.com> wrote in message
> news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
users,[vbcol=seagreen]
>|||To add to the other responses:
Use sp_addlogin to add a new SQL login.
Use sp_grantlogin to grant an existing Windows account access to SQL Server
Use sp_grantdbaccess to add a SQL login or Windows account as a database
user
For example:
EXEC sp_addlogin 'MySqlLogin', 'MyPassword'
EXEC sp_grantlogin 'MyDomain\MyAccount'
USE MyDatabase
EXEC sp_grantdbaccess 'MySqlLogin'
EXEC sp_grantdbaccess 'MyDomain\MyAccount'
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>
adding a counter to a select query?
I am using SQL Server 2000 v8
Is there a way to add a counter to SELECT statement without writing to
tables
Ultimately I am trying to get a dataset from a SQL statement split into
blocks of 80000 rowsSQL Server 2005 has a function
row_number()
which does just that.
There was a thread somewhere about doing this in 2000 but the solution
looked quite complex.
"KayC" wrote:
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"KayC" wrote:
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Do a search in this forum on "Rank" and you will find several examples.
This article also has some pointers...
http://www.aspfaq.com/show.asp?id=2427
Also check out this article on paging...
http://www.aspfaq.com/show.asp?id=2120
However, I dont think either solution is intended for that volume of data.
You will have to see how they perform.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||When I need a counter, I create a temp or variable table.
CREATE TABLE #mytemp ( entryid int IDENTITY (1,1), UserName varchar(12)
Insert into #mytemp (UserName)
Select UserName from dbo.Users order by UserName
Select entryid, UserName from #mytemp
DROP TABLE #mytemp
That gives me a counter.
2005 has the feature. the above is for 2000 pretty much.
Be careful, you don't want to do this for 1,000,000 rows. Use some common
sense.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Thanks guys for pointing me in the right direction
I have managed to solve the problem by creating a table variable using
the entryid sloan suggested
Cheers!
Kay
Is there a way to add a counter to SELECT statement without writing to
tables
Ultimately I am trying to get a dataset from a SQL statement split into
blocks of 80000 rowsSQL Server 2005 has a function
row_number()
which does just that.
There was a thread somewhere about doing this in 2000 but the solution
looked quite complex.
"KayC" wrote:
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"KayC" wrote:
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Do a search in this forum on "Rank" and you will find several examples.
This article also has some pointers...
http://www.aspfaq.com/show.asp?id=2427
Also check out this article on paging...
http://www.aspfaq.com/show.asp?id=2120
However, I dont think either solution is intended for that volume of data.
You will have to see how they perform.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||When I need a counter, I create a temp or variable table.
CREATE TABLE #mytemp ( entryid int IDENTITY (1,1), UserName varchar(12)
Insert into #mytemp (UserName)
Select UserName from dbo.Users order by UserName
Select entryid, UserName from #mytemp
DROP TABLE #mytemp
That gives me a counter.
2005 has the feature. the above is for 2000 pretty much.
Be careful, you don't want to do this for 1,000,000 rows. Use some common
sense.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Thanks guys for pointing me in the right direction
I have managed to solve the problem by creating a table variable using
the entryid sloan suggested
Cheers!
Kay
Tuesday, March 20, 2012
Adding "From Query" parameters degrades performance
Hi list,
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>
Sunday, March 11, 2012
Add sum column to query
What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:
order number
order amount
total amount
I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.
select a.ordernum, a.orderamt, b.totalamtfrom orders a, (select ordernum, sum(orderamt) from orders group by ordernum) b
where a.ordernum = b.ordernum
This will return data like:
ORDERNUM ORDERAMT TOTALAMT
1234 12.33 56.23
1234 11.25 56.23
1234 32.65 56.23
2345 ...ETC...|||
Hello,
Can you list the schema and an example of required output briefly as I'm not 100% sure what you want to do: do you want to select an order amount and sum what amount?
Sounds as if you should be using a subquery/join.
Cheers,
Rob
|||to complete Phil's answer, I think this should be a little more efficient to do this
SELECT a.ordernum, a.orderamt, b.totalamt, c.totalamt...
FROM orders a
inner join (select ordernum, sum(orderamt) from orders group by ordernum) b on a.ordernum= b.ordernum
inner join (select ordernum, sum(orderamt) from orders group by ordernum) c on a.ordernum= c.ordernum
Could it look something like this?
create table #x( ordernum int not null, amt int not null )
insert #x
select 1, 20 union all
select 1, 15 union all
select 2, 35 union all
select 2, 10 union all
select 2, 70
go
select ordernum, amt, sum(amt) as total
from #x
group by ordernum, amt
with rollup
go
ordernum amt total
-- -- --
1 15 15
1 20 20
1 NULL 35
2 10 10
2 35 35
2 70 70
2 NULL 115
NULL NULL 150
(8 row(s) affected)
/Kenneth
|||I tried using With Cube and With rollup, but they have a limit of 10 on the number of columns returned. The actual report I'm writing has over 20.|||So what are you trying to achieve?Give us an example output of what you'd like the data to look like.|||I think your first post will solve the problem. Haven't had time to try it yet.|||
sorry, i made a mistake...
SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum
I thought inner join could be more efficient than using where statement...
|||stephane - Montpellier wrote:
sorry, i made a mistake...
SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernumI thought inner join could be more efficient than using where statement...
Your statement won't work there either. You didn't include ordernum in your subquery.
Never-the-less, inner join and the join method I used are identical.
Thursday, March 8, 2012
Add new user at SQL Server Express Manager
I am unable to add a new user like 'xxx\ASPNET' to the Users under Security from AdventureWorks Database. I edited the SQL query which is "sp_grantlogin 'XXXX\ASPNET' " . It was completed successfully but was not able to add it to the Users.
We are still in beta so use the link below to download the developer edition and use the OLTP(online transaction processing) management studio to creat the user. Hope this helps.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx#EBAA
Tuesday, March 6, 2012
Add many users to Reporting Services
I am looking for a script, query or utility that can add many user's at a
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
Add Login
I want to add login in SQL Server 2005 through Query Analyzer and grant him
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Add Keywords to Query Analyzers Syntax
Does anyone know of a way to add *key words* to Query Analyzer's syntax
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.
Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:4096bd6a$0$31706$fa0fcedb@.lovejoy.zen.co.uk.. .
> I know of no way to add keywords in the way you want.
Then do you know of another way?|||Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,
It seems that the keywords are contained in the file
"../tools/binn/sqllex.dll" -- part of which appears in plain-text. For fun,
I tried the following command from a Linux prompt (after backing up the
file):
sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll
... and then renamed the new file. Sure enough, QA stopped highlighing
"CHECKALLOC" in blue. Sadly, it wouldn't highlight my name, though. :-(
There must be something important about the rest of the gobbledygook in that
file ;-)
Rich
"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||
Quote:
Originally Posted by Erland Sommarskog
Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
It's actually hardcoded, and found this out the hardway when a dll was no longer registered.
the syntax highlighting functionality is done by a dll named sqllex.dll that is typically found in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory;
if you unregister the dll, you'll lose the syntax highlighting completely; re-registering it restores the functionality, which i discovered when uninstalling/reinstalling after a beta of SQL 2005.
because the list of keywords is in the DLL, and not in a file the DLL uses, there's no way to ADD keywords unless you could replace that dll with your own enhanced verison.
HTH.
Add Keywords to Query Analyzers Syntax
Does anyone know of a way to add *key words* to Query Analyzer's syntax
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.
Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:4096bd6a$0$31706$fa0fcedb@.lovejoy.zen.co.uk.. .
> I know of no way to add keywords in the way you want.
Then do you know of another way?|||Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,
It seems that the keywords are contained in the file
"../tools/binn/sqllex.dll" -- part of which appears in plain-text. For fun,
I tried the following command from a Linux prompt (after backing up the
file):
sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll
... and then renamed the new file. Sure enough, QA stopped highlighing
"CHECKALLOC" in blue. Sadly, it wouldn't highlight my name, though. :-(
There must be something important about the rest of the gobbledygook in that
file ;-)
Rich
"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan
Saturday, February 25, 2012
add in query
hi
i have existing query ..now i want to add one more join in this with some condition in where clause.
my need is if emloyee's not present for last 4 week .so his working hour is zero..so i want to list those employeee who not worked for last 4 weeks.
see the bold one.
SELECT
HR.ClientID,
CD.Email,
CD.Surname,
CD.FirstNames,
CD.HomeTel,
CD.WorkTel,
CD.MobileTel,
CD.PreferredName,
HR.EmployeeNumber,
HR.EmploymentType,
HR.Status,
HR.Jobtitle,
HR.LocationID,
HR.DivisionID,
HR.IsPayrollEmployee,
HR.StartDate,
CASE
WHEN PIP.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsPrevented',
HR.PayrollCompanyID,
HR.ProbationEndDate,
IsNull(HR.ProbationStatus, 0) AS 'ProbationStatus',
CD.UserTypeID,
CLT.Location,
CLT.Division,
IsNull((SELECT COUNT(*)
FROM t_TerminationDetails AS TD INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_01 ON (PD_01.Frequency = 'M' AND TD.TerminationDate >= PD_01.StartDate AND PD_01.PayPeriod = 1)
INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_02 ON (PD_02.Frequency = 'M' AND TD.TerminationDate <= PD_02.StartDate AND PD_02.PayPeriod = 12)
WHERE TD.ClientID = HR.ClientID), 0) AS 'HasBeenTerminated',
IsNull(TD.Reemploy, 0) AS 'Reemploy',
NOP.NumberOfPayruns,
CASE
WHEN CAR.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsDriver',
HR.HasBeenRehired,
CD.DateCreated,
ISNULL(BD.AccountNumber, 0) AS 'AccountNumber',
(SELECT TOP 1 GD.CompanyID FROM t_BenefitCompanyEmployee BCE INNER JOIN t_GroupDetails GD ON BCE.CompanyID = GD.CompanyID WHERE (BCE.ClientID = CD.ClientID)) as 'PAYECompany',
JT.Name As 'JobTitleName',
JT.IsContractor As 'IsContractor',
CASE WHEN (IsNull(BD.PaymentMethod,0) = 1 AND Len(IsNull(BD.SortCode,0)) = 6) OR IsNull(BD.PaymentMethod,0) <> 1 THEN
1
ELSE
0
END AS 'ValidSortCode'
FROM
@.Client_Table AS CLT INNER JOIN (t_HRDetails AS HR INNER JOIN t_ClientDetails AS CD ON (HR.ClientID = CD.ClientID)) ON (CLT.ClientID = HR.ClientID)
LEFT JOIN #EmployeesByCompany AS EBC ON (CLT.ClientID = EBC.ClientID)
LEFT JOIN t_PayrollIsPrevented AS PIP ON (CLT.ClientID = PIP.ClientID)
LEFT JOIN #NumberOFPayruns_Table AS NOP ON (CLT.ClientID = NOP.ClientID)
LEFT JOIN t_CAR_Driver AS CAR ON (HR.ClientID = CAR.ClientID)
LEFT JOIN (SELECT ClientID, AccountNumber, SortCode, PaymentMethod FROM t_BankDetails WHERE Status = 1 AND AccountNumber <> '') AS BD ON BD.ClientID = HR.ClientID
LEFT JOIN t_JobTitle JT ON HR.Jobtitle = JT.JobTitleID
LEFT JOIN t_TerminationDetails TD ON HR.ClientID = TD.CLientID
Left join t_shiftmembers2 SM on HR.ClientID = SM.CLientID
WHERE
(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames
i tried ..but not sure this is correct way.
Change your query as follow as ,
Left join t_shiftmembers2 SM on
HR.ClientID = SM.CLientID
AND (@.NOTWORKED = 0
OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())
AND sum(SM.totalacthrs)= 0
)
)
WHERE
(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames
|||
First, I see in your query "... IsNull((SELECT COUNT(*) FROM t_TerminationDetails AS TD INNER JOIN ..." but any time select count(*) from any_table return at least 0 if the table has no rows, and you user isnull function ...
Beeing as so complicate selct i think you break it using views to simplify things.
I see @.Client_Table and i understand it is a temporary table variable but in
" (@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames"
@.NOTWORKED and etc are parameters of a stored procedure, isn't it ?
|||main thanx for reply..
by seeing your query ..i can add all my condition in left join itself..no need to add in where clause...my sum(actualhours) will not give any error.
|||The point is when you use the outer join, if there is a filter (where) condition then move them from the where clause to corresponding on clause.
|||mani i run your query i m getting followin g error
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
|||Ohh yes…How I missed that.. may be the following query might work..
Code Snippet
Left join (Select ClientID,isnull(sum(SM.totalacthrs),0) totalhrs from t_shiftmembers2
Where SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())
Group By ClientID) as SM on
HR.ClientID = SM.CLientID
Where
(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
AND
(@.NOTWORKED = 0 OR (totalhrs = 0))
|||thanx for reply
mani can u tell me dateadd(week,-4,getdate())
-4 week means 28 days
or some thing else?
|||in this query i think u r not checking this condition sum(totalacthrs) = 0|||sorry my mistake i didn't see your whole code..
i want to convert getdate like this 2007-09-26 00:00:00:00
mean i want min ,sec all in zero
Add in auto number in the query result
How can I add in the auto-increment field in my query result?
E.g
Select * from tmp1
The result from this transact sql is
VendorName VendorPhone
Ven1 123456
Ven2 789654
How can I modified my query to return result as below:( auto number in front
)
VendorID VendorName VendorPhone
1 Ven1 123456
2 Ven2 789654
Thanks lot if anyone can provide me the solutions cos I am using temporary
table in VBA. I am wondering whether I can do it in sql itself or not.
ThanksOne way is to declare a Table Variable to hold the PKs of the rows that will
be in the Final Output.. but when you declar ethe table, also include a
Identity column...
Declare @.OutPKs Table
(RowID Integer Identity Primary Key Not Null,
PkID Integer Not Null)
Then insert all the PKS into this table based on the exact same where clause
and Order By that you would use in your previous query...
Insert @.OutPKs (PkID)
Select PKID
From <Real Data Tables>
Where <Filter conditions>
Order By <Sort Conditions>
Then, finally, output the data you actually want by joining the real data
tables to this @.OutPks Table, and include the RowID in the output...
Select RowID, PkID, <Other Columns>
From @.OutPks P Join <Other Tables>
Order By RowID
"Sql Fren" wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Another way is to include an additional column which is the output of a
correlated subquery, which counts the number of records in the same set whic
h
have a columnvalue less than or equal to the columnvalue in the row the
subquery is running on... The columnvalue must be the column the output is
sorting on, and the columnvalues must be unique..
Say the column you're using is OrderDate...
Select (Select Count(*) From Table
Where OrderDate <= O.OrderDate) as RowNum,
<Other Columns>
From Table O
Order By OrderDate
If the outer query has a where clause, filtering the rows which will be
produced, the subquery must include the same where clause, or the count will
be wrong...
"Sql Fren" wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Much better to add this fake "rank" number in VBA since, presumably, you are
going to be looping through each row anyway, and VBA is pretty good at
simple math (rowNum = rowNum + 1). It is not very efficient at all to do
this at the database level, but of course, there are kludges.
http://www.aspfaq.com/2427
On 3/16/05 8:39 PM, in article
39D05AEC-CB60-40A3-A140-583EBDC58EA9@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>
E.g
Select * from tmp1
The result from this transact sql is
VendorName VendorPhone
Ven1 123456
Ven2 789654
How can I modified my query to return result as below:( auto number in front
)
VendorID VendorName VendorPhone
1 Ven1 123456
2 Ven2 789654
Thanks lot if anyone can provide me the solutions cos I am using temporary
table in VBA. I am wondering whether I can do it in sql itself or not.
ThanksOne way is to declare a Table Variable to hold the PKs of the rows that will
be in the Final Output.. but when you declar ethe table, also include a
Identity column...
Declare @.OutPKs Table
(RowID Integer Identity Primary Key Not Null,
PkID Integer Not Null)
Then insert all the PKS into this table based on the exact same where clause
and Order By that you would use in your previous query...
Insert @.OutPKs (PkID)
Select PKID
From <Real Data Tables>
Where <Filter conditions>
Order By <Sort Conditions>
Then, finally, output the data you actually want by joining the real data
tables to this @.OutPks Table, and include the RowID in the output...
Select RowID, PkID, <Other Columns>
From @.OutPks P Join <Other Tables>
Order By RowID
"Sql Fren" wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Another way is to include an additional column which is the output of a
correlated subquery, which counts the number of records in the same set whic
h
have a columnvalue less than or equal to the columnvalue in the row the
subquery is running on... The columnvalue must be the column the output is
sorting on, and the columnvalues must be unique..
Say the column you're using is OrderDate...
Select (Select Count(*) From Table
Where OrderDate <= O.OrderDate) as RowNum,
<Other Columns>
From Table O
Order By OrderDate
If the outer query has a where clause, filtering the rows which will be
produced, the subquery must include the same where clause, or the count will
be wrong...
"Sql Fren" wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Much better to add this fake "rank" number in VBA since, presumably, you are
going to be looping through each row anyway, and VBA is pretty good at
simple math (rowNum = rowNum + 1). It is not very efficient at all to do
this at the database level, but of course, there are kludges.
http://www.aspfaq.com/2427
On 3/16/05 8:39 PM, in article
39D05AEC-CB60-40A3-A140-583EBDC58EA9@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:
> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>
Friday, February 24, 2012
add columns from two queries
Hello all
im trying to add the the columns resulted from one query to other columns resulted from other query in a stored procedure
like this
select x1,x2,x3 from Table1
select x4,x5 from table
i want to put it like this
x1 x2 x3 x4 x5
:)
i really need this helpp
thank youuuYou need to use a join. From what you've posted I can't tell which fields relate the records in one table to the records in another. But it would look something like this:
Select Table1.x1,Table1.x2,Table1.x3, Table.X4, Table.X5
FROM Table1 inner join Table on (Table1.JoinField=Table.OtherJoinField)
Add column with fixed number of values (text) to the select statement
Hello,
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
I found on the net something to start with
Adding Columns in SQL SELECT statements by Andy Kramek http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx
SELECT *, CAST( 0 AS INT) AS newint FROM sample
but it just add a column with value 0 to the set of results.
|||What is the version you are using... (2000/2005) Give some sample data & required output..
|||
I'm using ver. 2005. The select is very simple concerns the tables with historical rates for curves.
So there are four columns:
1) Date
2) Name of the curve
3) DF
4) Implied rate
there are 17 rows of data
and I need to add column with fixed tenors
O/N
T/N
S/N
1W
2W
1M
2M
3M
4M
5M
6M
7M
8M
9M
10M
11M
1Y
|||Ok.. do you want the result as bellow,
1) Date
2) Name of the curve
3) DF
4) Implied rate
5)O/N6)T/N
7)S/N
8)1W
9)2W
10)1M
..
..
17)IY
if yes what data you want to display on this 17 columns
|||Your questions not clear properly. But I think this will help you,
USE PUBS
Go
SELECT COUNT(*) row_num, A.au_lname
FROM authors A, authors B
WHERE A.au_lname > B.au_lname
GROUP BY A.au_lname
ORDER BY COUNT(*)
I want results to look like this:
Tenor / Date / Name of the curve/ DF / Implied rate
O/N 1/08/07 EUR_CURVE 0.9999987 4.020000
T/N 1/08/07 EUR_CURVE . .
S/N 1/08/07 EUR_CURVE . .
1W 1/08/07 EUR_CURVE . .
2W 1/08/07 EUR_CURVE . .
1M 1/08/07 EUR_CURVE . .
.
.
.
1Y 1/08/07 EUR_CURVE . .
tks
Sunday, February 19, 2012
Add as many Where's as possible
Hello,
This was my first query. This is a subquery in a join, the date used for the DaysalHead table is specified on the on conditions and is not a part of this sample.
SELECT COMPC_START, COUNT(DSH_ID) PERFORMANCES
FROM TS.DAYSALHEAD DSH
INNER JOIN GEN.COMPLEX_DATES CD ON CD.COMP_UI = DSH.COMP_UI AND CD.COMPD_DATE = DSH.DSH_DATE
INNER JOIN GEN.COMPLEX_CALENDAR CC ON CC.COMPC_UI = CD.COMPC_UI
INNER JOIN NATF.MOVIESLINKING ML ON ML.MOV_UI = DSH.MOV_UI
WHERE ML.NMOV_UI = DSD.NMOV_UI
AND DSH.COMP_UI = 168
GROUP BY COMPC_START
HAVING COUNT(DSH_ID) > 3
It runs fine and when i watch the executionplan it is using the correct indexes to scan, the most 'un-used' rows come from the complex_dates, although sql goes over the clustered index that is on comp_ui, compd_date.
Now i've changed the query to
SELECT COMPC_START, COUNT(DSH_ID) PERFORMANCES
FROM TS.DAYSALHEAD DSH
INNER JOIN GEN.COMPLEX_DATES CD ON CD.COMP_UI = DSH.COMP_UI AND CD.COMPD_DATE = DSH.DSH_DATE
INNER JOIN GEN.COMPLEX_CALENDAR CC ON CC.COMPC_UI = CD.COMPC_UI
INNER JOIN NATF.MOVIESLINKING ML ON ML.MOV_UI = DSH.MOV_UI
WHERE ML.NMOV_UI = DSD.NMOV_UI
AND DSH.COMP_UI = 168
AND CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006'
GROUP BY COMPC_START
HAVING COUNT(DSH_ID) > 3
Results are the same, but when i examine the execution plan the costs are much lower.
This means that adding wherever you can extra where's (although they are known in this case through the join) increases speed ...
Any remarks ?
AND CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006'
I would expect that anytime you constrain the resultset down to one specific date, the execution would be faster. That seems, to me, to be a 'no-brainer'.
|||The query optimizer tries to generate the smallest set possible, with the constraints stated. So by using CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006', I would guess it decided CD with that constriant made the smallest initial dataset.Add And Query Linked Server To ADAM Instance...
I was not sure if this was the best SQL Server newsgroup to post this
question to but I thought I would start here.
I would like to add a linked server to an ADAM instance and then be able to
execute queries against this linked server. It looks like I can add the
linked server but I am not sure if I am doing it right. When I try to
execute a query, I receive the following:
An error occurred while preparing the query ... for execution against OLE DB
provider "ADsDSOObject" for linked server ...
I have not been able to get around this error. From what I have read, I am
getting the impression that this is a security issue. I have tried to add
logons to the linked server but again, I am not sure if I am doing this
right. Most of the articles seem to lead me in the right direction but there
is something I am not doing correctly.
Any help with this will be most appreciated.
By the way, I am using SQL Server 2005.
Thank you,
JasonWhen you created the linked server did you define the security at all? Can
you show the query you were trying to execute?
"Jason Richmeier" wrote:
> I was not sure if this was the best SQL Server newsgroup to post this
> question to but I thought I would start here.
> I would like to add a linked server to an ADAM instance and then be able to
> execute queries against this linked server. It looks like I can add the
> linked server but I am not sure if I am doing it right. When I try to
> execute a query, I receive the following:
> An error occurred while preparing the query ... for execution against OLE DB
> provider "ADsDSOObject" for linked server ...
> I have not been able to get around this error. From what I have read, I am
> getting the impression that this is a security issue. I have tried to add
> logons to the linked server but again, I am not sure if I am doing this
> right. Most of the articles seem to lead me in the right direction but there
> is something I am not doing correctly.
> Any help with this will be most appreciated.
> By the way, I am using SQL Server 2005.
> Thank you,
> Jason
question to but I thought I would start here.
I would like to add a linked server to an ADAM instance and then be able to
execute queries against this linked server. It looks like I can add the
linked server but I am not sure if I am doing it right. When I try to
execute a query, I receive the following:
An error occurred while preparing the query ... for execution against OLE DB
provider "ADsDSOObject" for linked server ...
I have not been able to get around this error. From what I have read, I am
getting the impression that this is a security issue. I have tried to add
logons to the linked server but again, I am not sure if I am doing this
right. Most of the articles seem to lead me in the right direction but there
is something I am not doing correctly.
Any help with this will be most appreciated.
By the way, I am using SQL Server 2005.
Thank you,
JasonWhen you created the linked server did you define the security at all? Can
you show the query you were trying to execute?
"Jason Richmeier" wrote:
> I was not sure if this was the best SQL Server newsgroup to post this
> question to but I thought I would start here.
> I would like to add a linked server to an ADAM instance and then be able to
> execute queries against this linked server. It looks like I can add the
> linked server but I am not sure if I am doing it right. When I try to
> execute a query, I receive the following:
> An error occurred while preparing the query ... for execution against OLE DB
> provider "ADsDSOObject" for linked server ...
> I have not been able to get around this error. From what I have read, I am
> getting the impression that this is a security issue. I have tried to add
> logons to the linked server but again, I am not sure if I am doing this
> right. Most of the articles seem to lead me in the right direction but there
> is something I am not doing correctly.
> Any help with this will be most appreciated.
> By the way, I am using SQL Server 2005.
> Thank you,
> Jason
Thursday, February 16, 2012
Add a Table to an Anonymous Merge Publication
What is the "correct" way to ADD a Table to an Anonymous Merge Publication?
How is it then propagated to the Subscribers?
Using Query Analyzer, I successfully ran a script to Create the Table. I
added it to the Publication (Show unpublished ...) and Reran the Snapshot
Agent. The Snapshot posted to the expected unc Folder(s).
From an Internet Client using SQL-DMO or Windows Synchronize Manager, it
successfully connects to the SQL Server, but fails with this Error:
"You must rerun the snapshot because the current snapshot is obsolete"
Does that mean the Entire Database needs to be downloaded again? If so, what
happens to the Changes on the Subscriber that have not been Uploaded yet?
TIA
Aubrey Kelley
Hi Aubrey,
Thanks for your post.
From your descriptions, I understood you have concern when you are notified
"You must rerun the snapshot because the current snapshot is obsolete". If
I have misunderstood your concern, please feel free to point it out.
Based on my knowledge, alter adding a new merge article, you must generate
a new snapshot before changes from any subscription can be merged. Although
a snapshot of all articles must be generated, only the snapshot of the new
article will be used to synchronized existing subscriptions.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks, Michael
The error message on attempting to rerun the Snapshot was due to a corrupt
Database. Using a different Published Database, after running the SQL Script
to successfully Add a Table, I Started the Snapshot Agent (successful also),
and was able to Synchronize with NO alterations from an Internet Connected
Access Runtime Remote Client with an existing Subscription to that Database.
An MSDN MSPSS call Helped run the Profiler to pinpoint the offender, a View
with a File Name name format. Also, we added this to the Snapshot Agent
Properties, Edit Job Step: -Output <filename.txt> -OutputVerboseLevel 3
Aubrey Kelley
"Michael Cheng [MSFT]" wrote:
> Hi Aubrey,
> Thanks for your post.
> From your descriptions, I understood you have concern when you are notified
> "You must rerun the snapshot because the current snapshot is obsolete". If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowledge, alter adding a new merge article, you must generate
> a new snapshot before changes from any subscription can be merged. Although
> a snapshot of all articles must be generated, only the snapshot of the new
> article will be used to synchronized existing subscriptions.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Aubrey,
Thanks for sharing this information with us and it's great to hear that you
have resolved it with the help of PSS support.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
How is it then propagated to the Subscribers?
Using Query Analyzer, I successfully ran a script to Create the Table. I
added it to the Publication (Show unpublished ...) and Reran the Snapshot
Agent. The Snapshot posted to the expected unc Folder(s).
From an Internet Client using SQL-DMO or Windows Synchronize Manager, it
successfully connects to the SQL Server, but fails with this Error:
"You must rerun the snapshot because the current snapshot is obsolete"
Does that mean the Entire Database needs to be downloaded again? If so, what
happens to the Changes on the Subscriber that have not been Uploaded yet?
TIA
Aubrey Kelley
Hi Aubrey,
Thanks for your post.
From your descriptions, I understood you have concern when you are notified
"You must rerun the snapshot because the current snapshot is obsolete". If
I have misunderstood your concern, please feel free to point it out.
Based on my knowledge, alter adding a new merge article, you must generate
a new snapshot before changes from any subscription can be merged. Although
a snapshot of all articles must be generated, only the snapshot of the new
article will be used to synchronized existing subscriptions.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks, Michael
The error message on attempting to rerun the Snapshot was due to a corrupt
Database. Using a different Published Database, after running the SQL Script
to successfully Add a Table, I Started the Snapshot Agent (successful also),
and was able to Synchronize with NO alterations from an Internet Connected
Access Runtime Remote Client with an existing Subscription to that Database.
An MSDN MSPSS call Helped run the Profiler to pinpoint the offender, a View
with a File Name name format. Also, we added this to the Snapshot Agent
Properties, Edit Job Step: -Output <filename.txt> -OutputVerboseLevel 3
Aubrey Kelley
"Michael Cheng [MSFT]" wrote:
> Hi Aubrey,
> Thanks for your post.
> From your descriptions, I understood you have concern when you are notified
> "You must rerun the snapshot because the current snapshot is obsolete". If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowledge, alter adding a new merge article, you must generate
> a new snapshot before changes from any subscription can be merged. Although
> a snapshot of all articles must be generated, only the snapshot of the new
> article will be used to synchronized existing subscriptions.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Aubrey,
Thanks for sharing this information with us and it's great to hear that you
have resolved it with the help of PSS support.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, February 13, 2012
Add a column to each record with a number increment?
Hi All,
I need to know if it is possible to add a column to my query that will
represent a number for each record?
(e.g...)
[Record#], [Last Name], [First Name]
0 , Miller , John
1 , Brady , Michael
2 , Smith , Wendy
etc...
I need to do something like this:
DECLARE @.RN Int
SET @.RN = 0
SELECT (@.RN = @.RN + 1) AS [Record#],
LastName As [Last Name],
FirstName As [First Name]
FROM Table1
Order By [Record#]
Any help would be greatly appreciated,
John.Hi,
Consider using autonumber in Access or an Identity field in MS SQL
Mike -- FrontPage MVP '97 - '02
http://www.websunlimited.com
FrontPage Add-ins Summer Promotion -
FREE Purchase J-Bots Plus 2004 & receive Meta Tag Maker 2004 a $24.95 value
FREE
"Thunder" <jrugo@.patmedia.net> wrote in message news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl..
.
> Hi All,
> I need to know if it is possible to add a column to my query that will rep
resent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Thunder" wrote:
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>
>|||Thanks for the ideas; but I should have qualified my need.
I am using a LEFT OUTER JOIN to pull data back.
So what happens is that I can have sever records from table2 creating
several rows with Table1's data. I wanted to number them so that it is
easier for the user to see the groupings. Does that make sense?
Table1 has Requisitions (one req per Row)
Table2 has the associated PO info. There can be several PO's related to one
Req.
[Record#], Req#, PONumber
1,111, PO123
1,111, PO456
2,222, POabc
2,222, POefg
2,222, POhij
etc...
Thanks so much,
John.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Thunder" wrote:
>|||http://support.microsoft.com/defaul...B;EN-US;q186133
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Try,
select
(select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
t1.reqid,
t2.ponumber,
..
from t1 left join t2 on t1.reqid = t2.reqid
go
AMB
"Thunder" wrote:
> Thanks for the ideas; but I should have qualified my need.
> I am using a LEFT OUTER JOIN to pull data back.
> So what happens is that I can have sever records from table2 creating
> several rows with Table1's data. I wanted to number them so that it is
> easier for the user to see the groupings. Does that make sense?
> Table1 has Requisitions (one req per Row)
> Table2 has the associated PO info. There can be several PO's related to o
ne
> Req.
> [Record#], Req#, PONumber
> 1,111, PO123
> 1,111, PO456
> 2,222, POabc
> 2,222, POefg
> 2,222, POhij
> etc...
> Thanks so much,
> John.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
>
>|||http://www.aspfaq.com/2427
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||if the order of the records doesn't matter, then you can use
SELECT IDENTITY(INT, 0, 1) AS [Record#], [Last Name], [First Name] INTO #t1
FROM Table1
SELECT [Record#], [Last Name], [First Name] FROM #t1 ORDER BY [Record#]
if the order of the records does matter, then you can use:
DECLARE @.t1 TABLE ([Record#] INT IDENTITY(0, 1) NOT NULL, [Last Name]
VARCHAR(25) NOT NULL, [First name] VARCHAR(25) NOT NULL)
INSERT @.t1 ([Last Name], [First Name])
SELECT [Last Name], [First Name] FROM Table1 ORDER BY [Last Name],
[First Name]
SELECT [Record#], [Last Name], [First Name] FROM @.t1 ORDER BY [Record#]
Note: you must use the second query if the order is important, becuase the
identity values are not guaranteed to be in any specific order if you use
the IDENTITY function of SELECT INTO because the ORDER BY clause is executed
after the IDENTITY values are assigned. You must separate the insert from
the select in order to guarantee a specific order. Assume that an order by
existed in the first example: the select is executed, the identity values
are assigned, the results are sorted, and then the rows are inserted. In
the second example, the select is executed, the results are sorted, the
identity values are assigned, and then the rows are inserted.
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Thank you to all,
This worked Perfectly, Thank You :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3F9E9BA5-1F29-460B-9848-A973968EAFB1@.microsoft.com...
> Try,
> select
> (select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
> t1.reqid,
> t2.ponumber,
> ...
> from t1 left join t2 on t1.reqid = t2.reqid
> go
>
> AMB
> "Thunder" wrote:
>
I need to know if it is possible to add a column to my query that will
represent a number for each record?
(e.g...)
[Record#], [Last Name], [First Name]
0 , Miller , John
1 , Brady , Michael
2 , Smith , Wendy
etc...
I need to do something like this:
DECLARE @.RN Int
SET @.RN = 0
SELECT (@.RN = @.RN + 1) AS [Record#],
LastName As [Last Name],
FirstName As [First Name]
FROM Table1
Order By [Record#]
Any help would be greatly appreciated,
John.Hi,
Consider using autonumber in Access or an Identity field in MS SQL
Mike -- FrontPage MVP '97 - '02
http://www.websunlimited.com
FrontPage Add-ins Summer Promotion -
FREE Purchase J-Bots Plus 2004 & receive Meta Tag Maker 2004 a $24.95 value
FREE
"Thunder" <jrugo@.patmedia.net> wrote in message news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl..
.
> Hi All,
> I need to know if it is possible to add a column to my query that will rep
resent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Thunder" wrote:
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>
>|||Thanks for the ideas; but I should have qualified my need.
I am using a LEFT OUTER JOIN to pull data back.
So what happens is that I can have sever records from table2 creating
several rows with Table1's data. I wanted to number them so that it is
easier for the user to see the groupings. Does that make sense?
Table1 has Requisitions (one req per Row)
Table2 has the associated PO info. There can be several PO's related to one
Req.
[Record#], Req#, PONumber
1,111, PO123
1,111, PO456
2,222, POabc
2,222, POefg
2,222, POhij
etc...
Thanks so much,
John.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Thunder" wrote:
>|||http://support.microsoft.com/defaul...B;EN-US;q186133
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Try,
select
(select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
t1.reqid,
t2.ponumber,
..
from t1 left join t2 on t1.reqid = t2.reqid
go
AMB
"Thunder" wrote:
> Thanks for the ideas; but I should have qualified my need.
> I am using a LEFT OUTER JOIN to pull data back.
> So what happens is that I can have sever records from table2 creating
> several rows with Table1's data. I wanted to number them so that it is
> easier for the user to see the groupings. Does that make sense?
> Table1 has Requisitions (one req per Row)
> Table2 has the associated PO info. There can be several PO's related to o
ne
> Req.
> [Record#], Req#, PONumber
> 1,111, PO123
> 1,111, PO456
> 2,222, POabc
> 2,222, POefg
> 2,222, POhij
> etc...
> Thanks so much,
> John.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
>
>|||http://www.aspfaq.com/2427
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||if the order of the records doesn't matter, then you can use
SELECT IDENTITY(INT, 0, 1) AS [Record#], [Last Name], [First Name] INTO #t1
FROM Table1
SELECT [Record#], [Last Name], [First Name] FROM #t1 ORDER BY [Record#]
if the order of the records does matter, then you can use:
DECLARE @.t1 TABLE ([Record#] INT IDENTITY(0, 1) NOT NULL, [Last Name]
VARCHAR(25) NOT NULL, [First name] VARCHAR(25) NOT NULL)
INSERT @.t1 ([Last Name], [First Name])
SELECT [Last Name], [First Name] FROM Table1 ORDER BY [Last Name],
[First Name]
SELECT [Record#], [Last Name], [First Name] FROM @.t1 ORDER BY [Record#]
Note: you must use the second query if the order is important, becuase the
identity values are not guaranteed to be in any specific order if you use
the IDENTITY function of SELECT INTO because the ORDER BY clause is executed
after the IDENTITY values are assigned. You must separate the insert from
the select in order to guarantee a specific order. Assume that an order by
existed in the first example: the select is executed, the identity values
are assigned, the results are sorted, and then the rows are inserted. In
the second example, the select is executed, the results are sorted, the
identity values are assigned, and then the rows are inserted.
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Thank you to all,
This worked Perfectly, Thank You :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3F9E9BA5-1F29-460B-9848-A973968EAFB1@.microsoft.com...
> Try,
> select
> (select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
> t1.reqid,
> t2.ponumber,
> ...
> from t1 left join t2 on t1.reqid = t2.reqid
> go
>
> AMB
> "Thunder" wrote:
>
Subscribe to:
Posts (Atom)