How To Return A Set Of Data To The Calling Program
Jul 9, 2007
hi,
i am new to SQL. i am trying to retrieve a set of data(i.e. set of rows which satisfies my condition given in the select statement). i want to output this recordset to my calling program i.e C#.net. how could i do this. i was adviced to use temp tables. but i am not sure how to do it. can any one say that using the temp table is the correct way. if so can u give me some sample code.
my proble is
Table name: ProductDetails Table
It has following columns
ProductID Name UnitPrice Category Stock
when i give a category value from front end it should bring me the rows which comes under the given category. there will be many rows under this category. how could i send this set of rows back to my calling program.
regards,
Johny
View 1 Replies
ADVERTISEMENT
May 21, 2007
Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?
View 1 Replies
View Related
May 19, 2004
Hi,
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like
SET @RET = sp_CallEXE('c:mypathmyprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @RET variable)
Thanks,
Skip.
View 1 Replies
View Related
May 7, 2007
Hi All,
I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.
One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.
However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.
Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.
Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?
Thanks in Advance.
Peter Nolan
www.peternolan.com
View 4 Replies
View Related
Sep 27, 2005
Hi EveryBody, I need to call a Stored Procedure in a UDF.This input of SP would be a string and in turn that output Of SP would also be a string.Also i need to store the output of SP in a Calling UDF.Is it possible if Yes Please tell me How can i make It.Please help.Thanks!!!Dheeraj
View 3 Replies
View Related
Jul 20, 2005
Hi All,I have a requirement of calling a Executable from SQL Server.I know we can use -EXEC @result = Master..xp_cmdshell @<command_string>However I want to get the return value (int) from the executable.xp_cmdshell always returns 0 or 1. How can the executable return codesay 99 be obtained in SQL Server?Thanks & Regards,Chandra Mohan
View 1 Replies
View Related
Aug 21, 2007
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
View 5 Replies
View Related
Jul 20, 2005
Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
View 4 Replies
View Related
Jul 9, 2014
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
View 2 Replies
View Related
Jul 23, 2005
Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies
View Related
Apr 18, 2006
I've been trying to create an async transform component and one of the things I'd like to do is add a new output and/or columns to existing outputs for each input column that is selected.
I thought that the SetUsageType method would be a good place to do this as it will allow me to delete output's and columns based on the user's selections but, despite the fact that my code doesn't actually throw or cause errors, when I return from the SetUsageType method I'm informed that error 0xC0047041 (DTS_E_OBJECTNOTINHASHTABLE) has occurred .
I pared the method down to an absolute minimum and found that a call to GetVirtualInput is enough to push it over the edge even if it does nothing! What is wrong here?
Thanks
Charlie
Sample:
public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput,
int lineageID, DTSUsageType usageType)
{
if (usageType == DTSUsageType.UT_READONLY || usageType == DTSUsageType.UT_READWRITE)
{
ComponentMetaData.InputCollection[0].GetVirtualInput();
}
return base.SetUsageType(inputID, virtualInput, lineageID, usageType);;
}
View 9 Replies
View Related
May 23, 2007
why we use sql exceptions ...
what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated.
The statement has been terminated.).. will it affect the functionality of the program...
hiow can i avoid this exception..
View 1 Replies
View Related
Oct 21, 2006
Is there any way to incorporate the SQL 2005 Mining Wizard into my program? Sure, I can create my own mining wizard using DMX, but that's quite a bit of work. Has anyone tried this? Is it possible? Any pointers?
-Young K
View 4 Replies
View Related
Jun 23, 2006
It is possible to program part of the process of load of data within the SSIS. The origin is a Flat file (.txt and .dat) and the destiny a SQL Server 2005. All the fields of the file are not mapean origin with the destiny table and data are needed other tables that are in the Data Base.
View 1 Replies
View Related
Sep 8, 2007
I am using visual basic 2008
I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,
when I close the program and build again, the data previosly updated are deleted, and I get empty database?? why is that. do i need to set some copy to.........properties. i have used copy if new.
View 3 Replies
View Related
Apr 19, 2008
Hi all,
there are 2 datagrids i have taken,
1st for displaying data before inserting the value into table
2nd for displaying data after inserting the value into table
just see the screeenshot which i have taken
actually the problem is, we are not getting any type of errors even though I have try/catch blocks.
I have colored that particular statement in RED color, where it is getting stuck, while executing, if we sit 40-45 minutes then also the execution pointer is not going on next step.
please look into this, and if u have any idea/person by which/whom we can solve this then please tell us.......
-chaukse rahul
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Data.SqlServerCe;
using System.Data.Common;
namespace ProjectSQLMobile2
{
public partial class Form1 : Form
{
private DataSet dsMemberList;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://169.254.25.129/WebSQLMobile/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"sa12345";
repl.Publication = @"PubSQLMobile";
repl.Subscriber = @"SubSQLMobile";
repl.SubscriberConnectionString = @"Data Source=TestLast.sdf;Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(@"TestLast.sdf"))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
/*
*
* To Display Data Into DataGrid1 Before Insert
*
*/
SqlCeConnection cn = new SqlCeConnection(@"Data Source=TestLast.sdf");
SqlCeDataAdapter daMemberList = new SqlCeDataAdapter("SELECT MemberID, MemberName FROM MembershipData", cn);
if (dsMemberList == null)
{
dsMemberList = new DataSet();
}
try
{
dsMemberList.Clear();
daMemberList.Fill(dsMemberList, "MembershipData");
dataGrid1.DataSource = dsMemberList.Tables["MembershipData"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void Merge()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://169.254.25.129/WebSQLMobile/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"sa12345";
repl.Publication = @"PubSQLMobile";
repl.Subscriber = @"SubSQLMobile";
repl.SubscriberConnectionString = @"Data Source=TestLast.sdf;Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(@"TestLast.sdf"))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
/*
*
* To Display Data Into DataGrid2 After Insert
*
*/
SqlCeConnection cn = new SqlCeConnection(@"Data Source=TestLast.sdf");
SqlCeDataAdapter daMemberList = new SqlCeDataAdapter("SELECT MemberID, MemberName FROM MembershipData", cn);
if (dsMemberList == null)
{
dsMemberList = new DataSet();
}
try
{
dsMemberList.Clear();
daMemberList.Fill(dsMemberList, "MembershipData");
dataGrid2.DataSource = dsMemberList.Tables["MembershipData"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void buttonInsertData_Click(object sender, EventArgs e)
{
//add directly into ce database sample:
SqlCeConnection cn = new SqlCeConnection(@"Data Source=""TestLast.sdf""");
string SQL = "INSERT INTO MembershipData(MemberName) VALUES('" + textBox1.Text + "')";
cn.Open();
SqlCeCommand cmd = new SqlCeCommand(SQL, cn);
cmd.CommandType = CommandType.Text;
try
{
/*
* This statement is taking too much time to be executed * what should we do??????? */ cmd.ExecuteNonQuery(); }
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
cn.Close();
}
Merge();
}
}
}
please help,
ur help will be appriciates
NOTE:- I am getting output in 1st datagrid, but in 2nd dtagrid there is no output
View 1 Replies
View Related
Jul 24, 2006
hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
View 1 Replies
View Related
Apr 11, 2007
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
View 6 Replies
View Related
Sep 17, 2007
SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data in MS Excel?
I can see that under
C:Program FilesMicrosoft SQL Server Compact Editionv3.5
I've one DLL called
sqlceoledb35.dll
But I don't have any oledb driver listed when creating an UDL file?
How can I consume the data in other apps that are not .NET and developed in house them?
Thanks, AM.
View 13 Replies
View Related
Jul 6, 2005
Hi all,
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams
View 2 Replies
View Related
Sep 30, 2005
I have a vbscript to read all files from a directory and, if the fileis valid, I would like my DTS to process it. I tried using thevbscript as an ActiveX workflow script in the DTS, but it does notexecute the data pump until it has completed looping through all thefiles, so only the last file read is sucked into the database(utilizing a global variable as the filename). Is there a way toexecute the data pump task from within the activex script? I can'tseem to find any documentation about executing a DTS task.Basically, the workflow I want is:1)Read files from directory (the number and names may change eachtime). (done with vbs)2)For each file, send it through the transformation into the database.3)When the information is in the database, append a date to the fileand move it to the archive folder. (done with vbs)If I am going about this the wrong way and you see something that isnot obvious to me, please let me know.Thanks in advance!
View 3 Replies
View Related
Aug 1, 2005
Hi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @MemNRIC
END
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Can someone pls help? Thanks!
View 11 Replies
View Related
May 30, 2006
I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.I am very new to this and would really appreciate any knowledge anyone could share.
View 1 Replies
View Related
May 2, 2007
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
View 2 Replies
View Related
Aug 31, 2000
Hi,
I am looking to create a job on our SQL Server 7.0 dB to:
1. Manipulate and move data from table 'a' to table 'b' (via stored proc)
2. Export table 'b' to a comma delimeted, double quote text identifier file (via dts export wizard stored as a DTS )
3. Cleanup table 'b' and end the job.
I am creating a DTS package to execute step 1, then 2 and lastly 3.
How do I execute another DTS package (#2) from within this DTS package?
Also, is there a way I can make the file name for #2 a dynamically generated name (i.e. include date and time in the file name)?
Thank You
Jamie Reis
View 1 Replies
View Related
Aug 31, 2007
I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
View 3 Replies
View Related
May 29, 2006
Hello
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
Any help is highly appreciated!
Kind Regards
Gayatri
View 4 Replies
View Related
Feb 12, 2008
Hi All,
It's been a while since I post here. Anyway, I'm trying to call a SSIS package as my data source for SQL reporting service but I keep getting the error "Cannot create a connection to data source 'DsSSIS'.
My DsSSIS consists of the following in the connection string.
="/file c:FarmBillTesting.dtsx /Set Package.Variables[User::FilterValue].Properties[Value];" & Parameters!FilterValue.Value.ToString()
I have tried everything that I know to make this work but I have been able to do so. Any help from the group would be great.
Thanks
Ham
View 11 Replies
View Related
Nov 29, 2007
This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).
Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is
"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"
which typically evaluates as
usp_ValveStatusForDay '18 Oct 07','Report_Name'
This previews correctly and the resulting columns are available for mapping to a destination. So far so good.
By the way, is this the best way to call a stored procedure with parameters?
I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.
I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.
At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.
It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?
ALTER procedure [dbo].[usp_ValveStatusForDay]
(
@dateTime DateTime,
@reportName VarChar(100)
)
AS
BEGIN
DECLARE @day VarChar(10)
DECLARE @month VarChar(10)
DECLARE @year VarChar(10)
DECLARE @start VarChar(25)
DECLARE @end VarChar(25)
SET @day = Convert(Varchar(10),DatePart(day, @dateTime))
SET @month = Convert(VarChar(10), DatePart(month, @dateTime))
SET @year = Convert(VarChar(10), DatePart(year, @dateTime))
IF @month = '1' SET @month = 'Jan'
IF @month = '2' SET @month = 'Feb'
IF @month = '3' SET @month = 'Mar'
IF @month = '4' SET @month = 'Apr'
IF @month = '5' SET @month = 'May'
IF @month = '6' SET @month = 'Jun'
IF @month = '7' SET @month = 'Jul'
IF @month = '8' SET @month = 'Aug'
IF @month = '9' SET @month = 'Sep'
IF @month = '10' SET @month = 'Oct'
IF @month = '11' SET @month = 'Nov'
IF @month = '12' SET @month = 'Dec'
SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'
SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'
--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)
exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'
END
View 8 Replies
View Related
Feb 22, 2008
Hi
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
ALTER Procedure [dbo].[usp_CheckUser]
@name nvarchar(50),
@ssn nvarchar(50),
@accountnumber nvarchar(50)
AS
BEGIN
if(@name!=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber
else if(@name!=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber AND name=@name
else if(@name!=null AND @ssn=null AND @accountnumber=null)
select * from Userinfo where name=@name
else if(@name!=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where name=@name AND ssn=@ssn
else if(@name=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent.
thanx for your help in advance.
ramya
View 7 Replies
View Related
Aug 25, 2015
I have a SSRS 2012 report which references a custom c# assembly. This report exists in multiple environments (alpha, beta and production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
View 2 Replies
View Related
Jan 5, 2004
Hi,
A partner company unfortunately chose Sybase SQL Anywhere v5 as their database and I have to return data from the tables for a web service that I am writing - YUCK !
The ODBC driver is riddled with errors and has no support. Third party .NET providers do not seem to support v5, so I have resorted to playing with DTS in Enterprise Manager.
After fumbling around for a while, I managed to get an "Execute SQL Task" to return data and saved the package in both SQL and VB.
Now, before I waste any more time heading in this direction, can anybody advise if DTS is supported by MSDE as the full blown SQL Server will not be an option ?
If it is supported - is this a good way to go, or is there a better solution ?
Thanks in advance.
Steve.
View 2 Replies
View Related
Feb 24, 2005
I am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.
Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50)
Parem.Direction = ParameterDirection.Output
View 3 Replies
View Related
