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 stored procedure to insert data?

Hello all,

I am having a lot of trouble with stored procedures. Could anyone help me out.

I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today.

I can select the values, and I can insert the values.

But how do I store the values so that i can pass the results of the select to the insert?

Im also having a lot of trouble with storing date values.

ANy help would be greatly appreciated.

Regards,

Padraic Hickey

if exists (select * from dbo.sysobjects where id = object_id(N'[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table2]
SELECT *
INTO table2
FROM table1
WHERE convert(varchar,table1.meetingdate,112)=convert(varchar,@.mydate,112)
|||

Cheers Motley,

I was having a lot of trouble with that one.

If ever i can return the favour.

Padraic

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a chart from only one row of data

I am trying to generate a chart from only one row of data that contains the
twelve fields that represent the data-points for the chart ( the chart will
have 4 sets of two columns and a 4 point line graph for a total of twelve
points). I know which field is which datapoint but the chart object will not
let me define individual data-points. It expects to create a series from
multiple rows of data. Grouping and Series fields also rely on multiple rows
of data. I can put the fields into a table on the report, but cannot
generate a chart off of the table. I am not a programmer, meaning that I
only know how to manipulate properties - code samples will not help. I would
appreciate any help that could be offered to a layman report designerIt may be possible to write a query such that the columns in the table
appear as rows. For example, if A, B and C are columns of table FOO then
SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
return 3 rows even if the table has only 1 row.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>I am trying to generate a chart from only one row of data that contains the
> twelve fields that represent the data-points for the chart ( the chart
> will
> have 4 sets of two columns and a 4 point line graph for a total of twelve
> points). I know which field is which datapoint but the chart object will
> not
> let me define individual data-points. It expects to create a series from
> multiple rows of data. Grouping and Series fields also rely on multiple
> rows
> of data. I can put the fields into a table on the report, but cannot
> generate a chart off of the table. I am not a programmer, meaning that I
> only know how to manipulate properties - code samples will not help. I
> would
> appreciate any help that could be offered to a layman report designer|||A minor clarification: You'd have to convert all columns in the SELECTs in
the UNION to same datatype using Convert() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajeev Karunakaran [MSFT]" <rajeevkarunakaran@.online.microsoft.com> wrote
in message news:eDCdqlthEHA.596@.TK2MSFTNGP11.phx.gbl...
> It may be possible to write a query such that the columns in the table
> appear as rows. For example, if A, B and C are columns of table FOO then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that contains
the
> > twelve fields that represent the data-points for the chart ( the chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart object
will
> > not
> > let me define individual data-points. It expects to create a series
from
> > multiple rows of data. Grouping and Series fields also rely on multiple
> > rows
> > of data. I can put the fields into a table on the report, but cannot
> > generate a chart off of the table. I am not a programmer, meaning that
I
> > only know how to manipulate properties - code samples will not help. I
> > would
> > appreciate any help that could be offered to a layman report designer
>|||I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
I would like each one to be a separate bar on the graph. The stored
Procedure will only ever return one row.
How can you do this using Reporting Services? What should I put in the
category column?
Any help will be appreciated,
Rajeev Karunakaran [MSFT] wrote:
> It may be possible to write a query such that the columns in the
table
> appear as rows. For example, if A, B and C are columns of table FOO
then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that
contains the
> > twelve fields that represent the data-points for the chart ( the
chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart
object will
> > not
> > let me define individual data-points. It expects to create a
series from
> > multiple rows of data. Grouping and Series fields also rely on
multiple
> > rows
> > of data. I can put the fields into a table on the report, but
cannot
> > generate a chart off of the table. I am not a programmer, meaning
that I
> > only know how to manipulate properties - code samples will not
help. I
> > would
> > appreciate any help that could be offered to a layman report
designer|||Changing the stored procedure is not an option.|||Just create 3 values - one for each column, no categories or series group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hovercraft2x@.gmail.com> wrote in message
news:1109017211.242947.155410@.c13g2000cwb.googlegroups.com...
>I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
> I would like each one to be a separate bar on the graph. The stored
> Procedure will only ever return one row.
> How can you do this using Reporting Services? What should I put in the
> category column?
> Any help will be appreciated,
> Rajeev Karunakaran [MSFT] wrote:
>> It may be possible to write a query such that the columns in the
> table
>> appear as rows. For example, if A, B and C are columns of table FOO
> then
>> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
> will
>> return 3 rows even if the table has only 1 row.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
>> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>> >I am trying to generate a chart from only one row of data that
> contains the
>> > twelve fields that represent the data-points for the chart ( the
> chart
>> > will
>> > have 4 sets of two columns and a 4 point line graph for a total of
> twelve
>> > points). I know which field is which datapoint but the chart
> object will
>> > not
>> > let me define individual data-points. It expects to create a
> series from
>> > multiple rows of data. Grouping and Series fields also rely on
> multiple
>> > rows
>> > of data. I can put the fields into a table on the report, but
> cannot
>> > generate a chart off of the table. I am not a programmer, meaning
> that I
>> > only know how to manipulate properties - code samples will not
> help. I
>> > would
>> > appreciate any help that could be offered to a layman report
> designer
>

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>