Crystal v9 and SQL stored procedure

I am having a heck of time doing what seems to should be simple. I have a stored Procedure (SQL Server) with one parameter that returns a records set. When I put this into a Crystal ver 9 report it prompts me for the param and produces the report. When I use this report as a subreport in a main report and link a value value from the main report to the subreport param (which is the SQL Stored Procedure param) I get a blank report.

Any clues?

I then decided that I would have the main report run the stored procedure and change the stored procedure to write a table of the result set into the database; I could not manage this as the crystal reports returns the succinct but cryptic 'not allowed when I try to write the table'. (When running this in the query analyzer, it generates the new table as expected).

If you can help it would be greatly appreciated.As a followup to my previous post, I found that if I changed the stored procedure so that it inserted the records into an existing table (as opposed to creating a new table and inserting the data) then the crystal reports interface successfully executed the stored procedure so it was hanging up on the create table part of the process. Can someone tell me if its possile (and if so how) to change the call from crystal reports so that it is permitted to create a table. The same user id and db login executes the create table correctly from QUery Analyzer.|||Not exactly know how to do it in SQL Server. But in Oracle, we can use object type to return
the record from stored procedure and call it from CR.
Other way is, to call this stored procedure from your program and save the result in text delimited
file, then load this file to CR...Just an idea.

Crystal Reports for VS 2005 - Passing SQL Stored Procedure Parameters

I have a report which uses a stored procedure with several parameters which gets loaded into the Report Viewer control. When I run this report on the development machine everything works correctly, but after I publish it to another machine (or even another website on the development machine), I get the message:

Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Procedure or Function 'uspReport_IncidentBrowse' expects parameter '@.departmentid', which was not supplied. SQL State: 42000 Native Error: Failed to open a rowset. Error in File C:\WINDOWS\TEMP\rptincidentbrowse {5F03A03D-6BF9-4121-B68C-06D9C89283A1}.rpt: Failed to open a rowset.

I have other reports which do not have SQL parameters which run fine. All the reports have a parameter for the title line of the report and this is getting set correctly.

I have searched and found numerous examples of how to programatically set parameters... all of which work fine on the development machine, but all produce the result above once the website is published ANYWHERE.

Is this a limitation of Crystal Reports for Visual Studio 2005?IIRC, the license for Crystal Reports included with Visual Studio is for no-production use ONLY. (It's been a while since I have used Crstal, much preferring SQLServer Reporting Services.....)|||According to their license agreement you are allowed to deploy to a production server.

In addition, regardless of licensing issues, I would not be willing to purchase an enterprise (or any other) CR license unless we are able to solve this problem in a proof of concept deployment.

Does anyone have an authoritative answer?

Crystal Reports and Unicode input

Hi!

I'm trying to use Crystal Reports with our proprietory data (some of it stored in CodeBase database, some in text files, some is captured in runtime, etc). The simplest solution would be to create comma separated file and pass it to CR. It works, except our text fields can be in a variety of langauges (some european and Chinese Simplified and Traditional). Our software uses Unicode, so I'd prefer to create Unicode CSV file. These files cause CR to fail - it is obvious that CR does not realise that the file is Unicode. I tried other options, and so far I've discovered that CR can read Unicode XML files, while treating them as ADO.Net databases. This is not the best way out, as I suspect it requires .Net framework to be present on target PCs, and it's a big install.

So - any ideas? What simple format can I use to pass Unicode text to CR, or may be some other approaches.

I'm using: CR ver XI, target OS - Win2000 and XP, we can not expect target PC to have anything but Windows installed

Many thanks

balrogPS, when I said CR fails - it does not crash, it simply shows incorrect column names (the first one includes Unicode BOM), number of columns and and all values are wrong
balrog|||hi!
i'm stuck in code and i came across your post when i was searching 4 a solution.
I want to connect to crystal through an XML data source. Im using Visual C++ 6.0. I know how to do it by using an Acess db, bt cant do it using XML.
Can u tell me how?
Im using ADO to connect, bt it gives an exception:
>>_bstr_t bstrAccessConnect ("Provider='CR XML v3.6';Data Source Name='RGenXML';");

Whts wrong here? Help pleeese...

Thanx! Hope 2 hear frm u!|||In Crystal Reports (http://www.saveonsupport.com) there is a ChrW function which takes a unicode number and returns the matching character. Have you tried this?sql

Crystal Reports - Newbie:subreports on two stored proc

Hi i would like to develop a report which displays unrelated data from two stored procedures.
I want to know how to code the ASP webform so that it passes different parameters to differnt stored procedures and displays the report with data from both stored procedure. pls help.Make two procedures to one and display the records

Select columns from table where col=@.col

Crystal Report with Images

Hi,
I have the images in a folder and the path is stored in a table for each record. Each record in the table is associated with a different image. I'm able to load the images dynamically in the crystal report. But when i create a Package using the Package & Deployment wizard, the images are not being displayed. Instaed the name of the images is displayed in that area.
Actually I use the OLE object to dispaly the image. and below is the code I have written

I'm using VB 6 and Crystal Reports 8.5

Private Sub Section6_Format(ByVal pFormattingInfo As Object)
If Field5.Value <> "" Then Picture1.SetOleLocation Field5.Value
End Sub

where field5 is the Path field placed in the report.

Please help. Its very very very urgent.

Thanks & Regards,
Venkatesh.Rplz tell how did u manage to change the image dynamicaly with cr 8.5....its really very urgent|||Attached is a zip file containing a sample project. Hope this will help you.

Regards,
Venkatesh.R|||Attached is a Sample project as a zip file. Hope this will help you.|||thanx a lot for ur help....i'll give it a try...bye for now..

Crystal Report Viewer on web timing out after 3 minutes

Help,

I am running CR 8.5 on the web, using IIS 6.0. The reports use stored procedures on a SQL server behind the scenes. When a report is kicked off the stored procedure begins running, and IF it completes within 3 minutes, the report viewer displays the report with no problem.

However, if the stored procedure takes longer than 3 minutes, which many longer date range reports do, the Crystal icon in the top right corner of the veiwer stops moving, and the viewer just remains blank. No error message is given. I know that the stored procedure continues to run behind the scenes, because if i hit the back button on the browser, and then forward, the icon begins moving again, and as soon as the stored procedure completes, the report returns. Sometimes i have to wait the 3 minutes, and go back and forward 3 or 4 times before the stored procedure completes and the report returns.

I have tried including a timeout request on the end of the URL calling the report, and even increasing the 'Connection Timeout' setting on the web component server, to no avail. I dont think this is the issue anyway, as the default timeout there is 20 minutes. This issue is occurring every 3 minutes.

Does anyone know of another timeout setting I should be looking for?See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm|||Hi I am having the same issue did you resolve this in the end?

Many thanks,
Chas|||yes i did. was a while ago and dont remember the exact steps, however, within the Crystal configuration manager on the web application server there were timeout settings which i think we just amped up to the max both the idle time & cache memory caps. worked like a charm. The only issue that still existed after that was once the cache reached that max that we set, crystal services required a manual restart, but that would normally takes weeks to happen. To alleviate that we put in a automatic cache purge to keep the cache down daily. worked like a charm, no issues since. knock on wood. :) Hope this helps

Crystal Report (using stored procedures - parameter passing)

i have created a crystal report using a stored procedure. in the web form im passing a parameter thru a textbox. but that value is not passing properly.

Dim myCMD As New SqlCommand("select_all_infor", myConn)
myCMD.CommandType = CommandType.StoredProcedure
Dim parr As New SqlClient.SqlParameter("@.Pid", SqlDbType.Int)
parr.Direction = ParameterDirection.Input
parr.Value = TextBox1.Text
myCommand.Parameters.Add(parr)
myCommand.Parameters.Add("@.Pid", TextBox1.Text)

Dim myDA As New SqlDataAdapter
myDA.SelectCommand = myCMD

Dim myDS As New myDS
myDA.Fill(myDS)

Dim myRPT As New fromSP_REPORT

myRPT.SetDataSource(myDS)

CrystalReportViewer1.ReportSource = myRPT

this is what i've written. Im not sure about connecting to a dataset. Pls correct me.

it's giving this error:

Procedure 'select_all_infor' expects parameter '@.Pid', which was not supplied.try to use this.

crReportDocument.SetParameterValue(0,(object) "first parameter");
crReportDocument.SetParameterValue(1,(object) "second parameter");

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.

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

Crosstab Qry with dynamic field names

Hi,

I am trying to create a stored procedures (SQL 7.0), to provide data
in
a crosstab format.
(I'm using Crystal Reports 8.5, but the Crosstab capabilities are
terrible, so I have to do as much as possible on the SQL side)

I have a table [Occurrences] with the following fields:
Year (int)
Month (int)
Occurs (int)
Claims (int)

I need a query to give me the following format:
Acct_Month 2001 2002 2003
Occurs Claims Occurs Claims Occurs Claims

January 120 180 132 196 110 140
February 154 210 165 202 144 178
March etc..
....

Catch! I need the Year field name to be the contents of the field
Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
approach.

I got the month name ok...
Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
Convert(Char(4),Year))

Is it possible to do this easely, without the use of cursors?

Any help would be much appreciated.

Luis PintoLuis (luispinto@.att.net) writes:
> I need a query to give me the following format:
> Acct_Month 2001 2002 2003
> Occurs Claims Occurs Claims Occurs Claims
> January 120 180 132 196 110 140
> February 154 210 165 202 144 178
> March etc..
> ...
> Catch! I need the Year field name to be the contents of the field
> Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
> approach.
> I got the month name ok...
> Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
> Convert(Char(4),Year))
> Is it possible to do this easely, without the use of cursors?

It takes a whole of dynamic SQL to get there.

You should probably investigate what RAC can do for you, see
http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Crosstab in TSQL

Does anyone know of any code to produce a crosstab table or
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details

> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>

Crosstab Help in Sql Server

I need some assistance, i have this Stored Procedure that will take my table
and create a cross tab. Unfor, I am getting duplicate value from the output,
when checking table1, there are no dups, i dont' know what i am doing wrong.
Please help
below, code that I am using. thaks
=================================
DECLARE @.Month_1_V as varchar(20)
DECLARE @.Create_Date_V as Datetime
DECLARE @.Corp_V as Numeric(13)
DECLARE @.Source_V as Varchar(20)
DECLARE @.Category_V as Varchar(50)
DECLARE @.Description_1_V as Varchar(50)
DECLARE @.Cycle_V as Varchar(1)
DECLARE @.Count_1_V as Numeric(13)
DECLARE @.Month_1_V2 as varchar(20)
DECLARE @.Create_Date_V2 as Datetime
DECLARE @.Corp_V2 as Numeric(13)
DECLARE @.Source_V2 as Varchar(20)
DECLARE @.Category_V2 as Varchar(50)
DECLARE @.Description_1_V2 as Varchar(50)
DECLARE @.Cycle_V2 as Varchar(1)
DECLARE @.Count_1_V2 as Numeric(13)
DECLARE @.V1 as Numeric(13)
DECLARE @.V2 as Numeric(13)
DECLARE @.V3 as Numeric(13)
DECLARE @.V4 as Numeric(13)
DECLARE @.V5 as Numeric(13)
DECLARE @.V6 as Numeric(13)
DECLARE @.V7 as Numeric(13)
DECLARE @.V8 as Numeric(13)
DECLARE @.V9 as Numeric(13)
DECLARE @.V10 as Numeric(13)
DECLARE @.V11 as Numeric(13)
truncate table tbl_Agency
DECLARE t_Noble_Agency CURSOR
FOR
SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
AS Count_1, Description_1, Cycle
FROM phonecol.temp_tblAgency
WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 102)
AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycle
ORDER BY Category, Corp, Source
OPEN t_Noble_Agency
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
IF @.@.FETCH_STATUS = 0
BEGIN
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
@.Source_V)
BEGIN
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
END
--@.V1 = 'Call_Backs'
--@.V2 = '[Left_Msg(Machine)]'
--@.V3 = '[Left_Msg(Live)]'
--@.V4 = 'Promise_to_Pay'
--@.V5 = 'Full_Pay'
--@.V6 = 'Partial_Pay'
--@.V7 = 'Past_Due_Pay'
--@.V8 = 'Wrong_Number'
--@.V9= 'TriTones'
--@.V10 = 'Skip_Trace'
--@.V11 = 'Not_Reported'
IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
Begin
SET @.V1 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
Begin
SET @.V2 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
Begin
SET @.V3 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
Begin
SET @.V4 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
Begin
SET @.V5 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
Begin
SET @.V6 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
Begin
SET @.V7 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
Begin
SET @.V8 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
Begin
SET @.V9 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
Contact'
Begin
SET @.V10 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
Begin
SET @.V11 = @.Count_1_V
END
END
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
END
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
END
CLOSE t_Noble_Agency
DEALLOCATE t_Noble_Agency
GOHi Justin,
I didn't look close into the SP. But from a high level I find that you
have two inserts for 1 fetch within the while loop (one inside the if
condition and one outside in the end). Can you tell why you are doing it.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:

> I need some assistance, i have this Stored Procedure that will take my tab
le
> and create a cross tab. Unfor, I am getting duplicate value from the outpu
t,
> when checking table1, there are no dups, i dont' know what i am doing wron
g.
> Please help
> below, code that I am using. thaks
> =================================
> DECLARE @.Month_1_V as varchar(20)
> DECLARE @.Create_Date_V as Datetime
> DECLARE @.Corp_V as Numeric(13)
> DECLARE @.Source_V as Varchar(20)
> DECLARE @.Category_V as Varchar(50)
> DECLARE @.Description_1_V as Varchar(50)
> DECLARE @.Cycle_V as Varchar(1)
> DECLARE @.Count_1_V as Numeric(13)
> DECLARE @.Month_1_V2 as varchar(20)
> DECLARE @.Create_Date_V2 as Datetime
> DECLARE @.Corp_V2 as Numeric(13)
> DECLARE @.Source_V2 as Varchar(20)
> DECLARE @.Category_V2 as Varchar(50)
> DECLARE @.Description_1_V2 as Varchar(50)
> DECLARE @.Cycle_V2 as Varchar(1)
> DECLARE @.Count_1_V2 as Numeric(13)
> DECLARE @.V1 as Numeric(13)
> DECLARE @.V2 as Numeric(13)
> DECLARE @.V3 as Numeric(13)
> DECLARE @.V4 as Numeric(13)
> DECLARE @.V5 as Numeric(13)
> DECLARE @.V6 as Numeric(13)
> DECLARE @.V7 as Numeric(13)
> DECLARE @.V8 as Numeric(13)
> DECLARE @.V9 as Numeric(13)
> DECLARE @.V10 as Numeric(13)
> DECLARE @.V11 as Numeric(13)
> truncate table tbl_Agency
> DECLARE t_Noble_Agency CURSOR
> FOR
> SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
> AS Count_1, Description_1, Cycle
> FROM phonecol.temp_tblAgency
> WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 10
2)
> AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
> GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycl
e
> ORDER BY Category, Corp, Source
> OPEN t_Noble_Agency
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> IF @.@.FETCH_STATUS = 0
> BEGIN
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
> @.Source_V)
> BEGIN
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> END
> --@.V1 = 'Call_Backs'
> --@.V2 = '[Left_Msg(Machine)]'
> --@.V3 = '[Left_Msg(Live)]'
> --@.V4 = 'Promise_to_Pay'
> --@.V5 = 'Full_Pay'
> --@.V6 = 'Partial_Pay'
> --@.V7 = 'Past_Due_Pay'
> --@.V8 = 'Wrong_Number'
> --@.V9= 'TriTones'
> --@.V10 = 'Skip_Trace'
> --@.V11 = 'Not_Reported'
> IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
> Begin
> SET @.V1 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
> Begin
> SET @.V2 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
> Begin
> SET @.V3 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
> Begin
> SET @.V4 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
> Begin
> SET @.V5 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
> Begin
> SET @.V6 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
> Begin
> SET @.V7 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
> Begin
> SET @.V8 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
> Begin
> SET @.V9 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
> Contact'
> Begin
> SET @.V10 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
> Begin
> SET @.V11 = @.Count_1_V
> END
> END
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> END
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> END
> CLOSE t_Noble_Agency
> DEALLOCATE t_Noble_Agency
> GO|||sure,
what happens first is that data will be imported to a temporary table
from there, the SP should take the data from that table and change it to a
crosstab
inserting output to finalize table
unfor, in the finalize table, i am getting duplicated values
"Omnibuzz" wrote:
> Hi Justin,
> I didn't look close into the SP. But from a high level I find that you
> have two inserts for 1 fetch within the while loop (one inside the if
> condition and one outside in the end). Can you tell why you are doing it.
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Justin" wrote:
>|||Where is the insert script for the finalise table?
I assume that tbl_Agency is the temp table you are referring to and it can
have duplicates. Can you post the cross tab query?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:
> sure,
> what happens first is that data will be imported to a temporary table
> from there, the SP should take the data from that table and change it to a
> crosstab
> inserting output to finalize table
> unfor, in the finalize table, i am getting duplicated values
>
> "Omnibuzz" wrote:
>

Cross-database Dependency Check

Is there anyway to check for cross-database dependencies?

For example:
I have a stored proc in the Pubs database that contains a query that references a table in the Northwind database (as well as reference a table in the Pubs database.) I'm having no problems finding the depencency for the Pubs table. I can't seem to find the dependency for the Northwind table.

Any help would be appreciated.

Thanks in advance.

ScottSQL Server 2000 only tracks objects in the current database within dbo.sysdepends.

-PatP

Cross tab stored procedure

Hi
I have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ie
DateTime Variable Value.
T1 V1 xxxx
T1 V2 xxxx
T1 V3 xxxx
T2 V1 yyyy
T2 V2 yyyy
T2 V3 yyyy
In a SQLserver stored procedure I would like to insert this data into a temp table like the following. I don't need summaries just need a table or query where I can select on a variables value. ie "select * from temptable where V3 = yyyy"
Datetime V1 V2 V3
T1 xxxx xxxx xxxx
T2 yyyy yyyy yyyy
any suggestions for an SQL statement.
thanks in advance.
I don't exactly understand your business requirements. Are V1, V2, and V3 the only possible values for the Variable column? If so, something like this might help to get you started:

SELECT
DateTime,
MIN(V1),
MIN(V2),
MIN(V3)
FROM
(
SELECT
DateTime,
CASE
WHEN Variable = 'V1' THEN Value ELSE NULL
END AS V1,
CASE
WHEN Variable = 'V2' THEN Value ELSE NULL
END AS V2,
CASE
WHEN Variable = 'V3' THEN Value ELSE NULL
END AS V3
FROM
yourTable
) AS subQuery
GROUP BY
DateTime


|||

Thanks Terri

The base table contains many variables. The data in this table is produced by a periodic time base event which inserts a row for each of the variables along with their current value. What I am trying to achieve is a record set where I can compare the values of selected variables in a single row based on the timestamp. ie
DateTime Var1 Var2 Var3
1/1/2005 10:00 Product1 10 20
1/1/2005 10:05 Product2 9 15
1/1/2005 10:10 Product2 12 30
1/1/2005 10:20 Product1 15 16
Once I have this record set I can then do selections based on the value of any variable ie
"select * from recorset where Var1 = 'Product1'"
I tried a statement similar to the one you have posted but my case statement sintax wasn't quite right.
I'm not in the office for a few days so may not get a chance try your code.
regards
Noel

Cross Join without Table?

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk

I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end

But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown

Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.

Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(

I think this will work...

Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.

It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.

Cross Join Without Table?

I have the following structure with remote select permissions; I cannot
create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a
cross-join, group by in the past - but what do you do if you don't have a
table? For example, the end result when selecting event_pk=(112,113) would
be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbYou can use a derived table construct with a set of numbers. Your
requirements are not very clear from the narrative, can you post your table
structure & sample data along with expected results? For details refer to:
www.aspfaq.com/5006
Anith

Cross Database Permission

I have a stored procedure that I created on one database with permission
granted to a select group of users. The proc grabs information from another
database on the same server. When users with permissions to the proc try to
run it, they get permission errors saying that they do not have permissions
to access the other database that is referenced in the proc. It was my
understanding that when you run a proc, it runs under the context of the proc
creator (in this case dbo), otherwise you have to grant select, insert,
update, etc. on all tables that your proc references. Does this not work
cross database?Clark,
You do not mention which version of SQL Server you are using, but both 2000
and 2005 have in the Books Online index an entry for "cross-database
permissions" which you should read.
Basically, the ownership-chain is broken at a database boundary as I quote:
"Cross-database permissions are not allowed; permissions can be granted only
to users in the current database for objects and statements in the current
database. If a user needs permissions to objects in another database, create
the user account in the other database, or grant the user account access to
the other database, as well as the current database."
There is a setting to turn on "cross database ownership chains", but that is
not recommended due to the (potentially serious) security side-effects.
RLF
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
>I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from
> another
> database on the same server. When users with permissions to the proc try
> to
> run it, they get permission errors saying that they do not have
> permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the
> proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?|||It is running on SQL Server 2000 with the allow cross-database ownership
chaining disabled. So I take it dbo does not have cross database access,
which makes sense now that I think about. Thanks.
"Russell Fields" wrote:
> Clark,
> You do not mention which version of SQL Server you are using, but both 2000
> and 2005 have in the Books Online index an entry for "cross-database
> permissions" which you should read.
> Basically, the ownership-chain is broken at a database boundary as I quote:
> "Cross-database permissions are not allowed; permissions can be granted only
> to users in the current database for objects and statements in the current
> database. If a user needs permissions to objects in another database, create
> the user account in the other database, or grant the user account access to
> the other database, as well as the current database."
> There is a setting to turn on "cross database ownership chains", but that is
> not recommended due to the (potentially serious) security side-effects.
> RLF
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
> >I have a stored procedure that I created on one database with permission
> > granted to a select group of users. The proc grabs information from
> > another
> > database on the same server. When users with permissions to the proc try
> > to
> > run it, they get permission errors saying that they do not have
> > permissions
> > to access the other database that is referenced in the proc. It was my
> > understanding that when you run a proc, it runs under the context of the
> > proc
> > creator (in this case dbo), otherwise you have to grant select, insert,
> > update, etc. on all tables that your proc references. Does this not work
> > cross database?
>
>|||Clark Kent,
1 - Cross-Database Ownership Chaining should be enabled
2 - If the owner of the sp is the owner of the objects being referenced from
the other db, then there should be no problem, but if the owner of the sp
just has rights to "select", then SS will check if the one executing the sp
also has "select" right on those objects.
Ownership Chains
http://msdn2.microsoft.com/en-us/library/ms188676.aspx
SQL Server 2005 Security Overview for Database Administrators
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLSecurityOverviewforAdmins.doc
Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
AMB
"Clark Kent" wrote:
> I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from another
> database on the same server. When users with permissions to the proc try to
> run it, they get permission errors saying that they do not have permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?