Function Returning Error During Compilation.....
Hi ,
I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t1_all)
declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)
declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin
OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END
CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata
RETURN
END
==========================
While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================
Can Anyone please help me how to resolve this issue...
Thanks with Regards.
-Mohit.
View Complete Forum Thread with Replies
Related Forum Messages:
SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared: Server Error in '/AverageTCE' Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error: Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server"> <div> Average TCE<br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="SampleID" DataValueField="SampleID"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString2 %>" SelectCommand="SELECT [SampleID] FROM [LabData]"></asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SampleID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="SampleID" HeaderText="SampleID" ReadOnly="True" SortExpression="SampleID" /> <asp:BoundField DataField="SampleName" HeaderText="SampleName" SortExpression="SampleName" /> <asp:BoundField DataField="AnalyteName" HeaderText="AnalyteName" SortExpression="AnalyteName" /> <asp:BoundField DataField="Concentration" HeaderText="Concentration" SortExpression="Concentration" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="ddlLabData" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString %>" SelectCommand="SELECT * FROM [LabData] WHERE ([SampleID] = @SampleID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="3" Name="SampleID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString3 %>" SelectCommand="SELECT * FROM [LabData]"></asp:SqlDataSource> <br /> <br /> LabData-Analyte: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> LabData-Conc: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> Average values: <asp:Label ID="Label1" runat="server" Text="lblAverageValue"></asp:Label><br /> <br /> <br /> <br /> </div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
View Replies !
Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes: DBConn= New OledbConnection("Provider=sqloledb;" _ DBInsert.Commandtext = "Insert Into GuestInfo" _ DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery() What I'm trying to do is connect to the SQL database and input new information to the database. This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out. Dim DBConn as oledbConnection Dim DBInsert As New oledbCommand DBConn= New OledbConnection("Provider=sqloledb;" _ & "server=localhost;" _ & "Initial Catalog=HMS;" _ & "User id=sa;" _ & "Password=yourpassword;") DBInsert.Commandtext = "Insert Into GuestInfo" _ & "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _ &"'" & txtFirstName.Text & "', " _ &"'" & txtLastName.Text & "', " _ &"'" & txtAddress.Text & "', " _ &"'" & txtCity.Text &"', " _ &"'" & txtState.Text &"', " _ &"'" & txtZipCode.Text &"', ")" DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery()
View Replies !
Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END RETURN @@ROWCOUNTENDGO --execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.
View Replies !
Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me? ------------------ Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader' Source Error: Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); Line 17: myConn.Open(); Line 18: SqlDataReader myReader = myConn.ExecuteReader(); Line 19: do Line 20: { Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18 void Login(string username, string password) { SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels"); SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); myConn.Open(); SqlDataReader myReader = myConn.ExecuteReader(); do { while (reader.Read()) { if (username == myReader.GetString(1) && password == myReader.GetString(2)) { messages.Text = "Your login was successful!"; } else { messages.Text = " Your login was unsuccessful!"; } } } while (reader.NextResult()); myReader.Close(); myConn.Close(); } void Submit_Click(Object sender, EventArgs e) { Login(username.Text, password.Text); } Edit by moderator - NetProfit: Added < code>< /code> tags.
View Replies !
CLR SP Or Function Returning A Table
Hi guys, I have to work with some configuration data that is stored in rows as a comma separated values. Something like this: Key1 A,1,Z,0;B,2,Y,9;C,,8,X; Key2 Alpha,101;Beta,102; Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR. There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this: A 1 Z 0 B 2 Y 9 C 3 X 8 But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other). So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF? Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function. THANKS! Code Snippet public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void spGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) return; StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE ("); for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i)); } SqlCreate.Remove(SqlCreate.Length - 1, 1); SqlCreate.AppendLine(");"); StringBuilder SqlInsert = new StringBuilder(); foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { SqlInsert.Append("INSERT INTO @Output VALUES ("); // busca las diferentes "columns" ~ Charly foreach (string column in row.Split(SeparatorColumn)) { SqlInsert.AppendFormat("'{0}',", column); } SqlInsert.Remove(SqlInsert.Length - 1, 1); SqlInsert.AppendLine(");"); } } string SqlSelect = "SELECT * FROM @Output;"; cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect; SqlDataReader reader = cmd.ExecuteReader(); SqlContext.Pipe.Send(reader); reader.Close(); reader.Dispose(); } conn.Close(); conn.Dispose(); cmd.Dispose(); } }; Code Snippet public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static IEnumerable fGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); conn.Close(); conn.Dispose(); cmd.Dispose(); DataTable dt = new DataTable(); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) { // throw exception } string ColumnName; for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i)); dt.Columns.Add(ColumnName, Type.GetType("System.String")); } foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { dt.Rows.Add(row.Split(SeparatorColumn)); } } } return dt.Rows; } };
View Replies !
SQL Function Returning Varchar(max)
I have a SQL function which returns a varchar(max). This gets truncated when the length is greater than 8000. Could you let me know how do I get the return value in a function without it being truncated.
View Replies !
Function Returning A Table
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar) RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype varchar(100),CDDate datetime, Label varchar(100)) AS BEGIN IF @art <>'/' INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and artist.artist=@art Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @cd <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @tra <> '/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @gen <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @cdate<>'01/01/1900' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @label<>'/' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf return end --------------------------------------------------------------------- upon running executing this function with valid values i am not getting any results. anything is wrong? thank you,
View Replies !
Question About Returning A Smalldatetime From A Function
I've been working this for a while. Kind of new to SQL Serverfunctions and not seeing what I am doing wrong. I have this functionCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS smalldatetime ASBEGINDeclare @retVal varchar(10)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDThe error I get isServer: Msg 296, Level 16, State 3, Procedure test, Line 6The conversion of char data type to smalldatetime data type resulted inan out-of-range smalldatetime value.1) I tried declaring @retVal as a smalldatetime and get the error "Mustdeclare the variable '@retVal'.'2) If I run that same query in query analyzer (manually inserting theparm) it returns 11/14/2006. That's what I want.If I change the function to this and run itCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS varchar(50) ASBEGINDeclare @retVal varchar(50)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDIt now works but the return value is Nov 14 2006 12:00AMWhat am I doing wrong?TIA
View Replies !
Function Returning Middle Values
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned. i.e. SELECT * FROM GetMiddleValues (3); If anyone has any ideas on how to accomplish this, that would be great. Thanks
View Replies !
EncryptByKey Function Always Returning Null
When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption: ALTER PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50)) AS BEGIN OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128'; Declare @Temp varbinary(8000); Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText); close symmetric key MyKey; select @Temp as temp; END The result I get for this procedure is null. Is there something wrong with this code?
View Replies !
Derived Column Returning No Data On GetDate() Function
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View Replies !
Need Advise. Problem With Connection Pool. Function Returning SqlDataReader.
Halo all I have a problem with "timeout expired. Thei timeout periode elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were use and max pool size was reached" Then i explore and found out that i did not close my SqlDataReader, SqlDataAdapter, SqlCommand or my connection. But i have a function that return a SqlDataReader. Is this function will cause a connection problem? Thanks in advance Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection()) If DBCon.GetConnection().State = ConnectionState.Closed Then DBCon.GetConnection().Open() End If Dim dr As SqlDataReader = MyCommand.ExecuteReader() Return dr dr.Close() End Function
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
Keeping Trailing Spaces On Function Returning Nvarchar(4000)
I'm trying desparately to write a PadRight function in SQL Server 2005. I seem to be failing miserably because the trailing spaces disappear when the data is returned. First of all, why does SQL Server think I want my string trimmed? And second, how do I overcome this? Code below: Code Snippet CREATE FUNCTION [dbo].[PadRight] ( @sourceString NVARCHAR(4000), @length INT, @padCharacter NCHAR(1) = ' ', @trimBeforePadding BIT = 1 ) RETURNS NVARCHAR(4000) AS BEGIN DECLARE @returnStringLength AS INT, @toReturn AS NVARCHAR(4000) SET @toReturn = LEFT(@sourceString, @length) IF @trimBeforePadding = 1 SET @toReturn = RTRIM(LTRIM(@toReturn)) SET @returnStringLength = LEN(@toReturn) IF @returnStringLength < @length SET @toReturn = @toReturn + REPLICATE(@padCharacter, @length - @returnStringLength) RETURN @toReturn END GO
View Replies !
Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas
View Replies !
Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny
View Replies !
OLEDB Destination Error In SSIS Package Not Returning Error Column/desc
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code. Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information. Error Code -1071607685 Error Column 0 Error Desc No status is available. The only thing I can find for the above error code is DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out. Any help is appreciated. Thanks Derrick
View Replies !
Compilation / Re-build Issue
Hi, We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we do modification in the code it take longer time to build approax it takes around 2 min to display the default page (login page). Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster. Regards K.Karthik Doss
View Replies !
SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does. In oracle, system will let you know whether the the procedure is successfully complied or not? Thanks/
View Replies !
SQL Compilation And Execution Plan
Hi all, I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something?
View Replies !
Compilation Of Stored Procs
Hi, I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs? The thing is when I am running the Stored procs in 2005, its performing really slow in first run. Any help in his regard is highly appreciated. Thanks, Ritesh
View Replies !
C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike
View Replies !
SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run? What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this? Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job. Thanks in advance for any help you can give me. Wayne E. Pfeffer Sr. Systems Analyst Hutchinson Technolgy Inc.
View Replies !
Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as: check documents table to determine whether a full text index for SQL's full text engine has been created. If it has not, query the documentText table (which is the table for my in-house full text search) If it has, use the full text indexing engine My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error: Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed. In my test lab, I tried: 1. creating the full text index 2. creating the stored procedure 3. deleting the ful text index which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option. My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure? Here's a code snippet stripped down to the bare minimum to generate the error: CREATE PROCEDURE [My_FullTextSearch] @Term VarChar(1000) AS BEGIN SET NOCOUNT ON; IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1 BEGIN Select [DocumentID] from [DocumentText] where [Term] like '%' + LTRIM(@Term) + '%' END ELSE BEGIN Select [key] from FREETEXTTABLE(Documents, Contents, @Term) END END
View Replies !
Stored Procedure Compilation Question: Doing Disparate Things In Aproc
To minimize the very large number of stored procedures typicallyassociated with an application, I have gotten in the habit ofcombining a select, insert, update, and delete all in one procedure,and passing an argument to indicate which to use. (I use defaultvalues for all input params to avoid having to declare them forselects and deletes.) So I'll have just one PersonAdmin proc insteadof PersonGet, PersonInsert, PersonUpdate, and PersonDelete procsWhile this is nice for housekeeping, I wonder what the compiler doeswith such an architecture,and I fear the worst. The select returns arecordset; the others don't.Is this a bad idea?If it is, I really wish SQL would permit some sort of user folderstructure in the proc list.
View Replies !
SQL Task Returning XML Error
Hi.. I use an Execute SQL Task to return XML via a stored procedure but the task fails for some reason.. My Settings in Execute SQL Task Editor is: General ResultSet: XML SqlStatement EXEC dbo.myProc ResultSet Result Name = 0 Variable Name = sXml (Variable Type = String) When i run the proc (which uses FOR XML PATH) from Management Studio the following is returned (no outputparemeters are used): <Data> <Sty> <Sys>PXX</Sys> <Ftg>01</Ftg> <Anv>xxx</Anv> <Losen>yyyy</Losen> <Fok>zzz</Fok> <Funk>02</Funk> </Sty> <InkOrd B="N" Id="000945"> <InkOrdNr>000945</InkOrdNr> <DelLevnsNr>0</DelLevnsNr> <LagId>001</LagId> <LevNr>000MAS</LevNr> <LevDat>2006-10-26T00:00:00</LevDat> <OrdTyp>01</OrdTyp> <LevnsVillk>004</LevnsVillk> <TranspSa>1</TranspSa> <Sped>001004</Sped> <BetVillk>017</BetVillk> <ValuKod>EUR</ValuKod> <ValuKurs>9.35</ValuKurs> <InKop>Sara</InKop> <LevRef>MR RUPINDER</LevRef> <UpplDat>2006-09-07</UpplDat> <BestDat>2006-09-18</BestDat> <Rad B="N"> <InkOrdRad>1</InkOrdRad> <LagArtNr>70903</LagArtNr> <LagAttr1>001</LagAttr1> <StlGrp /> <RabProc /> <KomplLevns /> <LevDat /> <Stl B="N"> <LagAttr2>001</LagAttr2> <AntBest>3000</AntBest> <AntVaruHus>0</AntVaruHus> <InkPr>1.48</InkPr> </Stl> </Rad> </InkOrd> </Data> When i run the Execute SQL Task i get the following error: [Execute SQL Task] Error: Executing the query "exec dbo.myProc " failed with the following error: "End tag 'ROOT' does not match the start tag 'LagArtNr'. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Sicne the XML is valid I have no idea why the task fails... I have read this link... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619288&SiteID=1 ... where using FOR XML RAW should do it, but it doesn´t help me Any ideas? Have a nice day /erik
View Replies !
If Exists Command Returning An Error
Hello, can anyone see a problem with this T-SQL? 1 set ANSI_NULLS ON 2 set QUOTED_IDENTIFIER ON 3 GO 4 ALTER PROCEDURE [dbo].[Logon_P] 5 @User_ID VARCHAR(50), 6 @User_Password VARCHAR(50) 7 AS 8 9 IF EXISTS(SELECT 1 10 FROM [User] 11 WHERE [User_Name] = @User_ID) 12 BEGIN 13 RETURN 1 14 IF ((SELECT User_Password FROM dbo.[User] WHERE [User_Name]) = @User_ID) = @User_Password 15 BEGIN 16 RETURN 2 17 END 18 END 19 ELSE 20 RETURN 0 21 Its returning the following error:Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11An expression of non-boolean type specified in a context where a condition is expected, near ')'.
View Replies !
Bulk Insert Returning Error
Help! I am importing a large comma delimited text file into an existing table useing the BULK INSERT command. The table is 4 colums (char16, char16, varchar50, char1). The first 100 or so lines go in without an error. then, I recieve an error stateing that an entry is too long for the field in the database, and kicks me out. The entry is 50 characters, which is allowed. Any ideas why this would happen?
View Replies !
MSDTC: Receive Returning Out Of Resources Error
HI,I am getting an error on my sqlserver database server. My clients aregetting kicked out of transaction, giving an error"New transaction cannot enlist in the specified transaction coordinator"When i saw the event log on the DB server i found the following message************************************Event Type: InformationEvent Source: MSDTCEvent Category: CMEvent ID: 4156Date: 6/13/2005Time: 9:30:48 AMUser: N/AComputer: XXXXXXXDescription:String message: ProcID= 0x780 QMGR::Receive Returning Out Of ResourcesError.************************************************** ***************I am using SQLServer 2000 Advanced server, on Windows 2000 Advanced server.Thanks for help.Indushekar.
View Replies !
Error Returning Rows Using Enterprise Manager In SQL 7
Lately I noticed that I cannot return any rows using enterprise manager. The version I am using is MS-SQL 7.0 with SP 1 on NT. This will return an error:- An unexpected error happened during this operatation. [Query]-Query Designer encountered a query error. Unspecified error. I can return rows using another computer with enterprise manager installed. Any help appreciated. Tony
View Replies !
Error Returning OLAP Data In RS 2005
I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error: An error has occurred during report processing. Query execution failed for data set 'Three_Month_Funding_Trend'. Unable to recognize the requested property ID 'ReturnCellProperties'. Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine. Thanks for your assistance
View Replies !
/Reports Site Returning 404.3 Error After Install
Hi All, I have just installed SSRS 2005 SP2 on a x86 box (one of several boxes we use to run SSRS). However, on this particular machine, I get an http 404.3 error (file not found) when attempting to access any aspx file in the reports virtual directory. aspx files in the /reportserver site all return just fine. All the check marks for the configuration site show green I've uninstalled / re-installed / re-booted / etc... No luck This is a VM server.... Ideas?
View Replies !
Error Returning Variable From Dynamic Stored Procedure
I am trying to return a variable from a dynamic SP - declare @v_outvarchar(400) ... execute ("select @v_out=" + @v_column + " from " + @v_table_name + " where " + @v_key_sql) I get the following error: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@v_out'. Any idea how to fix this? Carl
View Replies !
Conditional Lookup && Returning Undefined Values On Error
Hi, I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables. I do lookuping like this first a lookup that lookup my table with connected input column parameter second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table third a union that union lookup and derived column i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step. thanks
View Replies !
A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
View Replies !
ADO Error 3251: Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution?
I posted this in the MS Access group, but no luck.------------------------------------------I've got another stored procedure in the same app that returns multiplerecordsets and the code works.But now I've written another SP and the code traps out with the 3251 message.The SP is writing two recordsets.When I run the SP in Query Analyzer, both recordsets appear.But when I step through the code, when the first RS should be there, it's"Closed" and nothing I've tried will make it open.Provider=SQLOLEDB.1 (which works on the other screen...)Seems like I've been here before, but I can't remember what the problem was.--PeteCresswell
View Replies !
Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View Replies !
Error In Function
This is my function: =sum(Iif(Fields!FlagPresenca.Value = 1,1,0)=Fields!DataTexto.Value) I want the sum the values = 1 inside =Fields!DataText.Value, This function return me a error#, Can you help me? Thank you a lot!
View Replies !
Error With MAX Function
With this statement I get an error where "Column QuoteHeaderID does not belong to table. " because Object reference isn't being set to an instance of an object, yet if I remove the max part it functions correctly. Code Snippet string num1 = ""; string sqlString = "SELECT MAX(QuoteHeaderID) FROM QuoteHeader"; try { cmd = new OleDbCommand(sqlString, connectCmd); cmd.Connection.Open(); cmd.ExecuteNonQuery(); oleDbDataAdapter = new OleDbDataAdapter(cmd); oleDbDataAdapter.Fill(dt); if (dt.Rows.Count > 0) { num1 = dt.Rows[0]["QuoteHeaderID"].ToString(); } } catch(Exception ex) { string s = ex.Message; DisposeResources(ref oleDbDataAdapter, ref ds, ref connectFill, ref connectCmd, ref cmd); cmd.Connection.Close(); return 0; }
View Replies !
Function Error
what is the error in that function, please help to find out create function [dbo].[ufn_IsLeapYear] ( @pDate DATETIME) returns bit as begin declare @b bit if (YEAR(@pDate)%4=0 ) AND YEAR(@pDate)%100 != 0)) set @b = 1 else if (YEAR(@pDate) % 400 = 0) set @b= 0 Return(@b) end spatle
View Replies !
Error When Using SUM Function
Hi, I am write one storedprocedure,inside that i use sum function like SUM((CASE WHEN d_end_name_qualifier LIKE 'Oc' THEN duration ELSE 0 END)) d_to_stream then i get following error, [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Connection Broken Dnyaneshwar Bhamare
View Replies !
Where Is Error In My Function
Dear experts, please tell me where is the error in my code? create function getitemid(@uomid varchar(50)) returns table as begin declare @itemid varchar(50) select @itemid= column01 from table21 where column03=(select dbo.getuomid('no of leaves')) return (@itemid) end go Vinod Even you learn 1%, Learn it with 100% confidence.
View Replies !
Where Is Error In My Function
dear folks, please check my function i'm getting error like this Select statements included within a function cannot return data to a client. create function getuomid(@uomcode varchar(50)) returns varchar as begin declare @uom_id varchar(50) select uom_id from vuom where uom_code=@uomcode return @uom_id end go Vinod Even you learn 1%, Learn it with 100% confidence.
View Replies !
Error Creating CLR Function
Hi, When I try to create a CLR function in SQL 2005 (June CTP) I get the following error: Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'. The assembly registers successfully, with no errors. I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO The function's code is the following: using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient; namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) { //Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return ""; //Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . } return cFinal; } }} Thanks in advance, Anderson
View Replies !
IsDBNull Function Error!
What's wrong with this statement? Dim drTemp As dsContacts.ContactsRow = dsContacts.Contacts.FindByContactID(iContactCode) . . ""If Not IsDBNull(drTemp.MobilePhone) Then Me.lblMobilePhone.Text = cstr(drTemp.MobilePhone)"" . . When I Execute it , it returns this error message : Cast from type 'DBNull' to type 'String' is not valid
View Replies !
|
TRACKER
