No ISSQLROLE column in sysusers?

The query below complains of "Invalid column name 'ISSQLROLE'." when run on
installation that uses the Turkish character set.
SELECT * FROM sysusers WHERE NAME = 'BOB' AND ISSQLROLE = 0
I'm confused; why would the column be missing/renamed? Reinstalling SQL Ser
ver to use Latin character set works as always. Any words will be very much
appreciated. (I don't have much experience with character sets outside of t
he one that I use here in California.)
Thanks in advance,
James Hunter RossHi James,
Try the column name in lower case. Turkish has both dotted and non-dotted I'
s, and sometimes it is difficult to tell them apart if you're not used to th
e alphabet. In this case it seems to me that the uppercase I in ISSQLROLE in
interpreted by SQL Server as a non-dotted I, where it should be a dotted i.
--
Jacco Schalkwijk
SQL Server MVP
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message news:ujLG32q
CEHA.688@.tk2msftngp13.phx.gbl...
The query below complains of "Invalid column name 'ISSQLROLE'." when run on
installation that uses the Turkish character set.
SELECT * FROM sysusers WHERE NAME = 'BOB' AND ISSQLROLE = 0
I'm confused; why would the column be missing/renamed? Reinstalling SQL Ser
ver to use Latin character set works as always. Any words will be very much
appreciated. (I don't have much experience with character sets outside of t
he one that I use here in California.)
Thanks in advance,
James Hunter Ross|||I change only the "I" in "ISSQLROLE" in our query to "iSSQLROLE" and it work
s! What the...
I'm freaking out. I can't even think. Then, "I" is not actually upper case "
i", we are doomed. We have never been particularly disciplined when writing
queries to match the case of the query to the case of the column/table names
.
I don't suppose there is any easy way to dance around this, is there?
James|||Hi James,
Indeed in the Turkish collation "I" is not upper case "i", it is upper case
"i". Upper case "i" is "I".
I don't think that there is a way around it except a big search and replace
to change all the I's into i's.
The other option involves rebuilding the master database with a
Latin1_General collation (while keeping your own database at Turkish), but
your customer(s) might not be very happy with that, and if you have string
comparisons with columns in temporary tables they will fail most likely.
Jacco Schalkwijk
SQL Server MVP
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:uk1xlPrCEHA.3280@.TK2MSFTNGP09.phx.gbl...
I change only the "I" in "ISSQLROLE" in our query to "iSSQLROLE" and it
works! What the...
I'm freaking out. I can't even think. Then, "I" is not actually upper case
"i", we are doomed. We have never been particularly disciplined when writing
queries to match the case of the query to the case of the column/table
names.
I don't suppose there is any easy way to dance around this, is there?
James

No Inner Joins?

Hello:

I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:

update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'

I got the error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

So I tried changing the syntax to:

UPDATE inv_mast
SET inv_mast.short_code = 'Entrelec'
FROM inv_mast, inv_loc
WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
AND inv_loc.primary_supplier_id = '100086'

And didn't get an error, but I didn't get any results (0 row(s) affected).

What am I doing wrong?

If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.

Thanks all! :)

-jimyour update statment probably should have looked like:
update inv_mast
SET inv_mast.short_code = 'Entrelec'
from inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
WHERE inv_loc.primary_supplier_id = '100086'

and if you like aliases:
update im
SET im.short_code = 'Entrelec'
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
WHERE il.primary_supplier_id = '100086'

as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
select im.inv_mast_uid,il.primary_supplier_id
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
order by 1,2|||Paul,

Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.

I'm going to try your update query now...

Dude - I owe you big time!! It worked like a charm - how can I thank you?

-jim

No EM/Query Builder to access SQL Server on SQL Server 2005?

Hello
Since there is no Enterprise Manager to access Query Builder how do you
access Query Builder on Sql Server 2005In SSMS: Query-->Design Query in Editor
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"winlin" <winlin@.verizon.com> wrote in message
news:05C6CE07-E60F-47BA-AED6-7EC02534C716@.microsoft.com...
> Hello
> Since there is no Enterprise Manager to access Query Builder how do you
> access Query Builder on Sql Server 2005

No Databases in Enterprise Manager

SQL - 2000 running on Win2k Server
I am unable to view my databases in enterprise manager but can see them in Query Analyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot this morning Enterprise manager will not display any of my databases. What have I done?
rwjohn1@.sbcglobal.net
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Try deleting the registered server and then register it
again in Enterprise Manager.
In Enterprise Manager, right click on the server, select
Delete SQL Server Registration. Then right click on the
server group and select new server registration to register
the server again.
-Sue
On Thu, 28 Oct 2004 12:04:36 -0700, SqlJunkies User
<User@.-NOSPAM-SqlJunkies.com> wrote:

>SQL - 2000 running on Win2k Server
>I am unable to view my databases in enterprise manager but can see them in Query Analyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot this morning Enterprise manager will not display any of my databases. What have I done
?
>rwjohn1@.sbcglobal.net
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

No Databases in Enterprise Manager

SQL - 2000 running on Win2k Server
I am unable to view my databases in enterprise manager but can see them in Q
uery Analyzer/ODBC. I had deleted a bunch of unused databases last night an
d after reboot this morning Enterprise manager will not display any of my da
tabases. What have I done?
rwjohn1@.sbcglobal.net
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Try deleting the registered server and then register it
again in Enterprise Manager.
In Enterprise Manager, right click on the server, select
Delete SQL Server Registration. Then right click on the
server group and select new server registration to register
the server again.
-Sue
On Thu, 28 Oct 2004 12:04:36 -0700, SqlJunkies User
<User@.-NOSPAM-SqlJunkies.com> wrote:

>SQL - 2000 running on Win2k Server
>I am unable to view my databases in enterprise manager but can see them in Query An
alyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot
this morning Enterprise manager will not display any of my databases. What have I d
one
?
>rwjohn1@.sbcglobal.net
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports P
ost Alerts, Ratings, and Searching.

no data returned when using a stylesheet

Executing http queries works fine. But when I try to use a xls stylesheet in the query, I get headings but no data. It must be something in the stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0 sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms Spezialit?ten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari Carnes" /><Orders OrderID="10251" Employe
eId="3" Shipname="Victuailles en stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship Name</B></TD></TR><xsl:
apply-templates/></TABLE></BODY></HTML></xsl:template><xsl:template match="Orders"><TR><TD><xsl:value-of select="@.OrderID"/></TD><TD><xsl:value-of select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
The only problem I could find is with the stylesheet namespace declaration -
I had to change it to <xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> (i.e. with a
version attribute)
Other than that it worked OK for me from both the local PC and a remote
client.
You don't list SQLXML 3.0 sp2 as being installed - it *should* still work
without it, but I haven't tested it.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"David R" <david.roseneder@.dcs.qld.gov.au> wrote in message
news:0C44F895-7C60-4842-B601-D09197A70727@.microsoft.com...
Executing http queries works fine. But when I try to use a xls stylesheet in
the query, I get headings but no data. It must be something in the
stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL
Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler
shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0
sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools
Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms
Spezialitten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari
Carnes" /><Orders OrderID="10251" EmployeeId="3" Shipname="Victuailles en
stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output
media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE
width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship
Name</B></TD></TR><xsl:apply-templates/></TABLE></BODY></HTML></xsl:template
><xsl:template match="Orders"><TR><TD><xsl:value-of
select="@.OrderID"/></TD><TD><xsl:value-of
select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
|||Graeme
Thanks, adding the namespace corrected the problem. Data is now returned.
Thanks again
David Roseneder

No Data returned from OPENQUERY to Active Directory

I am trying to query our active directory for user information, but even
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
Message posted via http://www.sqlmonster.com
Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
Message posted via http://www.sqlmonster.com
|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..?
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/for...topic.php?t=14

>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>
|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
Message posted via http://www.sqlmonster.com
|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:

> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> information, but even
> back. I have a linked
> I am running the
> from
> = ''User'' ')
> returned. I have
> no final resolution.
>
|||Sonya, this query would list all the GROUPs.
SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
(&(objectCategory=GROUP)); distinguishedname,CN')
You'd then have to join all the Users to what groups they are in, if
that is what youre trying to do... Bruce
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:

> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

No Data returned from OPENQUERY to Active Directory

I am trying to query our active directory for user information, but even
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
--
Message posted via http://www.sqlmonster.comAlan, not sure if it matters, but I usually do the
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,homePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
--
Message posted via http://www.sqlmonster.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/forum/viewtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
--
Message posted via http://www.sqlmonster.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> >--Original Message--
> >I am trying to query our active directory for user
> information, but even
> >though I get the query to work I am getting no data
> back. I have a linked
> >server to the active directory from SQL Server 2000 and
> I am running the
> >query in the Query Analyzer.
> >
> >My query is as follows:
> >
> >select * from openquery(adsi, 'select name, homephone
> from
> >''ldap://DC=mydomain,DC=com'' where objectclass
> = ''User'' ')
> >
> >This query will complete without error, but no data is
> returned. I have
> >seen one other question of this nature here, but I saw
> no final resolution.
> >What is wrong?
> >
> >Alan
> >
> >--
> >Message posted via http://www.sqlmonster.com
> >.
> >
>|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>sql

No Data Returned for report

Hi,

In RS 2005, if a specific query does not return any rows, the table also is not displayed in the report.

Is there any way to get around this and show an empty table?

Manish

Hi,

select all the cells and in the properties make "borderstyle" - "Solid"

and select the table and make same as above in the properties.

you can see the empty table.

Amarnath

|||

One option is to set the "NoRows" property of the table. You can also apply styles to that message.

-- Robert

|||

This is correct but only if the table has a Header or Footer row.

If the dataset associated with a table returns no data, and there is no "NoRows" message specified for the table, AND if there is a header or footer, the table will be shown.

-chris

No data retuned

Hi,
Does anyone know of a way to show a user that there is no data returned into a report from a query, I am using ASP.NET with reportviewer and if the report does not return data I want to tell the user and not let them wait for no response. Is there a method or property within crystal that I can use?CrystalViewer control provides two functions called GetCurrentPageNumber() and ShowFirstPage(). Using these two functions you could move your cursor to page 1 and then try getting Getcurrentpagenumber or either call GetCurrentPageNumber which if returns 0 then there is no data else there is a data.

Thanks

No Crosstab Like Access?

I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).

I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:

TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT
SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID)
INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID
GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN

Thanks,
CarlNot in SQL 2000 (or SQL 7.0). PIVOT supposedly works in SQL 2005, but I have not seen it/worked with it.

Look at AGS Crosstab or RAC for SQL. Both are 3rd party add-ons for SQL.

Regards,

hmscott

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

no case statement in views + sql 7

I have a query which will be a subquery that I want to use a case statement on. (It is an outer join and I want to substitue nulls for a specific value.) I can make it work in query analyzer and can also make it work in a stored procedure, but I get a message that the case statement is not supported in views. So, I tried to make a function ,but they are not supported in SQL 7, and apparently stored procedures cannot be called inline in views, either. I can retrieve the desired recordset with exec sp_name, but I need the recordset to be used in another query and cannot figure out how to use the results from a Stored procedure as a subquery in a view. Any help is greatly appreciated.

Thanks,

Tman2Case statements are supported in views, but the GUI query designer built into Enterprise Manager cannot parse and graphically display statements using CASE. You can write your query using the Query Analyzer tool instead (no serious TSQL programmer uses the GUI).
Regardless, you do not need a CASE statement to substitute NULL for a specific value. Use the NULLIF() function instead.|||I was able to create a view in query designer that uses case, but it does not function correctly. (It defaults all values to 0). However, if I use the exact same SQL statement in query designer without it being a view, it works. (ie. create view sQL statement to create view, select * from view produces erroneous results, but SQL statement in query designer works.)

I will try the nullif function you mentioned, but htat will only work for this particular cases, and there will be many instances where I will need the case statement. Any ideas what to do? This only seems to be causing a problem in SQL 7, not the newer versions...|||Post your code if you want somebody here to review it. There could be a problem with default connection settings in Query Analyzer. The SET CONCAT NULL option, for instance, has bitten me several times...|||It probably is something like that. WHEN NULL was not identifying nulls as nulls. Any ideas, or this there something wrong with my syntax (ie must use isnull like in VB)? Thanks for all your help. I have it working now, (really can just use isnull without the case statement at all) but for academic purposes, I would be intrested to know if there is an option that changes this. I did a little research on the option you pointed out above, what a #!@.* that must have been to find...

Thanks again.

Nightmare query upgrade from Access

Hi, nice to meet you all ^^

I've been given this access database to look at which could eventually be upgraded to SQL, I am conducting a kind of initital research in to how we would go about it. The previous system has developed over time and is pretty messy. I'm not really a database developer, but as everyone is busy on other things, this side project has been given to me to look at as a warm up!

Basically I've managed to upsize all the tables and their contents, and have begun copying the forms over to a .adp - creating most of the queries as stored procedures.

I'm working through cronologically, and this is the 2nd query the system requires... It was full of IIf statements which I have commented out. The adp works, but is missing a lot of data - can't really work out where it was meant to be feeding from, I personally can't stand access - but it's what the original was created in and what they want to use.

ALTER PROC [qrySaleLotEntry] @.saleid nvarchar(3)
AS
SELECT [Sale & Lot].SaleNum, [Sale Details].SaleDate, [Sale Details].SaleDesc, [Sale & Lot].LotNum,
[Sale & Lot].onsite, [Sale & Lot].StockNum, [Sale & Lot].ThisEntryFee, [Sale & Lot].EntryFeePaid,
Accounts.[4x4ENTRYFEE], Accounts.CARENTRYFEE, [Sale & Lot].Vendor, Accounts.VENDORDESC, Accounts.ACCOUNTNAME,
Accounts.[GROUP], Accounts.CONTACT, Accounts.PHONE, Accounts.MOBILPHONE, Accounts.VATNO, Accounts.[e-mail],
[Sale & Lot].RegYear, [Sale & Lot].RegLetter, [Sale & Lot].RegDate, [Sale & Lot].RegNum, [Sale & Lot].ManuYear,
[Sale & Lot].Manufacturer, [Sale & Lot].Model, [Sale & Lot].[Type], [Sale & Lot].CAPCode, [Sale & Lot].Colour,
[Sale & Lot].MileageNum, [Sale & Lot].Kilometres, [Sale & Lot].Warranted, [Sale & Lot].ServiceHistory,
[Sale & Lot].ServiceNum, [Sale & Lot].ServiceMileage, [Sale & Lot].ServiceDate, [Sale & Lot].MoTDay, [Sale & Lot].MoT,
[Sale & Lot].Tax, [Sale & Lot].ReservePrice, [Sale & Lot].VATCode, [Sale & Lot].CAPValueClean, [Sale & Lot].CAPValueAve,
[Sale & Lot].CAPValuePoor, [Sale & Lot].AgrNum, [Sale & Lot].Extra1, [Sale & Lot].Extra2, [Sale & Lot].Extra3,
[Sale & Lot].Extra4, [Sale & Lot].Extra5, [Sale & Lot].Extra6, [Sale & Lot].Extra7, [Sale & Lot].Extra8,
[Sale & Lot].Extra9, [Sale & Lot].Extra10, [Sale & Lot].ASSEEN, Accounts.ASSEEN, Accounts.UNWARRANTED,
[Sale & Lot].NoMMF, [Sale & Lot].UNROADWORTHY, [Sale & Lot].PRESALEHPI, [Sale & Lot].[COLLECTION],
Accounts.[CollectionRequired?], [Sale & Lot].TotalLoss, [Sale & Lot].Accident, [Sale & Lot].FinanceOwed,
[Sale & Lot].Taxi, [Sale & Lot].V5, [Sale & Lot].V5Part2, [Sale & Lot].PlateTfr, [Sale & Lot].PlateTfrCharge,
Accounts.PlateTfrCharge, [Sale & Lot].PlateTfrPaid, [Sale & Lot].Fuel, [Sale & Lot].FuelCharge, Accounts.FuelCharge,
[Sale & Lot].PreSalePrep, [Sale & Lot].PreSalePrepCharge, Accounts.PreSalePrepCharge, [Sale & Lot].Misc1,
[Sale & Lot].Misc1Desc, [Sale & Lot].Misc1Charge, [Sale & Lot].Misc2, [Sale & Lot].Misc2Desc, [Sale & Lot].Misc2Charge,
[Sale & Lot].DeliveryIn, [Sale & Lot].DeliveryInCharge, Accounts.DeliveryInCharge, [Sale & Lot].DeliveryOut,
[Sale & Lot].DeliveryOutCharge, Accounts.DeliveryOutCharge, [Sale & Lot].WashOff, [Sale & Lot].WashOffCharge,
Accounts.WashOffCharge, [Sale & Lot].Polish, [Sale & Lot].PolishCharge, Accounts.PolishCharge, [Sale & Lot].ValetA,
[Sale & Lot].ValetACharge, Accounts.ValetACharge, [Sale & Lot].ValetB, [Sale & Lot].ValetBCharge,
Accounts.ValetBCharge, [Sale & Lot].ValetC, [Sale & Lot].ValetCCharge, Accounts.ValetCCharge, [Sale & Lot].Delogo,
[Sale & Lot].DelogoCharge, Accounts.DelogoCharge, [Sale & Lot].ENGINEER, [Sale & Lot].EngineersCharge,
Accounts.EngineersCharge, [Sale & Lot].Repairs, [Sale & Lot].RepairsCharge, Accounts.RepairsCharge,
[Sale & Lot].Provisional, [Sale & Lot].Purchaser, Accounts_1.ACCOUNTNAME, Accounts_1.CONTACT, Accounts_1.PHONE,
Accounts_1.MOBILPHONE, Accounts_1.[e-mail], [Sale & Lot].SalePrice, [Sale & Lot].Nett, [Sale & Lot].VAT,
[Sale & Lot].VATDesc, [Sale & Lot].TransInbound, [Sale & Lot].TransInboundPaid, [Sale & Lot].TransReturn,
[Sale & Lot].TransReturnPaid, [Sale & Lot].[Transfer Complete], [Sale & Lot].datetostock, [Sale & Lot].Reentry,
[Sale & Lot].ChassisNum, [Sale & Lot].Comments, [Sale & Lot].PackNumber, [Sale & Lot].MissedDeadline,
[Sale & Lot].Created, [Sale & Lot].CreatedBy, [Sale & Lot].Modified, [Sale & Lot].ModifiedBy, Accounts.TradeStatus,
[Sale & Lot].VIN, [Sale & Lot].PrevVRM, [Sale & Lot].DateVRMChanged, [Sale & Lot].ExpModel, [Sale & Lot].EngineNum,
[Sale & Lot].OrigCol, [Sale & Lot].NumPrevCols, [Sale & Lot].DateLastColChange, [Sale & Lot].PrevCol,
[Sale & Lot].AgreementType, [Sale & Lot].AgreementTerm, [Sale & Lot].AgreementDate, [Sale & Lot].FinanceCo,
[Sale & Lot].FinanceTel, [Sale & Lot].FinanceAgrNum, [Sale & Lot].FinanceDesc, [Sale & Lot].PoliceForce,
[Sale & Lot].PoliceTel, [Sale & Lot].PoliceReportDate, [Sale & Lot].RiskCompany, [Sale & Lot].RiskTel,
[Sale & Lot].RiskRef, [Sale & Lot].RiskPeriod, [Sale & Lot].RiskInterestDate, [Sale & Lot].RiskType,
[Sale & Lot].RiskOther, [Sale & Lot].ConditionInsurer, [Sale & Lot].ConditionClaimNum, [Sale & Lot].ConditionTel,
[Sale & Lot].ConditionMIAFTRDate, [Sale & Lot].ConditionMake, [Sale & Lot].ConditionModel, [Sale & Lot].KeeperPrevNum,
[Sale & Lot].KeeperChangeDate, [Sale & Lot].KeeperDateAcquired, [Sale & Lot].KeeperDateDisposed, [Sale & Lot].Scrapped, [Sale & Lot].Exported
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
WHERE dbo.[Sale & Lot].SaleNum = @.saleid
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;

/*
IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,
IIf([Accounts.VENDORDESC] Not Like "","Direct from") AS DirectFrom, [Sale & Lot.Manufacturer] & " " & [Sale & Lot.Model] & " " & [Sale & Lot.Type] AS [Desc], StrConv([sale & lot.RegNum],1) AS Reg,
IIf([accounts.unwarranted]=-1,"Unwarranted - Company Policy",
IIf([sale & lot.warranted] Like "1",[sale & lot.MileageNum] & " " & IIf([sale & lot.kilometres]=-1,"kms ") & "Warranted",
IIf([sale & lot.warranted] Like "2","Unwarranted",IIf([sale & lot.warranted] Like "3","Incorrect")))) AS MileageStatus,
IIf([Sale & Lot.VATCode] Like 1,"Plus VAT",
IIf([Sale & Lot.VATCode] Like 2,"No VAT",
IIf([Sale & Lot.VATCode] Like 3,"Inc VAT"))) AS VATStatus,
IIf([sale & lot.v5]=-1,"V5 here",IIf([sale & lot.v5part2]=-1,"V5/2 here",IIf([sale & lot.v5]=0 And [sale & lot.v5part2]=0,"V5 not here"))) AS V5here, IIf([Sale & Lot.Tax] Is Not Null,"Tax " & [Sale & Lot.Tax],"No Tax") AS Taxhere, IIf([sale & lot.MoT] Is Not Null,"MoT " & [sale & lot.MoTDay] & " " & [sale & lot.MoT],"No MoT") AS MoThere, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No Service History",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Service History",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS Services, IIf([Sale & Lot.ASSEEN]=-1,"As Seen",IIf([Sale & Lot.NoMMF]=-1,"No Major Mechanical Faults",IIf([Sale & Lot.UNROADWORTHY]=-1,"Unroadworthy"))) AS MechDesc, IIf([Sale & Lot.PrevLotNum]>0,[Sale & Lot.PrevSaleNum] & "/" & [Sale & Lot.PrevLotNum],"") AS Prev, IIf([sale & lot.MoT] Is Not Null,Trim([sale & lot.MoTDay] & " " & [sale & lot.MoT]),"No") AS PendragonMoT, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Yes",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS PendragonServiceHist, IIf([Sale & Lot.ServiceMileage] Is Null,"","(Last Serviced " & [Sale & Lot.ServiceMileage] & ", " & [Sale & Lot.ServiceDate] & ")") AS LastServiced, IIf([Sale & Lot.Tax] Is Not Null,[Sale & Lot.Tax],"No") AS PendragonTax, Trim([Sale & Lot]![Extra2] & [Sale & Lot]![Extra3] & [Sale & Lot]![Extra4] & [Sale & Lot]![Extra5] & [Sale & Lot]![Extra6] & [Sale & Lot]![Extra7] & [Sale & Lot]![Extra8] & [Sale & Lot]![Extra9] & [Sale & Lot]![Extra10] & IIf([Sale & Lot]![TotalLoss]=-1," Total Loss") & IIf([Sale & Lot]![Accident]="No",""," " & [Sale & Lot.Accident]) & IIf([Sale & Lot]![Taxi]="No",""," " & [Sale & Lot.Taxi])) AS PendragonExtras, IIf([sale & lot.v5]=-1,"YES","NO") AS LogBook, IIf([sale & lot.Extra2] Like " Service History,","YES","NO") AS ServiceHistCondRep
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;

*/

I've commented out the IIf statements. I began by trying to convert them in to CASE statements and such - but frankly can't get my head around that.

Getting back to basics... I suppose the question would be: I am upsizing from access to SQL, what the heck do I do with all these IIf statements?IS your question how to change IIF(Experssion,true,false) to a case statement

iif(1=2,'One=Two','ONE<>TWO')

would return 'ONE<>TWO'

case when 1=2 then 'One=Two' else 'ONE<>TWO' end

would return 'ONE<>TWO'

Also

IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,

CASE WHEN SALEDESC like 'C%' then lotnum + 'C' else lotnum + 'F' end|||Thanks - that's a starting step for me!

Nightmare Query

It's probably a piece of cake for those in the know but it's proving hard
work for me...
Scenario: I developing a report for a system that managed a filter exchange
business. Basically filters are sent to/from customers to our business. I'm
trying to produce a management report which summarises for each customer
depot, which filter types (PartNo) have we sent them and which have they
sent back.
The desired report is probably best explained by example:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
[Yeah, it looks a bit crap on usenet, but it looks better if you pop it in
Excel]
As you can see not all PartNos may be listed - some may not have been sent
or received within the reporting time period. I dont want to show these
PartNos in such a case.
I have developed a query that lists the totals for each Depot for each
PartNo, eg.
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
etc
SQL:
Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTotal
from
(Select L.LocationID, S.PartNo, Count(*) as NumSent
from StockMovements S
inner join Locations L on L.LocationID = S.LocationTo
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Sent
join
(Select L.LocationID, S.PartNo, Count(*) as NumRecd
from StockMovements S
inner join Locations L on L.LocationID = S.LocationFrom
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Recd
on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
[Note: a 'Location' is a Customer Depot]
I'm not sure how to take this query on further to provide the end result.
I'm not sure if some quasi-temporary tables are needed to store some
intermediate data, or whether it can be acheived with other means (ie a
View).
Any suggestions?
Thanks in advance.
CJM
Trimmed DDL:
CREATE TABLE [dbo].[Locations] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[SerialNo] [int] NOT NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PartMaster] (
[PartID] [int] IDENTITY (1, 1) NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartType] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartDesc] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
[Note: In case it's not obvious, PartMaster contains details on all
available PartNos]
If anything else is missing let me know...
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]First problem i am having in looking at this that no PartNo in
[StockMovements] so you query should be failing based on what you told
us your table creates are becuase you are pulling PartNo from
[StockMovements]
What is the field that holds the part number in [StockMovements]? Could
you change [StockMovements] or is it used buy other items?|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138296034.089998.119680@.g43g2000cwa.googlegroups.com...
> First problem i am having in looking at this that no PartNo in
> [StockMovements] so you query should be failing based on what you told
> us your table creates are becuase you are pulling PartNo from
> [StockMovements]
> What is the field that holds the part number in [StockMovements]? Could
> you change [StockMovements] or is it used buy other items?
>
Sorry - Trimmed a bit too enthusiastically... PartNo *IS* in StockMovements
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[Ref] [int] NULL ,
[SerialNo] [int] NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL ,
) ON [PRIMARY]
GO|||In the two examples that you gave:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
and
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
are different.
what are the heading in the report?
Is this how you want the data back. IE: Location ID, Part Number, Parts
Sent Out, Part Received Back, Total|||On Thu, 26 Jan 2006 16:32:21 -0000, CJM wrote:
(snip)
>I have developed a query that lists the totals for each Depot for each
>PartNo, eg.
>C1D1, PartNo1, 1
>C1D1, PartNo28, -1
>C1D2, PartNo7, -10
>etc
>SQL:
>Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTota
l
> from
> (Select L.LocationID, S.PartNo, Count(*) as NumSent
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationTo
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Sent
> join
> (Select L.LocationID, S.PartNo, Count(*) as NumRecd
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationFrom
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Recd
> on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
>[Note: a 'Location' is a Customer Depot]
>I'm not sure how to take this query on further to provide the end result.
Hi CJM,
First, I note that this query will only include parts that are in both
the Sent and the Recd derived table for the same LocationID. If there is
no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
'123' and LocationFrom = 14 will be excluded from the results. If that's
not what you intended, you'll have to replace the JOIN with a FULL OUTER
JOIN and add a bunch of COALESCE functions on the first SELECT line.
Second, I wonder why the PartNo column is called a number but declared
as character, why it is not a key, and why it is used to reference parts
instead of the PartID column that IS a key.
Third, I recommend you to rewrite the query above to the (slightly)
simpler query below:
SELECT L.LocationID, P.PartNo,
COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID, P.PartNo
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Now you also wanted the part numbers to be columns instead of rows. That
is usually called a crosstab or pivot - and the best way to do it is to
transform the table on the client side.
If you have to do it on the server side, AND you're on SQL Server 2005,
you can also check out the new PIVOT operator. For SQL Server 2000,
google this group for "CROSSTAB" to find some common kludges. Note
however that all these crosstabl techniques require you to know the
columns in advance - and if I understand your question correctly, you
want to be able to dynamically include or exclude columns, based on the
query results.
I know of no supported way to create a dynamic crosstab on the server.
You might want to try your luck with dynamic SQL - but ungh!! You might
also want to investigate if there are third-party products that can help
you out. Rac4SQL is often recommended in this group - though admittedly
always by the same poster, who probably works for the company that sells
Rac4SQL. I have no personal experience with either Rac4SQL or any other
third party product for crosstabbing.
Hugo Kornelis, SQL Server MVP|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138297710.003578.172650@.g14g2000cwa.googlegroups.com...
> In the two examples that you gave:
> Customer, Depot, PartNo1, PartNo7, PartNo28, Total
> Cust1, C1D1, 1, null, -1, 0
> Cust1, C1D2, null, -10, null, -10
> Cust2, C2D1, 1, -2, -3, -4
> Cust3 C3D1, null, null, 1, 1
> Total, null, 2, -12, -3, -13
>
> and
> C1D1, PartNo1, 1
> C1D1, PartNo28, -1
> C1D2, PartNo7, -10
>
> are different.
The first example is the output I want, the second is the data that results
in that output. The columns for that data are: DepotID, PartNo,
'NetTotalSent' (ie Total sent to that depot - Total received from that
depot)
You can see that for Depot C1D1, they have received 1 x PartNo1 and returned
1 x PartNo28. The net total is therefore 0. [See line 1 of the example
output]

> what are the heading in the report?
The headings are on the first line: Customer, Depot, PartNo1, PartNo7,
PartNo28, Total. Depending on when you run the report you may have more or
less columns - it depends on which parts have been shipped/returned in that
time period.

> Is this how you want the data back. IE: Location ID, Part Number, Parts
> Sent Out, Part Received Back, Total
>
No - see above.
I realise that this is quite confusing; and I'm trying to trade-off between
swamping you with too much superfluous detail, and not providing enough to
mek it clear. I hope this somewhat clairifies it.
Thanks|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...

> Hi CJM,
> First, I note that this query will only include parts that are in both
> the Sent and the Recd derived table for the same LocationID. If there is
> no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
> '123' and LocationFrom = 14 will be excluded from the results. If that's
> not what you intended, you'll have to replace the JOIN with a FULL OUTER
> JOIN and add a bunch of COALESCE functions on the first SELECT line.
>
TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
COALESCE functions that are needed? (And why?)

> Second, I wonder why the PartNo column is called a number but declared
> as character, why it is not a key, and why it is used to reference parts
> instead of the PartID column that IS a key.
Historical reasons. Many of these PartNos pre-date all our computer systems,
and I suspect some pre-date civilisation itself (as do some of the people in
charge here!)
Some PartNo's are indeed integers but most are alpha numerical. The PartID
was introduced to be a numerical key, but is under-used. In retrospect, I
would have stuck with PartNo as the key.

> Third, I recommend you to rewrite the query above to the (slightly)
> simpler query below:
>
It *is* simpler, and a lot neater too. I'll digest it (and probably use it
too - thanks)
Update: I've tried it and it works a treat (and the results are more like
what I was expecting)

> SELECT L.LocationID, P.PartNo,
> COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
> FROM Locations AS L
> CROSS JOIN PartMaster AS P
> LEFT JOIN StockMovements AS Sent
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationTo = L.LocationID
> LEFT JOIN StockMovements AS Recd
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationFrom = L.LocationID
> WHERE Sent.PartNo IS NOT NULL
> OR Recd.PartNo IS NOT NULL
> GROUP BY L.LocationID, P.PartNo
> (untested - see www.aspfaq.com/5006 if you prefer a tested solution)
> Now you also wanted the part numbers to be columns instead of rows. That
> is usually called a crosstab or pivot - and the best way to do it is to
> transform the table on the client side.
> If you have to do it on the server side, AND you're on SQL Server 2005,
> you can also check out the new PIVOT operator. For SQL Server 2000,
> google this group for "CROSSTAB" to find some common kludges. Note
> however that all these crosstabl techniques require you to know the
> columns in advance - and if I understand your question correctly, you
> want to be able to dynamically include or exclude columns, based on the
> query results.
>
Yeah, I've done cross-tabs in SQL before, but as you say, the columns need
to be fixed (defined in advance). But the specification for my report *does*
ask for dynamically included and excluded columns... hmmmm...

> I know of no supported way to create a dynamic crosstab on the server.
> You might want to try your luck with dynamic SQL - but ungh!! You might
> also want to investigate if there are third-party products that can help
> you out. Rac4SQL is often recommended in this group - though admittedly
> always by the same poster, who probably works for the company that sells
> Rac4SQL. I have no personal experience with either Rac4SQL or any other
> third party product for crosstabbing.
>
I've heard of these dynamic SQL techniques, but never actually used them.
I've always found a way around them (ie not using them), but I thought one
of you guys might have a really simple and effective technique (wishful
thinking, it appears).
Again I've heard about Rac4SQL, but have no experience either.
If I could get ALL PartNos listed for all depots, it would be a big
resultset with lots of zeros/nulls, but it would probably do as a starting
point. I've tried adapting my query, linking it in to the PartMaster table,
but I can't get it to returns results for all part regardless of whether
they have been used in the reporting period. Any suggestions?

> --
> Hugo Kornelis, SQL Server MVP
Thanks Hugo, you've been a great help.
Chris|||On Fri, 27 Jan 2006 10:08:37 -0000, CJM wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
> news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
>
>TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
>COALESCE functions that are needed? (And why?)
Hi CJM,
Replace
Select Sent.LocationID, Sent.PartNo, (...)
with
Select COALESCE(Sent.LocationID, Recd.LocationID) AS LocationID,
COALESCE(Sent.PartNo, Recd.PartNo) AS PartNo,
(...)
This is needed because the FULL OUTER JOIN can cause either one (but not
both) of Sent.LocationID and Recd.LocationID to be NULL.
(snip)
>If I could get ALL PartNos listed for all depots, it would be a big
>resultset with lots of zeros/nulls, but it would probably do as a starting
>point. I've tried adapting my query, linking it in to the PartMaster table,
>but I can't get it to returns results for all part regardless of whether
>they have been used in the reporting period. Any suggestions?
For a static crosstab, you create a CASE expression inside an aggregate
for each part. You'll have to change the query each time a part is added
to your catalog.
Something like this:
SELECT L.LocationID,
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) AS Part1,
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) AS Part2,
..
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) AS Part9999
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID
(untested, since you didn't post a repro script)
SQL Server 2005 introduces new techniques for crosstabbing, but I can't
offer any code as I haven't yet had a chance to play with them. If you
have SQL Server 2005, check PIVOT in Books Online.
Hugo Kornelis, SQL Server MVP

NHibernate, Views and SQL Server

Hi there,

First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.

Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?

Third, would it be better to create our crystal report using Views to improve performance?

Thanks for anyone who participates in this thread! :-)Views rarely speed anything up (partioning is a special case). Indexed views can help by providing a second pseudo clustered index. Generally views are to make life easy rather than faster, I'd avoid them wherever possible.

Next_run_time and next_run_date of sysjobschedules

I have written some code to query the next_run_time and next_run_date of the sysjobschedules table. It works well with the exception that when a new schedule is created or an existing one is changed, these two columns are set to zero. They will eventually be initialized to the appropriate values but that may take a while. That means that the code will not work until then.

Is there a way to FORCE the initialization of the two columns ?How does your code looks like?
Tried initializing those 2 values in the code itself?|||The code is simply selecting the two columns into variables and then do further processing.

The problem is the two columns (next_run_date and next_run_time) of sysjobschedules table are set to zero whenever a new schedule is created or changed. You can try it yourselef.

Create a job (xxxxx) with a schedule.

select a.name, b.name,next_run_date, next_run_time from msdb..sysjobs a inner join msdb..sysjobschedules b
on a.job_id=b.job_id
where a.name='xxxxx'

and you will see those two columns are zeros.

However, after a while they will updated to the appropriate values by SQL.

My question, how do we force SQL to update these two columns immediately? It seems SQL keeps them in cache somewhere and periodically updates the sysjobschedules table.|||True, what you said is right.

I believe as soon as the job finishes the execution these values will be updated internally and only with manual updation you can set other values if required.

May refer to book 'Inside SQL Server 2000' by Kalen Daleney.

next/previous record

Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek.

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID = 10)

See if this article helps you:

http://www.xaprb.com/blog/2006/04/28/how-to-find-next-and-previous-records-in-sql/

|||

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID <> 10)

HTH

Next/Prev record

If you have an identity column (i.e. 1-N) and you have 5, what is the most
efficent query to get the next (and prev) row? Gaps could happen so there
may not be a 6, but the next in order may be 8 for example. TIA
William Stacey [MVP]I usually use these:
Prev:
SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
Next:
SELECT TOP 1 key FROM table WHERE key > current ORDER BY key
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>|||Thanks Remus.
William Stacey [MVP]
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:euthl7jTGHA.2156@.tk2msftngp13.phx.gbl...
|I usually use these:
| Prev:
| SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
| Next:
| SELECT TOP 1 key FROM table WHERE key > current ORDER BY key|||William
create table William(c1 int NOT NULL primary key)
go
insert into William values (1)
insert into William values (2)
insert into William values (3)
insert into William values (4)
insert into William values (5)
insert into William values (6)
declare @.d as int
set @.d=4
select top 1 *,(select top 1 * from William where c1 >@.d order by c1
asc)as n
from William where c1 <@.d order by c1 desc
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>