Difference between max length and length in DataType Schema

Using OleDb I can retrieve the Database Native DataTypes Schema with LoadSchema("DataTypes")

In the CreateParameters column some variable fields such as varchar have CreateParametera of 'max length' while nchar has a CreateParameters of 'length'.

Can someone differentiate between 'length' and 'max length' for me?

TVM

BOL: "nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying."

so max length for nchar is 4,000.

for varchar(max) max length is 2^31-1 bytes depending on platform.

Thanks.

Difference between dates in different rows...

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
…
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
…

However, this gives me too much data:
…
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
…

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I’d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

SQL Server 2005:

SELECT a.CustomerID, a.OrderDate as Highdate, b.OrderDate as LowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs

FROM (SELECT CustomerID, OrderDate, ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) as RowNum FROM dbo.Orders) a

INNER JOIN (SELECT CustomerID, OrderDate, (ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) -1)as RowNumMinusOne

FROM dbo.Orders) b ON a.CustomerID=b.CustomerId AND a.RowNum=b.RownumMinusOne

|||

SQL Server 2000:

SELECT a.CustomerID, a.OrderDate as HighDate, b.OrderDate as lowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs FROM (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T.CustomerID and OrderDate < T.OrderDate ) + 1 as Rank1

from Orders as T ) a INNER JOIN (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T1.CustomerID and OrderDate < T1.OrderDate ) as Rank2

from Orders as T1 ) b ON b.CustomerID=a.CustomerID and a.Rank1=b.Rank2

ORDER BY a.CustomerID, a.OrderDate

|||

You're an absolute star! Just what I was after. My head was starting to spin trying to figure this one out.

Thank you for your help!

Regards,

Stephen.

sql

Diff Datatypes used doubt int and bigint ........SQL SERVER 2005......Any useful links ?

Hello Frdz,

I have doubt regarding the datatypes fields used in SQL SERVER 2005.

The value for bigint Int64 is 18

The value of int Int32 is 9/10

Now,if in int i write : 1234567890 (accepted)

This gives error : 9874565656 (not accepted........why is it so ? )

Why is it so ??

I want to know the perfect size of all the datatypes used in SQLSERVER 2005.

There are also smallint,tinyint....

What's the main difference with all of them ??

Can anyone provide me the nice links which can explain me what m i asking in this post...

Please help me...I want to know all the datatypes used differences...

you cannot store 9874565656 in an int as it exceeds the max value for an int which is: 2,147,483,647

http://msdn2.microsoft.com/en-us/library/ms187745(SQL.90).aspx

|||

thanxs...i think it's helpful link..

I want to also know that in SQL SERVER 2005 can we assign or fix manually the values upto the limit...like,

Id1 int - 4

Id2 bigint - 10

like in varchar we can do

Name Varchar(20) if we take varchar(50) as datatype...

Hope u understand what i ask...this questions are not solved in my mind...

Please help me...

Thanxs again....

|||

varchar is a variable length datatype and you are allowed to set its length

you cannot do that with the integer datatypes.

|||

If you really intend to limit the value <= 4 digits ( = 9999) you can create a constraing on the column to make sure the value <= 9999.

|||

You can also create your own datatype that is based on an integer value type that can store all your possible values and has a constraint.

This may help:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

More complex datatype needs can be done via a CLR UDT, but I'm not sure how well they perform. Like:

http://www.devx.com/dotnet/Article/22644

|||

thanxs all of...

I think it's better to make a constraint......

Nice answers to clear my doubt...

Diagram to Image

Hello

I have created a Database Diagram for the tables I have in the database and set the relationships of the tables with the fields.

Now I want to export this diagram to the Image format (jpeg, gif or any other one), so i can
add this image in my documentation (ms-word-xp doc) of the project..

I have looked into the options, but i did'nt find any way to export it. So i thought something went wrong while installation, but was made sure that was fine.

Please let me know what is the way to export that diagram to image. (its a exigency)

hope to your all valuable response as soon.

thanks, (in advance) and for your time.Right-click in your diagram, and copy it to the clipboard. You can then paste it into your doc.|||before you give any suggestions, please make sure that your words holds true.

copying diagram from SQL EM to office xp is not supported.

Anyway, thanks for investing your precious time in your one line.|||

try copying into MSPaint, and then using that image in your Doc.

BTW, the copy to clipboard method works fine in Office 2003, for anyone wondering.

|||Andrew Badera, thanks for your time again.

Just give a try of copying from SQL EM to MSPaint, IT IS NOT SUPPORTED.
I dont know how you have checked.

and thanks for suggestion on telling me try OFFICE 2003 (which I am not going to do).
For this simple thing, I cant change the office version i am using, which is really a waste of time.

Anyway, my problem solved long before.
|||

Beau Peep wrote:

Andrew Badera, thanks for your time again.

Just give a try of copying from SQL EM to MSPaint, IT IS NOT SUPPORTED.
I dont know how you have checked.

sorry, should have been more specific I guess: get a screen capture. copy that into mspaint.

|||

I realize this may not help the original poster, but for those that run across this thread by chance ...

My work around is to print to the Microsoft Office Document Image Writer. The file can be saved as either .mdi or .tiff.

I work remotely and cannot connect to my remote development environment with my printer (never figured that one out) ... anyway, because of this, I am not able to print my diagrams directly from EM. After printing to the Image Writer, I simply move the file to my local machine, where it will automatically open in Microsoft Office Document Imaging. From there I can print it to my local printer.

Of course, this option depends on whether or not Microsoft Office Document Image Writer is available on your system. I work with Windows XP and Windows Server 2003, and can confirm that the Document Image Writer is available.

Thanks

Jen

Diagram to Image

Hello
I have created a Database Diagram for the tables I have in the database and
set the relationships of the tables with the fields.
Now I want to export this diagram to the Image format (jpeg, gif or any
other one), so i can add this image in my documentation (ms-word-xp doc) of
the project documentation.
I have looked into the options, but i did'nt find any way to export it. So i
thought something went wrong while installation, but was made sure that was
fine.
Please let me know what is the way to export that diagram to image. (its a
exigency)
hope to see your all valuable response as soon.
thanks, (in advance) and for your time.
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();
Beau,
If you are using SQL Server 2005, and generated the diagram in
Management Studio:
1. Right-click in the diagram pane
2. Choose "Copy Diagram to Clipboard"
3. Paste in the destination program.
If you are using 2000 and Enterprise Manager, one option
is to print to the Microsoft Office Document Image Writer,
and choose TIFF output at a low resolution. Other options are
to capture the screen with Alt-PrtSc or a utility (like SnagIt) that can
capture a scrolling window's contents.
Steve Kass
Drew University
Beau Peep wrote:

>Hello
>I have created a Database Diagram for the tables I have in the database and
>set the relationships of the tables with the fields.
>Now I want to export this diagram to the Image format (jpeg, gif or any
>other one), so i can add this image in my documentation (ms-word-xp doc) of
>the project documentation.
>I have looked into the options, but i did'nt find any way to export it. So i
>thought something went wrong while installation, but was made sure that was
>fine.
>Please let me know what is the way to export that diagram to image. (its a
>exigency)
>hope to see your all valuable response as soon.
>thanks, (in advance) and for your time.
>
|||Hello Steve Kass,
Thanks for replying.
Actually, I am using MS-SQL 2000 Enterprises Manager on Windows 2000
Advanced Server. I looked in to your points, but I did'nt get any of such
option that you have mentioned ("Microsoft Office Image Writer"). Could you
please
guide me with the proper steps to achive this and How to configure this "TIFF"
stuff. (I have know idea).
Hope to see your response as soon.
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();
"Steve Kass" wrote:

> Beau,
> If you are using SQL Server 2005, and generated the diagram in
> Management Studio:
> 1. Right-click in the diagram pane
> 2. Choose "Copy Diagram to Clipboard"
> 3. Paste in the destination program.
> If you are using 2000 and Enterprise Manager, one option
> is to print to the Microsoft Office Document Image Writer,
> and choose TIFF output at a low resolution. Other options are
> to capture the screen with Alt-PrtSc or a utility (like SnagIt) that can
> capture a scrolling window's contents.
> Steve Kass
> Drew University
> Beau Peep wrote:
>
|||The microsoft office document image writer is part of Office, to get
this option
Office needs to be installed on the machine (think Office 2003 is
required).
Alternatively use Adobe PDF writer or the freeware option Cute PDF
(http://www.CutePDF.com)
Regards,
David
|||Hello David,
Thanks for the suggestion.
I downloaded that just checked. Its really great creating things in PDF.
But what I was expecting was to print this Database diagram completly in one
page. That CutePDF will prints all these images in page wise. ITs like
breaking the
complete image to page wise. I tried setting the page size but none of them
helped.
Is there any idea that I can get this diagram in complete one page?
Hope to see your response as soon. Thanks anyway. Its really a great tool.
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();
"David" wrote:

> The microsoft office document image writer is part of Office, to get
> this option
> Office needs to be installed on the machine (think Office 2003 is
> required).
> Alternatively use Adobe PDF writer or the freeware option Cute PDF
> (http://www.CutePDF.com)
> Regards,
> David
>
|||Hi, try to change the paper size in the print dialogue to A3 (the
largest available),
then change width and height to the maximum 91 cm. This is the largest
page
Ive found, then print it to the PDF and you get it all on one page
(hopefully).
Cheers,
David
|||Helllo David,
I tried this setting to A3 but i did'nt find any option for setting the
width and height
to 91cm. I just tried the setting A3 and printed to see how it comes. Dont
ASK.
Half of the Diagram was chopped off.
I guess, only microsoft should provide some functionality to export that
diagram to
any image format.
Well, any suggestions left, please let me know. if possible guide me how to
set the
width and height of the page?
Anyway thanks for the help and your ideas, hope to see your response as soon.
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();
"David" wrote:

> Hi, try to change the paper size in the print dialogue to A3 (the
> largest available),
> then change width and height to the maximum 91 cm. This is the largest
> page
> Ive found, then print it to the PDF and you get it all on one page
> (hopefully).
> Cheers,
> David
>

Diagram to Image

Hello

I have created a Database Diagram for the tables I have in the database and set the relationships of the tables with the fields.

Now I want to export this diagram to the Image format (jpeg, gif or any other one), so i can
add this image in my documentation (ms-word-xp doc) of the project..

I have looked into the options, but i did'nt find any way to export it. So i thought something went wrong while installation, but was made sure that was fine.

Please let me know what is the way to export that diagram to image. (its a exigency)

hope to your all valuable response as soon.

thanks, (in advance) and for your time.Right-click in your diagram, and copy it to the clipboard. You can then paste it into your doc.|||before you give any suggestions, please make sure that your words holds true.

copying diagram from SQL EM to office xp is not supported.

Anyway, thanks for investing your precious time in your one line.|||

try copying into MSPaint, and then using that image in your Doc.

BTW, the copy to clipboard method works fine in Office 2003, for anyone wondering.

|||Andrew Badera, thanks for your time again.

Just give a try of copying from SQL EM to MSPaint, IT IS NOT SUPPORTED.
I dont know how you have checked.

and thanks for suggestion on telling me try OFFICE 2003 (which I am not going to do).
For this simple thing, I cant change the office version i am using, which is really a waste of time.

Anyway, my problem solved long before.
|||

Beau Peep wrote:

Andrew Badera, thanks for your time again.

Just give a try of copying from SQL EM to MSPaint, IT IS NOT SUPPORTED.
I dont know how you have checked.

sorry, should have been more specific I guess: get a screen capture. copy that into mspaint.

|||

I realize this may not help the original poster, but for those that run across this thread by chance ...

My work around is to print to the Microsoft Office Document Image Writer. The file can be saved as either .mdi or .tiff.

I work remotely and cannot connect to my remote development environment with my printer (never figured that one out) ... anyway, because of this, I am not able to print my diagrams directly from EM. After printing to the Image Writer, I simply move the file to my local machine, where it will automatically open in Microsoft Office Document Imaging. From there I can print it to my local printer.

Of course, this option depends on whether or not Microsoft Office Document Image Writer is available on your system. I work with Windows XP and Windows Server 2003, and can confirm that the Document Image Writer is available.

Thanks

Jen

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy

Determining Report Format from expression

I would like to be able to check format to be running as, so I can make
some columns and fields invisible if run as excel or CSV etc.
Is this possible? I dont see a way at hitting render format from Report!
object.
Thanks.On May 15, 11:55 am, Weston Weems <wweemsBLH-BLAH@.G_NOSPAM_MAIL.com>
wrote:
> I would like to be able to check format to be running as, so I can make
> some columns and fields invisible if run as excel or CSV etc.
> Is this possible? I dont see a way at hitting render format from Report!
> object.
> Thanks.
As far as I know, this functionality is not available. That said, you
can create a custom application that controls the export options and
the fields/columns that are displayed. Sorry that I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Actually you might be onto something with that. I've already managed to
whip up my own dynamic report viewer in asp.net and it works quite
well... I will just inject a hidden parameter called format... then i
can use the format in the queries etc =)
That'll work great for me.
EMartinez wrote:
> On May 15, 11:55 am, Weston Weems <wweemsBLH-BLAH@.G_NOSPAM_MAIL.com>
> wrote:
>> I would like to be able to check format to be running as, so I can make
>> some columns and fields invisible if run as excel or CSV etc.
>> Is this possible? I dont see a way at hitting render format from Report!
>> object.
>> Thanks.
>
> As far as I know, this functionality is not available. That said, you
> can create a custom application that controls the export options and
> the fields/columns that are displayed. Sorry that I could not be of
> greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>