SPROC Problem - String[1]: The Size Property Has An Invalid Size Of 0.

Mar 16, 2007

Hi folks,
Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString));

        dataSource .CommandType = CommandType.StoredProcedure;
        dataSource .Parameters.AddWithValue("id", poid);
        dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output;
        dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output;
        dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output;
etc, etc...
And the SPROC:
------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[retrieveData]
    @id int,
    @title varchar(50) OUTPUT,
    @creator varchar(50) OUTPUT,
    @assignee varchar(50) OUTPUT,
    @contact varchar(50) OUTPUT,
    @deliveryCost numeric(18,2) OUTPUT,
    @totalCost numeric(18,2) OUTPUT,
    @status tinyint OUTPUT,
    @project smallint OUTPUT,
    @supplier smallint OUTPUT,
    @creationDateTime datetime OUTPUT,
    @amendedDateTime datetime OUTPUT,
    @locked bit OUTPUT
   
   
AS
    /**SET NOCOUNT ON;
    **/
    SELECT
    [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee],
    [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost],
    [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked]
    FROM purchase_orders
    WHERE [id] = @id;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
 The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:



Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.
Line 63: retrievePODetails.Connection.Open();
Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.]
System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally  
 

View 1 Replies


ADVERTISEMENT

String[18]: The Size Property Has An Invalid Size Of 0

Dec 11, 2007

I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter.
ExecuteNonQuery statement is called using SqlHelper.
Error : String[18]: the Size property has an invalid size of 0

View 1 Replies View Related

Error - Output Param - SP - Size Property Has An Invalid Size Of 0

Nov 14, 2007

Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR.  I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0.   par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar);  par.Direction = ParameterDirection.Output;  cmd.Parameters.Add(par);  cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas 

View 6 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.



Any help with this process?

View 1 Replies View Related

Invalid Error Msg - 511 - Cannot Create A Row Of Size <rowlength> Which Is Greater Than The Allowabl

Nov 7, 2000

I was getting the follow error message when trying to update a
table from another table within a stored procedure:

Server: Msg 511, Level 16, State 2, Line 1
Cannot create a row of size 8455 which is greater than the allowable maximum of 8060.
The statement has been terminated.

The row size did not exceed 8060......
Also, If I make it an 'Insert' instead of an 'Update' it works.
It will Insert the data into table.

Any ideas?

Thanks.

View 1 Replies View Related

Sql 2005 Install Error. MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Jun 15, 2006

I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message



native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.


I'll try to post the full log in a new post.



View 11 Replies View Related

Bulk Copy Error: Received Invalid Row Length X From Bcp Client. Minimum Row Size Is Y

Jul 23, 2005

Hi,I am attempting a bulk copy from a c program into SQL Server 2000 usingDBLib in freeTDS 0.63 RC11 (gcc 3.4.3, RH 9). I am getting an error messagethat I cannot find any documentation on.The server is sending back the following: "Received invalid row length 2from bcp client. Minimum row size is 4."I know the row is longer 2 bytes (see below). Once this happened I created atest table and C program. See below. Anyone with any ideas?ThanksProgram output ---------------->$ ./test_bcpbcp'ing This is a test with a length of 14sentMsg 4807, Level 16, State 1Server 'CENTIVIA_10', Line 1Received invalid row length 2 from bcp client. Minimum row size is 4.done<----------------------Table ddl --------------------->CREATE TABLE [xxx] ([col2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO<------------------------------------Compiled using gcc ------------------->gcc -g -I/home/test_user/dev/freetds-0.63RC11/include -Wall -Wno-strict-aliasing-g -O2 -c -o test_bcp.o test_bcp.cgcc -o test_bcp test_bcp.o -L/home/test_user/lib -lsybdb<----------------------------------program source (included the msg and error handlercode) -------------------------->#include <string.h>#include <stdio.h>#include <stdlib.h>#include <sqldb.h>intsyb_msg_handler(DBPROCESS * dbproc, DBINT msgno, int msgstate, int severity,char *msgtext, char *srvname, char *procname, int line){char var_value[31];int i;char *c;if (msgno == 5701 || /* database context change */msgno == 5703 || /* language changed */msgno == 5704) { /* charset changed */if (msgtext != NULL && (c = strchr(msgtext, ''')) != NULL){i = 0;for (++c; i <= 30 && *c != '' && *c != '''; ++c)var_value[i++] = *c;var_value[i] = '';}return 0;}if (severity >= 0 || msgno == 0) {if (msgno > 0 && severity > 0) {fprintf(stdout, "Msg %d, Level %d, State %d",(int) msgno, (int) severity, (int) msgstate);fprintf(stdout, "Server '%s'", srvname);if (procname != NULL && *procname != '')fprintf(stdout, ", Procedure '%s'",procname);if (line > 0)fprintf(stdout, ", Line %d", line);fprintf(stdout, "");fprintf(stdout, "%s", msgtext);fflush(stdout);} else {fprintf(stdout, "%s", msgtext);fflush(stdout);}}return 0;}intsyb_err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char*dberrstr, char *oserrstr){if (dberr == SYBESMSG)return INT_CANCEL;env_set(g_env, "batch_failcount", "1");fprintf(stdout,"DB-LIBRARY error (severity %d, dberr %d, oserr %d, dberrstr%s, oserrstr %s):",severity, dberr, oserr, dberrstr ? dberrstr : "(null)",oserrstr ? oserrstr : "(null)");fflush(stdout);if ((dbproc == NULL) || DBDEAD(dbproc)) {if (dberr != SYBECOFL) {exit(255);}}return INT_CANCEL;}int main(int argc, char *argv[]){DBPROCESS *dbproc;LOGINREC *login;DBINT test_int = 99999999;DBCHAR test_char[32] = "This is a test";dbinit();dberrhandle(syb_err_handler);dbmsghandle(syb_msg_handler);login = dblogin();DBSETLPWD(login, "audit");DBSETLUSER(login, "audit");dbproc = dbopen(login, "192.168.58.1");dbuse(dbproc, "audit_dev");bcp_init(dbproc,"xxx", (BYTE *)NULL, (BYTE *)NULL, DB_IN);printf("bcp'ing %s with a length of%d",test_char,strlen(test_char));//ii = bcp_bind(dbproc, (BYTE *) &test_int, 0, -1, NULL, 0, SYBINT4,1 );//printf("bound %d",ii;bcp_bind(dbproc, test_char, 0, strlen(test_char), NULL, 0, SYBCHAR,1 );bcp_sendrow(dbproc);printf("sent");bcp_batch(dbproc);printf("done");return 0;}<---------------------------------

View 1 Replies View Related

Reduce The File Size Less Then Original Size In SQL 2000

Jul 25, 2007

I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply

View 1 Replies View Related

Encrypted Data Size By Original Size, Algorithm ?

Jan 24, 2006

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

View 3 Replies View Related

Best Practice For Data Size/Log Size Ratio

Jul 23, 2005

Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com

View 4 Replies View Related

Analysis :: Different Size In RAM Than Actual Size On Disk

Apr 20, 2015

The Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.

View 3 Replies View Related

Column Max Size For String

Nov 13, 2003

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

Grant

View 2 Replies View Related

Large Size Of String In The Drill Down

May 24, 2007

Hello,



I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.

Is there any other way to pass the large string in the drill down?

View 2 Replies View Related

String Variable Size Issue

Nov 4, 2006

I have to send an email using a variable as message body... the variable gets its value from database varchar(8000). Due to limited size of string variable in SSIS the value gets truncated.

Any workaround?

Thanks

View 13 Replies View Related

Maximum Size Of The SSIS String Variable?

Aug 30, 2007

In SSIS, what is the maximum size of the String variable (one that you would define in the Variables dialog box)?

View 13 Replies View Related

Target String Size Is Too Small To Represent The XML Instance

Feb 7, 2006

This is a tough error....
The situation:  we are trying to manage Stored Procedures (ALTER, CREATE, DROP statements) on one of our SQL2005 servers and this error spews out:
"Target string size is too small to represent the XML instance"
Now this script i wrote (simple alter statement adding a column) works on 4 other versions of SQL 2005, but when i try a certain server, i get that error
Searching Google, Yahoo, and MSN, all three point to one single instance of this error, right here on Don Keily's blog
http://www.sqljunkies.com/WebLog/donkiely/archive/2005/10/20.aspx
 and unfortunately... no solution  :(
Maybe in the 3 months since that post, someone might have run across that and knows how to fix?

View 5 Replies View Related

DB Size Not The Actual Physical Size

Feb 15, 2000

Hi,

An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.

A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?

Does anyone know what the reason for this could be ? And how to clean this up ?

Thanks in advance,
Paul

View 2 Replies View Related

Table Size And Database Size

Mar 2, 2008

Hi,
i use this script that show me the size of each table and do the sum of all the table size.

SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]

the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?

THX

View 5 Replies View Related

SQL 2012 :: Existing Replica MDF File Size Increased In Size Than New Replica Install

Apr 14, 2015

Here are my scenarios:

We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.

1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.

2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.

3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.

Why it is allocating the space differently? This is effecting our initial replica set up times.

View 0 Replies View Related

Facing Problem In Increasing Connection String Pool Size Sqlserver

Mar 4, 2005

Hi to all,

I am using a connection string like

data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa;
Max pool size = 200;

And now strange thing is happening ,, I am receiving error :

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached

The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.

Huh , So stucked up , how to increase the Max pool size.. Is there any way .

I am getting worrried.

Any help ??

Thx and Regards

View 1 Replies View Related

Limit On SSIS String Variable Size, Trying To Get Big Xml File Into Xml Data Field

Nov 28, 2007

I have a SSIS package that opens an xml file, puts the contents into a string, then runs a stored procedure that dumps it into an xml column in a table. One of the xml files is huge. Putting the data into a ssis string causes an error. The length of the string variable is 58,231,886. The file will only get bigger.

How else can I get this data into a SQL Server XML field.

View 1 Replies View Related

SQL Server Admin 2014 :: Possible To Find Table Size And In That Table Each Row Size

Jun 10, 2014

It is possible to find table size and in that table each row size.

View 4 Replies View Related

Problem With Managed Code Simple Example Target String Size Is Too Small To Represent The XML Instance

Feb 10, 2006



I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:

"How to: Create and Run a CLR SQL Server Stored Procedure "

I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:

"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"

It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!

Here's the code as copied directly from the howto:

using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[SqlProcedure()]
public static void InsertCurrency_CS(
SqlString currencyCode, SqlString name)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCurrencyCommand = new SqlCommand();

InsertCurrencyCommand.CommandText =
"insert Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
" values('" + currencyCode.ToString() +
"', '" + name.ToString() +
"', '" + System.DateTime.Now.ToString() + "')";

InsertCurrencyCommand.Connection = conn;

conn.Open();
InsertCurrencyCommand.ExecuteNonQuery();
conn.Close();
}
}
}


Thanks for any help you can give!

View 7 Replies View Related

SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size

Aug 23, 2006

Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,

Any help on this greatly appreciated...

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)


Many thanks

View 5 Replies View Related

Getting Database Size And Log Size

Aug 11, 2000

Hi,
I am using
exec sp_helpdb
go
dbcc sqlperf(logspace) for
getting database size and log size. Is this gives the correct
database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.

Thanks in Advance.

Seenu. S

View 4 Replies View Related

Sql Size Vs. Access Size

Jul 20, 2005

I have a SQL Server database that is showing 2853.44 mb in size but when Iexport the data into MS Access the size is less than 1 mb. Can anyone tellme how to reduce the size of my SQL Server database so that it's less than15 mb?Thanks in advance! Rob

View 5 Replies View Related

Backup Size 2x DB Size

Oct 30, 2007

Why is it that lately our full backup (.bak) file size is more than 2x the size of the actual database file size (.mdb)?

View 9 Replies View Related

Log Backup File Size Not Matching Log File Size

Apr 15, 2008

I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.

Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.

So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.

When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.

What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.

Any ideas?

Stacy

View 8 Replies View Related

Invalid Syntax In My Sproc?!?!

Feb 18, 2007

Hi I have a gridview that is being populated from a method that gets it's data from a table view.
SELECT     dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone,                       dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName,                       dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact,                       dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentIdFROM         dbo.aspnet_Users INNER JOIN                      dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN                      dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId
No big deal, works great.  Now when I click update I call this method

<DataObjectMethod(DataObjectMethodType.Update)> Public Sub UpdateAlumni(ByVal Street As String, ByVal City As String, ByVal State As String, ByVal Telephone As String, ByVal Occupation As String, ByVal Description As String, ByVal Zip As String, ByVal FirstName As String, ByVal LastName As String, ByVal YearGraduate As String, ByVal Email As String, ByVal Contact As Boolean, ByVal original_UserName As String, ByVal UaaStudentId As String)
Try
Dim connx As New SqlConnection(getConnectionString)
connx.Open()
Dim sqlCmd As New SqlCommand("cis_UpdateAlumniContact", connx)
sqlCmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NVarChar))
sqlCmd.Parameters("@UserName").Value = original_UserName
sqlCmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar))
sqlCmd.Parameters("@FirstName").Value = FirstName
sqlCmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar))
sqlCmd.Parameters("@LastName").Value = LastName
sqlCmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar))
sqlCmd.Parameters("@Email").Value = Email
sqlCmd.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar))
sqlCmd.Parameters("@Street").Value = Street
sqlCmd.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar))
sqlCmd.Parameters("@City").Value = City
sqlCmd.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar))
sqlCmd.Parameters("@State").Value = State
sqlCmd.Parameters.Add(New SqlParameter("@Occupation", SqlDbType.NVarChar))
sqlCmd.Parameters("@Occupation").Value = Occupation
sqlCmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.NVarChar))
sqlCmd.Parameters("@Description").Value = Description
sqlCmd.Parameters.Add(New SqlParameter("@Telephone", SqlDbType.NChar))
sqlCmd.Parameters("@Telephone").Value = Telephone
sqlCmd.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar))
sqlCmd.Parameters("@Zip").Value = Zip
sqlCmd.Parameters.Add(New SqlParameter("@Contact", SqlDbType.Bit))
sqlCmd.Parameters("@Contact").Value = Contact
sqlCmd.Parameters.Add(New SqlParameter("@YearGraduate", SqlDbType.NVarChar))
sqlCmd.Parameters("@YearGraduate").Value = YearGraduate
Dim cmd As SqlDataReader = sqlCmd.ExecuteReader
Catch ex As Exception
Dim er As New cis_ODS_Error
er.InsertError("cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)
End Try
End Sub
The sproc it calls is:

ALTER PROCEDURE dbo.cis_UpdateAlumniContact
@UserName as nvarchar(50),
@Street as nvarchar(50),
@City as nvarchar(50),
@State as nvarchar(2),
@Telephone as nvarchar(50),
@Occupation as nvarchar(50),
@Description as nvarchar(50),
@Zip as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@YearGraduate as nvarchar(4),
@Email as nvarchar(50),
@Contact as bit
AS
UPDATE cis_AlumniContact
 
SET Street = @Street, City = @City, State = @State, Telephone = @Telephone, Occupation = @Occupation, Description = @Description, Zip = @Zip, FirstName = @FirstName, LastName = @LastName, YearGraduate = @YearGraduate, Email = @Email, Contact = @Contact
 
FROM aspnet_Users INNER JOIN cis_AlumniContact
 
ON cis_AlumniContact.UserId = aspnet_Users.UserId
 
WHERE @UserName = aspnet_Users.UserName
 
RETURN
I get this vague error
cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'
 
If I execute the SQL from the editor it works fine.  The only thing different about this sproc and my other update sprocs is the inner join.  Any idea? Thanks

View 2 Replies View Related

Sproc To Determine If Query Will Produce Invalid SqlNotificationEventArgs Event

Feb 7, 2007

I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.

I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...

exec sp_QueryInvalidForNotification "exec myTestSproc 99"

Does such a sproc exist? Or does anyone know how to write one?

View 1 Replies View Related

Data Files Size And Log Files Size

Sep 22, 2004

Hi!
I'm using SQL Server 2000!
How to know then Data files size and Log files size by Store Procedures or Sql query?
Thanks!

View 1 Replies View Related

Returning A String From A Sproc

Sep 8, 2006

I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:

select
@error = @@error,
@row_count = @@rowcount

set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @outgoingXML

where @outgoingXML is an output parameter. And I have tried:

set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @serilializedReturnCode

where @serilializedReturnCode is locally declared as varchar(250)

This is the error I get:

Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.

View 15 Replies View Related

Pass Concatenated String To SPROC

Jul 24, 2004

Hello,

We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.

My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?

Will I have to create Dynamic SQL to do this?...or... can I do something like this...




@ConcatenatedString --eg. 1,2,3,4,5,6,7

SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString



Thanks for your help.

View 2 Replies View Related







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