create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

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

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

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

CREATE TABLE scripts

I have a bunch of CREATE TABLE scripts I need to run.

In Visual Studio 2003 you could right-click on a .SQL file and choose Run from the shortcut menu... and it would run the script. Visual Studio 2005 there is not a Run on the right-click short cut menu.

What gives?

hi,

why not just downloading the free official management tool, SQL Server Management Studio Express, from http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en ..

after installing it, the .Sql extension will be associated to this tool... very easy to use..

regards

|||

You should consider asking this question of the VS folks, they hang out over in the VS forums.

Mike

CREATE SUBCUBE in Microsoft Visual Studio for SQL2005

Hi,

I am trying to create a SUBCUBE in Microsoft Visual Studio (Analysis Services) but

It keeps on giving me an error when trying to build the Subcube.

The MDX statement is correct because I have tested it in SQL2005 Management Studio and it works.

My question is that “am I doing it in the right place?”:

This is under the Cubes Folder > Calculations Tab> New Script Command

My understanding is that under the “New Script Command” I can type any MDX statement and it should work? Or should this be done in another place?

When processing the cube the error I am getting back is:

Parser: The script contains the statement, which is not allowed

What I am trying to do is to create a slice of a cube by e.g. Brand which one of my departments need to use and should not see the other Brands. All the other dimensions and measures should still apply.

I would appreciate it if someone can please assist me with this.

Thank you in advance

Pieter Nelson

im no expert in olap cubes and am only tinkering with them for the last few months, but ive never heard of sub cubes. in that suituation, id either use a front end to create a report that filters out all but the brand your interested in, or create a seperate cube, based on the exact same data source view, but just import the brand you require. there may be a more elegant way, but those ways are pretty simple.

Regards,

Winston.

|||

Hi Pieter,

It sounds like you need to be using dimension security, not subcubes here. See
http://msdn2.microsoft.com/en-us/library/ms175366.aspx

for details on how to do this.

HTH,

Chris

|||

Unfortuanately I can not use Security or Another Measure Group.

I went into the Calculations Tab in Visual Studio and edited the Calculate Script that look like this:

-

CALCULATE;

CREATE SUBCUBE [Cube_AcqMgmtGF_Mthly] AS SELECT

{[All Brands].[All Brands].[Brand].&[Goldfishka B]} ON 0

FROM [Cube_AcqMgmtGF_Mthly];

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ave first Purchase]

AS '[Measures].[First Purch]/[Measures].[Usaccountno]',

FORMAT_STRING = "Currency",

VISIBLE = 1;

-

I am still trying to filter this cube to one specific brand, but the deployment keeps on failing with the following Description:

"An MDX Statement was expected. An MDX expression was specified" "The script contains the statement, which is not allowed"

To be honest here, I am now getting really confused.

SUMMARY:

Is my code in the right place "Create Subcube" or is there an alternative way of doing this.

PLEASE HELP ANYONE?

|||

Hi Pieter,

To be honest, I can't see anything wrong with your MDX (which service pack are you runnning?) but even if you could create a subcube in your MDX Script I don't think it's going to do what you want - even though I'm able to deploy a CREATE SUBCUBE command in an MDX Script it doesn't look like it does anything useful.

Why can't you use security or another measure group? Can you explain what you want to do in more detail?

Chris

|||

Hi,

I am trying to create subcubes(by Brands) from a Master cube (by only processing one cube), but the situation is that if I create a cube for every brand then it will be different processing for every cube. Unfortuanately these fact tables contains millions of records and processing all these records for every cube will take much longer. The other problem is that if there is changes in the logic of one fact table then I will have to apply these changes to all the other cubes individually.

Hope this helps?

|||

This sounds exactly what dimension security is intended for. Why do you say you can't use it?

Chris

Create SQL Server developer version database in app_data folder

Hi

I am trying to create a sql server database in the app_data folder of visual studio 2005. It keeps telling me I need the express version. Can I not use the developer version

Thanks

By default installation, SQL Server 2005 Developer version stores its databases' files in places like this (in my computer):C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The express version can save a data file in app_data folder to work with visual studio 2005 or VWD. You need different connection strings to access your database.

Here is a sample section of connection strings in a web.config file to access databases (developer version):

<connectionStrings>

<addname="MSDN_forumConnectionString"connectionString="Data Source=localhost;Initial Catalog=MSDN_forum;Integrated Security=True"

providerName="System.Data.SqlClient" />

<addname="NorthwindConnectionString"connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

|||

Hi

Thankyou for your response. I have no problem connecting to a sql server deveoper version . The problem I have is when I right click app_data foler and add new item & choose databse I get the following error

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.

I dont want to use the Express version but the developer version. Hope this make it a bit more clear

Thanks

|||

Hi,

Just as Limno said, if you want to create your database in app_data folder of Visual Studio 2005, you have to install the SQL Server Express edition because other editions of SQL Server does not support attaching database files automatically at runtime.

Thanks.

|||

Many thanks for your answer. I have now installed sql server express

Create reporting services project in VS 2003

I've installed instance of SQL Server 2005 with reporting services on
my Windows 2003
computer .I've had Visual studio 2003 .After installation i can't
create new reporting
service project in my VS 2003 IDE,but i have now VS 2005 with only
reporting services
project types evalible.I need to work on reporting services in VS
2003.Please tell me how
can i do this.
ThanksHi,
I dont think it is possible in VS 2003. If you have installed Sql server
2005 and their tools you can go through "SQL Server Business Intelligent
studio" you can do report creation.
Regards
Amarnath
"gbletel@.gmail.com" wrote:
> I've installed instance of SQL Server 2005 with reporting services on
> my Windows 2003
> computer .I've had Visual studio 2003 .After installation i can't
> create new reporting
> service project in my VS 2003 IDE,but i have now VS 2005 with only
> reporting services
> project types evalible.I need to work on reporting services in VS
> 2003.Please tell me how
> can i do this.
> Thanks
>

Create project/stored procedure for SQLCRL

I installed SQLServer 2005 Standard Edition and tried to created a
stored procedure in VB. From START/PROGRAMS/MICROSOFT VISUAL STUDIO
2005, I created a blank solution. What type of project shall I create
for creating a stored procedure in VB ?

I tried to install SQL Server again in case I left back some parts, but
I got a message that all parts were installed.On 7 Jun 2006 00:37:56 -0700, Chris wrote:

>I installed SQLServer 2005 Standard Edition and tried to created a
>stored procedure in VB. From START/PROGRAMS/MICROSOFT VISUAL STUDIO
>2005, I created a blank solution. What type of project shall I create
>for creating a stored procedure in VB ?
>I tried to install SQL Server again in case I left back some parts, but
>I got a message that all parts were installed.

Hi Chris,

Create a "database" project, using the "SQL Server Project" template.
After that, you can choose the "Project" / "Add Stored Procedure" menu
choice to add a CLR stored procedure to your project.

--
Hugo Kornelis, SQL Server MVP|||Thanks a lot, Hugo.

Create Linked Report method

Does anyone have a sample of a CreateLinkedReport method they are using? I am using Reporting Services 2005 and Visual Basic 2005. I need the VB code and how I need to call it. I'd like to change one parameter, and then send it to select recipients using a specified schedule.

Thanks, Iris

Is there anything wrong with the BOL sample code?|||

I'll try that example.

Also, when I try to click Create Linked Report from a master report that I created, I get an internal error. In SS SP2, it shows that this problem was fixed. When I installed SP2 today, it did not fix the problem. I am using a Stored Procedure with one parameter of int value.

Any ideas?

Thanks, Iris

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>|||Thanks for the reply, John. The code creates a db for each chapter of the
book and brings in the data and log files for each chapter. It would seem
that the code from the book should run or else I could find lots of compaints
around the web. Since I'm new to the game of SQL Server, I wanted to
discover what missteps that I am taking. Could you look at the code for me
to see if there is any unmentioned setup that I need to do before running
this? Here's the chapter 2 sample:
--CreateSampleDB
--Execute statements from the master database.
USE master
GO
--Drop any prior version of Chapter02 database.
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = N'Chapter02')
DROP DATABASE Chapter02
GO
--Create new version of Chapter02 database.
CREATE DATABASE Chapter02
ON
(NAME = Chapter02_dat,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
SIZE = 1)
LOG ON
(NAME = Chapter02_log,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
SIZE = 1,
MAXSIZE = 5)
GO
"John Bell" wrote:
> Hi
> It sounds like you should be attaching the files rather than creating them?
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> > I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> > now,
> > I'm doing lots of crying.) When I try to create the database required for
> > exercises, I receive the error:
> >
> > "Server: Msg 5105, Level 16, State 2, Line 3
> > Device activation error. The physical file name
> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >
> > I copied the physical file name location from Windows Explorer so it
> > should
> > be ok.
> > I checked the MSKB for any bugs and worked around all that are listed
> > including the default directory malady.
> >
> > Any ideas on how to install the database?
> >
>
>|||The create database statement looks fine, provided of course that the file
named in the create db does NOT exist at the time you are creating the db.
Create database will try and create the underlying database files, so like
John if the book comes with example database files I would expect to copy
them off the cd from the book and then attach them using sp_attachdb. Have
you a file of the name used in the create database statement already?
Mike John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Hi
This version of the create database statement would error if the file
existed, but your error seems to imply
that the directory does not exist or that your permissions are incorrect.
Even if the file is read only (which you should change if you copied it from
CD!) the file existing would give the same error.
Check out sp_attach_db in Books online.
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Thanks all for the thoughts. I discovered script below that attaches the
files (unfortunately, I still receive the same Msg 5105 that started this
thread).
I double-checked the mdf and ldf files and they are not set to read only;
however, the file folder is read-only and will not change. Ideas?
New script:--
--AttachSampleDB
--Run the script from the master database.
USE master
--Update the paths for the data and log files so they
--are appropriate for your computer.
EXEC sp_attach_db @.dbname = N'Chapter02',
@.filename1 = N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
@.filename2 = N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
"John Bell" wrote:
> Hi
> This version of the create database statement would error if the file
> existed, but your error seems to imply
> that the directory does not exist or that your permissions are incorrect.
> Even if the file is read only (which you should change if you copied it from
> CD!) the file existing would give the same error.
> Check out sp_attach_db in Books online.
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> > Thanks for the reply, John. The code creates a db for each chapter of the
> > book and brings in the data and log files for each chapter. It would seem
> > that the code from the book should run or else I could find lots of
> > compaints
> > around the web. Since I'm new to the game of SQL Server, I wanted to
> > discover what missteps that I am taking. Could you look at the code for
> > me
> > to see if there is any unmentioned setup that I need to do before running
> > this? Here's the chapter 2 sample:
> >
> > --CreateSampleDB
> > --Execute statements from the master database.
> > USE master
> > GO
> >
> > --Drop any prior version of Chapter02 database.
> > IF EXISTS (SELECT *
> > FROM INFORMATION_SCHEMA.SCHEMATA
> > WHERE CATALOG_NAME = N'Chapter02')
> > DROP DATABASE Chapter02
> > GO
> >
> > --Create new version of Chapter02 database.
> > CREATE DATABASE Chapter02
> > ON
> > (NAME = Chapter02_dat,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > SIZE = 1)
> > LOG ON
> > (NAME = Chapter02_log,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> > SIZE = 1,
> > MAXSIZE = 5)
> > GO
> >
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> It sounds like you should be attaching the files rather than creating
> >> them?
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> > Microsoft
> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> >> > now,
> >> > I'm doing lots of crying.) When I try to create the database required
> >> > for
> >> > exercises, I receive the error:
> >> >
> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> > Device activation error. The physical file name
> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >
> >> > I copied the physical file name location from Windows Explorer so it
> >> > should
> >> > be ok.
> >> > I checked the MSKB for any bugs and worked around all that are listed
> >> > including the default directory malady.
> >> >
> >> > Any ideas on how to install the database?
> >> >
> >>
> >>
> >>
>
>|||Hi
Both the create and the attach scripts assume you have a directory called
C:\SQLVBNET\Chapter_02, in which the new database files will be put.
Do you have this directory already?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> Thanks all for the thoughts. I discovered script below that attaches the
> files (unfortunately, I still receive the same Msg 5105 that started this
> thread).
> I double-checked the mdf and ldf files and they are not set to read only;
> however, the file folder is read-only and will not change. Ideas?
> New script:--
> --AttachSampleDB
> --Run the script from the master database.
> USE master
> --Update the paths for the data and log files so they
> --are appropriate for your computer.
> EXEC sp_attach_db @.dbname = N'Chapter02',
> @.filename1 => N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> @.filename2 => N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
>
> "John Bell" wrote:
>> Hi
>> This version of the create database statement would error if the file
>> existed, but your error seems to imply
>> that the directory does not exist or that your permissions are incorrect.
>> Even if the file is read only (which you should change if you copied it
>> from
>> CD!) the file existing would give the same error.
>> Check out sp_attach_db in Books online.
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
>> > Thanks for the reply, John. The code creates a db for each chapter of
>> > the
>> > book and brings in the data and log files for each chapter. It would
>> > seem
>> > that the code from the book should run or else I could find lots of
>> > compaints
>> > around the web. Since I'm new to the game of SQL Server, I wanted to
>> > discover what missteps that I am taking. Could you look at the code
>> > for
>> > me
>> > to see if there is any unmentioned setup that I need to do before
>> > running
>> > this? Here's the chapter 2 sample:
>> >
>> > --CreateSampleDB
>> > --Execute statements from the master database.
>> > USE master
>> > GO
>> >
>> > --Drop any prior version of Chapter02 database.
>> > IF EXISTS (SELECT *
>> > FROM INFORMATION_SCHEMA.SCHEMATA
>> > WHERE CATALOG_NAME = N'Chapter02')
>> > DROP DATABASE Chapter02
>> > GO
>> >
>> > --Create new version of Chapter02 database.
>> > CREATE DATABASE Chapter02
>> > ON
>> > (NAME = Chapter02_dat,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
>> > SIZE = 1)
>> > LOG ON
>> > (NAME = Chapter02_log,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
>> > SIZE = 1,
>> > MAXSIZE = 5)
>> > GO
>> >
>> >
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> It sounds like you should be attaching the files rather than creating
>> >> them?
>> >>
>> >> John
>> >>
>> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> >> > Microsoft
>> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
>> >> > right
>> >> > now,
>> >> > I'm doing lots of crying.) When I try to create the database
>> >> > required
>> >> > for
>> >> > exercises, I receive the error:
>> >> >
>> >> > "Server: Msg 5105, Level 16, State 2, Line 3
>> >> > Device activation error. The physical file name
>> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >> >
>> >> > I copied the physical file name location from Windows Explorer so it
>> >> > should
>> >> > be ok.
>> >> > I checked the MSKB for any bugs and worked around all that are
>> >> > listed
>> >> > including the default directory malady.
>> >> >
>> >> > Any ideas on how to install the database?
>> >> >
>> >>
>> >>
>> >>
>>|||Thank you all for your help. As my name dictates I am really new to the game
and discovered that I transposed the directory names. I feel quite foolish.
Kalen's simple answer made me realize my grievous error.
Thank you.
"Kalen Delaney" wrote:
> Hi
> Both the create and the attach scripts assume you have a directory called
> C:\SQLVBNET\Chapter_02, in which the new database files will be put.
> Do you have this directory already?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> > Thanks all for the thoughts. I discovered script below that attaches the
> > files (unfortunately, I still receive the same Msg 5105 that started this
> > thread).
> >
> > I double-checked the mdf and ldf files and they are not set to read only;
> > however, the file folder is read-only and will not change. Ideas?
> >
> > New script:--
> > --AttachSampleDB
> > --Run the script from the master database.
> > USE master
> >
> > --Update the paths for the data and log files so they
> > --are appropriate for your computer.
> > EXEC sp_attach_db @.dbname = N'Chapter02',
> > @.filename1 => > N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > @.filename2 => > N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> This version of the create database statement would error if the file
> >> existed, but your error seems to imply
> >> that the directory does not exist or that your permissions are incorrect.
> >> Even if the file is read only (which you should change if you copied it
> >> from
> >> CD!) the file existing would give the same error.
> >>
> >> Check out sp_attach_db in Books online.
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> >> > Thanks for the reply, John. The code creates a db for each chapter of
> >> > the
> >> > book and brings in the data and log files for each chapter. It would
> >> > seem
> >> > that the code from the book should run or else I could find lots of
> >> > compaints
> >> > around the web. Since I'm new to the game of SQL Server, I wanted to
> >> > discover what missteps that I am taking. Could you look at the code
> >> > for
> >> > me
> >> > to see if there is any unmentioned setup that I need to do before
> >> > running
> >> > this? Here's the chapter 2 sample:
> >> >
> >> > --CreateSampleDB
> >> > --Execute statements from the master database.
> >> > USE master
> >> > GO
> >> >
> >> > --Drop any prior version of Chapter02 database.
> >> > IF EXISTS (SELECT *
> >> > FROM INFORMATION_SCHEMA.SCHEMATA
> >> > WHERE CATALOG_NAME = N'Chapter02')
> >> > DROP DATABASE Chapter02
> >> > GO
> >> >
> >> > --Create new version of Chapter02 database.
> >> > CREATE DATABASE Chapter02
> >> > ON
> >> > (NAME = Chapter02_dat,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> >> > SIZE = 1)
> >> > LOG ON
> >> > (NAME = Chapter02_log,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> >> > SIZE = 1,
> >> > MAXSIZE = 5)
> >> > GO
> >> >
> >> >
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> It sounds like you should be attaching the files rather than creating
> >> >> them?
> >> >>
> >> >> John
> >> >>
> >> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> >> > Microsoft
> >> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
> >> >> > right
> >> >> > now,
> >> >> > I'm doing lots of crying.) When I try to create the database
> >> >> > required
> >> >> > for
> >> >> > exercises, I receive the error:
> >> >> >
> >> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> >> > Device activation error. The physical file name
> >> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >> >
> >> >> > I copied the physical file name location from Windows Explorer so it
> >> >> > should
> >> >> > be ok.
> >> >> > I checked the MSKB for any bugs and worked around all that are
> >> >> > listed
> >> >> > including the default directory malady.
> >> >> >
> >> >> > Any ideas on how to install the database?
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Create database within SSIS package

I want to create a package that imports data from a Visual Foxpro database to SQL Server 2005 Express database. I used the wizard in BI Development Studio (similar to the DTS in SQL Server 2000) to create a package and noticed that the SQL statements created in the Preparation SQL Task only has code for creating tables. I want to make the package such that it first creates the destination database before creating all the related tables in it! When I tried to edit the SQL code to include DROP DATABASE and/or CREATE DATABASE statements, these were rejected.

Is it possible to do this or do I have to first create the database outside the package and then call the package? I want to make this a seamless process for clients who do not have the know-how of SQL Server database administration.

HELP!!!!!!

Can't you just run a CREATE DATABASE DDL statement in a execute sql task?

|||

Peter K wrote:

Can't you just run a CREATE DATABASE DDL statement in a execute sql task?

yes. i'm afraid that the import/export wizard won't be sufficient to solve this problem.

Create Database with Visual Basic (Urgent)

can i run such a transact SQL script with VB

use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go

Have you tried calling Osql.exe and passing the tsql as a parameter ?|||Yes you can use the TSQL script and run it in VB as a command|||

You cannot execute the GO, as it is a batch seperator defined by the SQL Server tools. You will have to set the context in your connection command, or simply run it in two different batches.

But you should be able to run any T-SQL command from VB.

|||

Use ADO in VB6, ADO.net with VB.net.

As previously mentioned by MSVP, run the db creation in your command text object

Adamus

|||

Ayhan Yerli wrote:

can i run such a transact SQL script with VB

use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go

Wait...you can't USE a db to CREATE a db?

What are you trying to do?

Adamus

Create database with username and password

Hello!

I can't figure it out how to create a database with an username and a password. I'm using Visual Studio 2005 and Microsoft SQL Server 2005.

Can someone help me out?

Greetings,
Persyn Bert.

u need to use CREATE LOGIN & CREATE USER....for more check

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

also,i think need to go thru the SQL server security model once...

http://www.developer.com/tech/article.php/721441

.....its an old article but still will gv a very clear picture

|||

Hello Imran Nathani!

Thank you for responding, but I what I really want to know is how tosecure a database with a username and password, not creating users in a table of a database.

Greetings
Persyn Bert

|||i think thats what i have given...............|||

Oops, I'm very sorry...

Where do I past the code for execution?

Greetings
Persyn Bert

|||they can be excuted in the query analyzer....can be executed from code behind...they are to be treated as queries (CREATE is an SQL clause)|||

I appreciate your help!

Greetings
Persyn Bert

CREATE DATABASE permission denied in database 'master'

Hi,

I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.

I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"

I created another windows user "TestUser" (Limited account) in the same physical PC.

I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']

At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.

I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.

SO HERE ARE MY QUESTIONS :

1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?

2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)

Thanks in advance for all you help.

It seems there are few things going on here. Let's take a moment and break each one down.

First, the account used to install SQL Server is normally a System Administrator. What's more important are the accounts used to start the services for SQL Server. You have a few choices there, but most often it's best to use a regular account, rather than LocalSystem or NetworkingSystem. You can find out more about that in Books Online searching for "Services" . When you install SQL Server, by default the local Windows Administrator's group is placed in the SQL Server sysadmin Role, which allows all rights for everything. Other users don't have access at all (yet).

Security inside SQL Server is independent of the installation or the startup accounts. Since the "Glen" account is a local administrator, he can do anything he wants in SQL Server. If you created a "TestUser" server login, they can connect, but they can't do anything else. You'll need to assign them a database, create a user in the database tied to the "TestUser" login, and grant rights there.

There are server-level rights, and database-level rights. The CREATE DATABASE statement is a server-level right, and most users don't need that.

Books Online has a great set of topics on SQL Server Security that will help you sort all this out. You can also see my articles on Security starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=35&rl=1

Buck Woody

|||

Thanks for a quick reply Buck.

I have another question related to your answer. You mentioned about "LocalSystem" or "NetworkingSystem".

Are you pertaining to the Log On tab section "Log on as:" found in the SQL Server (SQLEXPRESS) Properties in the SQL Server Configuration Manager? Are you advising me to select "This account:" and create a user from there?

I will read more on the Online Books at the same time. This is to know the database that I need to assign to "TestUser". I am guessing here if you are talking about the application database or the database originally in the SQLEXPRESS like the master, model, etc.

Thanks again.

|||

That's right. You can also set that in the Services applet of the Control Panel.

The application database is the only one that needs a user account, in addition to a server login. If you check that site on InformIT, you'll see a reference to those.

Buck

Create Database on a Virtual Drive (Created with subst) Failed

When I try to add a (SQL Server 2005 Express Edition) database to my project (I'v tried a windows application and an ASP.NET application) in Visual Studio 2005 Professional Edition; It fails with an error like this :
"create database failed. some file names listed could not be created (...)"
but when I open the project from the real path It works.
Is this a bug? Is there any solution? (In many situations there is a need for working with virtual drives. There must be some work around ...)

Thanks

SQL Server does not recognize OS level 'mapped' drives.

You must use a 'actual' drive, or a SAN/NAS lun(drive).

Create custom template in VS 2003 for SQL 2005 Reporting Service

Hello,
I'd like to create custom templates in Visual Studio 2003 when creating
reports for SQL 2005 RS, but I'm unable to find anything online that shows
how to do this. VS2003 has about 5 generic templates available, but it'd be
nice to have a template customized to our corporate colors and logo. Is
this possible'
Thanks --
Alexyes, create blank report with the formatting you want and copy the resulting
rdl file to the c:\program files\microsoft visual studio 8\common
7\ide\privateassemblies\projectitems\reportproject folder. this will add a
new template to the New Item dialog box.
"Alex" <samalex@.gmail.com> wrote in message
news:OJRpgup2HHA.2064@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I'd like to create custom templates in Visual Studio 2003 when creating
> reports for SQL 2005 RS, but I'm unable to find anything online that shows
> how to do this. VS2003 has about 5 generic templates available, but it'd
> be nice to have a template customized to our corporate colors and logo.
> Is this possible'
> Thanks --
> Alex
>