Just Character Only Of Procedure/function

Feb 12, 2008

Hi,

i don't know how i will explain you guys, but is there any way to compair the text of two procedures/functions.
the thing comes in my mind is to compair the text no any spaces or [enter].
So can any one help or idea.

Thanks and looking forward.
-MALIK

View 1 Replies


ADVERTISEMENT

CLR Function - Invalid Character In The Given Encoding

Dec 18, 2006

Hello!



I wrote a CLR function, which is receiving some XML parameters. In certein situations it gives me the following error message:


Msg 6522, Level 16, State 1, Line 58
A .NET Framework error occurred during execution of user-defined routine or aggregate "svmScale":
System.Xml.XmlException: Invalid character in the given encoding. Line 1, position 27.
System.Xml.XmlException:
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
at System.Xml.XmlTextReaderImpl.InvalidCharRecovery(Int32& bytesCount, Int32& charsCount)
at System.Xml.XmlTextReaderImpl.GetChars(Int32 maxCharsCount)
at System.Xml.XmlTextReaderImpl.ReadData()
at System.Xml.XmlTextReaderImpl.ParseText(Int32& startPos, Int32& endPos, Int32& outOrChars)
at System.Xml.XmlTextReaderImpl.ParseText()
at System.Xml.XmlTextReaderImpl.ParseElementContent()
at System.Xml.XmlTextReaderImpl.Read()
at System.Xml.XmlTextReader.Read()
at System.Xml.XmlWriter.WriteNode(XmlReader reader, Boolean defattr)
at System.Data.SqlTypes.SqlXml.CreateMemoryStreamFromXmlReader(XmlReader reader)
at System.Data.SqlTypes.SqlXml..ctor(XmlReader value)
at UserDefinedFunctions.svmScale(SqlXml sql_problem, Boolean perChannel, Single x_factor, Single y_factor)



The call:

declare @data xml, @param xml

set @data = '<root>
<d>
<y>3.950000000000000e+002</y>
<v>3.896900000000000e+004</v>
<v>3.950000000000000e+002</v>
<v>0.000000000000000e+000</v>
<v>0.000000000000000e+000</v>
<v>0.000000000000000e+000</v>
<v>1.517142857142857e+001</v>
<v>1.027035714285714e+003</v>
<v>1.241071428571428e+000</v>
<v>5.185714285714286e+000</v>
</d></root>'


set @data = dbo.svmScale(@data, 1, 0.5, 0.5)


If I cahnage one of the zeros in the given tag, the error message disappears. If i cut the given tag, the error message disappears. If I cut the given tag, and then paste the previous one in place of it, the error message APPEARS. So my conclusion is, that any tag can be wrong on a specific position in the XML. This makes me wonder.

Another wonderfull thing is, that if I take this CLR function, and the same TSQL code, and I do run it on my notbook with my SQL Express, no error message. But if I try to use it on the server, with SQL 2005 it drops me this error message. The resulting XML is coming from an SELECT FOR XML AUTO, so i suspect it not having illegal characters inside. This is probably true, becouse the above replace procedure can make the message disapear.


The server:
Product: Microsoft SQL Server Enterprise Edition
Op System: Microsoft Windows NT 5.2 (3790)
Platform: NT INTEL X86
Version: 9.00.3027.00
Language: English (United States)
Memory: 4095 (MB)
Processors: 2
Collation: SQL_Latin1_General_CP1_CI_AS
Clustered: False


The notebook:
Product: Microsoft SQL Server Express Edition
Op System: Microsoft Windows NT 5.1 (2600)
Platform: NT INTEL X86
Version: 9.00.1399.06
Language: English (United States)
Memory: 1015 (MB)
Processors: 1
Collation: SQL_Latin1_General_CP1_CI_AS
Clustered: False



I would be very greatul for any suggestions.


Thanks


Arpad Varga

View 2 Replies View Related

T-SQL (SS2K8) :: Split Function That Allows Multiple Character Delimiter

Mar 25, 2015

I have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,

OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE

Looking for split function that accepts a multiple character value as a delimiter?

View 7 Replies View Related

Simple Function For Returning A Character Based On Search Criteria..

Feb 20, 2007

Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen

View 2 Replies View Related

Stored Procedure And '+' Character

Sep 9, 2007

Hi.
I need some help with the following sp that I created on a SQL2000 server

CREATE Procedure Users_Authenticate
@account varchar(50),
@password varchar(24)

AS

Select * From Users
Where account = @account
And password = @password
GO

When calling this sp from my app (VS2005) it works fine for most passwords. However it fails if the password contains a plus sign e.g. tXke4572Wd+IbRmjmcGexw==

If I use a View to test this password then the password is split up as 'tXke4572Wd' + 'IbRmjmcGexw=='
I assume that SQL server does the splitting but correct me if I'm wrong.

How can I get this sp to work with passwords containing plus signs (or any other characters).

Cheers,
Frank

View 3 Replies View Related

Parsing Character Strings In A Stored Procedure

Jul 11, 2001

Hi guys,

I'm trying to parse a character string in a stored procedure but I'm not sure how to do it. For example:

-------------------------------
create procedure sp_test (
@fld varchar(1000)
)
AS

???
???
.
.
.
.
----------------------------------

sp_test '123:abc,456:def,789:ghi'


I need to put 123 into a seperate local variable, abc into a local variable, and so on...

What would be the best function to use?

Any help would greatly be appreciated.

- Gary

View 1 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

I have the following

Column Name : [Converted Date]
Data Type : varchar(50)

When I try and do month around the [Converted Date] I get the following error message

“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”

My Query is

SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

View 7 Replies View Related

Character Limit For Variables Inside A Stocked Procedure

Feb 23, 2007

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you! 

View 10 Replies View Related

How To Use Non-basic AscII Character In Stored Procedure Parameter

Apr 30, 2007

hi all,



I have a stored procedure with a input parameter ( xml block).



sp: spGetAddr @xmlText



the style of parameter @xmlText looks like

<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>



If I use non-basic ASCII character, just like 'á' in the parameter, the execution fails and I get 'The error description is 'An invalid character was found in text content.''



But this kind of special characters should be allowed in the name column of the database table.



If I use the htlm code '&#xE1" to replace 'á' in the parameter, then the execution of the stored procedure is fine. but how can I change all of the possible sepcial characters?



If somebody knows how to handle this issue, please help me. I'm pre-appreciated for all the idea and suggestion!



Thanks,



Jone

View 4 Replies View Related

Function To Return Remaining Of Field After It Finds A Character In The Field.

Feb 20, 2007

Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen

View 1 Replies View Related

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.

Sep 12, 2006

 Hi everybody,   I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End


oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View 1 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Mar 26, 2007

Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
 
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
 
Thanks Nickdel68

View 5 Replies View Related

Unclosed Quotation Mark Before The Character String 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]

Feb 7, 2007

I have an app that I created and I am trying to upload the MS SQL DB to my web host.  I downloaded MS SQL Server Database Publishing Wizard and then open up Visual Studio 2005.  I right click my database, and then try publishing it.  I convert to a MS 2000 DB (was originally 2005).  I save the .sql statement and try copying into the host's (Godaddy) query analyzer.  (It's only about 400k).
 Well, I tried cutting and pasting the doc so I could see exactly where I get the error.  I get a good part of it successful, but then I try the pasting the code below (not modified at all) and get the error about "unclosed quotation..."  I skip this and go to the next process, and is successful, but often, I am getting this error....  WHY?
 Please help.
 Code copied directly from generated script from MS Publishing wizard:
 
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END'
END
GO
***************************ERROR:****************************





Unclosed quotation mark before the character string 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranS...
/****** Object:  StoredProcedure [dbo].[aspnet_Users_DeleteUser]    Script Date: 02/07/2007 18:34:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]    @ApplicationName  nvarchar(256),    @UserName         nvarchar(256),    @TablesToDeleteFrom int,    @NumTablesDeletedFrom int ASBEGIN    DECLARE @UserId               uniqueidentifier    SELECT  @UserId               = NULL    SELECT  @NumTablesDeletedFrom = 0    DECLARE @TranStarted   bit    SET @TranStarted = 0    IF( @@TRANCOUNT = 0 )    BEGIN     BEGIN TRANSACTION     SET @TranStarted = 1    END    ELSESET @TranStarted = 0    DECLARE @ErrorCode   int    DECLARE @RowCount    int    SET @ErrorCode = 0    SET @RowCount  = 0    SELECT  @UserId = u.UserId    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a    WHERE   u.LoweredUserName       = LOWER(@UserName)        AND u.ApplicationId         = a.ApplicationId        AND LOWER(@ApplicationName) = a.LoweredApplicationName    IF (@UserId IS NULL)    BEGIN        GOTO Cleanup    END    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set    IF ((@TablesToDeleteFrom & 1) <> 0 AND        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))    BEGIN        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId        SELECT @ErrorCode = @@ERROR,               @RowCount = @@ROWCOUNT        IF( @ErrorCode <> 0 )            GOTO Cleanup        IF (@RowCount <> 0)            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    END    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set    IF ((@TablesToDeleteFrom & 2) <> 0  AND        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )    BEGIN        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId        SELECT @ErrorCode = @@ERROR,                @RowCount = @@ROWCOUNT        IF( @ErrorCode <> 0 )            GOTO Cleanup        IF (@RowCount <> 0)            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    END    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set    IF ((@TablesToDeleteFrom & 4) <> 0  AND        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )    BEGIN        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId        SELECT @ErrorCode = @@ERROR,                @RowCount = @@ROWCOUNT        IF( @ErrorCode <> 0 )            GOTO Cleanup        IF (@RowCount <> 0)            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    END    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set    IF ((@TablesToDeleteFrom & 8) <> 0  AND        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )    BEGIN        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId        SELECT @ErrorCode = @@ERROR,                @RowCount = @@ROWCOUNT        IF( @ErrorCode <> 0 )            GOTO Cleanup        IF (@RowCount <> 0)            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    END    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set    IF ((@TablesToDeleteFrom & 1) <> 0 AND        (@TablesToDeleteFrom & 2) <> 0 AND        (@TablesToDeleteFrom & 4) <> 0 AND        (@TablesToDeleteFrom & 8) <> 0 AND        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))    BEGIN        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId        SELECT @ErrorCode = @@ERROR,                @RowCount = @@ROWCOUNT        IF( @ErrorCode <> 0 )            GOTO Cleanup        IF (@RowCount <> 0)            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    END    IF( @TranStarted = 1 )    BEGIN     SET @TranStarted = 0     COMMIT TRANSACTION    END    RETURN 0Cleanup:    SET @NumTablesDeletedFrom = 0    IF( @TranStarted = 1 )    BEGIN        SET @TranStarted = 0     ROLLBACK TRANSACTION    END    RETURN @ErrorCodeEND' ENDGO
 
Thanks in advance,
Rob

 
 

View 1 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

Feb 1, 2008

Hi!

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks

int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}

message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());

View 1 Replies View Related

Procedure Or Function?

Feb 5, 2008

Trying to get a handle on how to attack this problem of adding a new record in a table that has 2 joins to access (user) and associate with a grandparent table (hotel).

There is also a date condition with a separate table (contracts) in order to start the operation.

Select from Contracts where TermBegin and TermEnd <> today
For each contract hotel{
#Do they already have user?
Match User.ID + Organization.ID + Hotel.ID
If not{
Create Person
}

I'm not sure how to return the negative results of the 3 table join of User.Id + Organization.ID + Hotel.Id

Thanks for any tips/iedas

View 5 Replies View Related

Procedure Or Function '' Has Too Many Arguments Specified.

Feb 2, 2007

Im getting this error when looping through a gridview. Im trying to save each row into the database. The weird thing is that it saves the first row, but errors out on the second.If I only add one item to the gridview, it works. The second row is screwing it up somehow.  Any ideas?  ------------------------------------------------------------------------------------------------------------------------------------------------------------------Some of my code to get an idea of how im doing it:(This
is for a shopping cart. It displays the products in the shopping cart.
If the order is approved by the CC processing company, each product
entry is saved in the DB. The gridview is being populated by my
ShoppingCart Profile Object.)   SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["rdevie"].ConnectionString);
SqlCommand objCmd = new SqlCommand("InsertOrderDetails", objConn);
objCmd.CommandType = CommandType.StoredProcedure;

objConn.Open();

foreach (GridViewRow row in gvOrderDetails.Rows)
{
objCmd.Parameters.Add("@idOrder", SqlDbType.Int).Value = orderNum;
objCmd.Parameters.Add("@fldName", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblName")).Text;
objCmd.Parameters.Add("@fldUnitPrice", SqlDbType.Money).Value = ((Label)row.FindControl("lblUnitPrice")).Text;
objCmd.Parameters.Add("@fldQuantity", SqlDbType.Int).Value = Convert.ToInt32(((Label)row.FindControl("lblQuantity")).Text);
if (!String.IsNullOrEmpty(((Label)row.FindControl("lblSku")).Text))
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSku")).Text;
else
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = DBNull.Value;
objCmd.Parameters.Add("@fldSize", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSize")).Text;
objCmd.Parameters.Add("@fldLogo", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblLogo")).Text;
objCmd.Parameters.Add("@fldPlacement", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblPlacement")).Text;
objCmd.Parameters.Add("@fldColor", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblColor")).Text;

objCmd.ExecuteNonQuery();
}

objCmd.Dispose();
objConn.Close();
objConn.Dispose(); 

View 5 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Oct 3, 2007

Hi,I have a simple stored procedure that basically SELECT some data from database. I am calling this stored procedure using a sqldatasource. The first time I try it, it works perfectly fine. But I get the error message "Procedure or function has too many arguments specified" the second time I try to search for thing. This is the code that I have that called the stored procedure: Session("SearchItem") = txtSearch.Text.Trim SqlDataSource1.SelectCommand = "_MyStoredProcedure" SqlDataSource1.SelectParameters.Add("SearchItem", Session("SearchItem")) SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure Gridview1.DataSourceID = "SqlDataSource1" Gridview1.DataBind() The following is the code in the stored procedure: CREATE PROCEDURE [dbo].[_MyStoredProcedure] (    @SearchItem nvarchar(255) = NULL)ASSELECT Field1, Field2, Field3 FROM Registration WHERE((@SearchItem IS NULL) OR (Field1 LIKE '%' + @SearchItem + '%') OR (Field2 LIKE '%' + @SearchItem + '%'))GO Please help.Stan 

View 6 Replies View Related

Procedure Or Function Has Too Many Argument Specified.

May 27, 2008

 Hey, friends, i have a problem on my bank transaction page now: Procedure or function has too many argument specified.people can log in by their usernames, 1 username can have many accounts, there are 2 account types:'c', 's'. after people login, they are only allowed to withdraw money from their own account: here is some of my codes:  1 else if (DropDownList3.SelectedItem.Text == "withdraw")2 {3 SqlConnection sqlCon = new SqlConnection("Data Source=bandicoot.cs.rmit.edu.au;Initial Catalog=shuli;User ID=test;Password=test");4 SqlCommand cmd = new SqlCommand("usp_withdraw", sqlCon);5 string spResult = "";6 cmd.CommandType = CommandType.StoredProcedure;7 cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = Session["username"].ToString();8 cmd.Parameters.Add("@AccountFrom", SqlDbType.VarChar).Value = TextBox3.Text;9 cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = TextBox5.Text;10 cmd.CommandType = CommandType.StoredProcedure;11 sqlCon.Open();12 spResult = cmd.ExecuteScalar().ToString();13 if (string.Compare(spResult, "Execute successfully") == 0)14 {15 SqlDataSource with = new SqlDataSource();16 with.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();17 18 with.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('W','" + TextBox3.Text + "','" + TextBox3.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() + "')";19 int inertRowNum = with.Insert();20 Server.Transfer("~/deposit_withdraw_confirm.aspx");21 }22 else23 {24 Server.Transfer("~/deposit_withdraw_error.aspx");25 }26
  here is my store procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================ALTER Procedure [dbo].[usp_withdraw](    @AccountFrom Varchar(20),    @Amount Decimal(10,2))AsDECLARE @ReturnMsg AS VARCHAR(20)DECLARE @balance AS Decimal(10,2)DECLARE @username AS nvarchar(50)SELECT @balance = balanceFROM Account,LoginWHERE   Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFromIF (    SELECT AccountType     FROM Account, Login    Where  Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFrom    ) ='c'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 199                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 199                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDIF (    SELECT AccountType     FROM Account    Where AccountNumber = @AccountFrom    ) ='s'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 0                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 0                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDI think the problem is in Line 7cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Session["username"].ToString();this is to get the current login user's uername.  

View 4 Replies View Related

Function Vs Stored Procedure

May 1, 2004

I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?

View 3 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Jan 19, 2006

I have two tables: Person (member_no, name) and Participant (participant, member_no) member_no is the primary key in Person.The stored procedure is:CREATE PROCEDURE dbo.InsertRecs@member_no char(10),@name char(10),@participant char(10)ASbegininsert into person (member_no, name)values (@member_no, @name)select @member_no = @@Identityinsert into participant (participant, member_no)values (@participant, @member_no)endGOWhen I run this via the DetailsView control and try to insert a record I always get "Procedure or function InsertRecs has too many arguments specified"If I remove the second table from the stored procedure it works fine.Does anyone have any ideas?

View 9 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Feb 18, 2006

Hello All,
I am having a very strange problem which I cannot figure out. I am using the ASP.NET 2.0 SqlDataSource control to insert a news item into a SQL Express database. When inserting the a message I receive the following error: Procedure or function has too many arguments specified
However according to me every thing adds up. Can anyone please help me figure out what is wrong? Here is the code:
SqlDataSource:    <asp:SqlDataSource ID="sqlNews" runat="server" ConnectionString="<%$ ConnectionStrings:FlevoOptiekDB %>"        SelectCommandType="StoredProcedure" SelectCommand="dbo.usp_News_ByID"        InsertCommandType="StoredProcedure" InsertCommand="dbo.usp_News_Insert"        UpdateCommandType="StoredProcedure" UpdateCommand="dbo.usp_News_Update"        DeleteCommandType="StoredProcedure" DeleteCommand="dbo.usp_News_Delete"         OldValuesParameterFormatString="{0}">        <SelectParameters>            <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" />        </SelectParameters>        <UpdateParameters>            <asp:Parameter Name="p_dtmPublished" Type="DateTime" />            <asp:Parameter Name="p_sTitle" />            <asp:Parameter Name="p_sDescription" />            <asp:Parameter Name="p_sStaticUrl" />            <asp:Parameter Name="p_iPhotoID" />            <asp:Parameter Name="p_iNewsID" />            <asp:Parameter Name="p_iAlbumID" />        </UpdateParameters>        <InsertParameters>            <asp:Parameter Name="p_dtmPublished" Type="DateTime" />            <asp:Parameter Name="p_sTitle" />            <asp:Parameter Name="p_sDescription" />            <asp:Parameter Name="p_sStaticUrl" />            <asp:Parameter Name="p_iPhotoID" />            <asp:Parameter Name="p_iAlbumID" />        </InsertParameters>        <DeleteParameters>            <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" />        </DeleteParameters>    </asp:SqlDataSource>Stored procedure:ALTER PROCEDURE dbo.usp_News_Insert( @p_sTitle VARCHAR(256), @p_sDescription TEXT, @p_sStaticUrl VARCHAR(256), @p_dtmPublished DATETIME, @p_iPhotoID INT, @p_iAlbumID INT)AS /* SET NOCOUNT ON */  INSERT INTO dbo.tbNews(news_Title, news_Description, news_StaticUrl, news_Published, photo_ID, album_ID) VALUES(@p_sTitle, @p_sDescription, @p_sStaticUrl, @p_dtmPublished, @p_iPhotoID, @p_iAlbumID)  RETURN
Thanks, Maurits

View 3 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Mar 7, 2006

I have tried to introduce information in  webform with sp and she leaves the following error

View 1 Replies View Related

Function From Stored Procedure

Nov 22, 2007

Hi Everyone,

i want to create a function in Stored procedure and then call this function
that returns true or false from a stored procedure .
is this possible?

please help me if you know something that can help me.

thanks

Tvin

View 2 Replies View Related

Function Inside The Procedure

Jan 29, 2008

i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid
mmy question how to put function inside the procedure?
my function below,pls guide me its very urgent.
create function NextCustomerNumber()
returns char(5)
as
begin
declare @lastval char(5)
set @lastval = (select max(customerNumber) from Customers)
if @lastval is null set @lastval = 'C0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)
end

Desikankannan

View 1 Replies View Related

When To Use A Function And When To Use A Stored Procedure.

Oct 28, 2006

Hi,

I thought that procedures do not return a value: well they only return a 1 or 0 depending on whether the procedure executed correctly. Functions are what you're supposed to use when you want to get a result set of some sort: ie a table or a scalar value.

Apparently that is not the case becuase you can return values from procedures. I've attempted to find out what the differences are between the two and when it is appropriate to use a procedure and when it is appropriate to use a function but I'm still unsure. Can someone please tell me the difference.

Thanks.

Przemek

View 16 Replies View Related

Differance Stored Procedure And Function

Nov 26, 2006

hello
what is differance beetween stored-procedure and Function
when use of stored rpocedure and function

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved