In SS 2000 it seems that there is no variable data type that can hold more than 8000 characters (varchar) or 4000 unicode characters (nvarchar). I've seen posts where multiple variables are spliced together to extend this limit. I am looking at performing string manipulations in an sproc and I need to be able to deal with the full 2GB/1GB limit of text and ntext field types. Is this possible? How do you deal with that?
View 14 Repliesif a user chooses to request a lot of customers to report on say from a multi select listbox - what is the best way to pass this list to my stored proc? Looking for suggestions.
thanks,
Hi,
I have a MDX query which is of an aprox length of 10000 characters. I
have to execute the query from within the stored procedure in sql. To
run this query I use the openrowset method.
If the length of my query is less than 8000 characters my query
executes perfectly, but the moment it exceeds 8000 characters it stop
working. Please suggest a solution for the same.
Sample Code:
declare @mdxqry varchar(8000)
declare @SearchCond varchar(8000)
set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '
set @mdxqry = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
' +
'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '
set @mdxqry = @mdxqry +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''
set @mdxqry = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'
exec(@mdxqry)
I have already tried splitting my query into smalled chunks and
executing it, but still I face the same problem.
This is how I have Done it:
declare @mdxqry1 varchar(8000)
declare @mdxqry2 varchar(8000)
declare @SearchCond varchar(8000)
set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '
set @mdxqry1 = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
'
set @mdxqry2 = 'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '
set @mdxqry2 = @mdxqry2 +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''
set @mdxqry2 = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'
exec(@mdxqry1 + @mdxqry2)
Thanks in Advance
Charu
Hi,
I had a VARCHAR(MAX) parameter declared in my stored procedure and trying to concatenat single column from a table which has~500 rows into a string and keep in this variable, if i am not mistaken, i read that the VARCHAR(MAX) actually can hold up to 2GB of data, so it make me confuse why the variable which i declared as MAX size, can only hold up 8000 characters, any idea?
Regards,
Derek
Hi all,I have a internet page written in asp to submit into authorscurriculum vitae publications (title, author, year, etc.).If the author submit less than 8000 characters it functions OK, but Ifthe author try's to submit more than 8000 characters the asp page doesnot return an error but the text is not saved in the database or,sometimes, it returned a "Typ mismatch" error.Here is the sp:---------------------------------------------------------------------CREATE PROCEDURE sp_CV_publications(@formCommandnvarchar(255)='process',@id numeric=null,@id_personint=null,@typPubID tinyint= NULL,@publicationstext=null)ASif @formCommand='process'beginINSERT INTO CV_publications(idperson,typPubID,publications)VALUES (@id_person@typPubID,@publications);select 1 as status, @@IDENTITY AS insertedID, * FROMCV_publications WHERE id=@@IDENTITYend----------------------------------------------------------------------------The server is running IIS5 and SqlServer 2000Any ideas ???
View 1 Replies View RelatedHi..
I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.
I was doing something like..
Create Procedure proc
(
@Items varchar(100) --- List of numbers
)
AS Begin
Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)
This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."
Can any1 please help me out with this ASAP??
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max)
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
@userId varchar(80),
@FolderId varchar(80),
@Insertcontent nvarchar(max)
as
declare @contentsql nvarchar(max);
set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
@Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+'''';
exec sp_executesql @contentsql;
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hello,
am using c# and a Stored proceedure in MS SQL 2005.
For data size i used the varchar (max) in my stored proceedure. But in the c# class. What do i use ? Here is a line of my code
thanks
Ehi
command.Parameters.Add(new SqlParameter("@csv", SqlDbType.VarChar, 8000, "recipients"));
Hello,
We are using Exec(@sql) with @sql varchar(8000), but 8000 is not enough. Like query get cut off in the middle of the script. We need more than that. Is there any way to store more than 8000 characters in the variable ?
We use SQL Server 7.
Thanx in advance.
Hi all!
I'm using this store proc to get a pivot table in SQL:
USE [m0851System]
GO
/****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
,@tbl_result varchar(100),
@fld_sufx varchar(10)
-- FIN JULIEN BONNIER
AS
--DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
-- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
IF LEFT(@tbl_result,1)='#'
BEGIN
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
ELSE
BEGIN
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
-- FIN JULIEN BONNIER
EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pvt'
-- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
--SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' +
SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
-- FIN JULIEN BONNIER
-- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
ORDER BY pvt
-- FIN JULIEN BONNIER
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
PRINT(LEN(@sql))
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
-- FIN JULIEN BONNIER
--EXEC (@select)
PRINT('YOYO'+@select)
RETURN
SET ANSI_WARNINGS ON
But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.
What can I do to fix it ?
Thanks in advance.
Or Tho
Hi ?
i have created a store procedure that will copy the proceduers of one database to another database. it's working fine, but i am hving problem to run the the store proceduer when character lenght goes more then 8000 words. i have couple of proceuder that has words more then 8000, is there any good and short way to do solve this problem
Thanks and looking forward.
-MALIK
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
Hi,
I have a problem with a text string which is more than 8000 chars.
I am taking this string as an input from an application.so,I cannot define a local variable as text or ntext and varchar has limitation of only 8000 char.
Can anyone help me in dealing with this situation.
Also,I cannot break the string at application level.I wish if I could solve it at db level somehow?
TIA
pd
Is there a way to create a datatype other than varchar that is greater than 8000? :o
View 3 Replies View RelatedI'm wondering if there's a way to use the string function replicate() to produce an output greater than 8000 bytes. From the documentation REPLICATE returns a varchar of max 8000 but I'm told there's a way to stop this restriction.
My mentor has been giving me tasks to try and accomplish and for this one I'm to create a stored procedure with no limitations, however I'm stuck at this replicate() problem.
Any help or guidance would be great.
Thanks
I have the following SQL select query:
Code:
SELECT
inbound_emails.inbound_email_id,
inbound_emails.campaign_id,
campaign_header.campaign_name,
customers.customer_name,
inbound_emails.date_received,
[Code] ....
Without any indexes (other than PK's) this query would take 1min 21 secs to return all 8500 records.
I wasn't satisfied with that so I added indexes to the tables.
Indexes are;
inbound_emails:
PK: inbound_email_id
1. campaign_id (non-unique, non-clustered)
2. email_from (non-unique, non-clustered)
3. email_to (non-unique, non-clustered)
campaign_header:
PK: campaign_id
1. campaign_name (unique, non-clustered)
customers:
PK: customer_id
1. customer_name (unique, non-clustered)
contracts:
PK: contract_id
1. contract_name (unique, non-clustered)
2. customer_id (non-unique, non-clustered)
Have I made a hash of the indexes? Or is the query statement badly written? What is a reasonable amount of time to expect to retrieve 8500 records?
I think email_from and email_to on inbound_emails is the main culprit, but I don't understand why?
Hi
I have a BULK INSERT task to do, inserting
contents of a file per row.. (not lines total contents of text files)
but it seems 8000 is the max size of a field in SQL 2000 ?
is this the case ?
Hello!
Is there an easy way to deal with this situation below when reading in data from a SQL Database:
int? myNullableColumn;myNullableColumn = Convert.ToInt32(datarow["datacolumn"]);
Where, ideally, 'myNullableColumn' would be 'null' if the value was 'DBNull.Value'. This does not work because Convert.ToInt32 will not convert 'DBNull.Value to null', but instead throws an error.
Is there a built in funtion that does do this?
Thanks!
Ok, I have a table with about 47000 records in it. I have the following query for that table:Select ReportType =
Case
When ReportType = 1 Then 'Uniquery Report'
When ReportType = 2 Then 'SABRE Report'
When ReportType = 3 Then 'Menu Report'
Else Null
End,
ReportNameTo_,
Frequency.Frequency as Frequency,
ReportDate,
ReportDescription
From Report
Inner Join Frequency on ( Report.ReportFrequency = Frequency.FID )
Where ( Active = 1 )
And ReportDate = ( Select Max ( ReportDate ) From Report Where ( Active = 1 ) )
And ReportID = ( Select Max ( ReportID ) From Report Where ( Active = 1 ) )
The idea is that i need to get only the last report based off of unique reportname. I added a computer column to the table to give me the ReportNameTo_, since my deliminator is the _. Now my issue is that I have 1 records showing (the last record added to the table), which is right for the query that is written, but wrong for what I want. I need to only return the last record for each unique ReportNameTo_. So as an example, my table has the following ID, ReportNameTo_, Date fields the data looks something like this:
1, 123_, 1/1/20082, 123_, 1/1/20083, 124_, 1/1/20084, 124_, 1/1/20085, 125_, 1/1/20086, 125_, 1/1/20087, 126_, 1/1/20088, 126_, 1/1/2008
I only want to return the following:
2, 123_, 1/1/20084, 124_, 1/1/20086, 125_, 1/1/20088, 126_, 1/1/2008
Hope someone out there can let me know how to do this... I am almost there, just not all the way.
Hello!
Just looking for advise on dealing with duplicates in database.
I have a contact table that have a bunch of duplicated customer records.
My goal is to combine all duplicated records into one record.
This involves couple tables:contact,contact history ,calendar.
All tables related by common column "accountno".
What would be the best approach for this?
Thank you,
Lena
I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.
select@MarketTotal = sum(isnull(MarketValue,0))
fromhistory
whereEmpUID = @EmpUID and
Shares > 0
Thanks,
Ken Nicholson
Hi i have the following :
select agent, name, surname, address, cust1_text01, cust1_text02, phone1,
case call_type_id
*when NULL then ''
else call_type_id
end as 'call_type_id'
from Record_T
* I have also tried when NULL then space(1)
yet the query still returns NULL when this field is empty ?
the idea is to always return data, even if the field is NULL to
replace it with an empty space or spaces.
Hello,
Question 1:
In my senario i've developed a system which utilizes 2 database, i've writen queries like db1.dbo.table1 join db2.dbo.table2 etc... Now that db2 is getting huge, client wants to shift it to another server.
I don't know how to modifiy my queries to cope with such situation. Could somebody plz tell me on how to you write queries involving two databases from different servers.
Question 2:
I'm maintaining second database (db2) to keep track of records of db1 which have been processed by my software, so that when db1 gets added with more records i can compare db2 table with db1 table to identify which records are new.
db1 is not my database and i don't have any control over that, (it's some erp db), is there any way of identifying which rows have been processed. Can the need for db2 be eliminated?
I'm using SQL Server 2005
Thank You,
Sandeep.
I am trying to change a text field into a varchar 8000.
I get his error message when trying to convert.
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or
to select only those that are over the 8000 character mark?
Please advise
Thanks
Susan
I was trying to build a string based on one field from one table in Stored Procedure. I know if I declare A VARCHAR variable for the string, it won’t allow to exceed 8000. If does, it will truncate the reminding parts of the fields. What might be the simple solution for that? Even though I know I might need to use several substrings but how?
Thanks!
J827
Declare @sql varchar 8000
Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions …
Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).
Thanks for any reply.
aex
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies View RelatedI am trying to make a stored procedure in SQLServer Express.The question is related to this stored procedure / transact - sql. I think i am doing something wrong with datetime.Here is the stored procedure.The error i am getting is that:Msg 241, Level 16, State 1, Line 20Syntax error converting datetime from character string. ===================================== DECLARE @websiteID intDECLARE @dateFrom datetimeDECLARE @dateTo datetimeDECLARE @sortbystring varchar (20)set @websiteID = 1set @dateFrom = Convert(datetime, '2007-02-07 12:01:00')set @dateTo = Convert(datetime, '2007-03-07 11:59:00')set @sortbystring = 'Campaign'IF ISNULL(@dateTo, '') = ''begin SET @dateTo = @dateFromendSET @dateTo = DATEADD(d, 1, @dateTo)DECLARE @str CHAR(400)LINE 20: SET @str = 'SELECT dateEntry, c.name as Campaign, e.firstname as FirstName FROM entry e, campaign c WHERE e.campaignID = c.id ' + 'AND c.websiteID = @websiteID' + 'AND (ISNULL(' + @dateFrom + ', '''') = '''' OR e.dateEntry BETWEEN '' + @dateFrom + '' AND '' + @dateTo + '') ' + 'AND e.IP NOT IN (SELECT IP FROM IP) ' + ' ORDER BY dateEntry DESC'print (@str)===============================================
View 9 Replies View RelatedI have a stored procedure that takes less than 1 second in sql query analyzer to return my results.
I run this same SP in ASP.NET using a calendar control and using perf monitor I notice that for me from my dev machine my cpu utilization is sometimes over 40%.Is there any tweaks I can do to help decrease CPU utilization.
I'm looking for opinions here:
I have a stored procedure that has one required variable, and two optional variables, like this:
CREATE PROCEDURE sp_tariff_rule
@tariff_id INT,
@start_date DATETIME = NULL,
@end_date DATETIME = NULL
...etc...
I want the procedure to process
1) all data is no dates are presented
2) all data after the start date, if no end date is supplied
3) all data before the end date if no start date is supplied
4) all data between the start and end dates if both are supplied
Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement:
AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))
It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here.
I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
Hello all. Got bit of a long winded question here...........so here we go lol.
OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:
SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
EMPLOY_REF + ''', '''+
FROM AtriumDD...['Employee Training Records$']
For most records this generates a correct insert statement.........for example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');
However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');
can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.
Thanks for reading.