Create A Compatible Function That Can Invoke A Stored Procedure Without Writing The ParameterName And Remembing The Type And Size....

May 30, 2008

suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?

public void storeOperate(string stringParameter,string name)
    {
        string[] strs=stringParameter.Split('&');
        SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString());
        SqlCommand cmd=new SqlCommand(name,conn);
        cmd.CommandText=name;
        cmd.CommandType=CommandType.StoredProcedure;
        foreach(string str in strs)
        {
            cmd.Parameters.Add(".....",SqlDbType........).Value=str;  //my trouble
        }
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        cmd.Dispose();
    }

View 2 Replies


ADVERTISEMENT

No Authorized Routine Named GETDATE Of Type Function Having Compatible Arguments Was Found. Sqlstate:42884

May 30, 2008



Hi All,

I am inserting a row in my SSIS_LOG table when my package executes. Here in this table i have column date_dt which is date data type. Once package executes i am getting error like "No authorized routine named "GETDATE" of type "Function" having compatible arguments was found. sqlstate:42884". Note i am using IBMDB2 data provider.

Can anyone help me out?

Thanks in advance,
Anand Rajagopal

View 9 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

How To Invoke Insert Stored Procedure

Apr 3, 2007

Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database.
Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________
CREATE PROCEDURE newuser003.InsertCompanyInfo
@CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20)
AS
INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN
C# CODE BEHIND: ______________________________________________________ 
public partial class ContractorSubmision : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {       }    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text);        cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text);        cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text);        TextBox4.Text = "Completed";    }}
 

View 5 Replies View Related

How To Invoke DTS Package From A Stored Procedure

Jan 15, 2001

I have a DTS Package to populate data from excel sheet to direct simple table. I like to call this DTS from User interface. thanks in advance

View 2 Replies View Related

I Need To Make This Stored Procedure 2005 Compatible

Jun 22, 2006

Hello.

I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.

Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!

--------------------------------------------------------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR

SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'

OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not

SET @has_rows = 0


FETCH NEXT FROM lcur INTO @Colname,@Tablename

WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0

SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid

SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END

FETCH NEXT FROM lcur INTO @Colname,@TableName

END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------------

error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.

(1 row(s) affected)

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

View 6 Replies View Related

Help Writing A Stored Procedure...

Dec 20, 2007

I'm developing a library and want to display the alphabets across the screen.  When a user clicks on one of the alphabets I want all titles beginning with that letter to appear on the screen.
How would I write this stored procedure?
My table is called Titles
Fields: Title ID Titles
Thanks!

View 2 Replies View Related

Need Help Writing A Stored Procedure

Feb 2, 2008

How would I write a stored procedure to get * where duedate is lessthan and not equal to today's date. Is this right?select * from librarywhere duedate < != getdate()Thanks

View 2 Replies View Related

Problem In Writing Stored Procedure

Oct 30, 2007

hi,
i am new to this.
in my aspx page, i am having a dropdown which has two value
Name
EmpID

if user clicks on name from drop down, user has enter the name of employee in a textbox. And if user clicks on EmpID, he has to enter the employee ID in textbox.

Based on selection the data will be displayed in grid view.

In my database, i have a table called EmpInfo, which has three fields - EmpName, EmpID and State.


can anybody help me in writing a stored procedure for this??

Thanks
Jaimin

View 3 Replies View Related

LINQ Layer - How To Invoke A Sort Function

Jun 5, 2007

 
 Dear all,
I'm trying to create a BLL for an objectdatasource to used for a sorted DataGrid. I am trying to learn about how to use LINQ and I'm sure this is a simple question.
var query = (from p in db.MP3Collections select p).OrderBy(?????????);
 
Now I want to invoke the following method  List<MP3Collection> GetAllMP3s(string sortColumns), which can contain a string name for the field in MP3Collection that is to be sorted (and optionally the "desc" tag).
Now I'm resorting tocoding the query on a case by case basis, which is very tiresome (see the partial code below). This works fine, but is very long and tedious coding. I'm not sure how to go about neatening the code, though am sure it involves the ?????? space above.
Any ideas?
 public static List<MP3Collection> GetAllMP3s(string sortColumns)
{System.Diagnostics.Debug.WriteLine("Pure sort expression: " + sortColumns);
bool descending = false;if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
{
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);descending = true;
}string[] columnNames = sortColumns.Split(',');
 MP3DataClassesDataContext db = new MP3DataClassesDataContext();
List<MP3Collection> query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();foreach (string columnName in columnNames)
{switch (columnName.Trim().ToLowerInvariant())
{case "filename":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename ascending");
}
break;case "filepath":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath ascending");
}
break;
//OTHER CASES HEREdefault:
query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();
//throw new ArgumentException("SortColumns contains an invalid column name.");break;
}
}
 
//var query = (from p in db.MP3Collections select p).OrderBy(p=>p.fileName).ToList<MP3Collection>();return query;
}

View 1 Replies View Related

Writing Stored Procedure In .NET 1.1 And Using In SQL Server 2005

Jul 7, 2006

Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani

View 1 Replies View Related

Writing A Monthly Report With Stored Procedure

Aug 13, 2007

Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
            1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
            2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
            3. There are around 500 employees data im dealing with
            4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
            5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
            6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
            7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...

View 7 Replies View Related

Need Help Writing Stored Procedure Involving Dates

Mar 7, 2005

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @StartDate and @EndDate

@StartDate Datetime = null
@EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null


AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)

View 7 Replies View Related

Writing To Text File From Stored Procedure

Sep 26, 2013

Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."

1. Is xp_cmdshell a bad idea to use even if I get permissions ?
2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?

View 2 Replies View Related

Writing To Text File From Stored Procedure

Mar 29, 2006

hi

Writing to text file from table/view is done using osql,bcp etc. How do we write output of stored procedure into text file??

Thank you

View 4 Replies View Related

Writing Code Vs Using Stored Procedure Within SSIS

Aug 7, 2007



Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?

Thanks

View 3 Replies View Related

Insert Multiple Rows With A Trigger That Invoke A Function

Jan 17, 2012

Multiple rows to insert:
---------------------
insert into Customer(CustomerId,Name,Value)
select CustomerId,Name,Value
from CustomerTemp

Trigger in Customer table that invoke a function:
alter TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS

update Customer
set Percentage = dbo.GetPercentage((select Value from inserted))
where CustomerId = (select CustomerId from inserted)

I'm getting the error for the multiple row.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Is there a way to let me insert multiple rows, using the trigger that invoke a function ?

View 1 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

SQL Server 2012 :: Run Stored Procedure Without Writing To Transaction Log?

Feb 26, 2015

Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.

The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.

Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?

View 6 Replies View Related

Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year

May 15, 2007

hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev]  [Selected Month]  [Next>>]
 the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).  
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!

View 2 Replies View Related

SSIS Invoke Procedure In Oracle Package

Feb 6, 2008

Hello there,

I'am asking you people if it's possible to invoke procedure, functions in package in Oracle database > 9.X with SSIS package...

Thx for your incoming answers

Regards,

KiK2k1

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

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

Stored Procedure Size

Jul 27, 2005

Is there an easy way to get the size in bytes that a stored procedure takes up in storage space?
Thanks.

View 3 Replies View Related

Can I Invoke Remoting Web Service In Store Procedure Directly?

Jan 29, 2004

i am using sqlserver 2000,if so how,thanks.

View 2 Replies View Related

SQL Server Row Size - Stored Procedure ?

Jul 23, 2005

Is there a stored procedure or extended stored proc which returns thetable row size defined for a table ?Thanks,Rob

View 4 Replies View Related

Getting Database Size: A Stored Procedure To Do It......

Jul 20, 2005

As I had real problems working my head around sp_spaceused, I've written anSP to do it (I also noted a lot of questions about this when "searching").Pass in a database name and it will return the size of the database as afloat (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB shouldupdate its size information before giving you the result.Anyway, comments appreciate.ALTER PROCEDURE dbo.proc_BL_Get_Space_Used@DatabaseName NVARCHAR(54),@updateusage BIT,@Size REAL OUTPUTASBEGINDECLARE @dbsize DECIMAL(15,0)DECLARE @bytesperpage DECIMAL(15,0)DECLARE @pagesperMB DECIMAL(15,0)DECLARE @Error INTEGERDECLARE @ExecString NVARCHAR(256)DECLARE @ParmString NVARCHAR(128)SET @Error = 0/*Update usage for this database.*/IF @updateusage = 1BEGINDBCC UPDATEUSAGE (@DatabaseName) WITH NO_INFOMSGSSET @Error = @@ERRORENDSET NOCOUNT ON/*Work out the database size.*/IF @Error = 0BEGINSET @ExecString = 'SELECT @dbsize = SUM ( CONVERT ( DECIMAL (15 ), SIZE ) ) FROM ' + @DatabaseName + '.dbo.sysfiles WHERE (status & 64 =0)'SET @ParmString = '@dbsize DECIMAL(15,0) OUTPUT'EXECUTE sp_executesql @ExecString, @ParmString, @dbsize OUTPUTSET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )END/*and bytes per page.*/IF @Error = 0BEGINSELECT @bytesperpage = LOWFROM master.dbo.spt_valuesWHERE number = 1AND type = 'E'SET @Error = @@ERROREND/*pages per mb*/IF @Error = 0BEGINSELECT @pagesperMB = 1048576 / @bytesperpageSET @Error = @@ERROREND/*and finally, the result.*/IF @Error = 0BEGINSET @Size = CONVERT(REAL, @dbsize) / CONVERT(REAL, @pagesperMB )SET @Error = @@ERRORENDRETURN @ErrorEND

View 1 Replies View Related

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View 6 Replies View Related

How To Invoke Nested Stored Procedures In SQL Server Projects In VS.NET 2005?

Jul 9, 2006

Hi,


I would like to use a stroed procedure within another stored procedure ( nested sp )

in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as

connection string, I wont be able to use this connection for another sqlconnection

object. Because its already open. and If i try to use a regular connection string

("server=localhost;...") it will raise a security permission error. Having this

problem, Im not able to use nested stored procedures. Would anyone please give me a

hint how to resolve this issue?

View 4 Replies View Related

Help! Limitations On Varchar Size And Not Being Able To Use TEXT In Stored Procedure

Nov 4, 1999

I am stuck! and need help!

I have a problem I can't seem to find the solution with this aweful limitations on VARCHAR fields of 255.

Within a stored procedure called Store_Check, I need to dynamically build a string (@string) using VARCHAR(255) since the text datatype can't be used in a stored procedure.

So,this string is built according to whether the Store ID is NOT NULL. So if the StoreID is not null, I start building this string 'Exec Update_Store_Address @StoreID1, @address2'. There are 20 StoreID's passed into Store_Check. IF all 20 StoreID's are not NULL, the executed String greatly exceeds 255 because the string winds up looking like this

'Exec Update_Store_Address @StoreID1, @address1 Exec Update_Store_Address @StoreID2, @address2 Exec Update_Store_Address @StoreID3, @address3 Exec Update_Store_Address @StoreID4, @address4 Exec Update_Store_Address @StoreID5, @address5 Exec Update_Store_Address @StoreID6, @address6 etc. etc.'

I am not executing this string within the StoredCheck procedure. It needs to be passed as ONE string to a VB program and it gets executed by the VB program. Even if I create 4 local variables and concatenate them, it stops at the 255th character.

Also, a local varialbe of type TEXT cannot be declared within stored procedure.

What can I do? I am stuck!
Angel

View 1 Replies View Related

Reporting Services :: Procedure Or Function Create Session Expects Parameter Which Was Not Supplied

Jun 4, 2015

When i open any reports getting the below error message.An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.

(rsReportServerDatabaseError)Procedure or function 'CreateSession' expects parameter '@SiteZone', which was not supplied.  

View 7 Replies View Related

Bit Type Parameter For Stored Procedure

Apr 19, 2004

I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI

The syntax I am trying to use is:

MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1

When I do my ExecuteNonQuery I get error 8114

What am I doing wrong?

View 9 Replies View Related







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