How to show attribute ValueColumn in Excel 2007?

I'm using Excel 2007 to access my SSAS 2005 cube. For an attribute, I have defined the ValueColumn as the alternative description for the attribute. How can I show the ValueColumn in Excel 2007? Better yet, I want to see if there is any way I can toggle between the NameColumn and ValueColumn.

Thanks,

Mitch

The only way I can think of to expose the MemberValue to excel would be to create a calculated measure, but this would mean you would have to have the value in the data section of the pivot table and it sounds like you want to get it into the row/column labels.

Another approach would be to set this "alternate description" up as an attribute in it's own right that is related to the main attribute, you could then display it using the member properties feature in Excel.

Finally, I have not tried this myself, but you could look into maybe using the translations feature to store the alternate description. You might then be able to write a small macro in Excel that would alter the connection to use a different language that would bring up your alternate description. I would suggest doing a small test before going too far down this path.

|||

Darren,

These are not bad suggestions. The closest solution is to use the member property. It is just that my users are used to toggle between one description and another with their current data warehouse tool, and I'm trying to replace the current tool with SSAS and Excel 2007.

Mitch

sql

how to share variables across packages?

I'm working on a solution in Visual Studio that has 3 SSIS packages, and now I want to add a 4th that needs access to variables defined in one of the other packages. How can I do that?

One way would be to dump out your variables and values to a raw file or db if you prefer. Then any other process from any execution context can access them anytime via a "source connection" to that file...

|||

Kevin,

Have you looked at package configurations at all. You could set parameter values from a table or a parent package variable.

Rafael Salas

|||

Rafael Salas wrote:

Have you looked at package configurations at all. You could set parameter values from a table or a parent package variable.

Yes, I intend to set my variables externally, via package configurations. But I don't know what a "parameter" is or how it differs from a variable. And I don't know how to allow my variables scoped to package X to be accessed by package Y -- how do I make package X to be a parent of package Y?

|||

Sorry I meant variable...not parameter.

In short, to make X parent of Y; you can use a Execute Package Task (control Flow) in X that would call package Y.

Rafael Salas

How to setup SQL Express for course use

I will be teaching a web scripting course, and I would like to switch from using MS Access to MS SQL as a database. Please forgive my ignorance, as have much to learn about SQL Express. We have installed SQL Express on a web server (windows box).

Question1:
is SQL like MS Access, in that the SQL Express software should be installed locally on the users machine. The user will create databases and tables locally, then upload the resulting database(s) to the appropriate folder on the web server along with web scripting files?
-- OR
is MS SQL like MySQL, where the db is installed on the remote server, and user accounts are created within the database with appropriate priv.'s. The user logs in to the db application remotely to create tables within a given database.

You can install it either way (locally or remotely). I could see advantages to both. If they install it locally to each machine, you wouldn't have to worry about setting up users/permissions/remote connections, etc on a central server. But if you install it on a central server, you only have to go through the install one time and don't have to worry about the student upload/transfer.

If you don't have much experience with Express, I'd suggest installing Express Advanced so you can get the GUI management tools as well.

Thanks,
Sam Lester (MSFT)

How to setup Replication with Access

hi all,
I want to setup replication on Access Database.. I want data will be updated
in SQL Server from Access database..
1) Which replication type will be best suited (merge or transactional)
2) How I will setup subscription on Access database. Whenever I try to
create a push subscription I only found option to create subsription on SQL
Server database. Although I have checked "Hetrogeneous datasources such as
Access" option when creating publication.
any suggesstion or link to article will be highly appreiciated..
Thanks
Ansari
Hi all
I succeeded in creating push subscription to an access datatbase. But
whenever I snapshot agest want to apply initial snap shot the process fails
and give me the following errors
"The process could not initialize 'MsJet'. Check to see if the component is
registered correctly"
any sugestion
Ansari
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message
news:O6I4OqSpEHA.1160@.tk2msftngp13.phx.gbl...
> hi all,
> I want to setup replication on Access Database.. I want data will be
updated
> in SQL Server from Access database..
> 1) Which replication type will be best suited (merge or transactional)
> 2) How I will setup subscription on Access database. Whenever I try to
> create a push subscription I only found option to create subsription on
SQL
> Server database. Although I have checked "Hetrogeneous datasources such as
> Access" option when creating publication.
> any suggesstion or link to article will be highly appreiciated..
> Thanks
> Ansari
>
>

How to setup replication between access to SQL Server , MSDE inste

Hi Karen.
Yes it is possible to have Access subscribers:
see http://msdn.microsoft.com/library/en...lctem_0815.asp
And the Access DBs should be subscribers and the SQL server would be the
Publisher.
And YES switch to the MSDE instead of Access, for a lot of reasons.
(Compatibility, flexibility, openness in regard to debugging and error
logging, stability)
HTH
JorgenD.
"Karen Middleton" wrote:

> Hello All
> We have a computer based application running on trucks and they
> capture the transaction into a Access database we need to collect the
> data from multiple trucks into a SQL Server database centrally.
> Can somebody please explain whether Access can replicate to SQL Server
> with Access as publisher and SQL Server as the subscriber. Also, I am
> wondering is it worthwhile to replace Access by MSDE since MSDE is
> free I would appreciate your feedback on this count.
> Also, any books, web sites, white papers and how to setup a
> replication from Access to SQL Server. Also, can somebody please
> explain how the replication works from Access to SQL Server and is it
> lot of coding to get this working and what kind of replication is it
> full table or delta replication.
> Also, some of our trucks will be in far off places is there a nice way
> in which Access can be made to replicate from these remote sites to
> the central site what kind of comms do we need.
>
> Many thanks in advance for your inputs
> Thanks
> Karen
>
Karen,
I agree with Jorgen that MSDE would be preferable long-term. However, your
original question was about having Access as a publisher and SQL Server as a
subscriber, which is not available out of the box using Jet. If you have sql
server as a merge publisher and access as a subscriber, in many senses
datawise it will be functionally equivalent, but care will need taking with
administrtion and conflict resolution where the role of the publisher is
indeed distinct to the subscriber.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
sql

How to setup replication between access to SQL Server , MSDE inste

Hi Karen.
Yes it is possible to have Access subscribers:
see http://msdn.microsoft.com/library/en...lctem_0815.asp
And the Access DBs should be subscribers and the SQL server would be the
Publisher.
And YES switch to the MSDE instead of Access, for a lot of reasons.
(Compatibility, flexibility, openness in regard to debugging and error
logging, stability)
HTH
JorgenD.
"Karen Middleton" wrote:

> Hello All
> We have a computer based application running on trucks and they
> capture the transaction into a Access database we need to collect the
> data from multiple trucks into a SQL Server database centrally.
> Can somebody please explain whether Access can replicate to SQL Server
> with Access as publisher and SQL Server as the subscriber. Also, I am
> wondering is it worthwhile to replace Access by MSDE since MSDE is
> free I would appreciate your feedback on this count.
> Also, any books, web sites, white papers and how to setup a
> replication from Access to SQL Server. Also, can somebody please
> explain how the replication works from Access to SQL Server and is it
> lot of coding to get this working and what kind of replication is it
> full table or delta replication.
> Also, some of our trucks will be in far off places is there a nice way
> in which Access can be made to replicate from these remote sites to
> the central site what kind of comms do we need.
>
> Many thanks in advance for your inputs
> Thanks
> Karen
>
Karen,
I agree with Jorgen that MSDE would be preferable long-term. However, your
original question was about having Access as a publisher and SQL Server as a
subscriber, which is not available out of the box using Jet. If you have sql
server as a merge publisher and access as a subscriber, in many senses
datawise it will be functionally equivalent, but care will need taking with
administrtion and conflict resolution where the role of the publisher is
indeed distinct to the subscriber.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]

How to setup Link Server properly

Hi,

I want to access another database seating on another server so I configured the Linked server with SQL Server as the server type. I set the security as NT_AUTHORITY\SYSTEM and checked the Impersonate. In Server Options, I set the RPC Out to true then I clicked ok.

I got this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB Provider "SQLNCLI" for linked server "server1" reported an error. Authentication failed.

Cannot initialize the datasource object of OLE DB Provider "SQLNCLI" for linked server "server1".

OLE DB Provider "SQLNCLI" for linked server "server1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

Note that my NT login is added as "Administrators" on the Server i want to connect to.

Have I missed anything?

cherriesh

I tend to use sp_addlinkedserver and sp_addlinkedsrvlogin for this.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

http://msdn2.microsoft.com/en-us/library/ms189811.aspx

The trick is to specify 'true' for @.useself for sp_addlinkedsrvlogin.

e.g.

Code Snippet

exec sp_addlinkedsrvlogin 'remotesrv', 'true'

how to setup access for sql jobs

Hello,
I have some users that I want to be able to view/add sql job under sql
agent. How can I give them access to this without making them sys admins?
Jake
The users will need to own the jobs to view them if you
don't want them to be members of sysadmin role.
The public role has permissions to execute sp_add_job.
-Sue
On Mon, 18 Oct 2004 16:10:43 -0700, "Jake"
<rondican@.hotmail.com> wrote:

>Hello,
> I have some users that I want to be able to view/add sql job under sql
>agent. How can I give them access to this without making them sys admins?
>Jake
>

how to setup access for sql jobs

Hello,
I have some users that I want to be able to view/add sql job under sql
agent. How can I give them access to this without making them sys admins?
JakeThe users will need to own the jobs to view them if you
don't want them to be members of sysadmin role.
The public role has permissions to execute sp_add_job.
-Sue
On Mon, 18 Oct 2004 16:10:43 -0700, "Jake"
<rondican@.hotmail.com> wrote:

>Hello,
> I have some users that I want to be able to view/add sql job under sql
>agent. How can I give them access to this without making them sys admins?
>Jake
>

how to setup access for sql jobs

Hello,
I have some users that I want to be able to view/add sql job under sql
agent. How can I give them access to this without making them sys admins?
JakeThe users will need to own the jobs to view them if you
don't want them to be members of sysadmin role.
The public role has permissions to execute sp_add_job.
-Sue
On Mon, 18 Oct 2004 16:10:43 -0700, "Jake"
<rondican@.hotmail.com> wrote:
>Hello,
> I have some users that I want to be able to view/add sql job under sql
>agent. How can I give them access to this without making them sys admins?
>Jake
>sql

How to set up connection to MS SQL remotely

If I let the pages (ASP) running on the third party server access my database (MS SQL) remotely, what should I do with my database server? I was told to open a port. If it is true, what is the number of the port I should open?
Thanks in advanceDANGER. Be careful. If you do this wrong you can open up your server for the world to access.

The default port is 1433, but if you are going to do this I reccomend changing that. You are also going to have to do stuff with your firewall. This is very dangerous and I would reccomend you explore other options. I imagine the peformance of the pages are going to suck as well.|||If you, against all advices, should decide to do this, you should definitely have a look at HTTP endpoints, and see if you could use a HTTP endpoint. If so, you should open ONE port (443) from ONE ip (the webserver) for ONE user, which authenticates using a rather secure method, as for instance a certificate.

This is the only solution I would think of that could come into consideration.

how to set up a 'cascading delte' in mssql

I'm used to working with Access but have moved to MSSQL 2005.
I have these tables:
tblCustomer: customerID (auto), name (nvarchar,50)
tblOrders: orderID (auto), customerID (int)
I use SSMSE. How can I set it up so that the related records in tblOrders
are deleted if I delete a customer.
Thanks.
Jesper
Jesper F (askfortheemail@.ask.com) writes:
> I'm used to working with Access but have moved to MSSQL 2005.
> I have these tables:
> tblCustomer: customerID (auto), name (nvarchar,50)
> tblOrders: orderID (auto), customerID (int)
> I use SSMSE. How can I set it up so that the related records in tblOrders
> are deleted if I delete a customer.
ALTER TABLE tblOrders ADD
CONSTRAINT fk_order_customers FOREIGN KEY (customerID)
REFERENCES tblCustomers (customerID) ON CASCADE DELETE
Although, I must say that from my corners, this looks like a funny
business rule...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||> ALTER TABLE tblOrders ADD
> CONSTRAINT fk_order_customers FOREIGN KEY (customerID)
> REFERENCES tblCustomers (customerID) ON CASCADE DELETE
> Although, I must say that from my corners, this looks like a funny
> business rule...
Those were imaginary tables, but thanks for pointing me in the right
direction.
Jesper

How to set ReportViewer DataSource to a Custom Collection in Another Project

I have a web site that uses a business logic layer (BLL) and a data access layer (DAL). The BLL is in a separate project. The DAL is in a separate project. The web site has a reference to the BLL project and the BLLproject has a reference to the DAL project. Just to learn how to use the reportviewer control I used the server explorer to create a web datasource based directly on a stored procedure and everything works just fine. However, now I want to reportviewer to be based on a method in my BLL project. My problem is that the web data source window only shows those methods that have not paramenters. I want it to show CovAdm.BLL.InitialInspectionMgr.LoadDataTableForParcelID which has one parameter for ParcelID and returns a datatable. How do I get this method to show up under the CovAdm.BLL item in the web data sources window?

I found what I think is a simpler and better solution, especially because the reportviewer control seems be a long way from WYSIWYG. My solution doesn't use a report viewer control at all. Here's what I did.

My web page has a formview control with a "Report" button. The formview control itemcommand event checks for which button has been clicked and executes the following code behind for the Report button:
' get the data
Dim bll As New CovAdm.BLL.InitialInspectionMgr
Dim col As CovAdm.BLL.InitialInspectionCollection
Dim lngParcelID As Long = MySession.ParcelSearchResultDetailParcelID(Session)
col = bll.LoadForParcelID(lngParcelID, "ParcelID", "ASC")
' use the default file name for cases that have no file name
' use a physical path because local reports don't work with virtual paths
For Each ii As CovAdm.BLL.InitialInspection In col
If String.IsNullOrEmpty(ii.FQPhotoFileName) Then
Dim str As String = AppConfigurationMgr.PhotoNotAvailableRelativeFile()
str = Server.MapPath(str)
ii.FQPhotoFileName = str
End If
Next
' set up the report
Dim lr As New Microsoft.Reporting.WebForms.LocalReport
lr.ReportPath = Server.MapPath("~/Home/Report/rptInitialInspection.rdlc")
lr.EnableExternalImages = True
lr.DataSources.Clear()
' make sure we use the correct data source name
Dim rds As New Microsoft.Reporting.WebForms.ReportDataSource("dsrptInitialInspection_dtrptInitialInspection", col)
lr.DataSources.Add(rds)
' show the file dialog for opening or saving the pdf verion of the report
Utility.ShowLocalReport(Response, lr)

The code from my Utility class is shown below:
Public Shared Sub ShowLocalReport _
( _
ByVal vResponse As System.Web.HttpResponse, _
ByVal vlr As Microsoft.Reporting.WebForms.LocalReport _
)
'
' Aruments
' ========
' vResponse - HttpResponse object from calling page
' vlr - LocalReport object from calling page
'
' the procedure is currently limited to PDF format
Dim strFormat As String = "PDF"
Dim strMimeType As String = Nothing
Dim strEncoding As String = Nothing
Dim strFileExt As String = Nothing
' the default filename for saving will be the name if the rdlc file
Dim strFileName As String = System.IO.Path.GetFileNameWithoutExtension(vlr.ReportPath)
Dim astrStream() As String
Dim aWarning() As Microsoft.Reporting.WebForms.Warning
Dim aBytes As Byte()
' render the report
aBytes = vlr.Render(strFormat, Nothing, strMimeType, strEncoding, strFileExt, astrStream, aWarning)
' show the open / save dialog
vResponse.Clear()
vResponse.ContentType = "application/" & strFormat
vResponse.AddHeader("content-disposition", "attachment; filename=" & strFileName & "." & strFileExt)
vResponse.BinaryWrite(aBytes)
vResponse.End()
End Sub

how to set permission for a database

hi,

I need to do a security setup in SQL server 2000. the scenario is as follows:

In a team, only one person(Team Leader) should access the particular DB (through Enterprise Manager and rest of the persons in the Team(developers) should access through code.

How to do this.

Thanks and Regards

Sridhar, not sure what you mean by "through code"... executing code in an app? or maintaining proc/trigger code on a database? You're trying to restirct WHAT groups can do, not necesarily thru what tool? (because if a developer has permission to update tables, they can get in thru EM)... In SQL 2005 SP2, you can put a SERVER TRIGGER on LOGONS and probably not allow certain logons in thru certain application names that way.. What type of access are you talking about? Bruce|||

In SQL server 2005 we introduced a new feature that hopefully will help to solve your scenario: digital signatures in SQL Modules.

The general idea is to allow certain permissions only when the call comes directly from a signed module (SP, user defined function, etc.). I strongly recommend reading the following resources:

· BOL

o Understanding Execution Context:http://msdn2.microsoft.com/en-us/library/ms187096.aspx

o Module Signing: http://msdn2.microsoft.com/en-us/library/ms345102.aspx

o ADD SIGNATURE: http://msdn2.microsoft.com/en-us/library/ms181700.aspx

· Laurentiu Cristofor’s Blog:

o http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

o http://blogs.msdn.com/lcris/archive/tags/SQL+Server+-+cryptography/default.aspx?p=2

· Raul Garcia’s blog:

o http://blogs.msdn.com/raulga/archive/tags/SQL+Server+Signatures/default.aspx

Feel free to ask us any question or feedback on this topic. We will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

hi bruce,

1. In my setup there are some developers for a project accessing the database through th EM and once the same once the same project implemented in production level, it should not get accessed through the EM (either they may only view the tables not edit/delete). how to do this?

2. What permission should be given to database so that throgh the application it can be edited/deleted/updated and not through the EM.

|||

I strongly recommend reading the following thread. Here we discuss in detail about application security, and from your last post, I think it is something similar to what you are asking for:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=775364&SiteID=1

Please let us know if this information was useful.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

how to set password when installing MSDE (was "Newbie to SQL")

Hi Guys,

I am Access user of both BE and FE. I would like to set my BE with SQL

Therefore, as trial I just downloaded MSDE2000A.Exe a Desktop SQL Engine from Microsoft. I need to install it. But due to some technical aspect (which I might not understand) I could not install it. Bcz whenever I am trying to install its setup file, it ask me a strong password. How to set it ? or how to supply it I dont know...

Any help.

Regards,
AshfaqueThis (http://support.microsoft.com/default.aspx?scid=kb;en-us;814463) is what you need (in English).

-PatP|||Thanks Pat,

I installed it successfully. The information page is good. Thanks.

Do you have any other referece web page that would show me how to transfer my Access table data over to this installed SQL server and allow my FE to be still in MS ACCESS.bcz I desined my beautiful forms and its code after a lot of excesize. What are the initial steps I must follow?

With kind regards,
Ashfafque|||I'd suggest using the Access Upsizing Wizard as a start. It isn't a complete solution, but it gives you one heck of a head start! Microsoft has a great "How to (http://support.microsoft.com/default.aspx?scid=kb;en-us;237980)", and there are versions for 2000 (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) and 2002 (http://support.microsoft.com/default.aspx?scid=kb;en-us;330468).

-PatP|||Again thanks Pat,

Article is very usefule. I will be back if face any problem.

With kind regards,
Ashfaque

How to set password through CRviewer to DSN less connection in crystal report

Hi,please help.
I have crystal reports bearing DSN less(direct) connection with password protected Access database.But i dont know how to set password through CRviewer in VB 6.0 .Moreover my reports have no. of subreports too.How are you calling Reports? If you use front end application, then you need to supply the password informations

How to set my ASP.NET application to access SQL Server 2005 using clients user credentials

Hi guys,

I'm not sure if I'm just bad at googling but I can't seem to find a way to set an ASP.NET 2.0 web application to connect to SQL Server 2005 using the current client's user credentials. My web application is using Integrated Windows Authentication so its Page.User.Identity is set to a DOMAIN\username value... I want to pass that to my connectionstring or have my connections pick up the identity automatically and use that Identity when accessing the db server.

Oh and another thing, my IIS Application Pool is using a specific Identity itself, so I don't know if that might affect the above.

Hope someone could help.

Hi,

what you need is process impersonation, that allows asp.net to switch the execute owner to an other user. I have used this for asp.net and remoting calls, so that i have the original caller. I have two links for you:

http://msdn2.microsoft.com/en-us/library/ms998292.aspx|||Thanks Zhou. I'll the articles out once I get my hands on our dev server. Thanks.

How to set MS Access as Data Flow Destination

I can't seem to find a way to make the Data Flow Destination in a Business Intelligence Visual Studion Project output an MDB file for Microsoft Access.

Hi Bart,

Data flow destination can be used to output to MDB file for Microsoft Access.

In the OLE DB "Connection Manager: Select "Microsoft Jet 4.0 OLE DB Provider" for the Provider field.
Click on the "Browse" Button next for the "Database File Name" property and Select an existing Microsoft Access Database file. and close that window.

In the OLE DB Destination Editor - for the "Name of the table or the view" property click on "New" and it will show the table format for the imported table in access database.

Thanks,
Loonysan

|||

Yes - its possible.

In the "OLE DB Destination Editor" Window - Click "New" for OLEDB Connection Manager.
In the "Connection Manager" Window - Select "Microsoft Jet 4.0 OLE DB Provider".
Select an existing Access Database file using the "Browse" button for "Database File Name and close the "Configure OLE DB Connection Manager" window.

In the "OLE DB Destination Editor" - Click "New" for "Name of the table or the view" - it will show the table structure of the imported data. Make changes to the table / column names if required.

Clcik on "Mappings" and check the default mapping for the imported columns. Click OK.

You should be good to go from here

Thanks,
Loonysan

How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..

How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..