Showing posts with label onthe. Show all posts
Showing posts with label onthe. Show all posts

Monday, March 12, 2012

Hewlp needed creating a stored proceedure

Can someone please help me...I have created a DNN module that works onthe test site but when I upload the module zip to a new site I get anerror on creating my stored proceedure as follows:

StartJobBegin Sql executionInfoExecuting 01.00.00.SqlDataProviderStartJobStart Sql execution: 01.00.00.SqlDataProvider fileFailureSQL Execution resulted infollowing Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@.Str_Title'. Line 51: Incorrect syntax near '@.Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESCEndJobEnd Sql execution: 01.00.00.SqlDataProvider file


The SP looks like this:

/* --------------------------
/ ListTAS_Journal
/ -------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@.PortalID int,
@.SortOrder tinyint = NULL,
@.Str_Title varchar(100) = '',
@.Str_Text varchar(100) = ''
AS

IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = ''

SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

Notice, your pluses are missing.

|||Hi Thanks for the reply,

Yes the Pluses are in the sql file if you look at my first post but theerror is reporting they are not there so DNN must be stripping them outfor some reason!

Mark

Wednesday, March 7, 2012

HELP: SSEE problems

I've a Windows 2003 Server Web Edition server. I've installed SSEE on
the same server. I've 5 domains (and 5 different web applications).
Every app needs to connect to 1 different db.
Both, with native securiy or Windows security, I obtain that only 1 app
work correctly while with the others I obtain "Application Server
Unavailable". If I connect all the apps to the same db then all works right.
Can someone hel me to solve this problem?
Thanks in advance,
FrancoHi Fransis
"Fransis il Mulo" wrote:

> I've a Windows 2003 Server Web Edition server. I've installed SSEE on
> the same server. I've 5 domains (and 5 different web applications).
> Every app needs to connect to 1 different db.
> Both, with native securiy or Windows security, I obtain that only 1 app
> work correctly while with the others I obtain "Application Server
> Unavailable". If I connect all the apps to the same db then all works righ
t.
> Can someone hel me to solve this problem?
> Thanks in advance,
> Franco
Have you enabled network protocols for SQL Express? By default they will be
disabled, but that can be overridden when you install SQL Express or later
using SQL Server Configuration Manager see
http://msdn2.microsoft.com/en-us/library/ms165718.aspx
John