Defining dataset query in vb.net

Hi all,
there are lots of objects about defining the report data source ,data set,
query etc.
but is there any examples of defining them and assign to a report?
i feel confuse cos i dun quite know how to use them.(i want to construct the
Query in web form first then pass to the report.)
thanks in advance!hi all,
may be i should post my questions clearly,
i know how to assign a datasource to the report using setreportdatasource(),
but|||BUT
HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
HOW TO ASSIGN A QUERY TO A DATASET?
i dun know if my concept is corroect or not, please let me know if i have
said something wrong
thanks in advance.|||Two points. First, the way you are envisioning it today can not easily be
done. People do this but it is not an out of the box experience. By that I
mean, it is not a built in capability but it is possible to use the
extensions and write your own data extension to do this.
The way it is really designed today is for you to create the reports and the
dataset from within Reporting Services. The name matches the design. It is
designed to be a service that you can invoke. You can use the product three
ways. First, it ships with its own portal. This is the quickest way to use
it and is the best way to at least get your feet wet. Then you can integrate
with your own web app by using either URL integration (your app creates the
URL string passing any parameters). You can also integrate with soap (web
services).
Version 2 (Yukon and Widbey) will come with new controls that should allow
you to do this more the way you are envisioning: create a recordset and
passing it to the control. There will be both a winform and a webform
control.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> BUT
> HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> HOW TO ASSIGN A QUERY TO A DATASET?
> i dun know if my concept is corroect or not, please let me know if i have
> said something wrong
> thanks in advance.|||thank you so much bruce,
Does it mean that what i can do for the time being is just passing
paramenters to the query built in the rs designer, not passing a query to a
dataset?
"Bruce Loehle-Conger" wrote:
> Two points. First, the way you are envisioning it today can not easily be
> done. People do this but it is not an out of the box experience. By that I
> mean, it is not a built in capability but it is possible to use the
> extensions and write your own data extension to do this.
> The way it is really designed today is for you to create the reports and the
> dataset from within Reporting Services. The name matches the design. It is
> designed to be a service that you can invoke. You can use the product three
> ways. First, it ships with its own portal. This is the quickest way to use
> it and is the best way to at least get your feet wet. Then you can integrate
> with your own web app by using either URL integration (your app creates the
> URL string passing any parameters). You can also integrate with soap (web
> services).
> Version 2 (Yukon and Widbey) will come with new controls that should allow
> you to do this more the way you are envisioning: create a recordset and
> passing it to the control. There will be both a winform and a webform
> control.
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> > BUT
> > HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> > HOW TO ASSIGN A QUERY TO A DATASET?
> >
> > i dun know if my concept is corroect or not, please let me know if i have
> > said something wrong
> >
> > thanks in advance.
>
>|||by the way, could you please explain more about the method you suggest?
as i really have to make a dynamic query string (not just for the data
value, also the whole query)|||RS has a lot of flexibility in how the query is designed. You can have query
parameters (which map to report parameters). You can refer to global
variable (for instance user!userid), you can base the query on an expression
(expressions are very powerful so there is a whole lot you can do once you
do that).
I think before you jump all the way to where you want to end up you should
play around with it a little bit so you understand the capabilities. Create
a few simple reports, have a query parameter, try out a simple query based
on an expression.
After you understand the capabilities you can decide if it meets your needs.
As I said you can integrate with URL if you want to have your own web site
or you can also use a very full featured web services (soap). Then there are
also extentions possible (data extensions, security extensions).
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> by the way, could you please explain more about the method you suggest?
> as i really have to make a dynamic query string (not just for the data
> value, also the whole query)|||Thanks a lot Bruce,
you said by expression, do you mean by SQL command?
my situation is like this , i use the Render() function to generate reports
with the query "SELECT * FROM table WHERE value=@.parameter" in the report
project, i have tried to pass the parameter to the query by inputting it to
the render function.
thats the most i can do, but the parameters are only limited to a value, not
a clause(esp WHERE clause)...what if i wanna get the data with "WHERE value2
= @.para2 AND value3 =@.para3"'
will URL solve my problem? i have found a method , which is about editing
the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
"Bruce Loehle-Conger" wrote:
> RS has a lot of flexibility in how the query is designed. You can have query
> parameters (which map to report parameters). You can refer to global
> variable (for instance user!userid), you can base the query on an expression
> (expressions are very powerful so there is a whole lot you can do once you
> do that).
> I think before you jump all the way to where you want to end up you should
> play around with it a little bit so you understand the capabilities. Create
> a few simple reports, have a query parameter, try out a simple query based
> on an expression.
> After you understand the capabilities you can decide if it meets your needs.
> As I said you can integrate with URL if you want to have your own web site
> or you can also use a very full featured web services (soap). Then there are
> also extentions possible (data extensions, security extensions).
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> > by the way, could you please explain more about the method you suggest?
> >
> > as i really have to make a dynamic query string (not just for the data
> > value, also the whole query)
>
>|||My suggestion is to back up and first learn the capabilities of Reporting
Services before you start doing integration work. Spend a little time
experimenting. Do some sample reports. Although you can edit RDl and deploy
when you deploy it it will be accesible to all users so you would have to
come up with a unique naming scenario. Plus you have the performance hit of
deploying every time the user requests the report.
Read up on dynamic SQL. Read up and play with expressions and make sure you
understand the power of expressions. Be sure you understand the difference
between query parameter and report parameter. Read up and try a creating a
url to pass a simple parameter to a report. Then get into creating a dynamic
query.
And no, an expression is not a SQL command. It will evaluate to an sql
command but it can have iif statements, nested iif statements etc so you can
create a SQL command with a varying amount of clauses in the where command.
So far I have not heard anything you are trying to do that requires editing
the rdl file directly or writing a data extension. Custom assemblies are
also possible but again, in most cases are not necessary. I write a little
code behind but have found that normally an expression solves my needs.
Bruce L-C
MVP Reporting Services
Again, you are jumping to fast to the end result without first learning
Reporting Services.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> Thanks a lot Bruce,
> you said by expression, do you mean by SQL command?
> my situation is like this , i use the Render() function to generate
> reports
> with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> project, i have tried to pass the parameter to the query by inputting it
> to
> the render function.
> thats the most i can do, but the parameters are only limited to a value,
> not
> a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> value2
> = @.para2 AND value3 =@.para3"'
> will URL solve my problem? i have found a method , which is about editing
> the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
>
> "Bruce Loehle-Conger" wrote:
>> RS has a lot of flexibility in how the query is designed. You can have
>> query
>> parameters (which map to report parameters). You can refer to global
>> variable (for instance user!userid), you can base the query on an
>> expression
>> (expressions are very powerful so there is a whole lot you can do once
>> you
>> do that).
>> I think before you jump all the way to where you want to end up you
>> should
>> play around with it a little bit so you understand the capabilities.
>> Create
>> a few simple reports, have a query parameter, try out a simple query
>> based
>> on an expression.
>> After you understand the capabilities you can decide if it meets your
>> needs.
>> As I said you can integrate with URL if you want to have your own web
>> site
>> or you can also use a very full featured web services (soap). Then there
>> are
>> also extentions possible (data extensions, security extensions).
>> Bruce L-C
>> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
>> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
>> > by the way, could you please explain more about the method you suggest?
>> >
>> > as i really have to make a dynamic query string (not just for the data
>> > value, also the whole query)
>>|||thank you so much bruce,
after a day's study, i start to understand now, and i think nested iif maybe
the direction that i am heading to~~~
thank you sooo much for showing me the way!
"Bruce Loehle-Conger" wrote:
> My suggestion is to back up and first learn the capabilities of Reporting
> Services before you start doing integration work. Spend a little time
> experimenting. Do some sample reports. Although you can edit RDl and deploy
> when you deploy it it will be accesible to all users so you would have to
> come up with a unique naming scenario. Plus you have the performance hit of
> deploying every time the user requests the report.
> Read up on dynamic SQL. Read up and play with expressions and make sure you
> understand the power of expressions. Be sure you understand the difference
> between query parameter and report parameter. Read up and try a creating a
> url to pass a simple parameter to a report. Then get into creating a dynamic
> query.
> And no, an expression is not a SQL command. It will evaluate to an sql
> command but it can have iif statements, nested iif statements etc so you can
> create a SQL command with a varying amount of clauses in the where command.
> So far I have not heard anything you are trying to do that requires editing
> the rdl file directly or writing a data extension. Custom assemblies are
> also possible but again, in most cases are not necessary. I write a little
> code behind but have found that normally an expression solves my needs.
> Bruce L-C
> MVP Reporting Services
> Again, you are jumping to fast to the end result without first learning
> Reporting Services.
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> > Thanks a lot Bruce,
> >
> > you said by expression, do you mean by SQL command?
> >
> > my situation is like this , i use the Render() function to generate
> > reports
> > with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> > project, i have tried to pass the parameter to the query by inputting it
> > to
> > the render function.
> >
> > thats the most i can do, but the parameters are only limited to a value,
> > not
> > a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> > value2
> > = @.para2 AND value3 =@.para3"'
> >
> > will URL solve my problem? i have found a method , which is about editing
> > the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
> >
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> >> RS has a lot of flexibility in how the query is designed. You can have
> >> query
> >> parameters (which map to report parameters). You can refer to global
> >> variable (for instance user!userid), you can base the query on an
> >> expression
> >> (expressions are very powerful so there is a whole lot you can do once
> >> you
> >> do that).
> >>
> >> I think before you jump all the way to where you want to end up you
> >> should
> >> play around with it a little bit so you understand the capabilities.
> >> Create
> >> a few simple reports, have a query parameter, try out a simple query
> >> based
> >> on an expression.
> >>
> >> After you understand the capabilities you can decide if it meets your
> >> needs.
> >> As I said you can integrate with URL if you want to have your own web
> >> site
> >> or you can also use a very full featured web services (soap). Then there
> >> are
> >> also extentions possible (data extensions, security extensions).
> >>
> >> Bruce L-C
> >>
> >> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> >> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> >> > by the way, could you please explain more about the method you suggest?
> >> >
> >> > as i really have to make a dynamic query string (not just for the data
> >> > value, also the whole query)
> >>
> >>
> >>
>
>

Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

Define Printer in Report

I have a client who tells me in Access you could assign the printer directly to the report using PrtDevNames and PrtDevMode. Apparently it's true.

Can this be done in Reporting Services?

check out the section "Direct Client Printing" in this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

Shyam

|||

Thank you for the response.

Although that is a nice feature in 2005 over 2000. It's not quite what I am looking for I don't think.

I want to be able to store which printer the report should be sent to when printed so the user does not have to, I am looking for the same functionality that is in Access I mentioned above.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
Christian
Nope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.
|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.
|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>
|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only wor
k
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
--
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:
> > You could add a check constraint that calls a function, but this is likely
> > to be very slow.
> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Default Values properties (table level) not working.

I am using SQL Server Management Studio Express (SSMSE) with SQL Server Express as my database tools/database to assign the ‘Default Value’ for a column at the table level.

Going over the basics… using the database tools (SSMSE) and when inserting a new row; all rows by default have a 'Null' value. Ok.
If a default value is assigned to a column (table level) using the database tools, the default value is inserted correctly if that column has a null value upon the creation of a new row. Ok.

This works fine when I am working with SSMSE on tables (inserting, deleting editing rows etc.) within my database…

But this doesn’t apply or work for adding new rows with datasets (example: using the default insert, update, delete statements provided by the wizard and using a DataGridView). My table level default values are not inserted into the new row, instead my column that had a default value assigned; now has a 'Null' value in the new row that was created by the dataset.

Isn’t a Null value is still a Null value for a new row?

Shouldn’t the database engine supply that ‘default value’ for a field that had a ‘Null’ value upon row creation?

I have always thought of a table level column ‘Default Value property’ as a trigger that tests for nulls and inserts the default value if that column has a null value when the new row is created. So I am expecting the database engine to insert the default value for that column, not the dataset when the value inserted into that column is null for a new row. I really don't need a (table level) column default property that only works with database tools for inserting new rows, that doesn't help me... totally baffled here...
Thanks

Hey Rick.

Default values will be applied to a column when NO explicit value is specified for the column in the corresponding insert (this includes a <NULL> explicit value)...so, for example, assume I have a table with 2 columns, colA and colB, and on colB I have a default value of 'colBDefault' specified...the following statement will end up with a record that includes a row with 'colAvalue' for colA, and null for colB, because I am explicitly saying to use a null value for colB:

insert table (colA, colB) select 'colAvalue', null

However, the following statement will end up with a value of 'colAvalue' for colA, and the default value of 'colBDefault' for colB, because no explicit value is specified for colB:

insert table (colA) select 'colAvalue'

I'd bet that the DataGridView is specifying all columns with a null value for anything you don't specify. To prove this, you could run a trace on the Sql server to see what the actual insert command being executed is...

HTH,

|||Hello Chad,

Thanks for the reply. That did help.

Unfortunately I couldn't get the ADO.Net trace logging to work... my tracing abilities are pretty much nil...

Another way to look at this is I am only pulling certain text fields that I want (no default value assigned) from the adapter / dataset for that table, not all of the fields from that particlular table.
The fields that I have designated a default value for are not included in the dataset, so they do not have an insert command etc. (or value assigned) for those fields for that table.
But... those fields that are *not* included in the insert statement etc. for that table do in fact show a value of 'Null' for the new row even though they have a default value assigned for them at the table level...

Example:
Fields: (ID), (LastName), (FirstName), ((Age) - default value set to 0), ((DeptNo) - default set to 100)

The adapter is only pulling fields: (ID), (LastName) and (FirstName); the insert etc. commands only pertain to those fields.
When a new row is inserted fields: (Age) and (DeptNo) do show a 'Null' value, not their assigned default value.

Thanks,
Rick

|||Whooooops...

My apologies!

It does work as you suggested!
I literally had six different forms to test things and simply got them mixed up, of what worked and what didn't!!!

Thanks,
Rick

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
Jeff
Instead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.ph
x.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert