Re: SQL table field - Database Discussion Boards

prithaa31-Jul-08 3:12

31-Jul-08 3:12

Hello,

Before posing question even I have googled on pivot query,pivot sql query but none of the sites explained the synttax abut making a new table from a pivot query .And that's the reason I asked the question .I know the pivot query but how make a table of the results from the query.
Using INTO for the pivot query didn't work.

The following query works but I want to make a table

TRANSFORM sum(cCapacity) AS SumOfCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract into y;

Thanks and sorry.

Pritha

Re: creating a pivot table

TheFM23431-Jul-08 7:44

TheFM234

31-Jul-08 7:44

Have you tried sub-querying the pivot?

Select *
Into Table
From
(
TRANSFORM sum(cCapacity) AS SumOfCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract into y
) t

Re: creating a pivot table

prithaa31-Jul-08 18:51

prithaa

31-Jul-08 18:51

hello,

thanks for your reply.Itried your sub-query but I get the following error

Cannot use the cross-tab of a non-fixed column as a sub-query

Pritha

How to fetch data from an Excel sheet,for a particular row.?

guriqbal8730-Jul-08 20:39

guriqbal87

30-Jul-08 20:39

Hi,
I am working on application,which requires fetching data from an Excel sheet,for a particular row.
Please suggest me hw I can do it.

Thanks in advance

G Singh

Cross Post!!

ChandraRam30-Jul-08 23:33

ChandraRam

30-Jul-08 23:33

Don't cross post

SQL table field

kirthikirthi30-Jul-08 19:51

kirthikirthi

30-Jul-08 19:51

Hi all

I am using SQL 2000 ver.

How can i store large text data in my table field .

What are the field propertys i have to use

Thank you

Kirthi

modified on Thursday, July 31, 2008 2:04 AM

Re: SQL table field

Vimalsoft(Pty) Ltd30-Jul-08 20:53

Vimalsoft(Pty) Ltd

30-Jul-08 20:53

e.g
Use

Mystring VarChar(200)

You can add as much string as you can and can increase a dimension.

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

Re: SQL table field

kirthikirthi30-Jul-08 21:14

kirthikirthi

30-Jul-08 21:14

Hi thank you for reply

but i want to put the table field length greater that 10,000

because my data is more to store

thank you

Kirthi

Re: SQL table field

Vimalsoft(Pty) Ltd30-Jul-08 21:36

Vimalsoft(Pty) Ltd

30-Jul-08 21:36

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

Re: SQL table field

kirthikirthi30-Jul-08 21:44

kirthikirthi

30-Jul-08 21:44

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

Re: SQL table field

Ashfield30-Jul-08 22:26

Ashfield

30-Jul-08 22:26

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

Re: SQL table field

Vimalsoft(Pty) Ltd31-Jul-08 4:43

Vimalsoft(Pty) Ltd

31-Jul-08 4:43

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

Re: SQL table field

Ashfield31-Jul-08 8:49

Ashfield

31-Jul-08 8:49

If you read up, your syntax is wrong

create table #b(a ntext)

Bob
Ashfield Consultants Ltd

Re: SQL table field

RyanEK31-Jul-08 18:23

RyanEK

31-Jul-08 18:23

Hi Bob,
Do you have more information on the future non-use of those fields you described? Thanks.

Ryan

Re: SQL table field

Ashfield1-Aug-08 9:52

Ashfield

1-Aug-08 9:52

Check out BOL

Bob
Ashfield Consultants Ltd

Dynamic DB Connection based on user type

vicky45730-Jul-08 19:28

vicky457

30-Jul-08 19:28

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!

in a stored procedure, can I directly write some lines to a txt file?

alexyxj30-Jul-08 11:56

alexyxj

30-Jul-08 11:56

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..........

Re: in a stored procedure, can I directly write some lines to a txt file?

goodideadave30-Jul-08 13:10

goodideadave

30-Jul-08 13:10

Look in BOL for xp_cmdshell.

Someone's gotta be the last to know, but why is it always me?

Re: in a stored procedure, can I directly write some lines to a txt file?

SimulationofSai30-Jul-08 19:24

SimulationofSai

30-Jul-08 19:24

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.

Re: in a stored procedure, can I directly write some lines to a txt file?

Vimalsoft(Pty) Ltd30-Jul-08 20:59

Vimalsoft(Pty) Ltd

30-Jul-08 20:59

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

how to use bulk insert to import a data file with this format? Please help

alexyxj30-Jul-08 9:09

alexyxj

30-Jul-08 9:09

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

Re: how to use bulk insert to import a data file with this format? Please help

Wendelius30-Jul-08 9:48

Wendelius

30-Jul-08 9:48

Hi,

Define a format file and specify it using FORMATFILE argument for BULK INSERT

For more info on format files for both bcp and bulk insert, see http://msdn.microsoft.com/en-us/library/ms178129.aspx[^]

Hope this helps,

Mika

Re: how to use bulk insert to import a data file with this format? Please help

alexyxj30-Jul-08 11:23

alexyxj

30-Jul-08 11:23

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..

Re: how to use bulk insert to import a data file with this format? Please help

Wendelius30-Jul-08 11:55

Wendelius

30-Jul-08 11:55

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

how to ArrayList store whole records?

zeeShan anSari30-Jul-08 6:32

zeeShan anSari

30-Jul-08 6:32

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 Confused | :confused:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.