|
hi
SQL Fields are not like Storing data in a text file. So if you probably tried to add a dimension of 20000 and get an error like this
The size (20000) given to the column 'name' exceeds the maximum allowed for any data type (8000).
You should know that the Limit is "8000", and i dont understand why would you store such a large string in a Database.
So its not Possible with SQL
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hi
ya i know the error but
just i am conforming is there any other way is there to store
like acces (memo field)
thank you
Kirthi
|
|
|
|
|
There is. Try the ntext data type.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.
BUT
Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi Ashfield
Wow is it Possible?
can i see an example code, because ntext even if you try 10000,it will not work, mybe am not doing it right see
create table #test
(
P_ID int null,
Pname ntext(10000) null
)
Msg 131, Level 15, State 2, Line 5<br />
The size (10000) given to the column 'Pname' exceeds the maximum allowed for any data type (8000).
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
If you read up, your syntax is wrong
create table #b(a ntext)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob,
Do you have more information on the future non-use of those fields you described? Thanks.
Ryan
|
|
|
|
|
Check out BOL
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I need to create an application: upon user login, check the user type and decide the database that application will use for that user. Here is what I have:
1: Master database: stores user information and application configuration data, such as which db to use and connection string
2: Database I on server 1: contains all the application data for user group I.
3: Database II on server 2: contains all the application data for user group II.
4: Database I backup on server 2: as backup for user group 1 in case server 1 fails.
5: Database II backup on server 1: as backup for user group 2 in case server 2 fails.
6: Through log shipping between server 1 and server 2 to keep DB I and I and their backup up-to-date.
7: The web site is running on web farm. The session state management is using SQL server session state management, which is on the same server as Master DB.
My questions are:
1: How do I determine if server 1 or server 2 is down and it's time for application switching to use the DB backup on the other server.
2: What to do if the master DB server is down?
3: Is there a better approach than the above design to achieve the same goals: using different DB for different users, and achieving fast recovery by using the backup server?
I would really appreciate any suggestion and help!
Thanks in advance!
|
|
|
|
|
i need to write some lines to a txt when running loops in a stored procedure, is there any syntax facilitating this kind of function? or I have to use some other ways? if you have done that before, please help me, thanks..........
|
|
|
|
|
Look in BOL for xp_cmdshell.
Someone's gotta be the last to know, but why is it always me?
|
|
|
|
|
It's going to slow down your loop terribly if you keep writing to a file. You'd do better to store the logs in a table and write it to a file in one shot in the end. For that use xp_cmdshell as suggested in the post above or use a CLR procedure if you're using SQL Server 2005.
|
|
|
|
|
They Guys are Correct, i think its not a Good idea to write to a file directly from your Procedure. You can just create an SQL table and dump the logs there ,and later if you want you can transform that table into a txtfile.About using xp_cmdshell, you have to consider the security issues. if they are not a DBA in your Company i think your DBA will advise you not to use it.
Hope this Helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thanks.....
a data file like this:
124356 1
376622 1
736197 1
............... .
the first 7 characters is one filed and the 8th character is the other filed. Can anyone show me the bulk insert syntax for this transaction, many thanks.....
Assumed destination and from as below:
BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH
|
|
|
|
|
|
hi Mika,
so I have to first create a table for this txt.file, and then I should use bcp command to generate a formatfile, right?
there is only one line like this example bcp command:
bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T
how can I define that my first field in data file is 8 characters?
can you be more detailed, thanks very much..
|
|
|
|
|
Yes, the table must exist. Try something like this:
CREATE TABLE BulkTest (
Column1 bigint,
Column2 bigint)
Then create the format file using notepad and save it:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="7"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLBIGINT"/>
</ROW>
</BCPFORMAT>
And then bulk insert the data:
BULK INSERT BulkTest FROM 'f:\orders\lineitem.tbl' WITH (FORMATFILE='f:\orders\BulkInsertFormat.txt')
Mika
|
|
|
|
|
hi all,
i write the following code for extract the data from a database, data also retrieved but there is a problem ...code is here
OracleConnection dataConnection = new OracleConnection();
ArrayList objList = new ArrayList();
try
{
dataConnection.ConnectionString = "Data Source=orc;Persist Security Info=True;User ID=scott;Password=tiger;Unicode=True";
dataConnection.Open();
OracleCommand datacommand = new OracleCommand("SELECT * FROM DEPT", dataConnection);
OracleDataReader datareader = datacommand.ExecuteReader();
Dept d = new Dept();
while (datareader.Read())
{
d.DNAME = datareader["DNAME"].ToString();
d.DEPTNO = datareader["DEPTNO"].ToString();
d.LOC = datareader["LOC"].ToString();
objList.Add(d);
}
datareader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataConnection.Close();
}
try
{
cmbDname.DataSource = objList;
cmbDname.DisplayMember = "DNAME";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
but i amazed ......all filled data of combobox is same i.e DNAME
like : OPERATIONS (which is last row of DEPT table)
because
objList store only last data (OPERATIONS)........
but when i replace
objList.Add(d); with
objList.Add(d.DNAME); then objList store different DNAME..........but this time objList has only one colum <dname>....
my problem is that how can i store whole row in objList 
|
|
|
|
|
|
i am trying to develop a school fees management system, can any1 help me identifying the database fields required for a school management system. thank u.
|
|
|
|
|
If you're trying to get your homework done by somebody else, nobody here will help you. The question you've asked is so trivial that google will throw up a million results for it. If you'd gone to school you would know most of the required fields by now.
Make an attempt yourself, explain what you've done, and then ask here if you still face any hurdles. We'll be more than willing to help if you've a genuine problem in your hands.
|
|
|
|
|
SimulationofSai wrote: Make an attempt yourself, explain what you've done, and then ask here if you still face any hurdles. We'll be more than willing to help if you've a genuine problem in your hands.
I second that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Why would you think someone will help you. We dont work for you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
I have an sql query that retrieves data for some ID where this ID has more than one data related to it.
Example:
ID Symbol
01 A
01 B
01 C
of course the primary key is (ID, Symbol).
when you select the ID and Symbol, it retieves the previous three rows.
I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following:
ID Symbol
01 A and B and C
Is this possible???
Kind Regards
OBarahmeh
|
|
|
|
|
You could do this using a cursor[^] to loop through the rows and build up the string.
I wouldn't advise it for large resultsets as you could end up with performance issues.
|
|
|
|
|
Yes, it is possible 
CREATE TABLE #T (ID varchar(2), Symbol varchar(1));
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'
--
SELECT
ID,
STUFF(
<code>(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))</code>
, 1
, 5
, ''
) AS Symbols
FROM #T AS t
GROUP BY ID
--
DROP TABLE #T
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
