creating a table column that contains long string

I have made a database inside a C# project using project --> Add New Item --> SQL Database.

I have made its tables and define the columns, but I need a data type that allows inserting a long string , as a news paper .

When I use the 'text' data type , the inserted document can't entered as a whole, only subdocument can be entered.

I need also to know how can I create a table that contains Objects .

of any created class.

Thanks,

Aya.

The text type can hold up to 2GB of data. Should be enough no?

Could you show us your insert procedure?

|||

Ok thanks.

I just detect an error in my inserting process.

But I need to know whether I can create a table that contains Objects or not.

And also if I can create a table that contains lists or not.

Thanks,

Aya.

|||

Hi,

You should be aware that the 'text' datatype is being deprecated, please look at varchar(max)/nvarchar(max) as a replacement, which should give you a better user experience in general.

Could you define what do you mean by Objects and Lists? Maybe there are other ways to achieve what you are trying to do. Please elaborate.

Thanks!

-Mat

Creating a range lookup table from a file of millions of rows

I have a file with the item id and the item type. The data looks as follows
:
ItemID ItemType
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 A
9 A
I want to create a lookup tables as follows:
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
Please keep in mind the file I have the ids on is millions of rows. Also,
there are gaps in the ids, (i.e. may jump from 4 to 6 no 5). Gaps are
acceptable as long as they are not too large.
Thanks in advance for any tips you can provide.I'm not sure what you mean by "gaps are acceptable as long as they are
not too large". Apparently no gaps in your sample data anyway. See if
this meets your requirements:
SELECT MIN(itemid), MAX(itemid), itemtype
FROM
(SELECT T1.itemid, T1.itemtype,
MIN(T2.itemid) AS x_itemid
FROM tbl AS T1
LEFT JOIN tbl AS T2
ON T1.itemtype <> T2.itemtype
AND T1.itemid < T2.itemid
GROUP BY T1.itemid, T1.itemtype) AS T
GROUP BY itemtype, x_itemid
If performance is an issue then you could do this for smaller subsets
of rows and then combine the results.
David Portas
SQL Server MVP
--|||David
Can I ask you, why did you join the table?
create table #test
(
itemid int not null primary key,
itemtype char(1) not null
)
insert into #test values (1,'a')
insert into #test values (2,'a')
insert into #test values (3,'a')
insert into #test values (4,'b')
insert into #test values (5,'b')
insert into #test values (6,'c')
insert into #test values (7,'c')
insert into #test values (8,'d')
select min(itemid),max(itemid),itemtype
from #test group by itemtype
What is differ between these queries?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123000386.349876.22890@.g47g2000cwa.googlegroups.com...
> I'm not sure what you mean by "gaps are acceptable as long as they are
> not too large". Apparently no gaps in your sample data anyway. See if
> this meets your requirements:
> SELECT MIN(itemid), MAX(itemid), itemtype
> FROM
> (SELECT T1.itemid, T1.itemtype,
> MIN(T2.itemid) AS x_itemid
> FROM tbl AS T1
> LEFT JOIN tbl AS T2
> ON T1.itemtype <> T2.itemtype
> AND T1.itemid < T2.itemid
> GROUP BY T1.itemid, T1.itemtype) AS T
> GROUP BY itemtype, x_itemid
> If performance is an issue then you could do this for smaller subsets
> of rows and then combine the results.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Uri,
Replace
insert into #test values (8,'d')
with
insert into #test values (8,'a')
and see the difference
With warm regards
Jatinder Singh|||So David's script gave me a wrong output.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123050090.479481.14310@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Replace
> insert into #test values (8,'d')
> with
> insert into #test values (8,'a')
> and see the difference
> With warm regards
> Jatinder Singh
>|||Hi Uri,
It gave correct ouput to me.
Start End ItemType
8 9 A -- (1)
1 3 A -- (2)
4 5 B
6 7 C
The only thing is (1) appears at top which can be easily adjusted by
using ored by clause
With warm regards
Jatinder Singh|||Hi
Should not be 1 for MIN and 9 for MAX for A?
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123056485.365685.300130@.g14g2000cwa.googlegroups.com...
> Hi Uri,
> It gave correct ouput to me.
> Start End ItemType
> 8 9 A -- (1)
> 1 3 A -- (2)
> 4 5 B
> 6 7 C
> The only thing is (1) appears at top which can be easily adjusted by
> using ored by clause
> With warm regards
> Jatinder Singh
>|||Hi Uri,
Again Let us see this
ItemID ItemType
1 A -- *
2 A -- * One Group with ItemType='a' Here
min(itemid)= 1 and max is 3
3 A -- * 1 3 A (One Row of Reuired Result)
4 B -- ^ Another Group with ItemType='b' Here
min(itemid)= 4 and max is 5
5 B -- ^ 4 5 B (Another Row of Reuired Result)
6 C -- ~Another Group with ItemType='c' Here
min(itemid)= 6 and max is 7 7 C -- 6 7 C
(Another Row of Reuired Result)
8 A -- Again 'A' is repeated but there is gap so it
is to be considerd as a
9 A -- Fresh Group
-- 8 9 A
So the resultant output produced by David's Query is Correct
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
I hope it made the author's requirements more clear.
With warm regards
Jatinder Singh|||The difference is that your query only gives one row per ItemType
rather than one row per contiguous sequence on ItemType. I call my
query a "condensed" or "stepped" sequence rather than an aggregation.
The point is that it shows the regions or periods over which a
particuar ItemType applies. In my interpretation that's what BTJ was
asking for.
David Portas
SQL Server MVP
--|||David
Thanks, I got it
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123058956.842173.89690@.o13g2000cwo.googlegroups.com...
> The difference is that your query only gives one row per ItemType
> rather than one row per contiguous sequence on ItemType. I call my
> query a "condensed" or "stepped" sequence rather than an aggregation.
> The point is that it shows the regions or periods over which a
> particuar ItemType applies. In my interpretation that's what BTJ was
> asking for.
> --
> David Portas
> SQL Server MVP
> --
>

Creating a numbered list

I've created a report, and in it I have a list and it displays name and relevant details of each person as one item. So, is there any way I can make this list numbered?

I'd like it to look like

Name: Kirk, James Occupation: Starship Captain Name: Richards, Reed Occupation: Superhero

Nevermind. I finally figured out how to accomplish this with rownumber. I'd tried before, but gotten an error because I didn't use quotation marks. Sorry for the bother.

Created database in Visual Studio 2005; How do I add it to SQL Server?

I have created a database in Visual Studio 2005 by adding a new "SQL Database" item to my project, and then designing the tables with Server Explorer.

How can I add this database to my local instance of SQL Server Express, and to my SQL Server 2000 database engine?

You will find that the database has probably been created as a User Instance. The easy way to check this is to have a look at the app.config file, look for an Attach file string in the connection String. To add it to the SQL Express Engine you will need to attach it, this can be done through the command line or through the SQL Management Studio for Express (Or Full version). For information on the command line syntax for attaching the database I would have a look at the help or books online for Attaching Databases.

In regards to adding it to the SQL 2000 engine you might find that you will have to change the database structure as several functions will not work with the old engine. And in summary the only way to add it would be to strip out the DDL (Data Definition Language) scripts as well as the scripts to add the data and recreate the database.

|||

Thank you for the response.

I'd like to ask a more general question. What is the best way to define databases within Visual Studio 2005, so that they will be part of my C# project and Source Safe version control? I guess what I'm looking for is sort of a "best practice" approach to developing my program and the associated databases together.

I will appreciate any information or MSDN references you can provide on this topic.

|||

I do not know of any real best practices, But I do not stor e the Database files in Source Safe I use a seperate database project and only store the Data Definition Language for the database (SQL Scripts). I can then add the project to the solution and then to the VSS Project. I have had Problems in the past adding the MDF Files used by SQl Express in VSS so have chossen to follow the above procedures.

Hope this helps.

Created database in Visual Studio 2005; How do I add it to SQL Server?

I have created a database in Visual Studio 2005 by adding a new "SQL Database" item to my project, and then designing the tables with Server Explorer.

How can I add this database to my local instance of SQL Server Express, and to my SQL Server 2000 database engine?

You will find that the database has probably been created as a User Instance. The easy way to check this is to have a look at the app.config file, look for an Attach file string in the connection String. To add it to the SQL Express Engine you will need to attach it, this can be done through the command line or through the SQL Management Studio for Express (Or Full version). For information on the command line syntax for attaching the database I would have a look at the help or books online for Attaching Databases.

In regards to adding it to the SQL 2000 engine you might find that you will have to change the database structure as several functions will not work with the old engine. And in summary the only way to add it would be to strip out the DDL (Data Definition Language) scripts as well as the scripts to add the data and recreate the database.

|||

Thank you for the response.

I'd like to ask a more general question. What is the best way to define databases within Visual Studio 2005, so that they will be part of my C# project and Source Safe version control? I guess what I'm looking for is sort of a "best practice" approach to developing my program and the associated databases together.

I will appreciate any information or MSDN references you can provide on this topic.

|||

I do not know of any real best practices, But I do not stor e the Database files in Source Safe I use a seperate database project and only store the Data Definition Language for the database (SQL Scripts). I can then add the project to the solution and then to the VSS Project. I have had Problems in the past adding the MDF Files used by SQl Express in VSS so have chossen to follow the above procedures.

Hope this helps.

Create View with 2 Select Queries

Hi,

I don't even know if this is possible, but here goes.

I have 2 select queries.

1) SELECT w.Item AS [WareHouse Item], w.Quantity AS [WareHouse Quantity],

w.RestockLevel AS [WareHouse Restock], w.ReorderPoint AS [WareHouse ReorderPoint],

SUM(Wp.QuantityOrdered - Wp.QuantityReceivedToDate) AS WareHouseOnOrder

FROM WH.dbo.PurchaseOrderEntry AS wp INNER JOIN

WH.dbo.Item AS w ON wp.ItemID = w.ID INNER JOIN

WH.dbo.PurchaseOrder AS Wpo ON Wp.PurchaseOrderID = WPO.ID

Where (wPO.POType < 2) AND (wPO.Status = '0')

Group By w.Item, w.Quantity, w.RestockLevel, w.ReorderPoint

Order By w.Item

My socond query is the same, but from a different database on the same Server. If I join the 2 queries I get duplicate Items in my results, because of the 2 databases.

My question is:

Can I run both queries to create one View so that I can create my Crystal report from that?

Any better Ideas would be appretiated!

Thanks

what are you joining the two queries on?|||

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

|||

Arnie Rowland wrote:

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

or "UNION ALL" if you want the duplicates.

|||

Thanks Arnie and Michael

That was exactly what I needed.

Thanks!!!

|||

Hi,

I am not sure if view is your requirement or not. Aa far as the data from query to a report is concerned you can you use a temp table in stored procedure to do that or use UNION to combine these results.

Thanks,

Paraclete

|||

That is also VERY true.

After seeing that I can actually get all the information using the union that Arnie suggested I will probably create a stored procedure to run my select Statement.

Thanks Again!!

Create verticle grouping instead of horizontal

Hello,
Generally when using the Report Wizard with MS SQL 2005 it wants to present
the data horizontally, like this:
Item 1 Item2 Item3 Item4 Item5
Value1a Value2a Value3a Value4a Value5a
Value1b Value2b Value3b Value4b Value5b
... and so forth.
Instead of this, I need my report to present the data vertically, like this:
Item1 Value1a Value1b
Item2 Value2a Value2b
Item3 Value3a Value3b
Item4 Value4a Value4b
Item5 Value5a Value5b
I'm actually limiting this query to the Top 4 so it will only show columns
of data. Problem is, I have no idea how change the orientation of the
table. Can someone give me
some guidance on how to accomplish this? I'm using VB.Net 2003 to create
the report.
Thanks,
AlexOn Jul 26, 10:07 am, "Alex" <sama...@.gmail.com> wrote:
> Hello,
> Generally when using the Report Wizard with MS SQL 2005 it wants to present
> the data horizontally, like this:
> Item 1 Item2 Item3 Item4 Item5
> Value1a Value2a Value3a Value4a Value5a
> Value1b Value2b Value3b Value4b Value5b
> ... and so forth.
> Instead of this, I need my report to present the data vertically, like this:
> Item1 Value1a Value1b
> Item2 Value2a Value2b
> Item3 Value3a Value3b
> Item4 Value4a Value4b
> Item5 Value5a Value5b
> I'm actually limiting this query to the Top 4 so it will only show columns
> of data. Problem is, I have no idea how change the orientation of the
> table. Can someone give me
> some guidance on how to accomplish this? I'm using VB.Net 2003 to create
> the report.
> Thanks,
> Alex
You will want to look into using either a matrix report or pivoting
the data in the stored procedure/query that is souring the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant