Syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Example:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol
then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUES
is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL
, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES
or not.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable
and do not Specify SomeCol
's value, then it will Default to 0
.
If you insert a Record and Specify SomeCol
's value as NULL
(and your column allows nulls),
then the Default-Constraint will not be used and NULL
will be inserted as the Value.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
Best Answer
To the best of my experience, exporting to excel2007 is not built into SSRS2008, you need to get an external component for that. Currently looking into what is available on the market, i'll get back to you with what i find.
Edit:
Ok had a look at both aspose.cells and OfficeWriter by SoftArtisans. Both claim to offer .xlsx-exporting capabilities for SSRS, but in both cases this is a partial truth at best.
Both work by having you recreate your report in Excel using their respective add-ons, and then pasting their own markup into your RDL-file. This also has the effect that if you are making an excel-exportable report in either tool, you won't be able to view or export it in anything else from SSRS. Both have the ability to open an existing report and access their datasets from there, which is a major advantage over trying to get MSQuery to work for you.
Aspose suffers from various issues with permissions on the server, where you need to grant it full trust (not everyone would want that). I also had a major hassle getting it installed properly.
OfficeWriter has some issues with shared datasources, where you generally have to go in and set them manually after you've published your report. It also seems to choke on VS2008 RDLs, if you want to use a dataset from a VS2008 report, you have to make a new report in VS2005 with your dataset, and use that as a basis for your excel-built report.
Personally I don't care much for either. But overall Officewriter does seem like it comes out ahead. Next stop is figuring out if it has built-in support for matrices, or that is something we would have to program in VB to get.