|
Hi there,
I'm trying to create a database programmatically using .Net3.5 and C#. I have this nice SQL-script which works fine in the SSMS editor, but now to try it for real in C#. I learned that the GO-statements were only for the SSMS editor, so I removed those. (am I right on that one?). What am I missing here?
Context: MSSQL 2008R2, VS2008, .Net3.5, WindowsXP.
Below is my script. The error I get is the following:
Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.
The source I use to execute this script is here. (removed the try/catch/finally blocks etc)
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string initDBQuery = string.Format(InstallationHelper.CreateDBScript(), settings.DatabaseLocation, settings.Database, settings.DatabaseUser, settings.DatabasePassword);
SqlCommand command = new SqlCommand(initDBQuery, conn);
command.ExecuteNonQuery();
This is the script (in C#-form):
string script =
@"
CREATE DATABASE [{1}] ON PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB );
LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
ALTER DATABASE [{1}] SET COMPATIBILITY_LEVEL = 100;
ALTER DATABASE [{1}] SET ANSI_NULL_DEFAULT OFF ;
ALTER DATABASE [{1}] SET ANSI_NULLS OFF ;
ALTER DATABASE [{1}] SET ANSI_PADDING OFF ;
ALTER DATABASE [{1}] SET ANSI_WARNINGS OFF ;
ALTER DATABASE [{1}] SET ARITHABORT OFF ;
ALTER DATABASE [{1}] SET AUTO_CLOSE OFF ;
ALTER DATABASE [{1}] SET AUTO_CREATE_STATISTICS ON ;
ALTER DATABASE [{1}] SET AUTO_SHRINK OFF ;
ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS ON ;
ALTER DATABASE [{1}] SET CURSOR_CLOSE_ON_COMMIT OFF ;
ALTER DATABASE [{1}] SET CURSOR_DEFAULT GLOBAL ;
ALTER DATABASE [{1}] SET CONCAT_NULL_YIELDS_NULL OFF ;
ALTER DATABASE [{1}] SET NUMERIC_ROUNDABORT OFF ;
ALTER DATABASE [{1}] SET QUOTED_IDENTIFIER OFF ;
ALTER DATABASE [{1}] SET RECURSIVE_TRIGGERS OFF ;
ALTER DATABASE [{1}] SET DISABLE_BROKER ;
ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ;
ALTER DATABASE [{1}] SET DATE_CORRELATION_OPTIMIZATION OFF ;
ALTER DATABASE [{1}] SET PARAMETERIZATION SIMPLE ;
ALTER DATABASE [{1}] SET READ_WRITE ;
ALTER DATABASE [{1}] SET RECOVERY SIMPLE ;
ALTER DATABASE [{1}] SET MULTI_USER ;
ALTER DATABASE [{1}] SET PAGE_VERIFY CHECKSUM ;
USE [{1}];
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [{1}] MODIFY FILEGROUP [PRIMARY] DEFAULT;
";
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
Helfdane wrote: I learned that the GO-statements were only for the SSMS editor, so I removed
those. (am I right on that one?).
Yap, that's correct.
Helfdane wrote: Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.
The semicolon is used to end a statement so in your database creation statement the LOG portion is part of the CREATE DATABASE statement. Try removing the extra semicolon from the first row:
CREATE DATABASE [{1}] ON PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
Haven't tried to execute a script using SqlCommand but hopefully it'll work. If it doesn't, you should split the statements to separate executions. Just a thought: From error-handling point of view it could better to use separate executions.
|
|
|
|
|
You're a lifesaver! Thanx a million!
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
No problem 
|
|
|
|
|
Yeah, what he said, plus I don't think the USE statement will work.
|
|
|
|
|
A while back I was trying to do the same, and several helpful souls told me it can't be done. Well, that just gets my dander up, so I went ahead and did it:
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
String str;
SqlConnection myConn = new SqlConnection
("Server=BAAL\\SQLEXPRESS2;Integrated security=SSPI ;database=master");
str = "CREATE DATABASE MyDatabase";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("Database Created Successfully", "MyApp",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyApp", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
}
I ran it once, then switched over to the server to check - sure enough, myDatabease was there, ready to be populated.
Your app will certainly need more, but it works, and should get you started. 
Will Rogers never met me.
|
|
|
|
|
Thanks!
A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)
|
|
|
|
|
Hi,
I'm designing an audit trail database to log user's activities. Should I create a single table to log all user activities e.g:
Id(PK) | UserId(FK) | ActionTime | ActionTaken
or, multiple tables; each per user e.g:
Table Name : AuditTrail_UserId
Id(PK) | ActionTime | ActionTaken
I'd like to be able to do 2 kind of text search though,
1. Search single user activities
2. Search all user activities
|
|
|
|
|
Just create one single table.
If you create a table per user the database will be unmaintainable.
Wout Louwers
|
|
|
|
|
Single table
This will do for now
|
|
|
|
|
Separating the tables per-user is not necessary. Make sure that you specify that the user ID is an index and the RDBMS will do all of the heavy work for you - making sure that internally the table is structures in such a way that queries such as:
select * from audit where UserID = 'someuserid'
are very rapid. It would be well worth examining the documentation on CREATE INDEX:
http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]
Careful usage of features such as these will give a much more performant solution that trying to roll your own equivalent (by separating into tables per user).
|
|
|
|
|
I think perhaps you replied to the wrong post?
This will do for now
|
|
|
|
|
eight wrote: 1. Search single user activities
2. Search all user activities
I don't see any reason to put them in separate tables. If it's the same data, with only a single different property, then it should go in the same table and the extra property becomes part of the key.
I are Troll 
|
|
|
|
|
|
I'm upgrading a MSDE SP4 Database consists of Tables containing Blob objects to SQL 2008 Express R2, during upgrade it goes on fine,and works after upgrade, but and after a while i get this microsoft error report.
Some unexpeceted errors have happened on the software you recently used.You were not asked to send the error reports at the time they occured.
Error Signature
Event Type : sqlexception001
Any clue ?
|
|
|
|
|
Wow, not even sure how to word the question... Here goes:
Basically, I'm trying to build a custom burn-down with TFS, Excel and SQL. Don't ask me why I'm doing a custom one when I shouldn't be, that's a question for my boss 
Anyhow, I have a query that's set up a few tables and now I want to join them. Here's an example of my two temp tables:
Tickets:
ID Changed date Points State
61 2011-02-25 06:38:07.090 0 For Production
61 2011-02-26 03:46:02.577 0 In Production
61 2011-03-04 03:22:32.620 0 Done
499 2011-03-04 04:26:10.060 0 New
623 2011-02-28 00:25:45.250 0.5 In Production
708 2011-03-03 00:55:31.407 3 In Development
708 2011-03-03 00:57:27.497 3 In Development
708 2011-03-03 03:55:17.390 3 In QA/UAT
708 2011-03-03 23:05:56.020 3 In QA/UAT
708 2011-03-04 05:21:43.133 3 In QA/UAT
738 2011-02-28 05:04:04.250 5 In Development
738 2011-02-28 22:56:58.053 5 In Development
738 2011-03-01 00:50:28.037 5 In Development
738 2011-03-01 21:06:35.550 5 In Development
738 2011-03-03 22:54:17.137 5 In QA/UAT
894 2011-02-28 00:29:04.183 0 To Do
Historical data in TFS is stored as a copy of the WorkItem row as it was BEFORE the change, along with a Changed Date field. In other words, the work item with id 3 has multiple copies in the WorkItemsWere table, each with a set of different properties, as can be seen above.
and Sprint Days:
Day Day of year
Mon 1 51
Tue 1 52
Wed 1 53
Thu 1 54
Fri 1 55
Mon 2 58
Tue 2 59
Wed 2 60
Thu 2 61
Fri 2 62
I am achieving my burn-down by joining these two tables. My results so far look like this:
Day Points Day of year
Mon 1 0 51
Tue 1 0 52
Wed 1 0 53
Thu 1 0 54
Fri 1 0 55
Mon 2 0 58
Tue 2 0 59
Tue 2 5 59
Wed 2 0 60
Wed 2 10 60
Thu 2 0 61
Thu 2 10 61
Fri 2 0 62
Fri 2 3 62
Fri 2 15 62
See, the problem is I am joining each row in my sprint days table against EVERY work item row with a changed date <= the sprint day, and then summing. Here's my query:
SELECT
[Day] AS [Sprint Day],
SUM(Points) AS Points,
[Day of year] AS [Order]
FROM
#sprintDays d
LEFT JOIN
(
SELECT
Id,
Points,
DATEPART(DAYOFYEAR, [Changed Date]) AS [Changed Date],
ROW_NUMBER() OVER
(
PARTITION BY
Id,
DATEPART(DAYOFYEAR, [Changed Date])
ORDER BY
DATEPART(DAYOFYEAR, [Changed Date]) DESC
) AS Row
FROM
#tickets
WHERE
([State] = 'Committed' OR
[State] = 'In Development' OR
[State] = 'In QA/UAT')
UNION
SELECT -- this is to ensure that something
0, -- comes out for any given day
0,
0,
1
) AS t
ON d.[Day of year] <= t.[Changed Date] -- this is the dodgy bit here, methinks
GROUP BY
t.Row,
t.Id,
[Day],
[Day of year]
HAVING
Row = 1
ORDER BY
[Order] ASC
What I REALLY need to do, is join against something like MAX(ChangedDate) WHERE ChangedDate <= SprintDay, i.e. the sum of the points of each row in #tickets that has both the highest (latest) ChangedDate and also has a ChangedDate that is smaller than the [Day of year] of the SprintDay row I am joining to.
Does that make any sense? Apologies if it doesn't, my brain is melting ever-so-slightly...
|
|
|
|
|
Hi,
First I must admit that I didn't follow the whole sequence, but if I understood correctly, you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates. If that's correct, could you simply use scalar in your select list to calculate a running total. Something like:
SELECT d.Day,
d.[Day Of Year],
(SELECT SUM(Points)
FROM #tickets t
WHERE t.[Changed Date] <= d.[Day Of Year]
...other possible restrictions...) AS Points
FROM #sprintDays d
|
|
|
|
|
Mika Wendelius wrote: you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates
Aha! That's the rub, see - not just the sum of points for all earlier dates, but the one, latest date found per individual id in a list of earlier dates... I will give your suggestion a go and see if I can tweak it to my needs.
Thanks for your help! If you have any more suggestions, throw them my way It's really hard to explain, I think
EDIT:
At the moment it feels like I need to partition again, but I need to partition on the [Changed Date] of the sprintDays table on "each loop" of the select in order to find the highest ranked item... Maybe I should use a FOR loop or something? I want to avoid it, though, surely there's a way to do this in "Plain Old SQL".
modified on Monday, March 7, 2011 6:22 PM
|
|
|
|
|
Hi,
I noticed that you solved the problem by using cursor, which is fine but fot performance reasons I think that if we could find a set-based solution it'd be best.
So if I understood correctly you need each individual ID in your result set and sums for them, so could the query be something like:
SELECT d.Day,
d.[Day Of Year],
(SELECT SUM(Points)
FROM #tickets t
WHERE t.[Changed Date] <= d.[Day Of Year]
AND t.Id = sub1.Id) AS Points
FROM #sprintDays d,
(SELECT DISTINCT t.Id
FROM #tickets t) sub1
Just correct me if If I'm going to wrong direction.
|
|
|
|
|
Fixed - I bit the bullet and used a cursor. Although, funny story, I couldn't figure out why the cursor was taking forever and the query plan was saying it was <2% of my processing time...
I wasn't fetching inside the loop. It was update the first row over... and over... and over...
*bashes head into desk*
|
|
|
|
|
Dear all,
my question sounds very simple and yes it is very simple.
what i am looking for just to copy data from one table to another table. i could write
insert into table2 ( select * from table1)
but in my case there is one database name MyDataBase.mdf and table name Customer
so now when user performs year ending (31st march) option then system should create new folder for new year for ex. 2011 if user is working in 2010.
so now in folder 2011 i have copy database which is MyDataBase.mdf
now i want to copy all the customers from Customer table(in 2010 folder) to Customer table (in 2011 folder)
so how can i copy data from same named database and same named table to same named database & table
insert into Customer(select * from Customer)
So can I differentiate insert Customer and select Customer table? I dont want to change table name in next year.
waiting for your kind help.
|
|
|
|
|
What can I say, your design sucks.
Why not do it from the other end like every other organisation out there. Backup the database at the end of the year (2010) and remove the data for 2009 from the production database. This way you maintain continuity, you don't have to do any dancing around database names.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What database? Sql Server? You can link the databases and then the INSERT/SELECT should work.
But I prefer to use a connection to each one, use a DataReader to read from the source, and a parameterized INSERT for the destination.
This allows me to report progress if the process is very long-running.
In other situations, using the DataReader allows me to log duplicates and referential integrity issues without stopping the whole process.
|
|
|
|
|
hi thanks for your rep.
so how can use datareder to get data from table any examples?
waiting for your kind rep.
|
|
|
|
|
Sorry for the cross/re post, but I really need a solution to this problem. So far I have had the question posted for two days with only 50 views and no hints or answers. I really do not want to have to go through the effort of sanitizing out any possible writes. I can not set the database to be readonly at the file level as there is another app that has to have read-write access. I would think there should be a way for SQLServer to simply refuse to do any write operations for a readonly connection, but I have not been able to figure it out so far.
http://www.codeproject.com/Questions/164065/Read-only-ADO-connection-still-allows-writing-to-d.aspx[^]
I wrote: I have connected to my SQL Server 2005 database by using ADO (through A set of ADO classes - version 2.20 by Carlos Antollini[^]). Now I have set the connection mode to be ReadOnly
CADODatabase *pDatabase = NULL;
pDatabase = new CADODatabase();
pDatabase->SetConnectionString(MyConnectionString);
pDatabase->SetConnectionMode(CADODatabase::connectModeRead);
pDatabase->Open();
SetConnectMode() calls ADODB::Connection15::PutMode
and CADODatabase::connectModeRead is adModeRead.
Now my understanding is that setting the adModeRead mode should make the database connection read-only. But a simple test of the connection shows that I can use the connection to create and drop tables, as well as insert and update data.
How can I make the connection truely read-only?
You may be right
I may be crazy
-- Billy Joel --
Within you lies the power for good - Use it!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
