Error Using SQL Datatype Text As Output Parameter From C# Data Access Layer

May 19, 2008



Hello,

My datalayer of C# code is invoking a stored procedure that returns a varchar(max) SQL data type. In my ASP.NET code, I have:

SqlCommand myCommand = new SqlCommand("usp_GetTestString", myConnection);
myCommand.Parameters.Add(new SqlParameter("@TestString", SqlDbType.Text));
myCommand.Parameters["@TestString"].Direction = ParameterDirection.Output;
myConnection.Open();
myCommand.ExecuteNonQuery();
return Convert.ToString(myCommand.Parameters["@TestString"].Value);

The query fails to execute and returns an error: String[1]: the Size property has an invalid size of 0. If I change the SqlDbType.Text parameter type to SqlDBType.Varchar, 100 (or any other fixed varchar length), it works but limits the length my unlimited field text. Any suggestions on how I can use db type text or varchar(max)? The field I need to retrieve is string characters of unlimited length and
hence the datatype varchar(max).

View 3 Replies


ADVERTISEMENT

Error Trying To Add Information To DateTime Field Using Data Access Layer

Sep 20, 2006

I am taking my first stab at a 3 tier architecture within ASP.Net.  I have been adding datasets and creating a new Insert to add certain parts to a table.  I am trying to add a date field called 'DateAdded' and is setup in SQL as a DateTime.  When Visual Studio auto created the dataset, the Insert function is not "DateAdded as Datetime" as I would have expected, but it is "DateAdded as System.Nullable(Of Date)".  There is a space in between 'Of' and 'Date'.  If I keep the space in there the insert function shows an error that says "Arguement not specified for parameter DateAdded of funtion( etc. etc.).  If I take the space out, the error on the insert function goes away but there is an error within the "OfDate" that says "Array bound cannot appear in type specifiers".  I am confused on why the date format changed and how I can get a date to go into the database using the autogenerated datasets from Visual Studio.  Any help would be appreciated.  Thanks, Mike 

View 1 Replies View Related

Stored Procedure Output Parameter's Issue In Dataaccess Layer

Aug 27, 2007

Hi,the stored procedure returns only the first letter of the ouput parameter i.e 'e'.  Actually It must return 'error while updating data'. can anybody helpme to solve this issue.How to set the size of output parameter.spvr_ErrorDescription='error while updating data     which I get from the output parameter of stored procedure. How to set the size of output parameter
in dataaccess layer. Below is the actual code.public string UserCostCenter_Upd(string spvp_Offering,string spvp_UserId,string spvp_CostCenter,DateTime spvp_StartDate,DateTime spvp_ExpiryDate,ref string spvr_ErrorDescription){// The instance ds will hold the result set obtained from the DataRequestDataSet ds = new DataSet();RequestParameter[] parms = new RequestParameter[7];parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);parms[1].Value = spvp_Offering;parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);if (spvp_UserId == null)parms[2].Value = DBNull.Value;elseparms[2].Value = spvp_UserId;parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);if (spvp_CostCenter == null)parms[3].Value = DBNull.Value;elseparms[3].Value = spvp_CostCenter;parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);if (spvp_StartDate == null)parms[4].Value = DBNull.Value;elseparms[4].Value = spvp_StartDate;parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);if (spvp_ExpiryDate == null)parms[5].Value = DBNull.Value;elseparms[5].Value = spvp_ExpiryDate;parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);parms[6].Value = spvr_ErrorDescription;// Create an instance of DataSQLClientAirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;// typDSRef holds the ref. to the strongly typed datasetDataSet typDSRef = (DataSet)ds;DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSetDataHelper helper = new DataHelper();if (!helper.MapResultSet(ref typDSRef, resultDataSet))ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value;spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);return spvr_ErrorDescription ;}

View 5 Replies View Related

Output Parameter With Text Data Type In Stored Procedure

Jul 20, 2005

How can I make a stored procedure which has a output parameter withtext data type? My procedure is:CREATE PROCEDURE try@outPrm text OutputASselect @outPrm =(select field1 from databaseName Wherefield2='12345')GOHere field1 is text data type.Thanks

View 1 Replies View Related

Multi-user Access Through A Data-access Layer/remoting Server

Oct 30, 2007

Hi guys,

I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:

- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE

- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE

Seems quite doable to me, but I may be wrong..please let me know either ways

Thanks,
CP

View 3 Replies View Related

Data Access Layer Advice

Jun 19, 2007

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.
 I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...1 SELECT CAR_MAKE, CAR_MODEL,
2 (SELECT DATE_TAKEN
3 FROM PHOTOGRAPHS
4 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS DATE_TAKEN,
5 (SELECT FORMAT
6 FROM PHOTOGRAPHS
7 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS FORMAT,
8 (SELECT REFERENCE
9 FROM PHOTOGRAPHS
10 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS REFERENCE,
11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID
12 FROM MS_PHOTOGRAPHS
13 WHERE (GALLERY_ID = @GalleryID)
This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

View 7 Replies View Related

Creating A Data Access Layer

Mar 10, 2008

Hello, everybody.

In my web application, i'm using 2 tabels; Users(Username(PK), Pwd, Name, Deptid(FK)) n Dept(Deptid(PK), Deptname)).
For creating a Data Access Layer 4 my project, I added dataset as new item n followed the wizard 2 create the required functions.
I have a function GetUser(@uname, @pwd), which takes username n password as input. M using this for authentication purpose.
While executing it poping an ConstrainException.
Plz help me out.

I've tried 2 as clear as possible here. OR u may ask me any other questions for clear picture of the scenario.
Thanks and Regards,
Sankar.

View 1 Replies View Related

Creating Data Access Layer

Jun 11, 2008

I request you plz tell how to create Data Access Layer. I mean DataAccess.dll. So that I can call stored procedure from dataaccess.dll as below.
DataAccess.SqlHelper.ExecuteDataset(DataAccess.DSN.Connection("DBConnectionString"), CommandType.StoredProcedure, "SP_GetEmpIds");
I request you how can I add this stored procedures to DataAccess.dll and function. I am not having any idea in this area. I request you plz give me some suggestions to work with task.

View 3 Replies View Related

Having Trouble Following Tutorial - Working With Data In ASP.NET 2.0 :: Creating A Data Access Layer

Nov 1, 2006

HiI'm having problems following the tutorial on creating a data access layer -  http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page.  <asp:GridView ID="GridView1" runat="server"             CssClass="DataWebControlStyle">               <HeaderStyle CssClass="HeaderStyle" />               <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines...    using NorthwindTableAdapters; <<<<<this is the problem - where does this come from?   protected void Page_Load(object sender, EventArgs e)    {        ProductsTableAdapter productsAdapter = new         ProductsTableAdapter();        GridView1.DataSource = productsAdapter.GetProducts();        GridView1.DataBind();    }Thanks in advance

View 6 Replies View Related

SQL - System Table In Data Access Layer?

Apr 5, 2007

How do I get a System Table like 'Sysobjects' into the Data Access Layer?
My app generates tables on the fly, and has to check in the sysobjects table which tables are present.

View 5 Replies View Related

What New Features Of .NET 2.0 Required In A Data Access Layer

Jan 10, 2008

Hi Experts ! I want to use maximum feature of SQL
Server 2005 and ASP.Net 2.0  in making Data Access LayerSuggestions will be welcomed .Thank you
RegardsKAMRAN

View 3 Replies View Related

Deleting Using SqlDataAdapter Via A Data Access Layer

Feb 20, 2008

I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId)
2 {
3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection());
4
5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier);
6 p1.Value = (Guid)companyId;
7 p1.Direction = ParameterDirection.Input;
8
9 deleteCommand.Parameters.Add(p1);
10 dataAdapter.DeleteCommand = deleteCommand;
11
12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds.
13
14 try
15 {
16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'"))
17 {
18 row.Delete();
19 }
20 return dataAdapter.Update(companyDS.Tables["pg_Company"]);
21 }
22 catch
23 {
24 return 0;
25 }
26 finally { }
27 }
I thank you in advance for any help here.

View 3 Replies View Related

How To Rollback A Transaction In Data Access Layer

Jul 9, 2007

Hi,

I am having a application in which from the front end i am saving details of three different things



i.Enquiry Details

ii.Parts Details

iii.Machine details



i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

View 4 Replies View Related

EnterpriseLibrary 2006 DATA ACCESS LAYER

Sep 27, 2007

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}


Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST
PLEASE CHECK THE CODE AND REDEFINE THE CODE


using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;-----------------------------------------------ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}




Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:KOTI_PRJSEnterpriseCustomerClass1.cs 34 13 Customer

View 1 Replies View Related

Access The Value Of An Output Parameter From Your ASP.NET Application Using C#

Mar 13, 2008

I have the below procedure with an output parameter '@rowcount'. Now My issue is how to access the value of '@rowcount' in my ASP.Net application using C#. I have also pasted my C# code which I am using .
create procedure mypro1(@id varchar(10),@rowcount int output)
as
select * from canid where id like @id
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @rowcount=@@rowcount
--------------------------------------------------
C# code to retrieve the output parameter valuecmd = new SqlCommand("mypro1", cn);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@id", TextBox1.Text).Direction=ParameterDirection.Input;
Parameter cout = new Parameter("@rowcount",SqlDbType.Int);cout.Direction = ParameterDirection.Output;
cmd.Parameters.Add(cout);
can some body assist me in the above code whether its right or what needs to be added to get the output parameter value.

View 3 Replies View Related

Tutorial: Creating A Data Access Layer...ceases To Be A Tutorial On Page 12

Apr 8, 2007

Before page 12, step by step instructions work!



Then there is code for AllProducts.aspx that doesn't work if one inserts the code



into the DataTutorial project started on page 1. Yes I changed the name of the CodeFile!



The code given for AllProducts.aspx.cs doesn't compile.



I was doing better without the tutorial!



I can gleen out some concepts but that is all.



If that is all, why have a tutorial?







View 3 Replies View Related

How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?

Jul 23, 2005

Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo

View 1 Replies View Related

Parameter Error When Performing A Transform Data Task From Access To SQL Server 2K

Dec 21, 2005

I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.

SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called,
Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds
FROM CallRecords
WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')

When I preview in the Transform Data Task, I get:
Package Error
Error Source: Microsoft JET Database Engine
Error Description: No value given for one or more required parameters.

When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)

When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.

Thanks for any help you can provide.

View 2 Replies View Related

Getting Error While Using Text Datatype

Oct 25, 2006

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18The data types text and text are incompatible in the equal to operator.-------------------------------------------------------------------------------------------------------------------create procedure InsertUserPreferences(@PublisherServer text)asbeginif exists(Select Preference_StringList from USER_Preference where Preference_StringList = @PublisherServer)begin--UPDATEexec dbo.uProc_USER_Preference end

View 3 Replies View Related

Getting Error While Using Text Datatype

Oct 25, 2006

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!

Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18

The data types text and text are incompatible in the equal to operator.

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

create procedure InsertUserPreferences

(

@PublisherServer text

)

as

begin



if exists(Select Preference_StringList from USER_Preference where Preference_StringList = @PublisherServer)

begin

--UPDATE

exec dbo.uProc_USER_Preference

end

View 4 Replies View Related

DataReader Source Error - Cannot Change The Datatype, Precision Or Scale In The Output Columns

Oct 3, 2007

I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors).

I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message:

Property Value is not valid.

The details are:

Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed.
Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Any help is greatly appreciated.
Dave

View 1 Replies View Related

Data Access :: How To Get Column Datatype Using Namespace

Jan 2, 2013

which is to parse a create table script using Microsoft.SqlServer.TransactSql.ScriptDom namespace..I get a list of statements : StatementList SQLStatement = parser.ParseStatementList(StreamReader, out errors);then I cast a relevant statement to the CreateTableStatement type which gives me access to the ColumnDefinition class.

foreach (TSqlStatement T in SQLStatement.Statements)
{ if (T is CreateTableStatement) { CreateTableStatement  TC= (CreateTableStatement)T}}
with foreach (ColumnDefinition CD in TC.Definition.ColumnDefinitions)

I am ready to loop through the columns so far so good. I can get to the column name using CD.ColumnIdetifier.Value, to the data type using CD.DataType.Name.BaseIdentifier.Value, but can't seem to get to the length definition. Meaning, I can get column1 , varchar; but I need column1, varchar(40). where do I get (40) from?I tried using ScriptTokenStream under C.DataType, but it seems to iterate through all tokens not just ones relevant to that fragment.

View 6 Replies View Related

Stored Procedure Output Parameter Error

Sep 18, 2014

Stored procedure displays the follwing error.

Procedure or function 'getFavoriteList' expects parameter '@category_name', which was not supplied.

I have code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getFavoriteList]
@customer_id int,
@category_name varchar(200) OUTPUT

[Code] ....

View 6 Replies View Related

Transact SQL :: How To Set Error Message To Output Parameter

Aug 31, 2015

In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?

create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY

[code].....

View 4 Replies View Related

Output And Reading Data From A Text File??

Sep 1, 1999

Hi,

How would I be able to query a table (ie. all people with last name 'Smith'), have that set of data outputted to a regular text file (in a formatted way)

And what's the best way to manipulate that set of data to let's say update a Yes/No field in that table to mark that that those individuals('Smith') which were outputted in that text file?

What about the reverse? If I got a regular text file with Last Name, Social Security(delimited by tab), etc is there a way I can get SQL Server to read that text file and make an update to the database based on the Social security in that text file.

Any help would be immensely appreciated!
Angel

View 3 Replies View Related

Output Query Data To Text File

Apr 17, 2012

I am running SQL Server 2000 and need to output query data to a text file. If I run the following query (Below) using XP_CMDSHELL and BCP, it runs fine and creates a text file with the data output.

However, I need to change the WHERE Field1=10 to a string value WHERE Field1='abc'. When I try to do this I get a general error on the Field1='xyz'. I tried to change the quotes, etc but I am still getting the error.

Command:
Exec master..xp_cmdshell 'bcp "SELECT Field1 FROM Table WHERE Field1=10" queryout c:filename.txt -U UserName -P Password /S SQLServerNam /c'

View 3 Replies View Related

Text Datatype Vs Nvarchar Datatype

Feb 25, 2008



Hi guys..

i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...

and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...

Thanks and looking forward.-MALIK

View 5 Replies View Related

Reporting Services :: Output A Report As Text File (TXT) In Data Driven Subscription?

Apr 22, 2015

Is it possible to create a Data Driven Subscription report as a text file output to a shared folder?

View 6 Replies View Related

Error: The Data Types Text And Text Are Incompatible In The Equal To Operator.

Apr 25, 2006

I get this error:
The data types text and text are incompatible in the equal to operator.
when trying to execute this query
SELECT id FROM users WHERE username=@userName
 
 
Any Ideas?

View 3 Replies View Related

Data-driven Subscription: Put Parameter In The Filename Text

Feb 1, 2007

Hi all,



I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.

Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?



Thanks in advance.

Marco.

View 1 Replies View Related

Why You Can't Set Up In 'disabled Status' A Task In Data Flow Layer???

May 26, 2006

In Control Flow you can do that.

View 10 Replies View Related

Reporting Services :: Selecting Data Using One Parameter With Multiple Text Values

Oct 9, 2015

I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster

The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)

The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.

I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.

The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?

View 5 Replies View Related

The Formal Parameter @ReportingId Was Not Declared As An OUTPUT Parameter, But...what Is This?

Apr 17, 2008

After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...

An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".

I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.

The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...




Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null

if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions






...later as part of an else it does the following...




Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end






...and before the return it's value is inserted to different tables.

View 5 Replies View Related







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