hi
am using vs2005 with sqlserver2000 .
i want to store word file/rtf/txt content in a table.which datatype i have to select.
which datatype supports unlimited characters?it has to support all type of characters(including html tags).
Thanks
shobha
Is there a way to make a string field that has an unlimited amount of characters?
View 1 Replies View RelatedHello ,
I would like to know the size in bytes required internally for each datatype MSSQLServer supports.
For eg.
I think for SMALLINT its 2 bytes
Would like to know the size in bytes for
SMALLINT, INTEGER, REAL, VARCHAR, DATETIME, IMAGE, DECIMAL
Thanks..
Hello,
I am developing a message board using ASP on IIS and SQL Server 2000, and I am running into a problem.
When a user enters their post, they enter their user name, subject, and of course all of their post content. Now the post content should be an unlimited number of characters since it could end up being multiple paragraphs.
I know I could just use an ASP file system object and write the text to a file, but I wanted to save all of these paragraphs as a field in a database table so I could pull it from the database so it could be edited by the user.
Is there a way to make a field be able to take in an unlimited amount of characters?
Or is this the wrong way to do things; is it bad to have an unlimited amount of characters in a table field? Should I stick to using the ASP System File Object?
Hi,
iam having vs2005 installed on my machine and i also installed sqlserver2005 dev edition on my machine...Now iam devloping a Window Appplication in which I need to populate a grid from Sqlserver2000 (dev edition) which installed on remote (i.e another network)....when i try to run my application it gives me a error tht "Unable to connect to sqlserver2005".actaully i specified in my connection string to connect to Sqlserver2000 on the remote machine.
I feel to know how to enable remote connection on Sqlserver 2000.So tht i can access this sqlserver2000 from another machine
With regards
Mahender
Hi Everybody,
I have a table with one column of text data type. This table contains around 1200 records(each row is 60 characters only). My task is, I have to append all these records into a single record(Concatenation of records from other table) to another table.
For that I have created table(destination table) with one column of text datatype. Using the 'UPDATETEXT' function I am able to append 133 records(each row of 60 characters) from the other table into a single row of my destination table. After that I am not able to append my records further. It is giving the following error.
"
Server: Msg 7135, Level 16, State 4, Procedure gene1_proc, Line 26
Deletion length 60 is not in the range of available text, ntext, or image data. The statement has been terminated.
"
Why I have created 'text' instead of char or varchar datatype is, it can accept more than 8000 characters. But here in my case it is not accepting more than 8000 characters. The problem is coming from 134 (133*60 = 7980 characters) records onwards.
Can anybody guide me how to proceed with this?.
tks in advance,
Sam
Hello,
I'm not sure if it's the setup I did wrong, but I can't seem to get my
text datatype in my database to store more than 900 characters.
I'm trying to setup a news database for my website, which will populate
the information into a datagrid. To test, I manually added a news
item in the database through the visual studio 2003 gui. I
immediately noticed a problem as the I was getting an error after a
long news item saying:
"The value you entered is not consistent with the data type or length of the column, or over grid buffer limit."
I couldn't find anthing to set the buffer limit and the datatype is
"text" filled with simple text in the column. As a further test,
I
simply entered 12334567890123... up to 900 characters and still
recevied the error.
I would appreciate someone leading me in the right direction on this one.
Thanks a lot.
Hi all,
I have a question? Whether Sql supports mutlithreads on triggers.
For example:
I have a Patient table. When two patient records getting inserted by two different transactions does it trigger the triggers in different threads and execute the code in Insert trigger Or only one row will fire the trigger and the other one won't?
Thanks
I see this question a lot, so I thought I would go ahead and post the answer here.
Q: How many connections does SQL Server Express support?
A: Unlike MSDE, SQL Server Express does not limit connections. SQL Server Express supports:
1 gb RAM (note the machine can have more memory, but 1gb will be used by SQL Express)
1 processor (again, the machine can have more, but just 1 proc will be used by SQL Express)
4 gb database size (note, there can be multiple databases, but none of them individually can be more than 4 gb)
Hope this helps,
Thanks< MJ
Why?
View 5 Replies View RelatedBit of a design question as I'm interested to know if anyone's done anythign like this...This is my main table (ish) Thing(ThingId, Ref)I then need to be able to give this "Thing" any number of attributes. Thing1 - Type:Red, Location:LondonThing2 - Type:Blue, Height:400, Width: 300Thing3 - Height:500, Location:Norwich But I have no idea how to model this in the database - it needs to be in such a way that I can add a Thing and all its attributes in one database hit basically (is there a stored procedure you could pass an array into?) My initial thoughts were to have Thing(ThingId, Ref) Attribute(AttributeId, ThingId*, AttributeTypeId*, Value) AttributeType(AttributeTypeId, Description) Is that completely mad? It seems like quite a lot of data accesses to enter a ThingIt could be Thing(ThingId, Ref, Type, Location, Height, Width) but then when "Thing - Color:White" comes along the model is stuffed Any ideas? (hope that makes sense)
View 3 Replies View RelatedIs there any way to write procedure with ulimited number of parameters?
Like in COALESCE function. You can pass one or more parameters.
Hi,
If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use?
Char, varchar and text have a max. limit of 255...
Will appreciate any suggestions.
Thanks,
Nishi
Introduction
This MS SQL Store Procedure solves a problem which is not common
except when you have a table in a database which each row needs
to be uniquely identified and their are more rows in the table
than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to
8.6904152163272468261061026005374e+50 unique rows at best.
If you used a "Big Int" you would be limited to -2^63 (
-9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the
identity field.)
or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for
this invention is due to the need to track every email that an
application sends for Sarbanes/Oxley Requirements. With this
technique, every email sent out will be uniquely identified for a
very very very long time.
The purpose of this is to show how to set up an identity column with
a larger range than a big int or unique id. Try transaction logs
where you need to track every change, or determining click paths
through a website, etc.
The point is, that this method pretty much does what the title
says, "Create unlimited Unique ID's". What table you apply this too
and for what reason is up the the programmer.
Background
This design uses basic counting methods and handles the limitations
of MS SQL TSQL. First, you can use a varchar(4000) as the unique id
column but the issue with this is that as of MSSQL 2000, the largest
indexable field is 900 character. So if you need to be able to
quickly search the table by key, or clustered keys, you need to limit
your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a
temporary table, select the subset into it and search that.
Using the code
First, copy and paste all the TSQL into a Query Window and
compile it in the database you wish to use it in.
[Code]
/**********************************************************************************
Program: ALTER Unlimited Unique ID's (Auto Increment)
Programmer: Vince Gee
Date: 9/28/2005
Parameters:
@TABLE_NAME - The name of the table to establish the auto incrementing field in
@COLUMN_NAME - The column name in the table to establish the auto incrementing field in
@DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with
the same name.
Theory:
A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description:
The purpose of the sql procedure is to automate the creation of
auto updating identities on a sql table without the trouble of
writing the trigger each time.
So what does this do? Well for example lets say we have the following
table which you will have many many many rows in.
ALTER TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
myKey is the unique identifier for each row. We can set it's size really
to anything, This proc will look for the column specified and determine it's
size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated
together exceed the storage compacity w/ the trigger code of 4000 characters. If this
is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc:
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output:
When data is inserted into the table, the auto incrementing field will look like
0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
000000000A
000000000B
000000000C
000000000D
000000000E
000000000F
000000000G
000000000H
000000000I
000000000J
000000000K
000000000L
with how many 0's set up etc. It goes 0-9, then A-Z
***********************************************************************************/
ALTER PROC SP_SET_UNIQUE_FIELD
@TABLE_NAME VARCHAR(255),
@COLUMN_NAME VARCHAR(255),
@DROP_EXISTING_TRIGGER BIT =0
AS
DECLARE
@EXECSQLSTRING nvarchar (4000),
@counter int,
@COLUMN_NAMES varchar(4000),
@tCOLUMN_NAME varchar(4000),
@MAXORDINAL int,
@KEYLENGTH int
--If the trigger
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
begin
IF @DROP_EXISTING_TRIGGER = 0
BEGIN
-- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
RETURN
END
ELSE
BEGIN
--CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT.
set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME
--EXECUTE THE SQL
EXEC SP_EXECUTESQL @EXECSQLSTRING
END
end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS
create table #temp
(
TABLE_QUALIFIER varchar(255),
TABLE_OWNER varchar(255),
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255),
DATA_TYPE int,
[TYPE_NAME] varchar(255),
[PRECISION] int,
LENGTH int,
SCALE int,
RADIX int,
NULLABLE int,
REMARKS varchar(255),
COLUMN_DEF varchar(255),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB varchar(255),
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(255),
SS_DATA_TYPE int
)
--POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE
insert into #temp
exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING
--FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD.
SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP
SET @COUNTER = 1
SET @COLUMN_NAMES = ''
WHILE @COUNTER <= @MAXORDINAL
BEGIN
select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
if (@tCOLUMN_NAME <> @COLUMN_NAME)
begin
SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ','
end
else
begin
select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
end
SET @COUNTER = @COUNTER +1
END
--CLEAN UP
drop table #temp
IF @KEYLENGTH > 900
Begin
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
END
SET @EXECSQLSTRING = '
CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
INSTEAD OF INSERT
AS
BEGIN
DECLARE
@VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@REVERSED_VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@COUNTER INT,
@LEFT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@RIGHT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@CHAR_VALUE CHAR
select @VALUE = ISNULL(MAX(' + @COLUMN_NAME + '),REPLICATE (' + "'0'" + ',' + CONVERT(VARCHAR(900),@KEYLENGTH) + ') ) from ' + @TABLE_NAME + '
SET @REVERSED_VALUE = REVERSE(@VALUE)
SET @COUNTER = 1
WHILE @COUNTER <= LEN(@REVERSED_VALUE)
BEGIN
SET @CHAR_VALUE = SUBSTRING(@REVERSED_VALUE,@COUNTER,1)
IF ASCII(@CHAR_VALUE) <> 122
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
IF ASCII(@CHAR_VALUE) + 1 = 58
SET @CHAR_VALUE = CHAR(97)
ELSE
SET @CHAR_VALUE = CHAR(ASCII(@CHAR_VALUE) + 1)
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
BREAK
END
ELSE
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
SET @CHAR_VALUE = CHAR(48) --SET THE CURRENT POSITION TO ZERO AND WE INCREMENT THE NEXT DIGIT.
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
END
SET @COUNTER = @COUNTER +1
END
SET @VALUE = REVERSE (@REVERSED_VALUE)
INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAMES + @COLUMN_NAME + ')
SELECT
' + @COLUMN_NAMES + '@VALUE
FROM
inserted
END'
if len(@EXECSQLSTRING) <4000
begin
EXEC SP_EXECUTESQL @EXECSQLSTRING
end
else
begin
print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.'
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Create TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on.
Column Name - Name of the column to use as the key.
Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int
set @t = 0
while @t <= 40000
begin
insert into countertest
select '','s','s','s'
set @t = @t + 1
end
GO
Once this completes, you can inspect the unique id by selecting it from the table
SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
I am trying to save my package (using MS Visual Studio) with ProtectionLevel = ServerStorage but it is failing with the following error "failed to apply package protection with error 0xC0014061, the protection level, serverstorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability...". I am trying to test a scenario on which the package is saved in SQL Server/msdb and schedule the package via SQL Job Agent. Can someone show me how to save a package using serverstorage and schedule it via SQL Job Agent?
View 4 Replies View RelatedWe have a project developed on asp.net 3.5 with SQL Server 2008 R2 as back end database. One of our client is facing issue of system performance in the afternoon time only. Mostly the tables are getting locked for unlimited time and system gets very slow. Need a professional SQL Server DBA to identify the root cause.
View 1 Replies View RelatedHi All,
Database size is not increasing automatically ,however I have set it as unlimited growth. Any idea about this ?
thanks for in advance,
Sedat Duztas
Probil
We have 300+ databases on one sinlge server. If I need to change log size to "unlimited" for all of them, is there any way to do so? Please advice.
-Julie
I am trying to fetch the sharepoint list data into the SSRS report.
I have given credentials in shared datasource to access remote datasource.
In preview i m getting the following error:
Data extension supports windows integrated security and no credentials only.
I've searched the forum and saw that this question has been asked several times. However, none of the responses point to any documentation about this limit. We've got different groups bickering over this ("There is no limit!" "Yes there is!" "No there isn't!") and it would help to have a link on the MSDN or a response from Microsoft to this question to put this to rest once and for all. Also, if there is a limit, is that per Sql Server instance or per physical server?
Secondly, we currently have over 400 catalogs. We've noticed it takes 4-5 minutes for new data to show up in search results. Does the number of catalogs affect performance, or is it the amount of data, irrespective of catalog count?
Thanks
Reformatting data in a PIVOT Table or find a better way to display.
--ORDERDETAIL TABLE
SKUOÂ Â QTYOÂ Â Â Â ORDERIDO
KUMÂ Â Â Â 1Â Â 12345
KUSÂ Â Â Â 2Â Â 12345
SUKÂ Â Â Â 1Â Â 12345
KHNÂ Â Â Â 4Â Â 12345
DREÂ Â Â Â 1Â Â 12345
[Code] ....
Number of SKU's in order could be over 1000.
Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.
Data I am looking to get.  MAX of 15 SKUS Per line.
ORDERIDÂ Â Â Â SKU1Â Â Â Â QTY1Â Â Â Â SKU2Â Â Â Â QTY2Â Â Â Â SKU3Â Â Â Â QTY3Â Â Â Â SKU4Â Â Â Â QTY4Â Â Â Â SKU5Â Â Â Â QTY5Â Â Â Â SKU6Â Â Â Â QTY6Â Â Â Â SKU7Â Â Â Â QTY7Â Â Â Â SKU8Â Â Â Â
QTY8Â Â Â Â SKU9Â Â Â Â QTY9Â Â Â Â SKU10Â Â QTY10Â Â SKU11Â Â QTY11Â Â SKU12Â Â QTY12Â Â SKU13Â Â QTY13Â Â SKU14Â Â QTY14Â Â SKU15Â Â QTY15Â Â
12345Â Â KUM 1Â Â KUS 2Â Â SUK 1Â Â KHN 4Â Â DRE 1Â Â HGF 2Â Â FDE 1Â Â CDS 1Â Â GYT 1Â Â POI 3Â Â LKH 2Â Â TTT 4Â Â JHG 8Â Â YUI 2Â Â WQE 1Â Â
12345Â Â PMN 1Â Â BVC 1Â Â ABD 1Â Â
[Code] ....
CURRENT PIVOT ONLY GOES TO 150 - BELOW
SELECTÂ Â Â Â PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
                      [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
[Code] ....
When trying to process my minding model (using Association Rules) i receive the following errors
Errors and Warnings from Response
Error (Data mining): The 'Items Recommendations' mining model has 64675 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.
Errors related to feature availability and configuration: The 'Unlimited data mining attributes.' feature is not included in the 'Standard Edition' SKU.
I searched through the internet and found that a hotfix can be installed to make it work (http://support.microsoft.com/kb/932609). I emailed microsoft and they provided me back with the hotfix and a password.
I tried to install the fix but i couldnt. It needed a prerequisit fix. So i installed SQL Server 2005 Service Pack 2 from (http://support.microsoft.com/?kbid=921896)
Then tried to install the fix for my unlimited attributes problem.
I tried to process my module after installing the fix and restarting my computer, but am still gettin the same error.I created another new model but the problem still exists.
Am i missing something here? Any idea to make it work?
Is there any alternative? I need to use all the elements in my datasource view.
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
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
Hi,
I have tried to attatch a database ,created by SQL server Express within a C# application , in SQL server 2005 Enterprise edition, but the following error message appears:
-----------------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MEDO'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1187.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database 'E:X.MDF' cannot be opened because it is version 611. This server supports version 607 and earlier. A downgrade path is not supported.
Could not open new database 'E:X.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)
------------------------------
so , how can I solve this problem , I need to reed the data resides in the tables of 'X' database , how can I do it?? please help me.
Thanks in advance for any help.
Aya.
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
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
Hi, I have a little question regarding
SQLServer2000 vs ADO of Visual Basic 6
In a code snipplet a have something like:
Conn1.BeginTrans
Set rec_tmp = Conn1.Execute("SELECT * FROM IDENTIFICATIONS;")
Conn1.Execute "DELETE FROM CARS WHERE CAR_ID = 8;"
.
.
.
Conn1.CommitTrans
and whe VB tries to execute the line with the
DELETE sentence the Err object says something like:
"cannot create a new connection because you are in
distributed or manual transaction mode"
However, if I remove the line that executes the sentence
SELECT * FROM IDENTIFICATIONS the error never shows up.
Seems like I can´t execute a SELECT before a DELETE/UPDATE
even if I execute the SELECT in some other table.
I´ve tried changing the IsolationLevel, Mode and Cursor
Location properties and the error always occurs.
Furthermore, I tried putting the SELECT out of the
transaction and the error continues showing up.
Why does this occurs? Is there a way to solve it?
NOTE: I need forcely to execute that SELECT before the DELETE.
Thanks for the time
Roland
Hi, I have a little question regarding
SQLServer2000 vs ADO of Visual Basic 6
In a code snipplet a have something like:
Conn1.BeginTrans
Set rec_tmp = Conn1.Execute("SELECT * FROM IDENTIFICATIONS;")
Conn1.Execute "DELETE FROM CARS WHERE CAR_ID = 8;"
.
.
.
Conn1.CommitTrans
and whe VB tries to execute the line with the
DELETE sentence the Err object says something like:
"cannot create a new connection because you are in
distributed or manual transaction mode"
However, if I remove the line that executes the sentence
SELECT * FROM IDENTIFICATIONS the error never shows up.
Seems like I can´t execute a SELECT before a DELETE/UPDATE
even if I execute the SELECT in some other table.
I´ve tried changing the IsolationLevel, Mode and Cursor
Location properties and the error always occurs.
Furthermore, I tried putting the SELECT out of the
transaction and the error continues showing up.
Why does this occurs? Is there a way to solve it?
NOTE: I need forcely to execute that SELECT before the DELETE.
Thanks for the time
Roland
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
View 1 Replies View Related