Showing posts with label building. Show all posts
Showing posts with label building. Show all posts

Monday, March 19, 2012

error on building up where clause statement

how can I resolve this error message coming from the line of code used to
build up a whereclause ?
error message
--
syntax error converting the varchar value ' and IDUser >= 1 ' to a column of
data type int
sproc
--
...
IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser = '
+ @.ClientID END
IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >= 1
' END
...SQL Server thinks that you want to mathematical add two numbers together
because there is a INT in the addition. To Keep sure to concat the two
strings together you have to cast the INT value to a character type like
varchar(10), so the whole expression should be:

> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + CAST(@.ClientID AS VARCHAR(50)) END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> how can I resolve this error message coming from the line of code used to
> build up a whereclause ?
> error message
> --
> syntax error converting the varchar value ' and IDUser >= 1 ' to a column
> of data type int
> sproc
> --
> ...
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + @.ClientID END
> IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >=
> 1 ' END
> ...
>|||thanks for replying, I tried your suggestion but I still get the same error
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
> SQL Server thinks that you want to mathematical add two numbers together
> because there is a INT in the addition. To Keep sure to concat the two
> strings together you have to cast the INT value to a character type like
> varchar(10), so the whole expression should be:
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TJS" <nospam@.here.com> schrieb im Newsbeitrag
> news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Can you send in he whole statements and the exact error to see where the
Parser throws the error ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:OKz0PDvaFHA.2768@.tk2msftngp13.phx.gbl...
> thanks for replying, I tried your suggestion but I still get the same
> error
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
>|||Could it be that @.WhereClause is declared as integer?
Ral Moloye|||here is current version, the purpose of the sproc is to return a list if
matching record id's
based on filter parameters provided.
========================================
============================
Alter Procedure "ListID"
(
@.admin nvarchar(10),
@.YearID int,
@.ClientID int,
@.ProjectID int
)
As
DECLARE @.List varchar(100)
DECLARE @.WhereClause nvarchar (1000)
DECLARE @.CastString1 varchar (50)
DECLARE @.CastString2 varchar (50)
SET @.WhereClause=''
SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
-- Define the selection criteria for client depending on whether or not
user is an admin
-- ----
---
IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString1 END
IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString2 END
IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
@.CastString2 END
-- Define the selection criteria for selections of project and year
-- ----
---
IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDProject
= ' + @.ProjectID END
IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
year(issueDate) = ' + @.YearID END
-- retrieve selection
-- ----
---
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
-- return results
-- ----
---
SELECT @.List|||Everywhere where a INt takes place (doesntmatter if you declared it in your
procedure or you get it from the database) and you want to add it to the
string, SQL Server will try to do a methematical addition to the strings
which wont work, so you will have to replace that in your whole code (e.g.
For YearID, projectId ...)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:uVOaKl4aFHA.3840@.tk2msftngp13.phx.gbl...
> here is current version, the purpose of the sproc is to return a list if
> matching record id's
> based on filter parameters provided.
> ========================================
============================
> Alter Procedure "ListID"
> (
> @.admin nvarchar(10),
> @.YearID int,
> @.ClientID int,
> @.ProjectID int
> )
> As
> DECLARE @.List varchar(100)
> DECLARE @.WhereClause nvarchar (1000)
> DECLARE @.CastString1 varchar (50)
> DECLARE @.CastString2 varchar (50)
> SET @.WhereClause=''
> SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
> SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
> -- Define the selection criteria for client depending on whether or not
> user is an admin
> -- ----
---
> IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString1 END
> IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString2 END
> IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
> @.CastString2 END
> -- Define the selection criteria for selections of project and year
> -- ----
---
> IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> IDProject = ' + @.ProjectID END
> IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> year(issueDate) = ' + @.YearID END
> -- retrieve selection
> -- ----
---
> SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
> FROM invoices, Projects
> WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
> -- return results
> -- ----
---
> SELECT @.List
>

Sunday, February 19, 2012

Error Message after installing SRS SP2

I am building my dev box. I installed SQL Server 2000. I installed SQL Server
2000 SP4. I installed SQL Reporting Services Developer Edition. Installed SRS
SP2 (this seemed to hang, and ran very slowly on my P4 3GHz box with 2GB of
RAM). When I try to launch Report Manager, I get this error:
Method not found: Void System.Xml.XmlTextReader.set_ProhibitDtd(Boolean).
Any ideas? Should I uninstall/reinstall SRS SP2?
TIA,
--
Berry Morgan
JSOHi Berry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood that you are failed to connect Report
Manager after applied SQL Server Reporting Service SP2. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowledge, it is abnormal when it seems to hang, and ran very
slowly during the installation of SP2. So please hlep me check whether the
installation of SP2 is successfully.
1. Check whether ReportServer in Services is launched successfully, check
whether there is any related information in Event Logs
2. Except Report Manager, could you go into Report Server? Assuming you are
install Reporting Service and IIS on the same local machine. Could you see
the RS version from http://localhost/ReportServer ?
3. Generally speaking, "Method not found" is a common error message which
means some SQL server component isn't installed correctly. It seems XML
compoents is wrong and I recommand you reinstalling VS.NET 2003 and let me
know the results.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, February 17, 2012

Error Message "Key Sequence columns are not supported at the case level"

Hi group,
I was trying to build a sequence clustering model in MSAS.
I could deploy the model. Then while building the model, I end up getting an error with message "Key Sequence columns are not supported at the case level".

Can some one tell me what it means and how i can avoid it?

Thanks in advance to any kind of help here.
The Microsoft_Sequence_Clustering algorithm requires the sequence
information to be modeled as a nested table so you can only have a nested table column with Content=Key Sequence (right-click on the column in the Mining Structure tab and look at the Properties pane).

In the above case, you will have a case level key that uniquely identifies a sequence set (modelled as multiple nested table rows) and you'll need to mark that with Content=Key and IsKey=True.|||Thanks for your suggestion Raman.
I have made the change that you have suggested and proceeded to the processing step.

Hmm, the processing fails with error msg:
"OLE DB or ODBC error: A column has been specified more than once in the order by list. Columns in the order by list must be unique.; 42000."

The "Key sequence" column in the nested table has been used twice in a query. Query attached in the end for reference.
What could be the cause for this? / How do i make AnSer stop generating this wrong query?
(How do you like my new short name to MSAS? AnSer. Sounds like 'answer') :)

FYI.. the "Key Sequence" column in the nested table has a FK relationship with the "Key" column of the case table.

The processing fails because it runs the query:
SELECT
[nestedTable].[col1]
,[nestedTable].[col2]
,[nestedTable].[col3]
FROM [nestedTable]
ORDER BY [nestedTable].[col1]
,[nestedTable].[col1]
--col1 is a sequence key and has FK relation with the case table.|||The "Key Sequence" is the key of the nested table, while the foreign key
should be the column you used to join case table
and nested table. These 2 columns should be 2 seperate columns in the nested
table. The following is an example:

SeqClustCase -- Case table
UserId (Key)
A1
A2

SeqClustNested -- Nested table
UserId (foreign key)
Order(Key sequence)
Sequence (sequence data)

Please try fix your data source view and mining structure according to the
above example.

Thanks,|||

Actually the foreign key column is implicitly included as part of the bindings for the nested table when you use BI Dev Studio and you don't need to specify it as a column in the nested table.

Can you please post your mining structure and model definitions?