ADDUSERASADMIN doesn't work if not in normal user

As pointed out in the SQL Express blog recently http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx (look for the section that starts "Watch out!"), the SQL Express SP2 argument ADDUSERASADMIN will not work correctly if the user is a normal user. If the user is a member of the BUILTIN\Administrators group then Vista will prompt to elevate them to allow the Admin rights to be effective.

However, my reading of the blog post is that if they are normal users, Vista will prompt for the Administrator credentials. This effectively runs the install as the Administrator user. So the ADDUSERASADMIN argument works, but adds the Administrator rather than the normal user.

This is not what I need to happen. Is there any way around this? I have a ClickOnce application. Is there any way to restrict the ClickOnce install to require the user to be a member of the BUILTIN\Administrator group?

Thanks.

Rather than this being a SQL Express problem its looking more like a ClickOnce deployment issue under Vista. When you publish a ClickOnce application it creates a Bootstrapper called setup.exe. Vista's Installer Detection Technology will recognise the string "setup" in the filename etc, and if found, assume it is an installer and prompt for Admin rights. http://technet2.microsoft.com/WindowsVista/en/library/00d04415-2b2f-422c-b70e-b18ff918c2811033.mspx?mfr=true

In my case of a standard user, it will prompt for the Administrator password and then run the install as the Administrator. That may explain why SQL Express adds the wrong use as Admin, rather than the standard user. Anyone had similar issues?

Scott.

|||

Hi Scott,

There are really two completely separate things going on here, first the Vista behavior related to setup.exe and second how ADDUSERASADMIN is working...

ClickOnce / Vista / UAC - ClickOnce is designed to install applications in a secure, per user cache with the benefit being that you do not require administrative permissions to install the VS part of a ClickOnce deployed applicatons. I hadn't really considered this, but you're right that Vista will notice the fact that a process named "setup.exe" has been started and automatically offer a prompt to request admin elevation. You're also correct that if you are a normal user and have to provide a different administrative account, that administative account will be used to run setup.exe. Technically, you don't need to be an administrator to install the VS part of a ClickOnce application, but I've never tried declining the request for admin elevation to see what would happen. This is an interesting question to discuss on the ClickOnce forum, I don't know what their answer is to this.

But that leads us to the second issue...

SQL installation does require admin permissions - Independent of the permission requirements of ClickOnce installation, SQL Express always requires adminstrative permissions to complete. There is no way around this. As you've found, the ADDUSERASADMIN switch will add the the account that is running setup to the SysAdmin group. In the case of a "normal user" who has to elevate the SQL Setup process (and remember, you must do this), the account that is running the setup process will be the adminstrative account that was provided for elevation. Long story short, this is just the way Vista works, we tried to find a way to figure out the actual account of the normal user who really started setup, but there was no way to do it, so we chose to err on the side of working most of the time rather than failing all of the time and forcing users to go deal with this manually for every installation on Vista. In the case of the "normal user" you will have to go back after installation and add the appropriate user with the provisioning tool located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlprov.exe. The tool can be used to add any user to SysAdmin and supports a command line so it can be called from within some type of wrapper. We're looking for ways to make this experince better in the next version of SQL Express.

Mike

|||

Thanks Mike,

That has helped me to sort a few things out in my own mind. My main objective here is to have a good experience for users installing my software. I want them to be able to run install off the CD or website, click a SQL Express EULA, have it install, and then the app fires up. The first step in the app after install is to create the database, if it doesn't already exist. User instances don't work because the data needs to be available over a small LAN. The symptom I face is that if they are a normal user, the install works, but the app fails because it can't create a database. The Windows Administrator is the SqlAdmin, not the installing user :-(

My current thinking is that if I can stop the ClickOnce setup.exe from requesting admin elevation and run as the normal user, the SQL Express bootstrapper package checks will fail because it doesn't have Admin rights. I'm comfortable changing the package to provide a useful error message for my install. If the user installing is in the Admin group, the ClickOnce installer should run without elevation, but the SQL Express installer will require elevation. From your blog, I believe the ADDUSERASADMIN option would then work correctly. Have I missed something?

I tried embedding a manifest in the ClickOnce bootstrapper setup.exe, but got errors. I tried adding stuff about Microsoft.VC80.CRT to the manifest as suggested in a couple of posts, but I got several confusing messages about VC++ and side-by-side (SxS). As I'm a C# guy this all went over my head. I'll take another look at it, but I'm wondering if I'll have any more luck with a standalone manifest.

Appreciate your help,

Scott.

|||

My theory was flawed. I got the manifest embedded in the ClickOnce bootstrapper, but when it tried running SqlExpressChk.exe from the bootstrapper package it seemed to have failed because it had no Admin rights, and then SQL Express didn't get installed at all. I have decided to abandon the ADDUSERASADMIN approach and use the sa login to create the new database from within my startup code. I set the SAPASSWORD when SQL Express is installed, and will include the (encrypted) password in my code.

sql

Adding, Deleting rows from Visual Basic 2005 Express Edition

Hi,

I'm a complete novice concerning SQL Server (Express Edition)

I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.

Have i missed an option during installation?

Thx,

Steven

Your installation of SQL Server 2005 Express may be operating in 'Snapshot Isolation' mode. Refer to Books Online for more details.

You may also find this series of instructional videos to be useful.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||

Make sure that you did not specify the datafile for "Always copy", if you did this, the file will always be copied from scratch upon new start of the Visual Studio debug session.

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

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Arnie,

Thx for your trouble, but i got the 'Always Copy' option wrong!

Greetings,

Steven

|||

Hi Jens,

Your answer was the correct one.

Thx,

Steven

adding the vc express bundled sql server to odbc

Hi, I have installed a trial version of VC++ Express 2005 with the
budled SQL Server. After creating a C++ Forms prototype, with some
tables into a database file named Test1.mdb, now I want to make ODBC
available this local database.

What are the steps required to add and ODBC entry so any ODBC-capable
program may access my prototype?

I know in advance the location of the database file and log file, know
it is local (so the server is localhost or just (local) ) and also the
driver name and the port number.

What is unknown to me is the user id and password as the installlation
was done to auth with OS credentials.
Could be "sa" without password?

Thanks in advance, CarlosChuck (carlos.crosetti@.gmail.com) writes:

Quote:

Originally Posted by

Hi, I have installed a trial version of VC++ Express 2005 with the
budled SQL Server. After creating a C++ Forms prototype, with some
tables into a database file named Test1.mdb, now I want to make ODBC
available this local database.
>
What are the steps required to add and ODBC entry so any ODBC-capable
program may access my prototype?
>
I know in advance the location of the database file and log file, know
it is local (so the server is localhost or just (local) ) and also the
driver name and the port number.
>
What is unknown to me is the user id and password as the installlation
was done to auth with OS credentials.
Could be "sa" without password?

Why can't use Windows authentication? Why would the ODBC application
log in as sa?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Adding SSMSE 2005 as a Prerequisite to a Visual Studio Setup Project

Hi,

I'm am deploying an application that uses SQL Express and wish to also deploy SSMSE along with it.

I have the .msi but I don't know how to get the setup project to deploy this as it does SQLExpress and .Net Framework 2.0. (and I am using VS 2005 SE)

There is an MS article on creating "Custom Prerequisites" (http://msdn2.microsoft.com/en-us/library/ms165429(VS.80).aspx) which speaks to creating product and project manifests.

Has anyone does this for SSMSE? Or am I going about this in the wrong way?

Thanks in advance,

Keith

Hi Keith,

You're going about this the right way, if you want to use SSMSE as a pre-requisite you'll need to create a custom pre-requisite for this following the instructions on MSDN. (Microsoft has not created this package.) If you have questions about creating your own prerequisites for VS, I would suggest posting those questions to the ClickOnce forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=6&SiteID=1.

One thing to consider is that the licensing of SSMSE is such that it can only be deployed if you are also deploying SQL Express, independent deployment is not part of the license.

Mike

|||

Thanks Mike! I am deploying it with SQL Express as well.

Take care,

Keith

Adding SSMSE 2005 as a Prerequisite to a Visual Studio Setup Project

Hi,

I'm am deploying an application that uses SQL Express and wish to also deploy SSMSE along with it.

I have the .msi but I don't know how to get the setup project to deploy this as it does SQLExpress and .Net Framework 2.0. (and I am using VS 2005 SE)

There is an MS article on creating "Custom Prerequisites" (http://msdn2.microsoft.com/en-us/library/ms165429(VS.80).aspx) which speaks to creating product and project manifests.

Has anyone does this for SSMSE? Or am I going about this in the wrong way?

Thanks in advance,

Keith

Hi Keith,

You're going about this the right way, if you want to use SSMSE as a pre-requisite you'll need to create a custom pre-requisite for this following the instructions on MSDN. (Microsoft has not created this package.) If you have questions about creating your own prerequisites for VS, I would suggest posting those questions to the ClickOnce forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=6&SiteID=1.

One thing to consider is that the licensing of SSMSE is such that it can only be deployed if you are also deploying SQL Express, independent deployment is not part of the license.

Mike

|||

Thanks Mike! I am deploying it with SQL Express as well.

Take care,

Keith

Adding SSMSE 2005 as a Prerequisite to a Visual Studio Setup Project

Hi,

I'm am deploying an application that uses SQL Express and wish to also deploy SSMSE along with it.

I have the .msi but I don't know how to get the setup project to deploy this as it does SQLExpress and .Net Framework 2.0. (and I am using VS 2005 SE)

There is an MS article on creating "Custom Prerequisites" (http://msdn2.microsoft.com/en-us/library/ms165429(VS.80).aspx) which speaks to creating product and project manifests.

Has anyone does this for SSMSE? Or am I going about this in the wrong way?

Thanks in advance,

Keith

Hi Keith,

You're going about this the right way, if you want to use SSMSE as a pre-requisite you'll need to create a custom pre-requisite for this following the instructions on MSDN. (Microsoft has not created this package.) If you have questions about creating your own prerequisites for VS, I would suggest posting those questions to the ClickOnce forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=6&SiteID=1.

One thing to consider is that the licensing of SSMSE is such that it can only be deployed if you are also deploying SQL Express, independent deployment is not part of the license.

Mike

|||

Thanks Mike! I am deploying it with SQL Express as well.

Take care,

Keith

adding sqlexpress sp1/2 into setup project

Hi All,

How can I add sql server express sp1 or sp2 into a setup project I created for an application. Normally sqlserver express is a prerequisite for my app and in deployment vs only adds sqlexpress not the sqlexpress with sp1 or sp2. I know that I need those to make my application run on vista. Any suggestion?

Thanks

The prerequisites in VS are driven by a set of manifests installed with VS that only target SQL Express RTM. Microsoft has not released updates to these prerequisites, but you can build your own prerequisite for SQL Express SP2 by following the directions in the SQL Express blog. Check out the instructions here.

Mike

|||After a long wait I knew that you will reply. I haven't tried your instructions yet but I am sure this is the answer. Thanks for the reply.

adding sqlexpress sp1/2 into setup project

Hi All,

How can I add sql server express sp1 or sp2 into a setup project I created for an application. Normally sqlserver express is a prerequisite for my app and in deployment vs only adds sqlexpress not the sqlexpress with sp1 or sp2. I know that I need those to make my application run on vista. Any suggestion?

Thanks

The prerequisites in VS are driven by a set of manifests installed with VS that only target SQL Express RTM. Microsoft has not released updates to these prerequisites, but you can build your own prerequisite for SQL Express SP2 by following the directions in the SQL Express blog. Check out the instructions here.

Mike

|||After a long wait I knew that you will reply. I haven't tried your instructions yet but I am sure this is the answer. Thanks for the reply.

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding procedure to SQL Server Express

I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.

When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.

Can anyone help me determine what to look for or what I might be doing wrong?

Thank you.

How exactly are you adding the object to the database inside the Management tools. Has you friend supplied a SQL script file and you are running it inside a new query window. If so you will need to make sure that the query you are running is inside the correct database. Generally you should have a use statement at the top of the database

Query for example

use northwind
go

and then have the Stored procedure script following it.

|||

He forgot the USE statement. Previously it was adding into the system area. My knowlede of procedures is limited, but I should have thought of that myself.

Thank you.

Adding logins via SSEUtil compared to SQL Server Management Studio

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

Just an update.

I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.

Is there another table that actually enables the login?

DSXC

|||

I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.

Lo and behold... it works!

Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!

EDIT: I didn't mention the command line to run it...

SQLCMD.EXE -i MYSCRIPT.SQL

Hope that helps.

DSXC

|||

Hi DSXC,

Sorry to have missed this thread earlier, I can shed some light on what you're seeing.

SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.

SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.

Mike

|||

Hi Mike,

Thanks for your response. It makes a bit more sense now.

I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.

Again thanks for your response.

DSXC

Adding images with SQL Server Express Management Studio

Can I add images to a table with SQL Server Express Management Studio? If so, how, if not, what alternative methods other than the point-and-click can I use?

Thanks in advance,

Fedor Steeman

hi Fedor,

there's no "point and click" solution for your requirement in SSMS... but you can "load" a column executing the appropriate Transact-SQL statement..

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.TestTB (

Id int NOT NULL PRIMARY KEY,

img varbinary(MAX) NULL

);

GO

PRINT 'retrieve the img from file';

DECLARE @.img varbinary(MAX);

SET @.img = (

SELECT Q.BulkColumn

FROM OPENROWSET(BULK 'D:\MVPLogo.gif', SINGLE_BLOB) AS Q

);

SELECT CONVERT(varchar(MAX),@.img);

PRINT 'then you can insert it into the desired table';

INSERT INTO dbo.TestTB VALUES ( 1 , @.img );

SELECT * FROM dbo.TestTB;

GO

DROP TABLE dbo.TestTB;

--<

retrieve the img from file

--

GIF89as ′ ? 4:u???jt￡CJ?K{abridged}

then you can insert it into the desired table

Id img

--

1 GIF89as ′ ? 4:u???jt￡CJ?K{abridged}

and that's it..

regards

Adding execute permission to stored procedures

Hi

I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.

For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.

In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.

Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.

If anyone can point me in the right direction on this...

I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?

Thanking you in advance

A db owner can execute, alternatively you could add server roles to the login such as sysadmin may be overkill though

Function SetLoginsDbOwner()Dim strcmdAsString

strcmd = "exec sp_addrolemember N'db_owner', N'MyLoginName_IWantSPExecutePerm'"

Dim dcAsNew OleDbConnection(connstringL)Dim cmdAsNew OleDbCommand(strcmd, dc)Try

dc.Open()

cmd.ExecuteNonQuery()

Catch exAs OleDbException

Label3.Text = ex.ToString()

Finally

dc.Close()

EndTryEndFunction

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right??|||See my post 3 posts up.

Adding data to columns

Hi

I'm am using SQL Server 2005 Express on my home PC.

To practice some of the SQL skills I learned on a course I was recently on, I am trying to build a mini database which I can run some queries from.

I have created some tables and added columns to these tables. However, I cannot find how to add data to these columns.

I'm sure it's pretty simple but I just can't find it! Can anyone help?

Cheers!

Hi,

use http://www.microsoft.com/downloads/details.aspx?FamilyID=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&DisplayLang=en

to do this with an administrative console, otherwise, code you own gui to put in the appropiate statements.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

MarkAdams,

If you want to practice SQL "by hand", you can use an insert instruction. For example :

INSERT INTO Members (Id, FirstName)

VALUES (1, 'Mike');

In this example, we have a table Members which contains 2 columns : Id and FirstName). The values to insert must be appear in the exact same order as the columns names listed.

If Id is an identity column used as a primary key, you don't enter any value by hand. So the insert instruction will look like :

INSERT INTO Members (FirstName)

VALUES ('Mike');

The SQL Server 2005 Management Studio Express application is a good tool to learn SQL, because you can use both graphical and script method to execute instructions.

Best regards.

Adding an image to details view

I'm some what of a noob when it comes to ASP.NET 2.0 and SQL Server 2005 Express editions, so please bare with me.

I have created a basic database that is only one table. One of the fields is LogPicture with type of image.

I just want to go into the table and manually place a picture in the table, but it seems like I can not get the format down. I have tried including the picture I want into the project, but no dice. I've also tried putting the physical pathname (on the harddrive) and the web address of this field in there. Suggestions please?

*bump*

I am basically just looking for the syntax of how an image is inserted. Anyone..... please?|||

Image data is stored as binary stream in SQL Server. You can either use bcp.exe utility to insert BLOB data (image/text/ntext) or write your own code to input binary stream to the column. You can take a look at this post:

http://forums.asp.net/thread/1257487.aspx

And here is a article that introduces bcp utility:

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

Adding a user to a database in SQL Server Management Studio Express?

Can someone provide me with instructions or a link on how to add a user to a database using SQL Server Management studio Express?

I also would like to know how you set the db_owner permission to the user?

My host has an sql express database and I would like to use the same connection string on my local machine, but need to set up the username/pw on the database locally.

Thanks

go to the security tab of the management express

in the object explorer. double click logins to expand

right click and then add new user

from there you can grant the user access to database.

you can also open the database to add new user to it when you

already created a login in the security tab