How to Schedule Backup for SQL Server 2005 Express

How do you schedule to backup database in SQL Server 2005 Express? I tried
following the steps mentioned at http://support.microsoft.com/kb/q241397, but
there are errors .
Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
myBackupScript.sql -n, I received timeout error, telling me that it might be
that my server doesn't allow remote access. So, I open SQL Client Tools, and
run the SQL statements. Then it told me that all of the mentioned stored
procedures does not exist.
Please help> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access.
Use Surface Area Configuration to enable remote access.
> So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
name.
But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access. So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Then is there no way to schedule backup? If that's so the log file will very
soon reach its limit and everyone won't be able to use the database, am I
right?
"Tibor Karaszi" wrote:
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access.
> Use Surface Area Configuration to enable remote access.
>
> > So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
> name.
> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
> Express.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> > How do you schedule to backup database in SQL Server 2005 Express? I tried
> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> > there are errors .
> >
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> >
> > Please help
>
>|||Hi,
To allow remote access remove the database name from namedpipes in
network configuration.All the stored procedure exists in master
database.
U can schedule them as u schedule it in sql server2000. butu have to
use master.storedprocedure name.
HTH
from
Doller|||>remove the database name from namedpipes in network configuration?
Under NamedPipes, it's either enable it or disable it. What do you mean by
removing the name?
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
I run sql statements at master, and msdb, but it returns me this message,
"SQLServerAgent is not currently running so it cannot be notified of this
action." But there is no SQLServerAgent for this version.
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
No! Express doesn't come with Agent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1145431836.282134.66530@.v46g2000cwv.googlegroups.com...
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> Then is there no way to schedule backup?
You can use the scheduler that comes with Windows. Together with SQLCMD.EXE, for instance.
> If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
Well, you can set the database to simple recovery. But of course, there are other more important
reasons to do backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run them, or qualifying the
>> proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I tried
>> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> >
>> > Please help
>>|||Here are some links you might find useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexcustapp.asp
Embedding Express in Apps
--
Andrew J. Kelly SQL MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Cool, Jasper!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
> Automating Database maintenance in SQL 2005 Express Edition Part I
> http://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part II
> http://www.sqldbatips.com/showarticle.asp?ID=29
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>|||Way cool. If you want to combine this with internal scheduling that doesn't
use the Windows scheduler, you could replace the brain-dead backup command
with your SP in this:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uJaD788YGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Cool, Jasper!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
>> Automating Database maintenance in SQL 2005 Express Edition Part I
>> http://www.sqldbatips.com/showarticle.asp?ID=27
>> Automating Database maintenance in SQL 2005 Express Edition Part II
>> http://www.sqldbatips.com/showarticle.asp?ID=29
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com|||Express databases by default use the SIMPLE recovery model so there
shouldn't be a problem with log files filling up unless you change this.
This DOESN'T mean you don't have to back up SQL Express databases - only
that log file backups probably aren't an issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will
> very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run
>> them, or qualifying the proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make
>> sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I
>> > tried
>> > following the steps mentioned at
>> > http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access. So, I open SQL Client
>> > Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> >
>> > Please help
>>

How to Schedule Backup for SQL Server 2005 Express

How do you schedule to backup database in SQL Server 2005 Express? I tried
following the steps mentioned at http://support.microsoft.com/kb/q241397, bu
t
there are errors .
Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
myBackupScript.sql -n, I received timeout error, telling me that it might b
e
that my server doesn't allow remote access. So, I open SQL Client Tools, and
run the SQL statements. Then it told me that all of the mentioned stored
procedures does not exist.
Please help> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
be
> that my server doesn't allow remote access.
Use Surface Area Configuration to enable remote access.

> So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
The procedures are in the msdb database, so you need to be there to run them
, or qualifying the proc
name.
But Express doesn't come with Agent, so the steps in this KB doesn't make se
nse to me for 2005
Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
be
> that my server doesn't allow remote access. So, I open SQL Client Tools, a
nd
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Then is there no way to schedule backup? If that's so the log file will very
soon reach its limit and everyone won't be able to use the database, am I
right?
"Tibor Karaszi" wrote:

> Use Surface Area Configuration to enable remote access.
>
> The procedures are in the msdb database, so you need to be there to run th
em, or qualifying the proc
> name.
> But Express doesn't come with Agent, so the steps in this KB doesn't make
sense to me for 2005
> Express.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>
>|||Hi,
To allow remote access remove the database name from namedpipes in
network configuration.All the stored procedure exists in master
database.
U can schedule them as u schedule it in sql server2000. butu have to
use master.storedprocedure name.
HTH
from
Doller|||>remove the database name from namedpipes in network configuration?
Under NamedPipes, it's either enable it or disable it. What do you mean by
removing the name?
"doller" wrote:

> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
I run sql statements at master, and msdb, but it returns me this message,
"SQLServerAgent is not currently running so it cannot be notified of this
action." But there is no SQLServerAgent for this version.
"doller" wrote:

> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
No! Express doesn't come with Agent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1145431836.282134.66530@.v46g2000cwv.googlegroups.com...
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> Then is there no way to schedule backup?
You can use the scheduler that comes with Windows. Together with SQLCMD.EXE,
for instance.

> If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
Well, you can set the database to simple recovery. But of course, there are
other more important
reasons to do backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...[vbcol=seagreen]
> Then is there no way to schedule backup? If that's so the log file will ve
ry
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>|||Here are some links you might find useful:
http://msdn.microsoft.com/library/d...r />
rview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/defau... />
erinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/expre...qlexcustapp.asp
Embedding Express in Apps
Andrew J. Kelly SQL MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help

how to save xml document in database table?

I have a db in Sql Server Express 2005. Into this db I have a table with an XML field and I want to save an XML document into this field through SQL.
Any suggestion to do this operation?
Thank you

Mirko

There is a nice article about XML fields and SQL server.

http://www.developer.com/db/article.php/3565996

However there is a problem on that article I'll paste the insert statements here:

INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')

|||

Thank you!

Mirko

how to save xml document in database table?

I have a db in Sql Server Express 2005. Into this db I have a table with an XML field and I want to save an XML document into this field through SQL.
Any suggestion to do this operation?
Thank you

Mirko

There is a nice article about XML fields and SQL server.

http://www.developer.com/db/article.php/3565996

However there is a problem on that article I'll paste the insert statements here:

INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')

INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')

|||

Thank you!

Mirko

how to save stored procedures on sql express server

Hi Guys

I have visual web developer and sqlexpress 2005 installed on my windows XP pro.

I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.

When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.

so, could you enlighten me little please.

thanks
Cemal

hi Cemal,

Cemal wrote:

Hi Guys

I have visual web developer and sqlexpress 2005 installed on my windows XP pro.

I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.

When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.

the "disk" button states to actually save the text inside the query window... you can type (or cut&paste) some text, whatever thext in whatever language, and you can later save that text into a file...

when you have to create a stored procedure, you have to execute the data definition language statements defining that object.. so, say you have a text like

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_my_stored_procedure]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_my_stored_procedure]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_my_stored_procedure] AS BEGIN /**/ /*Author:xxx yyy zzz*/ /*Date:14/07/2007*/ /*Modified:__/__/_*/ /**/ /*Please report suggestions/comments/bugs/feedback to:*/ /*me@.me.com*/ /**/ /* - */ /*this procedure perform these tasks...*/ /**/ DECLARE @.msg varchar(1000); BEGIN TRY SET NOCOUNT ON; SELECT col_list FROM [dbo].[my_table]; RETURN 0; END TRY BEGIN CATCH -- returns the occured exception DECLARE @.ErrorMSG varchar(2000); SET @.ErrorMSG = ERROR_MESSAGE() RAISERROR (@.ErrorMSG, 16, 1); RETURN -100 END CATCH END; GO

you have to execute it in order to "save" it within the database it belongs... to execute it, press the F5 key or the toolbarbutton with the "! Execute" mark...

regards

|||As this is a common misunderstanding, I once did a screencast for that, available on my site:

How to alter a stored procedure within SSMS: Difference between saving and executing a modified stored procedure

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to run ssis in sql 2005

I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?? Integration Services isn't included with any edition of SQL Server 2005 Express, as far as I am aware. See http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx for further information. See the Integration and Interoperability section of the following URL, http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx, for more specific information. Andrew Watt [MVP] <vhrao@.discussions.microsoft.com> wrote in message news:fc88e4bb-cb03-4ba7-8d14-e19bd3a2fd39@.discussions.microsoft.com...I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?|||SSIS is not part of SQL Express, it is included in higher editions.

How to run SQL-Server on a Webserver!

Hi!

Today I decided to finally install MS SQL 2005 Express on my VPS. For a long time I tried to work with MS Access what definitely brings a lot of complications.

I already installed SQL05Exp on my local machine and everything works great here. Because I'm quite new to these things I simply installed SQL05Exp on the Server the same way like locally. I just clicked through the installation with the predefined settings and everything installed without troubles.

When I uploaded my first Application and tried to run it, I got the following error-message:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

I then read somewhere that I could change the web.config connectionstring to "User Instance=False" what leads to the following:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I read a lot of documents about how to install, etc. but it couldn't really use it for me, surely for a lake of basics. When I understand the problem correctly, the user running the app on the server and trying to attach the mdf-database to the sql-server has not the rights to do that. However I don't know what to do against that.

What I would really love is a simple solution that allows me to connect to the sql-server from whatever domain on the server, just by copying the mdf. It also would be nice to use some kind of password in the connectionstring to assure that just my apps can connect to my databases.

I would be very thankful if someone could give me some good basic explanation what to do and maybe also give me a reference to some nice and understandable informations on this.

Best regards,

Markus

In most cases, the error is exactly what the error message says, but most people ignore what it's saying, so I'll break it down:

Error Description
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed.

Possible causes
1)database with the same name exists
2)specified file cannot be opened,
3)or it is located on UNC share

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

|||

Hi Motley, thanks for your help.

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

There is not even one database installed until now, however I tried to change the name but everything is the same.

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

It was running as Network-Service, I also tried as Local Service and as Local System, always the same result.

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

It is located within the App_Data folder, everything is on the same machine.

What else could it be?

Markus

|||

Did you check the second half of Fix#2? Most likely you need to make sure the user(Network Service) has write/read permissions to the folder (or to the database directly, if you assign permission database by database).

If you use window authentication, you may need assign window login user with write/read permission to this folder too.

|||

Now I tried to assign the user everyone with all possible rights to both the master database located in the installation folder of sql server and I did the same on both databases of my app.

Everything stays the same. What else could cause this errors?

Markus

|||

I didn't mean the user everyone. Try this one (yourMachinesName\theLoginUser) and Network Service (win2k3 IIS 6.0).

to App_Data with write/read permissions.

Here is a link you can check the basics.

Tell you the truth, I went through this one alittle while ago. What I can remember is the security settings. Since you mention this problem, I am curious that how did you access your application? through VWD on the server or through a virtual directory under IIS 6.0? It may help us to understand why the problem didn't go away.

If you are using IIS to access your application, there are some other settings to adjust like Application should run on asp.NET 2.0 , connction string in your web.config modification ...

Hope we can get the application run soon.

|||

Dear limno, thanks for your help.

Yesterday I assigned the user everyone because what I know this is like setting access for all the existing users. It is a quick way to test if it has something to do with rights, after that I can search for the right user which to give access to.

However, I did exactly like you told me, I gave the network service user and the user administrator which i us to log on full rights but the problem stays the same.

Not sure if I understand your second question. Locally I'm using VWD with SQL Express and it works fine. But on my VPS it's not working. Means if I call my website through the browser I get this error-message.

Markus

|||Change the anonymous user accout for the application to you. You can't log in as any of the accounts you mentioned, so they won't have a user directory (C:\Documents and Settings\{User Name}).|||

The Anonymous user of the application is gsp-peru and it also has a user directory. I tried to change it to administrator which changed nothing. Hope I understood your idea.

Markus

|||

If there are any more hints or ideas I would be happy to hear about them.

Limno, you wrote above about a link which seems that you have not set, maybe you could post it again.

Markus

|||

Hello:

Here is the link from the tutorial.

http://www.asp.net/QuickStart/aspnet/doc/data/default.aspx

Please read the Deploy your local database section.

You mentioned you had problem to access the site from the browser.

Which one IIS or the built-in web browser? what is your url looks like?

Also, I think the user we are talking about isyourmachinename\ausername.

If you cannot figure it out, I will start over on my machine to figure it out.

Good luck(sorry for the delay, I was locked out for a while on this site.)

|||

This thread has an answer.

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

|||

Hi limno,

Many thanks for all your replies. I finally got it running and the problem was where we searched. In IIS I chose the application-pool of my domain and assigned as identity "local system". After that I also had to change sql-server to run under local system. Now it is working, however I'm not quite sure if I will use that because of Security reasons. If I understand correctly, it now would be possible to get access to the whole file-system through script and probably more. Beside all users/domains will be able to access all databases within the instance of sql server.

So the next big step would be to get sql server running under the standard IWPD user of the domain and also to include some username/password security through the connection-string.

Is that possible?

Markus

|||

Hello:

FYI:

<connectionStrings>

<add name="conString1" connectionString="Data Source=yourServer;Integrated Security=false;Initial Catalog=YourDB;User ID=yourDBUser;Password=thePassword" providerName="System.Data.SqlClient" />

<add name="conString2" connectionString="Data Source=yourServer;Initial Catalog=yourDB;Integrated Security=True" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />

</connectionStrings>

<roleManager enabled="true"
defaultProvider="CustomizedRoleProvider">
<providers>
<clear />
<add name="CustomizedRoleProvider"
type="System.Web.Security.SqlRoleProvider"
connectionStringName="conString1"
applicationName="/"
/>
</providers>
</roleManager>

<membership defaultProvider="CustomizedMembershipProvider">
<providers>
<clear />
<add name="CustomizedMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="conString2"
applicationName="/"
requiresUniqueEmail="false"
minRequiredPasswordLength="1"
minRequiredNonalphanumericCharacters="0"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"

/>
</providers>
</membership>

you need create your database user in your own database first.

Assume you merged the ASPNETDB.MDF into yourDB and you are using customized prociders which point back to the default ones for membership and roles.

The applicationName is important. You can type your application name in it. You can search for explainations.

Hope this can save you a little bit time.

How to run SQL Server Exp 2005

Dear friends

I have installed SQL Server Express Edition (55 mb).
Program >> Start >> MS SQL Server 2005 >> Configuration Tool ... this is what being displayed. This is my first use of MS Sql Server, before this I was using Oracle and after installation one could see Sql Plus from where the user can log in.

I am confused how to start SQL Server Expr 2005 edition and try some sql.
Please help me out..

Have a nice day
Ramniklal

Yo have to download a graphical management tool as a separate download: http://go.microsoft.com/fwlink/?LinkId=64064

--
SvenC

|||Thanks dear

Will get you back after downloading and using the graphical mgt. tool

bye n have a nice day
ramniklal|||

If you want to run commandline queries (I know that Oracle guys like that :-) ) you can use the OSQL.EXE (but this will be deprecated in the future) or the SQLCMD.EXE which is the successor of the OSQL.exe.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

how to run queries??

Hi,

I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.

i want to write my own querywithout help ofsqlDataSource control from start something like

sqldatasource con=new sqldatasource;

con.connection String=""

then what all things will come...... ?

and please give me some poitners to some articles which help one to do the requested.

Regards,

Please checkheresql

How to run a script file

Hello,

How would I go about populating a table using a script which I downloaded for the book I purchased for SQL/EXPRESS 2005. I believe this could only be down using

Microsoft SQL Server Management. Any help would be much appreciated.

Thanks

Karl

Try the link below and download the Express Advanced for the management studio and other features. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||

You can also use the sqlcmd command line tool. It will take a few minutes to figure out the syntax you need, but it's reasonably easy.

Don

sql

How to run a .sql file in SQL Server 2005 Express Edition.

Hi All,

I have a .sql file withall my queries written in it. Now I want to know, Is it possible to runthis sql file in SQL Server 2005 Express Edition like we can do inOracle? If it is possible then tell me how to do it?

Thanx in advance for any kind of help.

Regards,
Paramhans Dubey.

You can use sqlcmd utility.

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

|||If you've got SQL Server Management Studio (or the Express version which is a free download from Microsoft), then you can use the GUI to open the .sql script in a query window and execute the contents - its all point-and-click.

How to Retain Text and Paragraph Formatting

Is it possible to retain text or paragraph formatting in a SqlServer 2005 Express edition table? If so, how?

I am particularly interested in keeping a linebreak between paragraphs and the only way I can think to do that is to put each paragraph in its own row. But I want some input before I undertake that substantial task.

Thanks for any help provided.
You can use char(10) which is the hard break in SQL.

eg.

Code Snippet

Print 'Microsoft' + char(10) + 'Website'

The Output is like below:
Microsoft
Website

You can as many as char(10) to provide line break in your query.

Even the below syntax will also work.

Code Snippet

select 'Microsoft' + char(10) + 'Website'

|||Hi Vidhya. Thanks for your advice.

I am trying to understand how to insert the data into a table and retrieve to a control on a VB form. In Visual Basic Express I tried including the char(10) with the data by pasting it into my table like this:

Paragraph 1 text here. + char(10) + Paragraph 2 text here. + char(10) + Paragraph 3 text here.

But all I get for output in my datagrid control is the literal string as one block of text.

I use a similar technique with html tags (

) if I am outputing to a webcontrol and it seems to work okay if the control renders html (ie.,

Paragraph 1 text here

Paragraph 2 text here

Paragraph 3 text here

). But this doesn't work for Windows forms unless I am using a webbrowser control and html controls (which I don't want to do).

So I opened up SQL Server Management Studio Express and created a database with a table (text) and column (paragraph) and ran these scripts:

INSERT INTO Text (Paragraph)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

SELECT *
FROM Text

select 'Microsoft' + char(10) + 'Website'
From Text

But my output is still a single line of text without paragraph formatting.

When I run the code you provided (Print 'Microsoft' + char(10) + 'Website'), that works perfectly. But I don't know how to insert and retrieve the formatted text data in my db.

I am relatively new to SQL, so I realize that I am missing something simple in my implementation of your advice. Any suggestions on how to apply this concept is greatly appreciated.|||To Insert into table jus insert as usual

insert into <tablename> values('col1','col2')


To select from the table with char(10) you can use

select col1,char(10),col2 from <tablename>

Run the above query in text format(ctrl+t)

|||Thanks for the follow-up. I have had some success (I think). Here is what I have tried:

CREATE TABLE Paragraph
(ID int Primary Key IDENTITY(1,1) NOT NULL,
FormattedText nvarchar(500) NOT NULL)

INSERT INTO Paragraph (FormattedText)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

select FormattedText from Paragraph

If I have the "Results to Text" button clicked, the output looks great, like this:

Paragraph 1 text here.
Paragraph 2 text here.
Paragraph 3 text here.

If I output with "Results to Grid" clicked, it loses the format and looks like this:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So I think I am making progress. I am getting the data into my table ok. Getting it out in the proper paragraph format is the challenge, which is easy in Management Studio.

But when I use the same table in Visual Basic and output the row of data to a datagrid, it comes out like this, without the paragraph formatting:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So my problem now is how to get it to display properly on my Windows Form. Any thoughts on this, or do I need to switch to the VB forum?|||Im not sure abt VB. But i think there is a command "Break" in VB. Pls check
|||I will look into it further. There is a break property for creating menus, but I am not sure if it can be applied to datagrids or other controls for displaying formatted data.

Thanks again for your helpl.

how to restore sql server 2000 db backup into sql server 2005 express edition

Hi Friends,

I have installed SQL server 2005 Express Edition and SQL Server management

I have a SQL server 2000 db backup file. I try create a new database in my SQL server 2005 express Edition and try restore that backup file from device, it only searching for file with *.bak and *.tm extension! I tried generate backup file with .bak extension and tried restore into SQl server 2005 express edition but still it is not allowing to do so! I also tried copy my database's data file and log file and paste it under SQL server 2005 express edition Data folder and still not able to read the tables.

Is that any ways to do restoring for this SQL server 2000 backup file into SQL server 2005 express edition! Anybody can help me on this please...:eek:Can you please be a little more specific, with maybe some error codes, etc? I don't have any express edition experience, but if you give us some error codes or the scripts you use for the restore, we might be able to help you.|||Hi Friend, Thanks for your help!

Well, I guess i found the way. I believe it is because the sql 2005 doesn't support the sql 2000 backup file, Im not sure! So i tried attached the database from sql server 2000 into sql server 2005 then it can already.After that i hv tried take backup from sql server 2005 itself and then restore it back ,it is can be restored successfully! Thank God finally i got a way to do it ...phew