database errors: February 2012

Saturday, February 25, 2012

Crystal 4.6 Question

this is my code, and when It comes to show the report on the screen nothing happens:

CrystalReport1.Connect = "Provider = Microsoft.Jet.OLEDB.4.0;" &
App.Path & "\config.mdb;UID=sa;PWD=12345;"
Me.CrystalReport1.DataFiles(0) = App.Path + "\db.mdb"

CrystalReport1.ReportFileName = App.Path + "\boent_rep.rpt"
CrystalReport1.Destination = crptToWindow
CrystalReport1.DiscardSavedData = True

CrystalReport1.ReportFileName = "c:\lm_cliente.rpt"
CrystalReport1.SelectionFormula = formula 'whatever formula is
CrystalReport1.Action = 1

After the last line I have checked the .status property being the result -1. I know that after a successful display the result in .status is 3.

What is the .status property means, what are the possible results. What does it means if a -1 result is what I get? Please help :D*bump*|||Did you know the answer?

Crystal 4.6 connection to Access 2000?

I have a VB6 package that uses CR 4.6 and has installed successfully on numerous PCs under all versions of Windows (97 to XP).
Now I when I run the package on my new laptop and try to print a report, I get error error 2147417848 - disconnected from client.
Moreover, although the CR report designer recognises the rpt files, the preview window gives an error. I'm assuming connecting to the database is the problem. The new laptop has no problem with any other software or with printing in general.
I have tried re-installing VB6 and Crystal Reports, re-registering crystl32.ocx, tried the Dependency Walker from Crystal, spent hours trawling the web, all to no avail.
Any bright ideas out there?Are you able to open the report?
Do verify database

Crystal 2 ReportingServices

Does anybody know a free tool to convert Crystal Reports to Reporting
Services ?
Thanks ins advance,
Tito.On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> Does anybody know a free tool to convert Crystal Reports to Reporting
> Services ?
> Thanks ins advance,
> Tito.
Unfortunately, I have not found any free conversion options. Here are
a few that can do the conversion for a price though:
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
http://www.rpttordl.com/
Sorry I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you !
"EMartinez" <emartinez.pr1@.gmail.com> escreveu na mensagem
news:1181100921.907554.304710@.p77g2000hsh.googlegroups.com...
> On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
>> Does anybody know a free tool to convert Crystal Reports to Reporting
>> Services ?
>> Thanks ins advance,
>> Tito.
>
> Unfortunately, I have not found any free conversion options. Here are
> a few that can do the conversion for a price though:
> http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
> http://www.rpttordl.com/
> Sorry I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 6, 8:22 am, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> Thank you !
> "EMartinez" <emartinez...@.gmail.com> escreveu na mensagemnews:1181100921.907554.304710@.p77g2000hsh.googlegroups.com...
> > On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> >> Does anybody know a free tool to convert Crystal Reports to Reporting
> >> Services ?
> >> Thanks ins advance,
> >> Tito.
> > Unfortunately, I have not found any free conversion options. Here are
> > a few that can do the conversion for a price though:
> >http://www.microsoft.com/sql/technologies/reporting/partners/crystal-...
> >http://www.rpttordl.com/
> > Sorry I could not be of greater assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Crystal 10 Using ASP page for datebase connections

I hope someone can help me get started on this. I wish I knew all of the terminology to use for this, but I have limited knowledge of Crystal Enterise 10. I am trying to figure this out as the person who created it is no longer around. He is my problem. I have a Crystal report ( Crystal 10) that has two subreports. The main report and the two subreports are all from different datasources. The report runs fine on my machine using the Crystal application. The problem comes in when I run it via Internet Explorer. There is an ASP page that is used to pass the connection information to the Crystal report. It works fine via the web with only one subreport. I can not figure out how to add the third datasource to the ASP page. I will include the ASP page that is used for the two current connections. What I need to know is how to add the third datasource connection to the existing information. I am going to attach a PDF of the code. Thank you for your help.

<%@. Language=VBScript CodePage=65001 %>
<%

'=======================================================================
' CRYSTAL ENTERPRISE REPORT APPLICATION SERVER (CE EMBEDDED) 10
' Purpose: Demonstrate how to change the datasource location at runtime
' Special Note* - ODBC Datasources
'=======================================================================

' This line creates a string variable called reportName that we will use to pass
' the Crystal Report file name (.rpt file) to the OpenReport method.

reportname = "Reports/"&Request("CRReport")

'============================================================================
' CREATE THE REPORT CLIENT DOCUMENT OBJECT AND OPEN THE REPORT
'============================================================================

' Use the Object Factory object to create other RAS objects (useful for versioning changes)
Set objFactory = CreateObject("CrystalReports.ObjectFactory")

' This "While/Wend" loop is used to determine the physical path (eg: C:\) to the
' Crystal Report .rpt by translating the URL virtual path (eg: http://Domain/Dir)
Dim path, iLen

path = Request.ServerVariables("PATH_TRANSLATED")

While (Right(path, 1) <> "\" And Len(path) <> 0)
iLen = Len(path) - 1
path = Left(path, iLen)
Wend

' Create a new ReportClientDocument object
Set Session("oClientDoc") = objFactory.CreateObject("CrystalClientDoc.ReportClientDocument")

' Specify the RAS Server (computer name or IP address) to use (If SDK and RAS Service are running on seperate machines)
Session("oClientDoc").ReportAppServer = "localhost"

' Open the report object to initialize the ReportClientDocument
Session("oClientDoc").Open path & reportName

'===============================================================
' CHANGING THE MAIN REPORT DATABASE INFO
'===============================================================
Set oDBInfo = Session("oClientDoc").DatabaseController.GetConnectionInfos().Item(0)
oDBInfo.UserName = "sa"
oDBInfo.Password = "blackfin"

'Get the collection of tables in the main report
Set Tables = Session("oClientDoc").DataDefController.Database.Tables
For Each table in Tables

'clone the table object
Set newTable = Table.Clone

'set the table's connectionInfo to the current connection info
newTable.ConnectionInfo = oDBInfo

'set the table object qualified name to include the new database name
'i.e. original = 'db1.dbo.myTable', new = 'db2.dbo.myTable'
'newTable.QualifiedName = Database & ".dbo." & Table.Name

'put this newly modified table object back into the report client doc
Session("oClientDoc").DatabaseController.SetTableLocation table, newTable

Next

'===============================================================
' CHANGING THE DATABASE FOR ALL SUBREPORTS
'===============================================================

'get a collection of subreport names
Set subReportNames = Session("oClientDoc").SubReportController.QuerySubreportNames

For each subName in subReportnames

'we can't reference a subreport table object directly, so we get the collection of tables first
Set subTables = Session("oClientDoc").SubReportController.GetSubreportDatabase(subName).Tables

For each subTable in subTables

'clone the subreport table object
Set newSubTable = subTable.Clone

'set the subreport table's connectionInfo to the current connection info
newSubTable.ConnectionInfo.UserName = "william_l"
newSubTable.ConnectionInfo.Password = "bdl700"

'set the table object qualified name to include the new database name
'i.e. original = 'db1.dbo.myTable', new = 'db2.dbo.myTable'
'newSubTable.QualifiedName = Database & ".dbo." & subTable.Name

'put this newly modified table object back into the report client doc
Session("oClientDoc").SubReportController.SetTableLocation subName, subTable, newSubTable
'on error resume next
Next
Next

'============================================================================
' CHOOSING THE REPORT VIEWER
'============================================================================
'
' There are four Report Viewers:
' 1. Crystal Reports Interactive Viewer (CrystalReportsInteractiveViewer.asp)
' 2. Crystal Reports Viewer (CrystalReportsViewer.asp)
' 3. Crystal Reports Parts Viewer (CrystalReportsPartsViewer.asp)
' 4. Legacy ActiveX Viewer (ActiveXViewer.asp)
'
' Note that to use this these viewers you must have the appropriate .asp file in the
' same virtual directory as the main ASP page. Choose from one of the four viewers below,
' simply uncomment the one you want to use:

' *** Crystal Reports Interactive Viewer ***
Response.Redirect "CrystalReportsInteractiveViewer.asp"

' *** Crystal Reports Viewer ***
'Response.Redirect "CrystalReportsViewer.asp"

' IMPORTANT NOTE - To use the report parts viewer successfully you are required to
' choose and name three objects in the report to Node1, Node2 and Node3.
' You can access an objects name by using the Format Editor dialog box.
' For more information on the Format Editor Dialog Box and setting objects
' names, please refer to the Help Contents (Help Menu->Crystal Reports Help)
' or by pressing F1

' *** Crystal Reports Parts Viewer ***
'Response.Redirect "CrystalReportsPartsViewer.asp"

' IMPORTANT NOTE - The ActiveXviewer does NOT have the ability to prompt for parameters,
' selection formulas, or login information. If your report requires this information
' to run, look at the the appropriate code samples to pass this information before
' using this viewer.

' *** Legacy ActiveX Viewer ***
'Response.Redirect "ActiveXViewer.asp"

'=============================================================================

%>
%>Hi, Does anyone have an ideas? Does my question make sense?
Thank you|||What I see is that the line that change to new database location is a comment

'newSubTable.QualifiedName = Database & ".dbo." & subTable.Name|||Thank you jggtz for the reply.
Would the Database piece be the ODBC name? What needs to be modified on the ".dbo." section? Does the subTable.Name stay like it is?

You don't know how much I appreciate your help.

Thank you.|||Maybe this can help -->
http://www.codeproject.com/aspnet/crystalwithaspdotnet.asp?df=100&forumid=188132&exp=0&select=1455404

Crystal 10 multi-threading?

I have a debug windows service which picks up records from a database and based on some flags runs few hundreds reports. Reports are created not by odbc connection but accessing a .dll which has a dataset and so on. Not a very nice solution to write 30 pages of sql instead of using stored procedures... anyway.
For some reason not all threads are running at the same time, for some reason second thread won't start until first one releases CR object. My question is... does Crystal Report 10 support multithreading?? Almost forgot... objects used: craxdrt.dll

Thanks,

RobertTry searching in http://support.businessobjects.com

Crystal 10 grouping layout issue

I need some assistance with some layout issues I am having with a report. I have searched through help for the software as well as a book I have and so far having absolutely no luck in fixing my problem!

I have a report that groups by Country and then by site (multiple sites within a country) and I have totals(summaries) for each of those categories. The problem that it is splitting up in the middle of the detail data for site groupings from the end of one page and continuing to the top of another page. I am wanting to keep all the site detail data together for any one site and not have it split between pages in the middle of the data. I have made sure on the group expert and section expert that "keep together" is checked and in the group footer 1(country) in the section expert I have checked New Page After with the formula Not OnLastRecord which helps in the differentiation between the countries for layout, but not sure what else I can do to keep site detail data together. I am wracking my brain and I have tried everything I already know how to do and absolutely having no luck.

Help a girl out please!

Thanks!

StacyDid you enable "keep together" option for both Country and Site groups? This should provide you with enough protection against splitting details. This option will also keep together group headers and footers where the summary fields are usually displayed.
Again, you should use "keep together" option in groups, not sections, because keeping together sections will not keep together your group summaries.

When kept together, the group will only be split if it cannot fit into a single page. In rare occasions, though, the summary for such group can still be moved to the next page, leaving all detail rows to prevous page, *IF* the detail rows will occupy exactly 1 page. I call it orphaned summary. There are 2 workaround technics for avoiding orphaned footers in Crystal knowledge base:

http://support.businessobjects.com/library/kbase/articles/c2000973.asp
http://support.businessobjects.com/library/kbase/articles/c2004981.asp

Hope it helps.|||I did uncheck "Keep Together" in all of the group sections and in the details section. I kept the "Keep Together" checked on the group expert for each grouping and that seemed to resolve things for two of the reports, however there is still one report where the details are still splitting up. It's one of the bigger reports of course so not sure if it's just a matter of size, guess I will find out as those reports grow as to whether or not the issue is really fixed with them. No issues with orphaned/widowed summaries though.

Essentially here is what it would look like and it seems to only be a problem on the first page with that includes the report header, the rest of the report is just fine with no splitting of details.

Top of page 1
RH
PH
GH1 - Country
GH2
D
D
D
GF2
End of page 1
GH2
D - Site, patient
D - Site, patient
D - Site, patient
PF
Top of Page 2
PH
D - Site, patient
D - Site, patient
GF2 - summary of sites

No idea if this helps at all. Group Footer 1 is hidden as is Report Footer|||Two things I would try - first, remove "keep together" for outer group, as this group cannot be kept together anyway. Second, I would create a new report based only on couple of tables used for grouping. Then I would insert the same 2 groups and see if the behaviour will be the same.

BTW, you have CR10 SP4 applied, don't you?|||Okay removing the "Keep Together" on the outer group seems to have taken care of it. Still having our IT folks install the service pack regardless. Thanks for the tip on that and for all the help. Literally I had been working on this for several weeks and was not about to give up. Again thank you so much!

Stacy|||No prob :D

crystal & SQL Server

Hi ,
I am new to Crystal reports and I wana use CrystalReports using SQL SERVER .
How can i create DSN with SQL Server ?
pls let me know
Thx and Regards
SamDisregard this if you think I have mistaken your question. I think you want to be able to get date from SQLServer database using CR. If so, here it goes, assuming you're using window XP:

1. Make sure you can connect to the SQL database. To check this, go to Control panel => Administrative Tools => Data source (ODBC) => check on either User DSN, System DSN or File DSN tab to see if your database show. If it doesn't, you can add new. I can't help you there because I don't know how you setup securities & stuff.

2. Once it shows, you can open up CR. Create new connection, go to ODBC sources, your SQL database name should show up here. If not, you did not configure / connect the SQL properly. Go back to step 1.

3. Select the database / tables that you need.

Good luck.|||Thx thg for your reply.
I would like to work on Stored procudures in SQL Server.. Is there any Ref-Cursor's available in SQL Server as Oracle ?
which is best suggested books Online for Crystal Reports and SQL Server ?
Thx and Regards
Sam|||Make sue of help files that are part of CR and SQL Server

Cryptography API for MS SQL Server

Hi,

I'm developing a software that makes a lot of access to a MS SQL
Server. All information in the data base is decrypted and encrypt upon
each read and write access -- this is causing a big overload of CPU in
my system. I'd like to implement something to make it faster so I
wondered if there is any MS SQL Server API that would make the
cryptography transparent for the application. Another solution would be
setting up an encrypted file system so I could unmount
the device when the application finishes. The last one is not optimal
though, since the encrypted device would have read access while
mounted.

So, I'd like to get some suggestions about tools, api's and probably
other better solutions. Any information would be of great help.

The software runs on Windows 2k, Windows XP, Windows 2k3, Windows NT
and probably would have to be ported to Vista.

thanks a lot in advance for any help,

sSav wrote:

Quote:

Originally Posted by

Hi,
>
I'm developing a software that makes a lot of access to a MS SQL
Server. All information in the data base is decrypted and encrypt upon
each read and write access -- this is causing a big overload of CPU in
my system. I'd like to implement something to make it faster so I
wondered if there is any MS SQL Server API that would make the
cryptography transparent for the application. Another solution would be
setting up an encrypted file system so I could unmount
the device when the application finishes. The last one is not optimal
though, since the encrypted device would have read access while
mounted.
>
So, I'd like to get some suggestions about tools, api's and probably
other better solutions. Any information would be of great help.
>
The software runs on Windows 2k, Windows XP, Windows 2k3, Windows NT
and probably would have to be ported to Vista.

Maybe try here:
http://msdn.microsoft.com/msdnmag/i...ty/default.aspx
HTH,
TC (MVP MSAccess)
http://tc2.atspace.com|||Sav wrote:

Quote:

Originally Posted by

Do you need to encrypt at the row level? Do different rows have
different keys?

Quote:

Originally Posted by

I'd like to implement something to make it faster so I
wondered if there is any MS SQL Server API that would make the
cryptography transparent for the application.

Not natively I believe. If you're using SQL Server 2005 you could embed
an assembly in to the database. This assembly that could do the
encryption "closer" to the database and doing the encryption here would
probably allow greater throughput.

Quote:

Originally Posted by

Another solution would be
setting up an encrypted file system so I could unmount
the device when the application finishes. The last one is not optimal
though, since the encrypted device would have read access while
mounted.

By this do you mean that you worry that while the drive is open, some
other application could steal the database?

I'd personally modify something like Truecrypt so that only your
application can access the information. This is probably not for the
faint of heart.

Quote:

Originally Posted by

So, I'd like to get some suggestions about tools, api's and probably
other better solutions. Any information would be of great help.

I'd like to know more about your threat model. After all, assuming that
some user can enter and retrieve information, don't they already have
access to all the information anyway?

What assests are you trying to protect?

Simon|||Sav (sav.smb@.gmail.com) writes:

Quote:

Originally Posted by

SQL Server 2005 has considerable additions in the encryption area, so
you can encrypt/decrypt in SQL Server. If you are using SQL 2000, you
should probably look into what SQL 2005 can give you.

But encryption of data, always means that things go slower. Not the least
if you encrypt everything, including the keys.

Quote:

Originally Posted by

Sav wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I'd like to implement something to make it faster so I
wondered if there is any MS SQL Server API that would make the
cryptography transparent for the application.

>
Not natively I believe. If you're using SQL Server 2005 you could embed
an assembly in to the database. This assembly that could do the
encryption "closer" to the database and doing the encryption here would
probably allow greater throughput.

The reference that I cited describes various things that are
implemented natively; eg. managing keys, creating certificates,
encrypting & decrypting fields, & so on. Why would you need an external
assembly?

TC (MVP MSAccess)
http://tc2.atspace.com|||You don't specify if you're using 4.21a, 6.0, 6.5, 7.0, SQL 2005 or SQL
2000. If SQL 2005, look into the built-in Transact-SQL encryption
functionality. If SQL 2000, look into this:
http://www.sqlservercentral.com/col...oolkitpart1.asp.
If SQL 7.0 or lower, upgrade ASAP.

"Sav" <sav.smb@.gmail.comwrote in message
news:1153412674.112270.128440@.i3g2000cwc.googlegro ups.com..

Quote:

Originally Posted by

CryptographicException

I started getting the error
CryptographicException "the parameter is incorrect" whilst designing a
report. Interestingly, I removed everything from the report so there is
nothing in the report, including the dataset and I still get this error. I
guess it must be corruption in the RDL. Can anyone suggest where I might
look?
thanksHi,
I get this error very often while designing reports.
When you get this error, Just Save the changes and Close the Project. Open
it again, you will not have the error.
Hope this helps
Ponnurangam
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:eGGM$BYvEHA.3856@.TK2MSFTNGP10.phx.gbl...
>I started getting the error
> CryptographicException "the parameter is incorrect" whilst designing a
> report. Interestingly, I removed everything from the report so there is
> nothing in the report, including the dataset and I still get this error.
> I
> guess it must be corruption in the RDL. Can anyone suggest where I might
> look?
> thanks
>|||I got this problem also. The problem exists even after I reboot my computer.
There are other corruption issues, such as all of a sudden, the report would
stop passing parameter to execute a stored procedure in dataset... Again,
saving, closing, rebooting, and reopening didnt help. I ended up having to
recreate the rdl,
I now establish version control, so that I can revert back to an older,
workable version.
"Ponnurangam" wrote:
> Hi,
> I get this error very often while designing reports.
> When you get this error, Just Save the changes and Close the Project. Open
> it again, you will not have the error.
> Hope this helps
> Ponnurangam
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:eGGM$BYvEHA.3856@.TK2MSFTNGP10.phx.gbl...
> >I started getting the error
> > CryptographicException "the parameter is incorrect" whilst designing a
> > report. Interestingly, I removed everything from the report so there is
> > nothing in the report, including the dataset and I still get this error.
> > I
> > guess it must be corruption in the RDL. Can anyone suggest where I might
> > look?
> >
> > thanks
> >
> >
>
>|||Thanks for your replies,
I found that deleting the data file resolve the problem in my case.
regards
Matt
"LBJOHN" <LBJOHN@.discussions.microsoft.com> wrote in message
news:B7A559E8-576B-43AC-9164-548AC6F145BA@.microsoft.com...
> I got this problem also. The problem exists even after I reboot my
computer.
> There are other corruption issues, such as all of a sudden, the report
would
> stop passing parameter to execute a stored procedure in dataset... Again,
> saving, closing, rebooting, and reopening didnt help. I ended up having
to
> recreate the rdl,
> I now establish version control, so that I can revert back to an older,
> workable version.
> "Ponnurangam" wrote:
> > Hi,
> >
> > I get this error very often while designing reports.
> >
> > When you get this error, Just Save the changes and Close the Project.
Open
> > it again, you will not have the error.
> >
> > Hope this helps
> >
> > Ponnurangam
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:eGGM$BYvEHA.3856@.TK2MSFTNGP10.phx.gbl...
> > >I started getting the error
> > > CryptographicException "the parameter is incorrect" whilst designing a
> > > report. Interestingly, I removed everything from the report so there
is
> > > nothing in the report, including the dataset and I still get this
error.
> > > I
> > > guess it must be corruption in the RDL. Can anyone suggest where I
might
> > > look?
> > >
> > > thanks
> > >
> > >
> >
> >
> >

cryptographic service need help :)

im having probs downloading updates and the like and its says about the cryptographic service so i whent in to sevvices to start cryptographic service and when i do i says "could not start the cryptographic service on local computer Error 126: the specified module coulnd not be found what do i do please help

please help me

Is this SQL Server related?

Have you looked at ASP.net or windows operating system related forums for relevant information.,

Cryptographic Exception

Midway thru a report development I get an error:
Cryptographic Exception: The parameter is incorrect.
I'm told to look in the task list for details, but it is empty. I've
previewd this report a dozen times during the design and now it barks. This
is the first time i've opened it today, yesterday it was fine. I'm
referencing a stored procedure in SQL server and can view the data from the
data tab. Not sure what to do.
Any suggestions appreciated.
--
RichardDid you ever find out what this means. I just re-opened a report I worked on
last week. THe report renders but I get the message when trying to export the
results to ANY format.
"Richard Roche" wrote:
> Midway thru a report development I get an error:
> Cryptographic Exception: The parameter is incorrect.
> I'm told to look in the task list for details, but it is empty. I've
> previewd this report a dozen times during the design and now it barks. This
> is the first time i've opened it today, yesterday it was fine. I'm
> referencing a stored procedure in SQL server and can view the data from the
> data tab. Not sure what to do.
> Any suggestions appreciated.
> --
> Richard

CryptoAPI Function failed error

Hi all

I am facing this error can you help me please it is urgent

CryptoAPI function 'CryptAcquireContext' failed. Error 0x80090006: Invalid Signature.

thank you allhttp://support.microsoft.com/default.aspx?scid=kb;en-us;811886

CryptoAPI function 'CryptAcquireContext' failed

Hi,
I am receiving the following error after installing MSDE
on a machine with NT 4.0 SP6a:
MSSQL Server logon. Connection failed. SQL State 42000 SQL
Server error 552
[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI
function 'CryptAcquireContext' failed.
Error 0x8009001d: Provider DLL failed to initialize
correctly.
I have found the following KB article which addresses
this, but don't know how to sign on to the server using
sysadmin login.
http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 1459
If anyone has seen this before would be very interested to
hear.
Many thanks,
Mark.
Yep... I've had this problem - or rather a customer 4000 miles away did.
As we were unwilling to advise the customer to do the fix - as we'd be the
ones responsible for 'trashing the machine' if it went wrong, we gave the
customer a link to the article. They didn't fancy doing it, their IT
looked at it and did not want to do the BOOT.INI thing either.
They upgraded subsequently to Win2K, bringing forward that users upgrade
window.

cryptoAPI

When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
VB6 application we sometimes get this error
[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
'CryptAcquireContext' failed.
Error 0x8009001d: Provider DLL failed to initialize correctly.
On Win2000/2003 XP we not get this error any one come across this before
Thanks
Andy
Hi
Probably the first of these but it does not mention Win 98
http://support.microsoft.com/default...b;en-us;321459
http://support.microsoft.com/default...b;en-us;811886
Is there a reason not to retire the Win 98 machine?
John
"Andy Atherton" <AndyA@.Computill.com> wrote in message
news:410b9065_4@.newsfeed.slurp.net...
> When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
> VB6 application we sometimes get this error
> [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
> 'CryptAcquireContext' failed.
> Error 0x8009001d: Provider DLL failed to initialize correctly.
> On Win2000/2003 XP we not get this error any one come across this before
>
> Thanks
> Andy
>
>
|||I agree with John on that - why support securing data on an insecure o/s?
Regards,
Greg Linwood
SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8JNOc.11968$jq3.121975959@.news-text.cableinet.net...[vbcol=seagreen]
> Hi
> Probably the first of these but it does not mention Win 98
> http://support.microsoft.com/default...b;en-us;321459
> http://support.microsoft.com/default...b;en-us;811886
> Is there a reason not to retire the Win 98 machine?
> John
> "Andy Atherton" <AndyA@.Computill.com> wrote in message
> news:410b9065_4@.newsfeed.slurp.net...
our
>

cryptoAPI

When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
VB6 application we sometimes get this error
[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
'CryptAcquireContext' failed.
Error 0x8009001d: Provider DLL failed to initialize correctly.
On Win2000/2003 XP we not get this error any one come across this before
Thanks
AndyHi
Probably the first of these but it does not mention Win 98
http://support.microsoft.com/default.aspx?scid=kb;en-us;321459
http://support.microsoft.com/default.aspx?scid=kb;en-us;811886
Is there a reason not to retire the Win 98 machine?
John
"Andy Atherton" <AndyA@.Computill.com> wrote in message
news:410b9065_4@.newsfeed.slurp.net...
> When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
> VB6 application we sometimes get this error
> [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
> 'CryptAcquireContext' failed.
> Error 0x8009001d: Provider DLL failed to initialize correctly.
> On Win2000/2003 XP we not get this error any one come across this before
>
> Thanks
> Andy
>
>|||I agree with John on that - why support securing data on an insecure o/s?
Regards,
Greg Linwood
SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8JNOc.11968$jq3.121975959@.news-text.cableinet.net...
> Hi
> Probably the first of these but it does not mention Win 98
> http://support.microsoft.com/default.aspx?scid=kb;en-us;321459
> http://support.microsoft.com/default.aspx?scid=kb;en-us;811886
> Is there a reason not to retire the Win 98 machine?
> John
> "Andy Atherton" <AndyA@.Computill.com> wrote in message
> news:410b9065_4@.newsfeed.slurp.net...
> > When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with
our
> > VB6 application we sometimes get this error
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
> > 'CryptAcquireContext' failed.
> > Error 0x8009001d: Provider DLL failed to initialize correctly.
> >
> > On Win2000/2003 XP we not get this error any one come across this before
> >
> >
> > Thanks
> > Andy
> >
> >
> >
>

cryptoAPI

When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
VB6 application we sometimes get this error
[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI functio
n
'CryptAcquireContext' failed.
Error 0x8009001d: Provider DLL failed to initialize correctly.
On Win2000/2003 XP we not get this error any one come across this before
Thanks
AndyHi
Probably the first of these but it does not mention Win 98
http://support.microsoft.com/defaul...kb;en-us;321459
http://support.microsoft.com/defaul...kb;en-us;811886
Is there a reason not to retire the Win 98 machine?
John
"Andy Atherton" <AndyA@.Computill.com> wrote in message
news:410b9065_4@.newsfeed.slurp.net...
> When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
> VB6 application we sometimes get this error
> [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI funct
ion
> 'CryptAcquireContext' failed.
> Error 0x8009001d: Provider DLL failed to initialize correctly.
> On Win2000/2003 XP we not get this error any one come across this before
>
> Thanks
> Andy
>
>|||I agree with John on that - why support securing data on an insecure o/s?
Regards,
Greg Linwood
SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8JNOc.11968$jq3.121975959@.news-text.cableinet.net...
> Hi
> Probably the first of these but it does not mention Win 98
> http://support.microsoft.com/defaul...kb;en-us;321459
> http://support.microsoft.com/defaul...kb;en-us;811886
> Is there a reason not to retire the Win 98 machine?
> John
> "Andy Atherton" <AndyA@.Computill.com> wrote in message
> news:410b9065_4@.newsfeed.slurp.net...
our[vbcol=seagreen]
>

cryptoAPI

When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
VB6 application we sometimes get this error

[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
'CryptAcquireContext' failed.
Error 0x8009001d: Provider DLL failed to initialize correctly.

On Win2000/2003 XP we not get this error any one come across this before

Thanks
AndyHi

Probably the first of these but it does not mention Win 98

http://support.microsoft.com/defaul...kb;en-us;321459
http://support.microsoft.com/defaul...kb;en-us;811886

Is there a reason not to retire the Win 98 machine?

John

"Andy Atherton" <AndyA@.Computill.com> wrote in message
news:410b9065_4@.newsfeed.slurp.net...
> When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our
> VB6 application we sometimes get this error
> [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
> 'CryptAcquireContext' failed.
> Error 0x8009001d: Provider DLL failed to initialize correctly.
> On Win2000/2003 XP we not get this error any one come across this before
>
> Thanks
> Andy|||I agree with John on that - why support securing data on an insecure o/s?

Regards,
Greg Linwood
SQL Server MVP

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8JNOc.11968$jq3.121975959@.news-text.cableinet.net...
> Hi
> Probably the first of these but it does not mention Win 98
> http://support.microsoft.com/defaul...kb;en-us;321459
> http://support.microsoft.com/defaul...kb;en-us;811886
> Is there a reason not to retire the Win 98 machine?
> John
> "Andy Atherton" <AndyA@.Computill.com> wrote in message
> news:410b9065_4@.newsfeed.slurp.net...
> > When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with
our
> > VB6 application we sometimes get this error
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function
> > 'CryptAcquireContext' failed.
> > Error 0x8009001d: Provider DLL failed to initialize correctly.
> > On Win2000/2003 XP we not get this error any one come across this before
> > Thanks
> > Andy

Cryptic error from SqlXml 3.0 Managed Classes

that SqlXml is returning this message - In order to
troubleshoot I would need to know the service dependencies for SqlXml -
something I have not been able to establish so far from the on-line
documentation.
Message: The service cannot be started, either because it is disabled or
because it has no enabled devices associated with it.
Source: mscorlib
Stacktrace:
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at Microsoft.Data.SqlXml.SqlXmlCommand.fillManagedWrapper()
at Microsoft.Data.SqlXml.SqlXmlCommand.construct()
at Microsoft.Data.SqlXml.SqlXmlCommand..ctor(String cnString)
at xxxxx.DataAccess.SqlClientHelper.ExecSqlXmlQuery(String sqlxmlquery,
String sqlxmlconnectionstring, Hashtable sqlXmlParams, Boolean isSP)
at xxxxx.Occam.ReportQueue.Report.GetNext(Int32 currentLoad, Int32
ceiling, DBConfig dbconfig, Config config, LanguageManager languageManager)
Eagerly awaiting any suggestions on this. This is an important issue for us,
as it's preventing us from completing roll-out to an important client.
regards,
James AshtonHi James,
As far as I know SQLXML does not have any service dependencies,. I think
this is happening because either sqlxml is not registered properly or the
managed wrapper is not being loaded properly. Please make sure that sqlxml
is registered properly and that the managed wrapper is in the assembly
cache.
If you are still hitting this problem let me know.
Thank you,
Amar Nalla
PS: This posting is provided AS IS and confers no rights or warranties.
"James Ashton" <JamesAshton@.discussions.microsoft.com> wrote in message
news:A6967A3A-31D8-4EAE-ABE6-8BDE6BE39A8F@.microsoft.com...
> Hi,
> I have a windows service written in C# that is using the SqlXml 3.0
Managed
> Classes.
> The service has functioned reliably until we came across this problem on
one
> Windows 2000 server installation.
> I'm

that SqlXml is returning this message - In order to
> troubleshoot I would need to know the service dependencies for SqlXml -
> something I have not been able to establish so far from the on-line
> documentation.
>
> Message: The service cannot be started, either because it is disabled or
> because it has no enabled devices associated with it.
> Source: mscorlib
> Stacktrace:
> at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly)
> at System.Activator.CreateInstance(Type type, Boolean nonPublic)
> at Microsoft.Data.SqlXml.SqlXmlCommand.fillManagedWrapper()
> at Microsoft.Data.SqlXml.SqlXmlCommand.construct()
> at Microsoft.Data.SqlXml.SqlXmlCommand..ctor(String cnString)
> at xxxxx.DataAccess.SqlClientHelper.ExecSqlXmlQuery(String sqlxmlquery,
> String sqlxmlconnectionstring, Hashtable sqlXmlParams, Boolean isSP)
> at xxxxx.Occam.ReportQueue.Report.GetNext(Int32 currentLoad, Int32
> ceiling, DBConfig dbconfig, Config config, LanguageManager
languageManager)
>
> Eagerly awaiting any suggestions on this. This is an important issue for
us,
> as it's preventing us from completing roll-out to an important client.
> regards,
> James Ashton
>|||I did instruct the client on this, but I haven't heard from them yet as to
whether this was successful. I will contact them to find out, and then updat
e
this thread.
Unfortunately this is one of those where we couldn't reproduce the error in
house. So hopefully, the client will inform me that it's an installation
problem.
many thanks Amar, and hopefully they won't have any more problems.
regards,
James Ashton
"Amar Nalla [MSFT]" wrote:

> Hi James,
> As far as I know SQLXML does not have any service dependencies,. I think
> this is happening because either sqlxml is not registered properly or the
> managed wrapper is not being loaded properly. Please make sure that sqlxml
> is registered properly and that the managed wrapper is in the assembly
> cache.
> If you are still hitting this problem let me know.
> Thank you,
> Amar Nalla
> PS: This posting is provided AS IS and confers no rights or warranties.
> "James Ashton" <JamesAshton@.discussions.microsoft.com> wrote in message
> news:A6967A3A-31D8-4EAE-ABE6-8BDE6BE39A8F@.microsoft.com...
> Managed
> one
> languageManager)
> us,
>
>

Cryptic error from SqlXml 3.0 Managed Classes

Hi,
I have a windows service written in C# that is using the SqlXml 3.0 Managed
Classes.
The service has functioned reliably until we came across this problem on one
Windows 2000 server installation.
I'm confused that SqlXml is returning this message - In order to
troubleshoot I would need to know the service dependencies for SqlXml -
something I have not been able to establish so far from the on-line
documentation.
Message: The service cannot be started, either because it is disabled or
because it has no enabled devices associated with it.
Source: mscorlib
Stacktrace:
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at Microsoft.Data.SqlXml.SqlXmlCommand.fillManagedWra pper()
at Microsoft.Data.SqlXml.SqlXmlCommand.construct()
at Microsoft.Data.SqlXml.SqlXmlCommand..ctor(String cnString)
at xxxxx.DataAccess.SqlClientHelper.ExecSqlXmlQuery(S tring sqlxmlquery,
String sqlxmlconnectionstring, Hashtable sqlXmlParams, Boolean isSP)
at xxxxx.Occam.ReportQueue.Report.GetNext(Int32 currentLoad, Int32
ceiling, DBConfig dbconfig, Config config, LanguageManager languageManager)
Eagerly awaiting any suggestions on this. This is an important issue for us,
as it's preventing us from completing roll-out to an important client.
regards,
James Ashton
Hi James,
As far as I know SQLXML does not have any service dependencies,. I think
this is happening because either sqlxml is not registered properly or the
managed wrapper is not being loaded properly. Please make sure that sqlxml
is registered properly and that the managed wrapper is in the assembly
cache.
If you are still hitting this problem let me know.
Thank you,
Amar Nalla
PS: This posting is provided AS IS and confers no rights or warranties.
"James Ashton" <JamesAshton@.discussions.microsoft.com> wrote in message
news:A6967A3A-31D8-4EAE-ABE6-8BDE6BE39A8F@.microsoft.com...
> Hi,
> I have a windows service written in C# that is using the SqlXml 3.0
Managed
> Classes.
> The service has functioned reliably until we came across this problem on
one
> Windows 2000 server installation.
> I'm confused that SqlXml is returning this message - In order to
> troubleshoot I would need to know the service dependencies for SqlXml -
> something I have not been able to establish so far from the on-line
> documentation.
>
> Message: The service cannot be started, either because it is disabled or
> because it has no enabled devices associated with it.
> Source: mscorlib
> Stacktrace:
> at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly)
> at System.Activator.CreateInstance(Type type, Boolean nonPublic)
> at Microsoft.Data.SqlXml.SqlXmlCommand.fillManagedWra pper()
> at Microsoft.Data.SqlXml.SqlXmlCommand.construct()
> at Microsoft.Data.SqlXml.SqlXmlCommand..ctor(String cnString)
> at xxxxx.DataAccess.SqlClientHelper.ExecSqlXmlQuery(S tring sqlxmlquery,
> String sqlxmlconnectionstring, Hashtable sqlXmlParams, Boolean isSP)
> at xxxxx.Occam.ReportQueue.Report.GetNext(Int32 currentLoad, Int32
> ceiling, DBConfig dbconfig, Config config, LanguageManager
languageManager)
>
> Eagerly awaiting any suggestions on this. This is an important issue for
us,
> as it's preventing us from completing roll-out to an important client.
> regards,
> James Ashton
>
|||I did instruct the client on this, but I haven't heard from them yet as to
whether this was successful. I will contact them to find out, and then update
this thread.
Unfortunately this is one of those where we couldn't reproduce the error in
house. So hopefully, the client will inform me that it's an installation
problem.
many thanks Amar, and hopefully they won't have any more problems.
regards,
James Ashton
"Amar Nalla [MSFT]" wrote:

> Hi James,
> As far as I know SQLXML does not have any service dependencies,. I think
> this is happening because either sqlxml is not registered properly or the
> managed wrapper is not being loaded properly. Please make sure that sqlxml
> is registered properly and that the managed wrapper is in the assembly
> cache.
> If you are still hitting this problem let me know.
> Thank you,
> Amar Nalla
> PS: This posting is provided AS IS and confers no rights or warranties.
> "James Ashton" <JamesAshton@.discussions.microsoft.com> wrote in message
> news:A6967A3A-31D8-4EAE-ABE6-8BDE6BE39A8F@.microsoft.com...
> Managed
> one
> languageManager)
> us,
>
>

CryptAcquireContext failed. Error 0x57

On a production db of over 100 GB, i am getting the below error freqently and i have to restart my server as it becomes dead.

[Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function 'CryptAcquireContext' failed. Error 0x57: The parameter is incorrect.

Alias: DBDest [07-07-16 17:14:04]

Please advise what is the cause of this problem/error and how to resolve this.

Can you tell us what you are doing when this error occurs? Anything to help isolate the cause of this issue will be helpful.

Can you also describe your setup? Are you running SQL Server 2005? 2000? What service pack?

Thanks,

jkh

Cryatal Viewer

I need to test if the user had clicked the print button on the crystal viewer in my design (not in run time) this button is disable how can I by code know if the user had clicked this button on any else button of the crystal reportLet me get this straight...

You want to determine if the Crystal Viewer was clicked in design time

Do you want to add custome events to any of those controls
I can't see why you want to do this, they already have functionality, they already function as they should :confused:

Or..

You can in any case loop through the Form's controls, determine if the current control is a
CrystalDecisions.Windows.Forms.PageView then loop further through that container to (for example) change the caption of the Tabpage.

Can you explain your problem a bit more please!|||I need to know when he clicked the print button I need to know if he choosed to print as Landscape or portrait

CRXI windows 98

Hi
Is there any method to use CR XI reports on windows 98.
Any tools ..
Thanks in AdvanceI dont think CR X1 will run in Windows 98
Anyway check here
http://support.businessobjects.com/

Crviewer or Crystal Report(rtp Control)

Is it crviewer or crystal report(rpt control) is the best option to get the report from VB.
What are the advantages and disadvantages of both.
Is Crystal report is going to stop the rpt Control SupprotSafest approach is to use CRPE object, as the ActiveX Control (CRAXDRT) is simply a COM wrapper around CRPE functionality.
crviewer is Ok to use if reports are being designed elsewhere and you just want to generate reports from VB.

dave|||Thank you.
I am using Segate Crystal report.
in that Crviewer and rpt Control is there .
Is Ceagate is going to stop support for rpt control.|||As long as OS's use activeX, I guess the viewer will stay supported.....

Crviewer

Hi
HAs anybody used crystal reports 9 or 10 with asp.net
I have crystal reports 9 and 10 professional editions
The issue here is i dont want to use the viewer that comes with .net
How do i use crviewer9.dll in asp.net
Or how do i use the crviewer that comes with cr 10 professional

It would be helpful if i get code examples
I am able to use it in vb.net

Regards
amarNot 100% sure, but as I know this is not possible. :sick:
Maybe someone else...

CR-V timing belt

Hi all,

i have 97 honda CR-V with about 100,000 KM on it. i checked a doc on
Honda web site and it said that 60,000 miles is point to change timing
belt. compared with other Honda cars, it is pretty low. is it correct? if
yes, do you know the reason??

thanks for your help,
jji'd get it changes ASAP if i were you, i had a belt break on my old 85 civic
and it royally whacked all the rods, cost me a bunch, and it never quit ran
the same after that.

You may get a more specific response in another news group, as I think they
stopped using timing belts in SQL Server in 6.5. Though I don't know about
MySQL.

"jj" <jjcpa@.rocketmail.com> wrote in message
news:1md5u9ugrbemg$.1kiwbzt9ye2cw$.dlg@.40tude.net. ..
> Hi all,
> i have 97 honda CR-V with about 100,000 KM on it. i checked a doc on
> Honda web site and it said that 60,000 miles is point to change timing
> belt. compared with other Honda cars, it is pretty low. is it correct? if
> yes, do you know the reason??
> thanks for your help,
> jj|||LOL

--
David Portas
SQL Server MVP
--

Crump Links

I would like to add crump links feature to my drill down reports. Anybody has
an lead on this ?
Thanks for your time in advance.What are crump links?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Rajan" <Rajan@.discussions.microsoft.com> wrote in message
news:A5B7EC27-2C15-4589-AA2F-E13CEC1D3BC4@.microsoft.com...
>I would like to add crump links feature to my drill down reports. Anybody
>has
> an lead on this ?
> Thanks for your time in advance.|||i have a drill down report which is going to five level. I would like to give
a link how the drill down came all the way, so that by clicking the link user
can straightaway go to the drill down level say from 5 directly to level 3.
Normally we can see this type of links in sites. somethink like the following
one
MSDN Home > MSDN Library > Development Tools and Languages > Visual
Studio .NET > Visual Basic and Visual C# > Reference > Visual Basic
Language > Visual Basic Language and Run-Time Reference > Functions > S-Z
Functions
Hope i had made u understand. If not please give me a buzz.
"Jeff A. Stucker" wrote:
> What are crump links?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Rajan" <Rajan@.discussions.microsoft.com> wrote in message
> news:A5B7EC27-2C15-4589-AA2F-E13CEC1D3BC4@.microsoft.com...
> >I would like to add crump links feature to my drill down reports. Anybody
> >has
> > an lead on this ?
> >
> > Thanks for your time in advance.
>
>

CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005

I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

Hi,

For a free one, look at mygenerationsoftware.com.

For a cheap-ish one (I use it all the time), try codesmithtools.com (trial version is free).

Hope this helps,

Jules

|||

Thanks for solution.

Regards

|||Hope you get on OK with the utilities.

CRPEAUTO : Set default Printer

Ciao, i'm using VB6 and i need to set my report with the default printer then i can printout it.
How can I do this?
I'm using automation code and i need to do that using the same code.
Could you help me, please? It's very urgent and important.
ThanksSolution:

crystalreport.SelectPrinter "", "", ""

Ciao :-)

Crosstabs queries

Hello,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:

> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,

CrossTable - SQL Server 2000 Help me

Hello,

sorry for my English.. i have this problem

two tables

TABLE_A

Import

132

244

165

TABLE_B

FromValue ToValue

0 100

101 200

201 1000

i'd like a query that have to result this

Field1 Field2 Field3 (Count of table A)

0 100 1

101 200 2

201 1000 1

and if i change the table B this query automatically change..

thank's very much

Try

SELECT FromValue ToValue,
(SELECT COUNT(*) FROM TableA A WHERE A.ImportBETWEEN B.FromValue AND B.ToValue) AS Cnt
FROM TableB

|||Thank's very much

crosstable

Hi,
Could someone provide me the syntax or a procedure how to create a
alternative for a crosstable like in access.
For example i have a 2 tables, orders and orderdetails. What i want is to
display the data as a crosstable like view.
Here's a example in sheetmode in which i would like to view the data. The
productname comes from the orderdetails.
orderid | productname1 | productname2 | productname3
Is this possible?HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
Dynamic Crosstab Queries
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=15608&DisplayTab=Article[
/url]
AMB
"Jason" wrote:

> Hi,
> Could someone provide me the syntax or a procedure how to create a
> alternative for a crosstable like in access.
> For example i have a 2 tables, orders and orderdetails. What i want is to
> display the data as a crosstable like view.
> Here's a example in sheetmode in which i would like to view the data. The
> productname comes from the orderdetails.
> orderid | productname1 | productname2 | productname3
> Is this possible?
>
>|||The RAC utility is the closest thing to the Access crosstab
query in S2k.Like Access it's easy to use, no complex coding required for
dynamic crosstabs, and it's much more powerful with many
features/options.
www.rac4sql.net|||For your problem check out the RAC @.rank parameter
in the Help file.
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:OLOCGy$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> The RAC utility is the closest thing to the Access crosstab
> query in S2k.Like Access it's easy to use, no complex coding required for
> dynamic crosstabs, and it's much more powerful with many
> features/options.
> www.rac4sql.net
>

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon Derbyshire
Can you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire
|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab?

Hello,
I have this:
Part Month Type
0001A 10/2004 EM
0001A 10/2004 MM
0001A 11/2004 EM
Would like to get to this:
Part Month EM MM
0001A 10/2004 1 1
0001A 11/2004 1 0
thanks
stevehttp://www.aspfaq.com/2462
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"steve9" <steve9@.gmail.com> wrote in message
news:1111523038.812065.303570@.f14g2000cwb.googlegroups.com...
> Hello,
> I have this:
> Part Month Type
> 0001A 10/2004 EM
> 0001A 10/2004 MM
> 0001A 11/2004 EM
> Would like to get to this:
> Part Month EM MM
> 0001A 10/2004 1 1
> 0001A 11/2004 1 0
> thanks
> steve
>|||Try,
select
Part,
[Month],
count(case when Type = 'EM' then 1 end) as EM,
count(case when Type = 'MM' then 1 end) as MM,
from
table1
group by
Part,
[Month];
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"steve9" wrote:

> Hello,
> I have this:
> Part Month Type
> 0001A 10/2004 EM
> 0001A 10/2004 MM
> 0001A 11/2004 EM
> Would like to get to this:
> Part Month EM MM
> 0001A 10/2004 1 1
> 0001A 11/2004 1 0
> thanks
> steve
>|||Simple...thx
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E9A4EAE8-A669-49FE-9C99-40125D798F22@.microsoft.com...
> Try,
> select
> Part,
> [Month],
> count(case when Type = 'EM' then 1 end) as EM,
> count(case when Type = 'MM' then 1 end) as MM,
> from
> table1
> group by
> Part,
> [Month];
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...574&Product=sql
>
> AMB
> "steve9" wrote:
>|||If anyone is looking for the BMW of crosstabs as opposed
to the Chevy version check out the RAC utility for S2k:)
www.rac4sql.net

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon DerbyshireCan you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
--
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient
in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in messag
e
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net

Crosstab. 2 totals ?

Hi,

Am using robvolks crosstab-procedure to generate a crosstab query.

I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342

So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260

Is that possible?

/jim
--call to procedure
execute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as
'MonthsTotal'' from tblData group by
DATENAME(month,(theDate))','count(letter)','letter ','tblData'

----Robvolks procedure--
CREATE PROCEDURE crosstab
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100),
@.where varchar(1000)='1=1'

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET LANGUAGE Danish

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.where + ' AND ' + @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql
+ ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GOJim Andersen (jba020@.politiSLET.dk.invalid) writes:

Quote:

Originally Posted by

Am using robvolks crosstab-procedure to generate a crosstab query.
>
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
>
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
>
Is that possible?

You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;

But why not use a report writer in the front end, like you are supposed
to?|||--CELKO-- wrote:

Quote:

Originally Posted by

SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
>
But why not use a report writer in the front end, like you are
supposed to?

So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.

I think I will try Erlands suggestion.

/jim

Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)

CrossTab(?) Query Problem

I've got a fairly tricky report to produce, so I'm trying to build it up bit
by bit, but I've fallen at the first hurdle!
I need to produce a view that lists all PartNo's and for each, what quantity
is in what location, e.g.
PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
WE12024, 5, null, 3, 10, 18
WE12067, 1, 1, null, 5, 7
etc...
I have the SQL to extract each bit of the data, but the problem is
presenting it in this way.
SQL:
Select PartNo, Count(*) as InGoodsIn
from Filters
Where LocationID = 15 /* for GoodsIn */
The PostInspection, Quarrantine & Stock locations have the following
LocationIDs - 16, 14, 17
How do I combine these result into one structure as indicated? Can somebody
point me in the right direction,please?
[In case it isn't clear from the above, the Filters table has this
structure: SerialNo (primary key), PartNo, LocationID]
Thanks in advance
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%237q365qlFHA.360@.TK2MSFTNGP09.phx.gbl...
> I've got a fairly tricky report to produce, so I'm trying to build it up
> bit by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what
> quantity is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can
> somebody point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
>
Apologies - problem solved.
I was on the right lines but had made a couple of errors. The solution I
have is:
SELECT Partno,
[GoodsIn] = SUM(CASE LocationID WHEN 15 THEN 1 ELSE 0 END),
[PostInspection] = SUM(CASE LocationID WHEN 16 THEN 1 ELSE 0 END),
[Quarantine] = SUM(CASE LocationID WHEN 14 THEN 1 ELSE 0 END),
[Stock] = SUM(CASE LocationID WHEN 17 THEN 1 ELSE 0 END)
FROM Filters
GROUP BY PartNo|||Try,
select
PartNo,
sum(case when location = 15 then 1 else 0 end) as GoodsIn,
sum(case when location = 16 then 1 else 0 end) as PostInspection,
sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
sum(case when location = 17 then 1 else 0 end) as Stock
from
Filters
order by
PartNo
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"CJM" wrote:

> I've got a fairly tricky report to produce, so I'm trying to build it up b
it
> by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what quanti
ty
> is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can somebod
y
> point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Try,
> select
> PartNo,
> sum(case when location = 15 then 1 else 0 end) as GoodsIn,
> sum(case when location = 16 then 1 else 0 end) as PostInspection,
> sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
> sum(case when location = 17 then 1 else 0 end) as Stock
> from
> Filters
> order by
> PartNo
> go
>
Curious...
Your syntax is different to mine but still works - eg. "Case when LocationID
= 15" rather than "Case LocationID when 15"
Is this difference significant?
Chris|||The result of both expressions are the same. See CASE in BOL to read more
about "simple case" and "search case" formats.
AMB
"CJM" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Curious...
> Your syntax is different to mine but still works - eg. "Case when Location
ID
> = 15" rather than "Case LocationID when 15"
> Is this difference significant?
> Chris
>
>

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
Dion
Have a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

Crosstab woes

Good day all.

I'm using crystal reports 9.

I have a list of names down the left (sDriverName) and the months across the top (liMonth). The summarised field is the Driver score (liScore). There is another database field called liBandColour which indicates what colour band the driver's score is for that month (red, amber, green).

I have a formula on the Background colour of the liScore field (Basic syntax):
if {Rag.liBandColour} =255 then
formula = crRed 'Red
elseif {Rag.liBandColour} =65535 then
formula = rgb(255,255,0) 'Amber
elseif {Rag.liBandColour} =13434828 then
formula = crGreen 'Green
else
formula = crWhite 'White
end if

However this seems to make ALL the scores appear amber and doesn't seem to work on a per record basis.

On to question 2:
The user has now requested to add another column on the right with the YTD kms travelled. Now it's a simple thing to add another summarised field and suppress it (the DB also contains the kms travelled each month so this is easy), but then it appears underneath the averaged driver score total and not next to it in it's own column... Is there anyway around this?
Can I perhaps duplicate the look of my report without using a crosstabFor your first problem, I came thru something like that in CrystalDecisions.com

visit this url (http://support.businessobjects.com/library/kbase/articles/c2006837.asp)

For your second problem, it may not. any way

have a look at here (http://support.businessobjects.com/library/kbase/articles/c2006237.asp)

Cross-Tab vs SQL query

Hi,
I have a report like this...

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
---
---

02/12/2006 1 1 1 3 1
-----------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)

So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!Is it possible to built a query like this,I mean assigning data as column?Yep. If there are a fixed number of possible column values you can use CASE. If not then you need to use dynamic SQL (e.g. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817,
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608,
http://www.sqlteam.com/item.asp?ItemID=2955). I've not used any of the above - I just have them bookmarked in case I need them.

BTW - your better option might be the front end doing this if at all possible

HTH|||Thanks a lot pottie for those links.I will give it a try tonight...Thanks once again..:cool:

crosstab stored procedure

I have a table that has the following information like this:

Name Date 1st item 2nd item 3rd item 4th item 5th item
Defaul name 11/1/2005 100 0 0 0 0
Defaul name 11/2/2005 100 0 0 0 0
Defaul name 11/3/2005 99.69 0 0 0 0

I need to create a crosstab query (NOT using crystal report) that will display the information like:

11/1/2005 11/2/2005 11/3/2005
1st item 100.00 100.00 99.69
2nd item 0.00 0.00 0.00
3rd item 0.00 0.00 0.00
4th item 0.00 0.00 0.00
5th item 0.00 0.00 0.00

I have never used a crosstab query in sql server before. Please help!First, look up CROSSTAB in Books Online and you will see the general method for handling this using CASE statements.
Unfortunately you are going to have problems if your column headers are dynamic, which is often the case when you are grouping your columns by date values.
If, as your example implies, you are only dealing with one year's worth of data at a time, then you can group your columns by datepart(month...), which will give you 12 consistent column labels.
Avoid dynamic crosstab queries if at all possible (or until you upgrade to SQL Server 2005...). And though you apparently already know this, I have to say that dynamic crosstab functionality does not really belong in SQL server anyway, SQL Server 2005 not withstanding. It is a presentation issue.|||Thanks for your reply. Unfortunately, the column values have to be dynamic. There is an easy way to present this in crystal reports but unfortunately i have to write directly to an excel file from a stored procedure. I'm at a loss right now as to how to accomplish this presentation in a stored procedure with dynamic columns.|||Not to beat a dead horse, but look at ags crosstab and/or RAC (used to be replacement for access crosstab, but they changed their name).

Regards,

hmscott

Saturday, February 25, 2012

Blog Archive

About Me