Error loading report

I have created many crystal reports within VB.Net that are based upon an *.xsd file as the report source is dynamic. This works fine on the machine they were developed on. However I now wish to roll out the final solution and store the reports on a shared network drive whilst the VB app is on each desktop. I have tried copying the *.rpt file to the shared drive along with the *.xsd files. I even changed each report to now look at the *.xsd in the new location. However when I try to open a report I get the following error:

Error in file e:\directory\report.rpt.
Failed to load database information.

Any ideas.?

ThanksMake sure it is pointing the correct database|||Check if proper DSN is created and Verify the Database in the report at design time.|||With e:\ being the network drive my report e:\myreport.rpt is pointing to e:\myreport.xsd

which is correct as far as I know.|||Have ttried changing network paths to UNC, no joy.

Strangely, if I load the project within VB.Net, the app and the reports run fine. But when I run the installed package I get this error. This makes me think that maybe I need to package up some additional files with the app.?

Error in sql stored procedure

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks.

ALTERprocedure [dbo].[USP_Account_Search_Mod]

@.ClientCode VARCHAR(7)=''

,@.DebtorName VARCHAR(25)=''

,@.DebtorNumberINT= 0

,@.AccountNumber VARCHAR(30)=''

,@.ReferenceNumber VARCHAR(30)=''

,@.TierINT= 0

,@.Status VARCHAR(5)=''

,@.UserIDINT

,@.MonthDateTime=NULL

,@.FromDateDateTime=NULL

,@.ToDateDateTime=NULL

,@.OriginalMindecimal= 0

,@.OriginalMaxdecimal= 0

,@.CurrentMindecimal= 0

,@.CurrentMaxdecimal=0

,@.lstAmountSelect VARCHAR(3)

,@.IsActivebit= 1

AS

DECLARE

@.SQLTier1Select VARCHAR(2000)

,@.SQLTier2Select VARCHAR(2000)

,@.Criteria VARCHAR(2000)

,@.SQL VARCHAR(8000)

,@.CRI1 VARCHAR(100)

,@.CRI2 VARCHAR(100)

,@.CRI3 VARCHAR(100)

,@.CRI4 VARCHAR(100)

,@.CRI5 VARCHAR(100)

,@.CRI6 VARCHAR(200)

,@.CRI7 VARCHAR(500)

,@.CRI8 VARCHAR(500)

,@.CRI9 VARCHAR(500)

SELECT @.CRI1=''

,@.CRI2=''

,@.CRI3=''

,@.CRI4=''

,@.CRI5=''

,@.CRI6=''

,@.CRI7=''

,@.CRI8=''

,@.CRI9=''

,@.Criteria=''

SELECT @.DebtorName=REPLACE(@.DebtorName,'''','''''');

Print @.DebtorName

if(SELECT UserTypeIDFROM dbo.tbl_Security_UsersWhere UserID= @.UserID)= 3AND @.ClientCode=''

return(-1)

IFLEN(@.DebtorName)> 0

SET @.CRI1=' AND Name like '+'''%'+ @.DebtorName+'%'''

IF @.DebtorNumber> 0

SET @.CRI2=' AND Number = '+CAST(@.DebtorNumberAS VARCHAR(7))

IFLEN(@.AccountNumber)> 1

SET @.CRI3=' AND AccountNumber like '+'''%'+ @.AccountNumber+'%'''

IFLEN(@.ReferenceNumber)> 0

SET @.CRI4=' AND Account like '+'''%'+ @.ReferenceNumber+'%'''

IFLEN(@.ClientCode)> 1

SET @.CRI5=' AND Customer = '+''''+ @.ClientCode+''''

SET @.Status=RTRIM(@.Status)

IF((@.StatusNotIN('ALL','ALA','ALI'))AND(LEN(@.Status)>1))

BEGIN

IF(@.Status='PAID')

SET @.CRI6=''

IF(@.Status='CANC')

SET @.CRI6=' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'

END

--PRINt @.CRI6

IFLEN(CONVERT(CHAR(8), @.Month, 112))> 0

BEGIN

IF(LEN(CONVERT(CHAR(8), @.FromDate, 112))> 0ANDLEN(CONVERT(CHAR(8), @.ToDate, 112))> 0)

BEGIN

SET @.CRI7=' AND Received BETWEEN '+''''+CONVERT(CHAR(8), @.FromDate, 112)+''''+' AND '+''''+CONVERT(CHAR(8), @.ToDate, 112)+''''

END

ELSE

BEGINSET @.CRI7=' AND DATEPART(mm, Received) = DATEPART(mm, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''+') AND DATEPART(yy, Received) = DATEPART(yy, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''

END

END

IF @.lstAmountSelect='ALL'

SET @.CRI8=''

elseIF @.lstAmountSelect='DR'

BEGIN

SET @.CRI8=' AND OriginalBalance >= '+convert(Varchar,@.OriginalMin)+'AND OriginalBalance<='+convert(Varchar,@.OriginalMax)+' AND CurrentBalance >= '+convert(Varchar,@.CurrentMin)+'AND CurrentBalance<='+convert(Varchar,@.CurrentMax)

END

ELSEIF @.lstAmountSelect='OLC'

BEGIN

SET @.CRI8=' AND OriginalBalance < CurrentBalance '

END

ELSEIF @.lstAmountSelect='OGC'

BEGIN

SET @.CRI8=' AND OriginalBalance > CurrentBalance '

END

ELSEIF @.lstAmountSelect='OEC'

BEGIN

SET @.CRI8=' AND OriginalBalance = CurrentBalance '

END

SELECT @.Criteria= @.CRI1+ @.CRI2+ @.CRI3+ @.CRI4+ @.CRI5+ @.CRI6+ @.CRI7+ @.CRI8

--PRINT @.Criteria

--PRINT @.CRI7

if @.Status='ALL'OR @.Status='ALA'OR @.Status='ALI'--All Period

BEGIN

if(@.Status='ALL')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'

END

if(@.Status='ALA')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'

END

if(@.Status='ALI')--All Inactive

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'

END

END

ELSEIF @.Status='PAID'

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'

END

ELSE

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

END

SELECT @.SQL=CASE @.Tier

WHEN 0THEN @.SQLTier1Select+' UNION '+ @.SQLTier2Select+'ORDER BY NAME ASC'

WHEN 1THEN @.SQLTier1Select+'ORDER BY NAME ASC'

WHEN 2THEN @.SQLTier2Select+'ORDER BY NAME ASC '

END

PRINT @.SQL

--SELECT @.SQL

EXEC(@.SQL)

Could you help us help you better by opening your stored procedure up in a query analyzer (like SQL 2005 QA or SQL Express Managment Studio) and parsing the query to determine what line number and what the syntax error is?

That is a lot of code and would be difficult for us to go over line by line to determine what the issue is.

|||

I do not have SQL Query Analyzer. Here is the error I get when I saw in event viewer

Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction., Error Source:.Net SqlClient Data Provider.

|||

there's a lock on one of the tables your trying to do an insert on or update on. Talk with your DBA about that error and let him/her know what the Process ID is to help track it down.

Out of curiosity, what environment are you writing your stored procedures in?

|||

I'm writing my stored procedure in SQL Server Management Studio Express

|||

In SQL Server Management Studio Express you can parse your query (stored procedure syntax) and it will either tell you the syntax error along with the line number or tell you that the command was successful, which means you can run (execute) it and thus create your stored procedure.

Error in MS Stored Procedure

I am current creating a Oracle membership provider in dotnet based on the MS membership provider. One of the stored procedures that need converting is shown below. The variable UserId (highlighted blue) is declare and never assigned to but is used within the update query also highlighted in blue. If this is an oversight then I presume that this part of the stored procedure is never actually executed. Can somebody put an eye over this code to confirm.

Thanks.

ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.CurrentTimeUtc datetime,
@.UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @.UserId uniqueidentifier

IF (@.UpdateLastActivity = 1)
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @.CurrentTimeUtc, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1

UPDATE dbo.aspnet_Users
SET LastActivityDate = @.CurrentTimeUtc
WHERE @.UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1
END

RETURN 0
END

It is a bug in the code. The code is wrong and there are other problems with the logic. You should file a bug at http://connect.microsoft.com.

Error in Dynamic SQL.....help!!

Can someone please help me in troubleshooting the code below. I have a table called credit_app_table_status which is based on the following create statement:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

When I run the code below, it gives me the following error message. I have tried to change the data types of bill_period_start and bill_period_end apart from changing the code, but it doesn't work. Instead of @.bill_period_start and @.bill_period_end, if I have actual dates, it works. Can someone pleaseeeee help.

/* Error Message
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
*/

--Code

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start varchar (20),
@.bill_period_end varchar (20)

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = bill_period_start from credit_app_table_status
SELECT @.bill_period_end = bill_period_end from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
','
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
' and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

Exec ( @.SQL)

GO+ @.bill_period_end +
'group by a.org_id order by a.org_id'


Exec ( @.SQL)

GO
It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?|||It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?
Thanks for the information Tomh3. The problem that I saw was that
@.bill_period_start and @.bill_period_end didn't have the single quotes such as in:
'Sep 1 2004 12:00AM'. Can you please let me know how I can get single quotes in @.bill_period_start and @.bill_period_end.

Thank you so much!!

Below is the code I received using select @.sql:

insert into csorg_billing_flash
(bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date)
select
Sep 1 2004 12:00AM,
Oct 31 2004 12:00AM ,
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group
where group_type_cn not in (7,8,9,10)
)
and a.ship_date between
Sep 1 2004 12:00AM and Oct 31 2004 12:00AM
group by a.org_id
order by a.org_id|||DECLARE @.bill_period_start datetime
SELECT @.bill_period_start = 'Sep 1 2004 12:00AM'
SELECT ''''+CONVERT(varchar(25),@.bill_period_start)+''''|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start datetime,
@.bill_period_end datetime

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
', '
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
'and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

SELECT (@.SQL)

GO|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status


Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").|||Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").
Sorry, but I am still getting the same error message. Also I tried to run it having an apostrophe around @.bill_period_start and @.bill_period_end and still it doesn't work.
For example:

+ ' @.bill_period_start ' +
'and '
+ ' @.bill_period_end ' +|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...|||--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...
--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO

Error in DMX query

Hi

I have the following query used to predict the values in 2006 based on cluster model built using 2004 and 2005 data. I'm using a prediction join on a cube containing the data. I can join a single dimension from the cube to a column in a the data mining model. However when I try to join to a column in a nested table I get this error:

Executing the query ...

Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader

Internal error: An unexpected exception occured.

Execution complete

The query I'm running is this

select t.*

, Predict([Time].[Deal Count])as predicted_deal_count

, Predict([Time].[Deal Revenue])as predicted_deal_revenue

, Cluster()

From

[Time]

prediction join

(

select {[Measures].[Deal Count], [Measures].[Deal Revenue]} on 0,

non empty {{[Time].[Month Number Of Year].[Month Number Of Year].members} *

{[Country].[KeyCountry].[KeyCountry].members} * {[Country].[Region].[Region].members}} on 1

from [DM]

where ([Time].[Year].&[2006-01-01T00:00:00])) as t

on

[Time].[Month Number Of Year] = t.[[Time]].[Month Number Of Year]].[Month Number Of Year]].[MEMBER_CAPTION]]]

and [Time].[Country].[KeyCountry] = t.[[Country]].[KeyCountry]].[KeyCountry]].[MEMBER_CAPTION]]]

and [Time].[Country].[Region] = t.[[Country]].[Region]].[Region]].[MEMBER_CAPTION]]]

I'm running SQL Server 2005 with SP2 CTP2.

Thanks

Sanjay

Given the limited error message - we need to try additional queries to see if we can narrow the problem down. Alternatively, you could send us a backup of your database - let me know if this is an option.

Can you see if this query works? Does it return what you expect?

select t.*

From

[Time]

NATURAL prediction join

(

select {[Measures].[Deal Count], [Measures].[Deal Revenue]} on 0,

non empty {{[Time].[Month Number Of Year].[Month Number Of Year].members} *

{[Country].[KeyCountry].[KeyCountry].members} * {[Country].[Region].[Region].members}} on 1

from [DM]

where ([Time].[Year].&[2006-01-01T00:00:00])) as t

|||

Hi,

Your query works as expected, all the selected data from the cube is return. It would be possible to send you the cube offline, do you need the just the cube or the relational db as well?

Thanks

Sanjay

|||

A backup of AS database with the cube and model is enough. Please remove any additional objects (e.g. cubes or mining structures) to reduce the database size. You can also do a process clear structure only on the Mining Structure to reduce the size even further. Try compressing it as well (although it should be compressed already).

You can send the data to jamie "at" sqlserverdatamining.com and we'll look into the issue

Thanks!

|||We are still looking into this issue - thanks for your patience.

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.