Creating DB from scripts: ive

I've seen several post asking for that possibility, but all 've read, didn't help me.

Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".

I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.
But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".
Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?

Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.

You are right you can do it with DMO(data management object) and SMO(sql server management objects) but it is better to use ADO.NET ExecuteNoneQuery to create the database and objects. Try the link below for code sample. Hope this helps.

http://www.c-sharpcorner.com/Code/2002/Mar/CreatingDBProgrammaticallyMCB.asp

|||

Sorry thats was for Windows Form try the link below for code from Microsoft.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305079

|||

Txh,

I've tried a similar excample and it did work for SQLServer. With SQLExpress I get an error, when opening the connection, something like "File not found".

What is the connection string for the SQLExpress? I replaces simple "Server=(local)" with "Server=.\\SQLEXPESS".

|||That code was talking about your server name, and the Master database that installs with every SQL Server. You are connecting to the Master database and file not found is not a valid error because the Master is always there. So try changing Express to your computer name. Hope this helps,|||

Sorry, if my english is so bad.

I wrote, that I've tried this excample, slightly changed and that it did work for SQL Server, but not SQLExpress.
Changing the server name to ".\SQLEXPRESS" didn't help. May be, because SQLExpress has no master database.

So, how to change this sample code that it works with SQLExpress?

|||

You cannot run SQL Server without the Master database, all your internal house keeping is done by the Master, I know I ran the Express in beta and it had a Master. And it must be your computer name and the Express for it to work YOUR_COMPUTER_NAME\SQLEXPRESS. Hope this helps.

I have to try and get my head around this question and ive never seen sql this advanced before.please set me in the right direction.thanks in advance.

Using the meeting table schema below provide a SQL statement to create a view in Microsoft SQL Server that will return only 1 record displaying the contents of the MEETING_NUMBER field. The rules for the view are as follows:
• The START_DATE field which contains the start date of the meeting record must contain a value before the current system date.
• The START_DATE field must be the closest date to the current system date.

ID int (4)
DOC_CLASS varchar (30)
DOC_NUMBER int (4)
REVISION int (4)
START_DATE datetime (8)
FINISH_DATE datetime (8)
MEET_TIME varchar (5)
VENUE varchar (50)
TYPE varchar (50)
MEETING_NUMBER varchar (11)
CUTOFF_PERIOD int (4)
CUTOFF_TIME varchar (5)
AGENDA_STATUS varchar (50)
SUBJECT varchar (50)
SPONSORING_MIN varchar (50)
SUPP_AGENDA_STATUS varchar (50)

Possible answer:
select meeting_number
from meeting
where start_date < getdate()
order by start_date descplease set me in the right direction.

Homework!!!!

the right direction is to

1. your notes
2. your course material
3. your teacher

then give the query a try yourself and we might offer comments if it isn't working|||its a exam q for a job im apllying to.I dont have a teacher or course work or i wouldnt have bothered to ask|||well, if sql is a requirement for the position you're applying for, you should be able to do this easily

can we see your attempt?|||It is part of the job....but since im starting at a graduate level ive never seen sql this advanced before.Im a Java programmer by college standards I suppose

Heres my attempt:

CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting
WHERE START_DATE < GETDATE()|||good start

that takes care of one criterion, now let's see if we can add the other one -- "The START_DATE field must be the closest date to the current system date"

this means there cannot be another row that is closer

hint: EXISTS subquery|||Will the getdate() function work without a declaration?|||not sure what you mean by "declaration" but i suspect the answer is yes|||I think he means, can you call getdate() in a where clause, or do you have to assign it to a variable first, and the use the variable in a where clause.

if that's what you meant, answer is yes.|||thats what i meant...sorry...im not used to sql syntax|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting
WHERE START_DATE < GETDATE()
AND EXISTS
(SELECT Meeting.MEETING_NUMBER
FROM Meeting
WHERE START_DATE < GETDATE()
ORDER BY START_DATE DESC)|||well, that's progress, i guess, although you still have a ways to go

let's have a look at that subquery

the subquery says "get all meetings that start before right now"

as long as there is at least one meeting that starts before right now, the EXISTS will evaluate true

so if there are any meetings that start before right now, your view will return all of them, and if there aren't, then it won't return any rows at all

does that make sense?|||I know i need a single value.A single date closest to the current date but will DISTINCT work?|||no, you will need MAX at some point

you want the latest date that's less than getdate()|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting
WHERE START_DATE < GETDATE()
AND EXISTS
(SELECT MAX(START_DATE)
FROM Meeting
WHERE START_DATE < GETDATE())|||okay, you're getting warm

your query now says "select all meetings that start earlier than today, provided there exists a meeting that is the latest meeting that starts earlier than today"

another hint: you want the meeting where there isn't one that's later than the one you're looking at

you'll need a correlated subquery for this purpose

are you sure you really want to apply for a job where you need to demonstrate sql skills?|||I know.I have to learn by doing I suppose|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND EXISTS
(SELECT MAX(START_DATE)
FROM Meeting m2
WHERE m1.MEETING_NUMBER = m2.MEETING_NUMBER)|||hint: NOT EXISTS a meeting with a greater date

you have EXISTS the latest meeting with the same number|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT MAX(START_DATE)
FROM Meeting m2
WHERE m1.START_DATE > m2.START_DATE)

Sorry wasnt payin attention to my last part|||The select list in an exists query doesn't add any value.
I will not solve this using an exist query but you can if you want to
Here are a few options
select top 1 meeting_number
from meeting
where start_date<getdate()
order by start_date desc

select top 1 a.meeting_number
from meeting a
,(select max(start_date) start_date
from meeting where start_date<getdate() ) b
where a.start_date=b.start_date

select top 1 meeting_number
from meeting
where start_date=
( select max(start_date) from meeting where start_date<getdate() )

select meeting_number
from meeting a
where start_date<getdate()
and not exists
(select 1 from meeting b
where start_date<getdate() and b.start_date>a.start_date )|||How bout this?

CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT MAX(START_DATE)
FROM Meeting
WHERE START_DATE < GETDATE())

I think I just went backwards|||no, that's not gonna get 'er done

your subquery says "where there is no meeting which is the latest meeting before today"

try saying what you want in english, and let's see if we can revert that back to sql

also, since you said there is "only minor sql needed" then why not just make something up and submit it -- if it isn't right, you can tell the examiner that the question was beyond beginner level (which it is!) and that if you ever run across the need to write any sql on the job, you know a forum where you can find a few guys to help you out|||the question was beyond beginner level (which it is!) I thought the same. You might want to sound these guys out because if it is indicative of the level of SQL they require you will have a pretty uncomfortable time explaining why you can't do the job should you get it. They aren't going to be content for you to spend three days writing a single statement.

No offense - I can't write a line of JAVA but I also wouldn't want my prospective employers to expect me to be able to write more than a "hello world" app either.|||I think this is what I want to say?

"select a meeting that start earlier than today, provided there doesn't exist a meeting that is earlier than the meeting selected"|||"... provided there doesn't exist a meeting that is earlier than the meeting selected"you are getting very, very close

what would happen if you substituted the word "later" for the word "earlier" in that sentence?

if you think i'm being picky, wait'll you see how picky SQL can get ...

:)|||"select a meeting that start earlier than today, provided there doesn't exist a meeting that is later than the meeting selected"

i was looking for a meeting earlier than the one selected rather than after the one selected......which would be closer to the current date.|||"select a meeting that start earlier than today, ..."that part you got okay, that's the first part of the WHERE clause

" ... provided there doesn't exist a meeting that is later than the meeting selected"so this would be your NOT EXISTS condition

"than the meeting selected" means you will be using a correlated subquery, you've done that part correctly too

"later than the meeting selected" is a simple comparison

and i'll save you a bit more time, the spec needs just a bit more tweaking...

" ... provided there doesn't exist a meeting that is later than the meeting selected and also less than today"|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT MAX(START_DATE)
FROM Meeting m2
WHERE START_DATE < GETDATE() AND m2.START_DATE > m1.START_DATE)|||i think that will do it

you don't actually need MAX in the subquery|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT START_DATE
FROM Meeting m2
WHERE m2.START_DATE > m1.START_DATE AND START_DATE < GETDATE())

Thanks a million|||My $.02 worth:

-- create the sandbox
create table meeting (meetid int identity, mdate datetime)
GO

-- populate the sandbox
declare @.i int, @.date datetime
select @.i = -5
while @.i < 5
begin
select @.date = dateadd(dd,@.i, getdate())
insert into meeting (mdate)
values(@.date)
set @.i = @.i + 1
end

-- check the contents of the sandbox
select * from meeting
GO

-- create the view
create view meetview as
select meetid, mdate
from meeting
where mdate =
(select max(mdate)
from meeting
where mdate < getdate())
GO

-- test the view
select meetid, mdate from meetview
GO

-- cleanup the sandbox
drop view meetview
GO
drop table meeting
GO|||CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT START_DATE
FROM Meeting m2
WHERE m2.START_DATE > m1.START_DATE AND START_DATE < GETDATE())In case you are interested - it doesn't matter what you select in the subquery.
This is exactly the same:
CREATE VIEW Meeting_View
AS
SELECT Meeting.MEETING_NUMBER
FROM Meeting m1
WHERE START_DATE < GETDATE()
AND NOT EXISTS
(SELECT '101 pootles' AS wakkwakkawahwah
FROM Meeting m2
WHERE m2.START_DATE > m1.START_DATE AND START_DATE < GETDATE())
.... not interested? Aw :o|||You can also use the TOP clause:

select top 1 m.MEETING_NUMBER
from Meeting where START_DATE < getdate()
order by START_DATE desc

... or have I missed any requirements?

Thursday, March 29, 2012

Creating an Alias for a (non-default) SQL Server 2005 Instance

Sunday, March 11, 2012

Creating a new database programmatically

Tuesday, February 14, 2012

Create View from specification (was "Help!")

Creating DB from scripts

Blog Archive

About Me