CREATE DATABASE failed: script

Showing posts with label script. Show all posts

Thursday, March 29, 2012

Create an automated routine to check database definitions

Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...[vbcol=seagreen]
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>

Create an automated routine to check database definitions

Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> > Is there any utility, command, or query in SQL Server, that I can execute
> > from a batch mode to script out all of my database definitions (tables,
> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> > create
> > an automated routine to check database definitions?
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy :)
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
>> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
>> > Is there any utility, command, or query in SQL Server, that I can
>> > execute
>> > from a batch mode to script out all of my database definitions (tables,
>> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
>> > create
>> > an automated routine to check database definitions?
>> I wrote one some time ago, using DMO. I can provide compiled exe or
>> sources
>> if you want. AFAIR works only with integrated security (kind of bug I
>> never
>> really needed to fix). Works fine if you need to monitor database (or all
>> databases) schema.
>> Cheers,
>> Wojtek
>>

Tuesday, March 27, 2012

Create a UNC Share

hi all,

i'm trying to write a script just like "copy database wizard" operation, for detaching, copy and ataching a database from a remote sql server to a local server.

Now the problem is when i try to copy the mdf file from the sql remote server to my local server.

Is it possible to create a UNC share in the remote sql server and delete after with t-sql like the Copy Database wizard does?

hope i made my self clear enought.

regards

Things like this should be planned and not ad-hoc. Though it's possible...

Code Snippet

exec xp_cmdshell 'net share sharename=<drive path>'

Sunday, March 25, 2012

create a script to display all the rows of the view

I have a view that has a link to 3 tables.
I need to display all the rows of the view.
here is the code for the view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
I like to thank you ahead for the help
Thank you
Cristianselect * from Oils_Cautions_View
"Cristian" <vitanc@.hotmail.com> wrote in message
news:0c9901c392a4$2903c2b0$a001280a@.phx.gbl...
> I have a view that has a link to 3 tables.
> I need to display all the rows of the view.
> here is the code for the view.
> USE Aromatherapy
> GO
> if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
> = 'V')
> DROP VIEW
> Oils_Cautions_View
> go
> CREATE VIEW Oils_Cautions_View AS
> SELECT o.oilID, oilName,
> Description FROM Oils AS o, Cautions as c, OilCautions as
> oc
> Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
> I like to thank you ahead for the help
> Thank you
> Cristian
>|||1. Create DB Links OLE DB SQL Links for all the 3 servers.
say:
DBLINK1 points to Server1.employee
DBLINK2 points to Server2.employee
DBLINK3 points to Server3.employee
create view v_employee as
select * from dblink1...employee
union
select * from dblink2...employee
union
select * from dblink3...employee
go
select * from v_employee
There is no need for 'Hardcoding' the db servername.
If server needs to changed, you change it at the Link creation time.

Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
JosUPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
UPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
sql

Create a script rename value of a column

Hi
Is there a way, to make a script that changes the values of a colum
I have a tabel called "fasesrt
That tabel has 3 columns
Fase & Fasesrtom & fasegrpn
In the column fasesrtom I have 6 values that I want to change
I know the old value and the new valu
Can Anyone help me to make a script, If I have a start, I think I can do it
I just need a start
Thank
JoUPDATE Fasesrt
SET fasesrtom = CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
--
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

create a script for sql server 2005 database maintenance plan

hi,
after I manually create maintenance plan using SQL server Maintenence
Plan Wizard with SQL Server Management Studio, is there any way to
create a script for all these setting that I've done?
So that I can just modify the database name and run it on another pc,
the same setting will apply in this pc. Or is there other ways to do
it?
Thanks.Hi
AFAIK there is no way of doing this for the whole plan, but the SQL for
individual tasks can be views in the View T-SQL button when they are editted.
John
"skyloon" wrote:
> hi,
> after I manually create maintenance plan using SQL server Maintenence
> Plan Wizard with SQL Server Management Studio, is there any way to
> create a script for all these setting that I've done?
> So that I can just modify the database name and run it on another pc,
> the same setting will apply in this pc. Or is there other ways to do
> it?
> Thanks.
>|||Here's an idea:
A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
package to a file. That file is XML. You can now modify that file and distribute it to the target
machines. (This is from theory, you need to experiment a bit...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> hi,
> after I manually create maintenance plan using SQL server Maintenence
> Plan Wizard with SQL Server Management Studio, is there any way to
> create a script for all these setting that I've done?
> So that I can just modify the database name and run it on another pc,
> the same setting will apply in this pc. Or is there other ways to do
> it?
> Thanks.
>|||but i can't find the path of my Database Maintenance Plan file that
created under Database engine.
when i connect Integration service, can't find the physical file, how
can i open the plan using SSMS or DTEXEC.EXE?
Tibor Karaszi wrote:
> Here's an idea:
> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> package to a file. That file is XML. You can now modify that file and distribute it to the target
> machines. (This is from theory, you need to experiment a bit...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "skyloon" <skyloon@.gmail.com> wrote in message
> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> > hi,
> > after I manually create maintenance plan using SQL server Maintenence
> > Plan Wizard with SQL Server Management Studio, is there any way to
> > create a script for all these setting that I've done?
> > So that I can just modify the database name and run it on another pc,
> > the same setting will apply in this pc. Or is there other ways to do
> > it?
> > Thanks.
> >|||You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
or DTUTIL.EXE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> but i can't find the path of my Database Maintenance Plan file that
> created under Database engine.
> when i connect Integration service, can't find the physical file, how
> can i open the plan using SSMS or DTEXEC.EXE?
> Tibor Karaszi wrote:
>> Here's an idea:
>> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
>> package to a file. That file is XML. You can now modify that file and distribute it to the target
>> machines. (This is from theory, you need to experiment a bit...)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "skyloon" <skyloon@.gmail.com> wrote in message
>> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
>> > hi,
>> > after I manually create maintenance plan using SQL server Maintenence
>> > Plan Wizard with SQL Server Management Studio, is there any way to
>> > create a script for all these setting that I've done?
>> > So that I can just modify the database name and run it on another pc,
>> > the same setting will apply in this pc. Or is there other ways to do
>> > it?
>> > Thanks.
>> >
>|||I've uploaded a picture for your reference because i can't find any
export function in SSMS. Mind to teach me step by step? Thanks.
http://img.photobucket.com/albums/v202/skyloon/1.jpg
Tibor Karaszi wrote:
> You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
> or DTUTIL.EXE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "skyloon" <skyloon@.gmail.com> wrote in message
> news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> > but i can't find the path of my Database Maintenance Plan file that
> > created under Database engine.
> > when i connect Integration service, can't find the physical file, how
> > can i open the plan using SSMS or DTEXEC.EXE?
> >
> > Tibor Karaszi wrote:
> >> Here's an idea:
> >>
> >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> >> package to a file. That file is XML. You can now modify that file and distribute it to the target
> >> machines. (This is from theory, you need to experiment a bit...)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "skyloon" <skyloon@.gmail.com> wrote in message
> >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> >> > hi,
> >> > after I manually create maintenance plan using SQL server Maintenence
> >> > Plan Wizard with SQL Server Management Studio, is there any way to
> >> > create a script for all these setting that I've done?
> >> > So that I can just modify the database name and run it on another pc,
> >> > the same setting will apply in this pc. Or is there other ways to do
> >> > it?
> >> > Thanks.
> >> >
> >|||Hi
If you wish to save a package to a file you would use BIDS and not SSMS. You
may want to check out http://msdn2.microsoft.com/en-us/library/ms137712.aspx
and the package deployment tutorial.
John
"skyloon" wrote:
> I've uploaded a picture for your reference because i can't find any
> export function in SSMS. Mind to teach me step by step? Thanks.
> http://img.photobucket.com/albums/v202/skyloon/1.jpg
>
> Tibor Karaszi wrote:
> > You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
> > or DTUTIL.EXE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "skyloon" <skyloon@.gmail.com> wrote in message
> > news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> > > but i can't find the path of my Database Maintenance Plan file that
> > > created under Database engine.
> > > when i connect Integration service, can't find the physical file, how
> > > can i open the plan using SSMS or DTEXEC.EXE?
> > >
> > > Tibor Karaszi wrote:
> > >> Here's an idea:
> > >>
> > >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> > >> package to a file. That file is XML. You can now modify that file and distribute it to the target
> > >> machines. (This is from theory, you need to experiment a bit...)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "skyloon" <skyloon@.gmail.com> wrote in message
> > >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> > >> > hi,
> > >> > after I manually create maintenance plan using SQL server Maintenence
> > >> > Plan Wizard with SQL Server Management Studio, is there any way to
> > >> > create a script for all these setting that I've done?
> > >> > So that I can just modify the database name and run it on another pc,
> > >> > the same setting will apply in this pc. Or is there other ways to do
> > >> > it?
> > >> > Thanks.
> > >> >
> > >
>|||You connected Object Explorer to a SQL Server instance. You need to connect Object Explorer to SSIS.
In Object Explorer, select the Connect button (top of Object Explorer), select Integration Services,
specify the server name (no instance name, you can only have one SSIS service). Now, expand the
Stored Packages folder, MSDB, Maintenance Plans. Right-click your package and select Export.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1164243388.835755.301510@.j44g2000cwa.googlegroups.com...
> I've uploaded a picture for your reference because i can't find any
> export function in SSMS. Mind to teach me step by step? Thanks.
> http://img.photobucket.com/albums/v202/skyloon/1.jpg
>
> Tibor Karaszi wrote:
>> You need to export it from msdb to a file first. Use either SSMS (connect object explorer to
>> SSIS)
>> or DTUTIL.EXE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "skyloon" <skyloon@.gmail.com> wrote in message
>> news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
>> > but i can't find the path of my Database Maintenance Plan file that
>> > created under Database engine.
>> > when i connect Integration service, can't find the physical file, how
>> > can i open the plan using SSMS or DTEXEC.EXE?
>> >
>> > Tibor Karaszi wrote:
>> >> Here's an idea:
>> >>
>> >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
>> >> package to a file. That file is XML. You can now modify that file and distribute it to the
>> >> target
>> >> machines. (This is from theory, you need to experiment a bit...)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "skyloon" <skyloon@.gmail.com> wrote in message
>> >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
>> >> > hi,
>> >> > after I manually create maintenance plan using SQL server Maintenence
>> >> > Plan Wizard with SQL Server Management Studio, is there any way to
>> >> > create a script for all these setting that I've done?
>> >> > So that I can just modify the database name and run it on another pc,
>> >> > the same setting will apply in this pc. Or is there other ways to do
>> >> > it?
>> >> > Thanks.
>> >> >
>> >
>

Thursday, March 22, 2012

create a log file of a script

I'm am new to using SQL Server. In Oracle, when I create scripts to
manipulate data, I have a log file automatically created from within the
script by issuing the SPOOL command. Is there a similar functionality in SQL
server? besides the log file which records each transaction. Is there any
other way to run a script (automatically) besides cut & paste into SQL query
analyzer?
ThanksLee Ann,
The transaction log for the database is the only automatic record of the
transactions that I'm aware of. You could implement a custom solution using
a trigger/audit table or use a third-party log viewing tool. If you're
trying to test out the command prior to having it automatically commit
(admin type stuff) you can embed the code in a user-defined transaction
(BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).

> Is there any other way to run a script (automatically)
You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
Windows scheduler, auto-startup procs.
HTH
Jerry
"Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
> I'm am new to using SQL Server. In Oracle, when I create scripts to
> manipulate data, I have a log file automatically created from within the
> script by issuing the SPOOL command. Is there a similar functionality in
> SQL
> server? besides the log file which records each transaction. Is there any
> other way to run a script (automatically) besides cut & paste into SQL
> query
> analyzer?
> Thanks|||Lee Ann,
One other approach would be to add auditing code (INSERTs and PRINTs) into
your original script.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Lee Ann,
> The transaction log for the database is the only automatic record of the
> transactions that I'm aware of. You could implement a custom solution
> using a trigger/audit table or use a third-party log viewing tool. If
> you're trying to test out the command prior to having it automatically
> commit (admin type stuff) you can embed the code in a user-defined
> transaction (BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).
>
> You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
> Windows scheduler, auto-startup procs.
> HTH
> Jerry
> "Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
> news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
>|||Thanks Jerry...I will go and read up on your suggestions and then try it
out.
Lee Ann
"Jerry Spivey" wrote:

> Lee Ann,
> One other approach would be to add auditing code (INSERTs and PRINTs) into
> your original script.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
>
>sql

Create a grants rights script fails during configuration

I am in the reporting services configuration tool. When I am on database setup I get Create a Grants Scripts Right with a red X. Everything else passes. I am using sql server credentials and the user is sa on the database server. Any help would be appreciated.

Joni,

Validate that sa user is in the security/users on your "ReportServer" database and also give to access to the RSExec Role for reporting services. When I encounter this issue by user Id did not have RSExec Role after adding it then I was able to configure my services correctly.

Ham

sql

Tuesday, March 20, 2012

create a .txt file from a table using sql script

Hi all,
Im new on SQL. I need to create a sql script to be run in Query Analyzer.
This script must create a table in .txt format on a server folder
(d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
I need to make it with many tables (about 30) and many times (every week).
Your time is really appreciate it.
Thanks
Ped_esc
Seems you are looking at generating DDL and/or data for that DDL. I have listed some options here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ped_Esc" <PedEsc@.discussions.microsoft.com> wrote in message
news:FB03A791-F776-4B57-B163-82F392CB0AF4@.microsoft.com...
> Hi all,
> Im new on SQL. I need to create a sql script to be run in Query Analyzer.
> This script must create a table in .txt format on a server folder
> (d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
> I need to make it with many tables (about 30) and many times (every week).
> Your time is really appreciate it.
> Thanks
> Ped_esc
>
sql

create a .txt file from a table using sql script

Hi all,
Im new on SQL. I need to create a sql script to be run in Query Analyzer.
This script must create a table in .txt format on a server folder
(d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
I need to make it with many tables (about 30) and many times (every week).
Your time is really appreciate it.
Thanks
Ped_escSeems you are looking at generating DDL and/or data for that DDL. I have listed some options here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ped_Esc" <PedEsc@.discussions.microsoft.com> wrote in message
news:FB03A791-F776-4B57-B163-82F392CB0AF4@.microsoft.com...
> Hi all,
> Im new on SQL. I need to create a sql script to be run in Query Analyzer.
> This script must create a table in .txt format on a server folder
> (d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
> I need to make it with many tables (about 30) and many times (every week).
> Your time is really appreciate it.
> Thanks
> Ped_esc
>

Monday, March 19, 2012

Crate connection in T-SQL

Helo everybody

How to crate differente connection in the same script and apply this in a query. I use Query analyser. I can do that?

If you ave something about that ... internet link...

Exemple like that (is not working...!)
========================
Server Name:ServerName1
User:user_srv1
Pass:pass_srv1
Server Name:ServerName2
User:user_srv2
Pass:pass_srv2

SELECT a.FieldID, b.FieldID FROM
ServerName1.DatabaseName.dbo.TableName a,
ServerName2.DatabaseName.dbo.TableName b
WHERE a.FieldID=b.FieldID
=========================

THANKSYou should be creating linked server to access the database on a remote server. You could create the linked server and the login on the fly and drop them after you are done with your code. check out BOL for creating the linked server.

-cbarus

Saturday, February 25, 2012

CPU and MEMORY usage

Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfomingexecute sp_who2 active

this will tell you whos is on your db and how much cpu (CPUTime) and mem (DISKIO) each user is taking

Cheers|||Actually gives the user activity accross the server you run it on. Not just the db activity - Sorry|||should use Performance Mointor to assess your server activity over a period of time.

you can log this info in .csv then import into SQL Server

have a look at :

www.sql-server-performance.com/performance_monitor_tips.asp

cheers|||On sp_who2 does the CPUTime equals to the CPU usage on the task manager and what about the Memory because i also want this memory to be the same as the one on the Task Manager|||Not sure about that. I think cputime that a sp_who will show the diskIO for each user and system query that occurs at the time of the SP execution. This does not take into consideration the O/S resources getting used.

I guess you could find out how much mem is allocated to SQL by looking at the properties of the server (right click on the server in Enterprise Manger) . From this figure you can work out how much resources are getting used in terms of how much mem has been allocated to SQL Server.

Cheers|||I 100% agree with you when you say we can manually look at properties and again on the task manager properties, the thing is we want a script that will do this for us automatically every hour to check the memory and cpu usage.|||Use performance mointor as it is the easiest way to shedule.

Look over the link above to see what counters you want to look at.

i.e Memory -- Pages/Sec, Memory Manager -- Total Server Memory (KB)
etc

What you could do is shedule it to output a csv file on the hour then set up a dts task to import this into SQL Server for anyalsis.

Once you know how much mem you allocated to SQL Server then you have constant that you can do calculations with.

Cheers|||Thanks, I think this will work perfect

Friday, February 24, 2012

Couple of question about SQL 2000

Hi

I've bin using MySQL and now trying to convert it on to SQL 2000 and i've got a couple of questions

1. Ive got a SQL Script of a database generated by MySQL, can i run this script stright into SQL 2000 and if so how do i go about it ??

2. In my MySQL i have a Gender field which data type is Enum ('M','F'). How do i recreate this type of field in SQL 2000.

Thanks1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||Hi thanks for your help

just to be a pain, if i wanted 3 or 4 options would the field type be varchar and i'd have the sort the options out in my application

is this right

thanks again

Originally posted by rhigdon
1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||It's not going to compile...you need to determine the differences and develop a migration plan..

Do a google...

http://www.databasejournal.com/features/mssql/article.php/3087841

Sunday, February 19, 2012

counting rows in all tables in a database

Hej,
Has anyone a script that will produce a list of all the
tables in a database sorted by the number of rows each
table contains?
thanks,
KevinFor something simple using the estimates instead of actually counting the
rows, try something like
select name, rows from sysobjects
where type = 'U' order by rows desc
For a more costly and more accurate version, you'd have to loop through each
table, select count(*), insert the results into a temp table or table
variable, then select from the temp table..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin|||The script below will give information about the row count and space used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=629
The script that is attached to this message isn't mine btw, but I can't find
back who the original author is.
--
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OPxHQI40DHA.2528@.TK2MSFTNGP10.phx.gbl...
> For something simple using the estimates instead of actually counting the
> rows, try something like
> select name, rows from sysobjects
> where type = 'U' order by rows desc
> For a more costly and more accurate version, you'd have to loop through
each
> table, select count(*), insert the results into a temp table or table
> variable, then select from the temp table..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:05c101c3d379$42411190$a401280a@.phx.gbl...
> > Hej,
> > Has anyone a script that will produce a list of all the
> > tables in a database sorted by the number of rows each
> > table contains?
> >
> > thanks,
> > Kevin
>|||"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin
The following was adapted from SP_SPACEUSED, and will also give the size in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS = (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =O.ID),
RESERVED = (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES I
WHERE I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128),
DATA = (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I WHERE
I.INDID < 2 AND I.ID = O.ID)) / 128),
TEXT = (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES I
WHERE I.INDID = 255 AND I.ID = O.ID),0)) / 128),
USED = (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE = 'U'
Mike Kruchten|||http://www.aspfaq.com/2428
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin

Counting rows from Flat File Source

Hello,

Is there a way (perhaps a property) to capture the number of rows selected from a Flat File Data Flow Source without having to develop a script to loop through the rows and count them?

Thanks a lot,

Grace

Yep. Use the ROWCOUNT component.

Row Count Transformation

(http://wiki.sqlis.com/default.aspx/SQLISWiki/Row%20Count%20Transformation.html)

-Jamie

Thursday, March 29, 2012

Create an automated routine to check database definitions

Create an automated routine to check database definitions

Create an automated routine to check database definitions

Tuesday, March 27, 2012

Create a UNC Share

Sunday, March 25, 2012

create a script to display all the rows of the view

Create a script rename value of a column

Create a script rename value of a column

Create a script rename value of a column

create a script for sql server 2005 database maintenance plan

create a script for sql server 2005 database maintenance plan

create a script for sql server 2005 database maintenance plan

Thursday, March 22, 2012

create a log file of a script

Create a grants rights script fails during configuration

Tuesday, March 20, 2012

create a .txt file from a table using sql script

create a .txt file from a table using sql script

Monday, March 19, 2012

Crate connection in T-SQL

Saturday, February 25, 2012

CPU and MEMORY usage

Friday, February 24, 2012

Couple of question about SQL 2000

Sunday, February 19, 2012

counting rows in all tables in a database

Counting rows from Flat File Source

CREATE DATABASE failed

Blog Archive

About Me