Can not insert input field with length gt 128

I got this problem that really puzzled me. I used a stored procedure to
insert records into SQL. When the field of input string contained more than
128 characters, it gave me this message:
The identifier that starts with 'Today is a nice day..........Good bye'
is too long. Maximum length is 128.
But here I am not sending in an identifier name, I am sending in the actual
input which is more than 128 characters long. The input field in the data
base is defined as varchar(1000).
Any ideas?What is the size on the parameter declaration in the stored procedure? I'll
bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:D4B955D4-C318-4E38-BB9F-77B439561881@.microsoft.com...
>I got this problem that really puzzled me. I used a stored procedure to
> insert records into SQL. When the field of input string contained more
> than
> 128 characters, it gave me this message:
> The identifier that starts with 'Today is a nice day..........Good bye'
> is too long. Maximum length is 128.
> But here I am not sending in an identifier name, I am sending in the
> actual
> input which is more than 128 characters long. The input field in the data
> base is defined as varchar(1000).
> Any ideas?|||> What is the size on the parameter declaration in the stored procedure?
> I'll bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
Actually, it sounds like the call he is making to the database doesn't
properly encapsulate this string. It seems SQL Server has mistaken the
string for an identifier (otherwise the error would be "string or binary
data would be truncated"). This usually happens when someone uses " instead
of ' for delimiting a string, but there are other possibilities as well.
"pelican", how about showing us the actual code you are using, instead of
vaguely describing it -- then we don't have to guess. I suppose DDL for the
table you are trying to INSERT into wouldn't hurt either (see
http://www.aspfaq.com/5006).|||Good point, probably an embedded ' in the input somewhere that wasn't
properly escaped by the app. Interesting that it would pick 128 as the
cut-off size, however. I would definitely need to see the code for the SP
and the input string.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:u6KSE6AUFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses "
> instead of ' for delimiting a string, but there are other possibilities as
> well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for
> the table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>|||> Good point, probably an embedded ' in the input somewhere that wasn't
> properly escaped by the app. Interesting that it would pick 128 as the
> cut-off size, however.
That is the maximum size for an identifier. I don't think it is an errant
', it just received this string that it thinks is supposed to be a column,
and is saying, WHOA, this column name is bigger than 128, hold on. It could
be 129 or 8000 characters... the same error will get kicked out.|||Thank you all so much! "AB - MVP" is right, once I changed the double quote
(") to single ('), it worked fine! In the stored procedure, the size on the
parameter is varchar(1000). I did not realize that the "" sign will make SQ
L
believe I was passing an identifier instead of the actually content.
You are my life saver...
"AB - MVP" wrote:

> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses " inste
ad
> of ' for delimiting a string, but there are other possibilities as well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for th
e
> table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>
>|||I read about the identifier length in the help section. It said the same
thing as AB-MVP pointed out, that 128 is the maximum length per row for an
identifier. I just never dreamed that a " will do it.
Thanks a lot!|||Yeah it's called a "quoted identifier" and can be circumvented by using
"parameterized queries".
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:6F3F8CAB-1EBD-45A6-BF74-4DC272EA1804@.microsoft.com...
> Thank you all so much! "AB - MVP" is right, once I changed the double
> quote
> (") to single ('), it worked fine! In the stored procedure, the size on
> the
> parameter is varchar(1000). I did not realize that the "" sign will make
> SQL
> believe I was passing an identifier instead of the actually content.
> You are my life saver...
> "AB - MVP" wrote:
>|||Yep that's what they get for slapping a SQL statement together with +'s
instead of using parameterized queries.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGWbvCBUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> That is the maximum size for an identifier. I don't think it is an errant
> ', it just received this string that it thinks is supposed to be a column,
> and is saying, WHOA, this column name is bigger than 128, hold on. It
> could be 129 or 8000 characters... the same error will get kicked out.
>

Can not Backups on SQL2005

Can not Backups.No Error!

The Code:

Code Snippet

static void Main(string[] args)
{
try
{
SqlConnection Connection = new SqlConnection(@."Data Source=SUPER-9ZT4OE2OQ;Initial Catalog=DataBase;Persist Security Info=True;User ID=tj;password=tj");
Server server = new Server(new ServerConnection(Connection));
Backup bak = new Backup();
bak.Action = BackupActionType.Database;
bak.Database = "DataBase";

BackupDevice backupDevice = new BackupDevice(server, "Full_Backup");
backupDevice.PhysicalLocation = @."\\192.168.2.13\DatabaseBak\over.remoteBak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;

bak.Devices.AddDevice("Full_Backup", DeviceType.File);
bak.Incremental = false;
bak.SqlBackup(server);

}
catch (Exception)
{

throw;
}
}

Any help is highly appreciated

Thanks.

Try changing the line

bak.Devices.AddDevice(xxx)

to

bak.Devices.Add(xxx)

Here's the example (in VB.Net) from BOL:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Backup object variable.
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.Database = "AdventureWorks"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)

Can not Backups on SQL2005

Can not Backups.No Error!

The Code:

Code Snippet

static void Main(string[] args)
{
try
{
SqlConnection Connection = new SqlConnection(@."Data Source=SUPER-9ZT4OE2OQ;Initial Catalog=DataBase;Persist Security Info=True;User ID=tj;password=tj");
Server server = new Server(new ServerConnection(Connection));
Backup bak = new Backup();
bak.Action = BackupActionType.Database;
bak.Database = "DataBase";

BackupDevice backupDevice = new BackupDevice(server, "Full_Backup");
backupDevice.PhysicalLocation = @."\\192.168.2.13\DatabaseBak\over.remoteBak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;

bak.Devices.AddDevice("Full_Backup", DeviceType.File);
bak.Incremental = false;
bak.SqlBackup(server);

}
catch (Exception)
{

throw;
}
}

Any help is highly appreciated

Thanks.

Try changing the line

bak.Devices.AddDevice(xxx)

to

bak.Devices.Add(xxx)

Here's the example (in VB.Net) from BOL:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Backup object variable.
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.Database = "AdventureWorks"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)

Can isolation level be set in connectionstring?

Hi,

I would like to be able to alter the default isolation level at connection time via the ADO connection string. Can this be done?

Why? I have various reporting applications (Crystal etc.) that queries against MS SQL server using ADO (SQLOLEDB). I would like to be able to alter the isolation level for these queries to readuncommitted. But many of the reporting applications does not have this option and they autogenerate the SQL making it impossible to use the use the WITH(table_hints) clause in the SELECT statement. So if I could set the isolation level in the connection string this could be a workaround.

Any help will be appreciated!

Bertrand

No, not in the connection string.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can isolation level be set in connectionstring?

Hi,

I would like to be able to alter the default isolation level at connection time via the ADO connection string. Can this be done?

Why? I have various reporting applications (Crystal etc.) that queries against MS SQL server using ADO (SQLOLEDB). I would like to be able to alter the isolation level for these queries to readuncommitted. But many of the reporting applications does not have this option and they autogenerate the SQL making it impossible to use the use the WITH(table_hints) clause in the SELECT statement. So if I could set the isolation level in the connection string this could be a workaround.

Any help will be appreciated!

Bertrand

No, not in the connection string.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can I use Strings in iif?

Hi everyone.

Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:

iif(somevalue is nothing, 'Other', somevalue)

Thank you in advance.

yes, but VB.net uses double quotes for strings

can i use single function for a big project ??...

Hi,

I wnat to write a single function in code behind file for all DML operation, and i just want to pass query string in its parameter. but when ever my whole project will use this function to insert, update and delete. what will be the performance.

bool DMLoperation(string str){obj.conn =new SqlConnection(obj.connstring);obj.cmd =new SqlCommand(str,obj.conn);obj.conn.Open();try{if(obj.cmd.ExecuteNonQuery() > 0){obj.conn.Close();return true;}else{obj.conn.Close();return false;}}catch{obj.conn.Close();return false;}}

I am totally confused about performance, is it good idea or worst ?.

Are you going to create a SQL manager for your project?
seems it's better for you to overload SQL object other then just pass-in the sql string and execute (just my opinion)

What i can only share is my company did the same things, ie. build a customized SQL manager for SQL execution,
although performace is a key issue, you should also consider other benfit that come from this design; such as you
can run extra SQL within each SQL call, such as SQL logging;

if you build the Manager in clean and well design, i believe performance is not a problem.

Hope this help

|||

My dear, can you send some snippet of code, to use by overloading SQL object, It may be help me

Can I use integrated security in an ODBC connection string without using a DSN?

Can I use integrated security in an ODBC connection string without using a
DSN?
If so, what would the syntax be for SQL Server and Oracle (if supported by
Oracle)?
TonyTrusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trust
ed_Connection=yes;
"
from http://www.connectionstrings.com/ (really good source for
connectionstrings)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
news:O4upNWsbFHA.3492@.TK2MSFTNGP14.phx.gbl...
> Can I use integrated security in an ODBC connection string without using a
> DSN?
> If so, what would the syntax be for SQL Server and Oracle (if supported by
> Oracle)?
> Tony
>|||Thanks...
Do you know of a way to connect using integrated security with Oracle?
Tony
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Onf$jAtbFHA.2936@.tk2msftngp13.phx.gbl...
> Trusted connection:
> "Driver={SQL Server};Server=Aron1;Database=pubs;Trust
ed_Connection=ye
s;"
> from http://www.connectionstrings.com/ (really good source for
> connectionstrings)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
> news:O4upNWsbFHA.3492@.TK2MSFTNGP14.phx.gbl...
>|||YOu have to set a parameter in the SQLNet.Ora
http://www.windowsitpro.com/Windows...2280/42280.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
news:Oq36BPwbFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Thanks...
> Do you know of a way to connect using integrated security with Oracle?
> Tony
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:Onf$jAtbFHA.2936@.tk2msftngp13.phx.gbl...
>

Can I use integrated security in an ODBC connection string without using a DSN?

Can I use integrated security in an ODBC connection string without using a
DSN?
If so, what would the syntax be for SQL Server and Oracle (if supported by
Oracle)?
Tony
Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;"
from http://www.connectionstrings.com/ (really good source for
connectionstrings)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
news:O4upNWsbFHA.3492@.TK2MSFTNGP14.phx.gbl...
> Can I use integrated security in an ODBC connection string without using a
> DSN?
> If so, what would the syntax be for SQL Server and Oracle (if supported by
> Oracle)?
> Tony
>
|||Thanks...
Do you know of a way to connect using integrated security with Oracle?
Tony
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Onf$jAtbFHA.2936@.tk2msftngp13.phx.gbl...
> Trusted connection:
> "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;"
> from http://www.connectionstrings.com/ (really good source for
> connectionstrings)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
> news:O4upNWsbFHA.3492@.TK2MSFTNGP14.phx.gbl...
>
|||YOu have to set a parameter in the SQLNet.Ora
http://www.windowsitpro.com/Windows/...280/42280.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tony" <tonyng2@.spacecommand.net> schrieb im Newsbeitrag
news:Oq36BPwbFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Thanks...
> Do you know of a way to connect using integrated security with Oracle?
> Tony
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:Onf$jAtbFHA.2936@.tk2msftngp13.phx.gbl...
>

Can i Send query string to reporting services 2000 ?

I would like to build the query on my code behind and send it as is

not to send params and use a build query on the R.S

Can i do it? how can i do it?

Hi,

what do you mean by "build query" ? If you just want to open a report you can go with the URL Access syntax which can be found in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I Mean that i build my quey on run time

the user picks something and by that i decide which query to build

this is the reason i want to send the whole query and not only params

p.s

i use sql 2000 not 2005

|||

Hi,

you would probably use a procedure then to redirect the flow to a specifc query.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Reporting Services 2000 allows the usage of an expression-based command text. However, keep in mind that once you use an expression you can no longer directly execute the query in the query designer. I recommend to first build the expression in a report textbox and try it with various parameter combinations to visually verify the generated queries are correct and then as a last step replace the original command text with an expression, such as
="select a, b from table1"

Also make sure that if you use string concatenation to build your query based on parameter values, you have to very careful to prevent SQL injection attacks in your query.

Alternatively to an expression-based command text, you could use a parameterized query to avoid the risk of SQL injections (e.g. select a, b from table1 where country = @.Country)

-- Robert

Can I pass '=' or '>' as parameters?

Hi
I'm moving SQL in my app to Stored Procedures.

My SQL string is currently created within my app, and I have two check boxes and this C# code...

if(chkPartTime.Checked&&!chkFullTime.Checked) { sql+=" AND P0_Hours = 0"; }

if(!chkPartTime.Checked&&chkFullTime.Checked) { sql+=" AND P0_Hours > 0"; }

where sql is my query string

If both boxes are checked I don't care what value P0_Hours is, but at least one of them will be checked.

What I want to know is how I transfer such logic into a Stored Procedure, or whether I will need to creat 2 similar sp's and choose which to call in my app.

It will combined with a query (currently 60 lines) comprising of 3 UNIONs

TIA

Sure why not...

you could literally pass a char(1) of '>' or '=' and use it to build the sql string

However wouldn't it be better to pass 'bit' fields like this:

SP paremeters:

@.chkPartTime bit

@.chkFullTime bit

then in the code:

in the Where clause:

Code Snippet

and ((p0_hours = 0 and @.chkPartTime = 1 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1 and @.chkPartTime = 0))

or better yet... just have one parameter:

@.chkFullTime bit and then 0 = Parttime 1 = FullTime

so then the code would look like:

Code Snippet

and ((p0_hours = 0 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1))

-Robert

|||

use the flag parameter (new param) & pass the appropriate flag value to the sp.

for example on your code,

Code Snippet

int iFlag = 0;

if(chkPartTime.Checked && !chkFullTime.Checked) { iFlag = 1; }

if(!chkPartTime.Checked && chkFullTime.Checked) { iFlag = 2; }

//Pass this flag variable as parameter while calling the SP

on your SP,

Check the flag using IF condition are on where condition to get the required result.

Code Snippet

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

@.Flag = 0

Or

(@.Flag = 1 And P0_Hours =0)

Or

(@.Flag = 2 And P0_Hours >0)

--or

If @.Flag = 0

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

If @.Flag = 1

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours =0

If @.Flag = 2

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours > 0

|||

I don't know, I usually would just suggest a dynamic SQL stored procedure in these cases, particularly if there is any real heft to your data. The other solutions suggested are a good possibilities too, but the complexities of coding tend to turn things like this into a nightmare both to maintain, and to get a decent query plan anyhow.

Louis

|||

Thank you all

It was difficult to choose which to mark as the answer!

I had not heard of dynamic sp's (I come from a DB2 background, now using SQL Server) and I have learnt from all 3 answers, still not made my mind up which way to go, but thanks.

Can I just get a string back from SQL Server with "FOR XML AUTO, ELEMENTS"?

I keep getting back some hexadecimal garbage data rather than the nice XML I
see in the Query Analyzer.
What gives?
WTH
Are you using OLEDB or ADO?
You should use the command STREAM object instead of the recordset. See the
documentation for some example code.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
>I keep getting back some hexadecimal garbage data rather than the nice XML
>I
> see in the Query Analyzer.
> What gives?
> WTH
>
|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Are you using OLEDB or ADO?
> You should use the command STREAM object instead of the recordset. See the
> documentation for some example code.
> Best regards
> Michael
> "WTH" <nospam@.spamtheman.com> wrote in message
> news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
XML[vbcol=seagreen]
I read that using that method results in differing results depending upon
whether a remote server returns the results or a local server does, no
thanks.
I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
WTH
|||> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
There is no problem that I am aware off regarding connecting to a local or
remote server using the command stream object and FOR XML. Do you have a
reference to this?
However, if your other solution works, that's fine.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:emhf28RwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...
> XML
> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
> I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
> CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
> WTH
>