copy database: oracle

Showing posts with label oracle. Show all posts

Friday, March 30, 2012

How to Create Store Procedure

Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do?Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.

Supporting Tables

create table Users (Username varchar(255), [Password] varchar(255))

Supporting Data

insert Users (UserName, [Password]) values ('tscott','123')
go
insert Users (UserName, [Password]) values ('ascott','abc')
go
insert Users (UserName, [Password]) values ('bscott','Abc')
go

select * from users2

Stored Procedure

CREATE PROC spValidatePassword ( @.UserName varchar(255),
@.Password varchar(255)
)

/************************************************** *****
* spValidatePassword
* hmscott
*
* Validates a username/password against a table of known username/passwords
* Warning: data is not encrypted; these passwords are not secure. This
* sample is for demonstration only and is not intended for production
* use.
*
* Parameters:
* IN: UserName The usernmae provided by the user
* Password The password provided by the user
*
* Returns:
* Single-row recordset (ReturnCode):
* -1: UserName not found
* 0: Password incorrect for this user
* 1: Password correct for this user
************************************************** ****/

AS

DECLARE @.LocalPassword varchar(255), @.ReturnCode int

SELECT @.LocalPassword = [Password]
FROM dbo.Users2
WHERE UserName = @.UserName

IF @.LocalPassword IS NULL
BEGIN
SELECT @.ReturnCode = -1 -- User not valid
END
ELSE
BEGIN
IF @.LocalPassword = @.Password COLLATE Latin1_General_CS_AS
-- IF @.LocalPassword = @.Password -- Use this line instead of the above line if you want the passwords to be case insensitive
BEGIN
SELECT @.ReturnCode = 1 -- Password matches
END
ELSE
BEGIN
SELECT @.ReturnCode = 0 -- Password does not match
END
END

SELECT @.ReturnCode as ReturnCode

spValidatePassword 'nouser', 'Foo'

spValidatePassword 'bscott', 'Abc'

spValidatePassword 'bscott', 'abc'sql

How to create sql server login for workgroup computer?

How can I add a new sql server login using windows authentication where the login id is from a user belonging in a workgroup?

No domain connection. Just a workgroup account will have a login in the sql server using windows authentication. Thanks
Thats not possible, WIndows authentication can be used if the server can prove your identity. I a (non-trusted) workgroup, this is not possible, because everyone can join the workgoup and could spoof another ones identity.

You can only use the local accounts of the SQL Server to do so. In those enviroments you can impersonate the user with a remote account on the SQL Server machine programmatically (like in C# to run under a remote account of the SQL Server box).

There is another way of choosing the same name and the same password on the SQL Server as well as on the client bix, but I don′t think and stated that several times, that this is not a proficient solution.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to create SQL Server Express server (INSTANCENAME) programmatically?

In deploying SQL Express, how do I create a new server (INSTANCENAME)
programmatically?
What would be the best approach to accomplish this.

Thank you so much,(ianicbass@.yahoo.com) writes:
> In deploying SQL Express, how do I create a new server (INSTANCENAME)
> programmatically?
> What would be the best approach to accomplish this.

You should look in Books Online under installing SQL Server, and
particularly unattended installation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to create sql server 2005 instance(after uninstalling sql server express)

I have uninsttaled sql server 2005 express then installed sql server 2005 developer edition.All things are installed and working fine like management studio,BOL,reporting services ,e.t.c.

But there is no instance of sql server 2005.I can connect to sql server 2000 instance from management studio.

I have check from all ways but there isn't any instance.

Could I create a instance of sql server 2005 from some where if yes the how.

You can also check my post at sqlteam forum http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63600

If you run setup again you can create another instance of SQL Server on the same machine. Just give it a new instance name when you run setup.

|||

Thanks Alexander.

I achieve it exactly the same way a week earlier.What I were trying to find is that is there any way we can create an instance without reinstalling full sql server 2005.As at that time when there isn't any instance but i can connect from management studion to sql server 2000.

|||No You need to go through installation again to install a new instance. For speed you can limit what you install to only a new DB Engine which is quicker than installing all the sql server 2005 components again.|||

Thanks alexander.

That indeed increased my knowledge

How to create SQL Login audit log?

Hi, All
I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
Login audit log using Profiler, but cannot find the tools. I looked at the
Windows Event Viewer, it only logs Windows authentications users for SQL
Server not SQL Server users (Mixed mode users).
Please help on creating SQL Server Login log (username,password and time).
Thanks
KaiHello,
SQL Profiler is a component of SQL Client Tools. Check if you have
installed SQL Client Tools on your server. By default, you can start SQL
Profiler by clicking Start > All Programs > Microsoft SQL Server >
Profiler.
To audit on successful login attempts or failed login attempts, you need to
select the option "Audit level: success" or "Audit level: failure" or
"Audit level: All" in the SQL Server Properties (Security Tab). You can
refer to the following information in the "SQL Server Properties (Security
Tab)" topic in SQL server Books Online(BOL):
All
Audit on both successful and failed login attempts. You can record
attempted user accesses as well as other SQL Server log information, and
enable auditing for both security modes, and you can record information on
both trusted and nontrusted connections. Log records for these events
appear in the Windows application log, the SQL Server error log, or both,
depending on how you configure logging for your SQL Server.
If you select this option, you must stop and restart the server to enable
auditing.
--
For more information, refer to the "SQL Server Properties (Security Tab)"
topic in BOL.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Guo,
Thanks for your help.
I setup "All" for Audit level in security, after that, in application
view, I restart SQL Server, I found user colum is "N/A" or "System". Any
thing I did not do?
Thanks
Kai
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:15x7bmQjFHA.588@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> SQL Profiler is a component of SQL Client Tools. Check if you have
> installed SQL Client Tools on your server. By default, you can start SQL
> Profiler by clicking Start > All Programs > Microsoft SQL Server >
> Profiler.
> To audit on successful login attempts or failed login attempts, you need
> to
> select the option "Audit level: success" or "Audit level: failure" or
> "Audit level: All" in the SQL Server Properties (Security Tab). You can
> refer to the following information in the "SQL Server Properties (Security
> Tab)" topic in SQL server Books Online(BOL):
> --
> All
> Audit on both successful and failed login attempts. You can record
> attempted user accesses as well as other SQL Server log information, and
> enable auditing for both security modes, and you can record information on
> both trusted and nontrusted connections. Log records for these events
> appear in the Windows application log, the SQL Server error log, or both,
> depending on how you configure logging for your SQL Server.
> If you select this option, you must stop and restart the server to enable
> auditing.
> --
> For more information, refer to the "SQL Server Properties (Security Tab)"
> topic in BOL.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
User colum is "N/A" in application view of Event Viewer if you log on as a
SQL login. However, double-click the event , you may read the information
looks like the followings in the event properties:
18454 :
Login succeeded for user 'sa'. Connection: Non-Trusted.
18456 :
Login failed for user 'sa'.
or you can find the following information in the SQL logs:
2005-07-20 16:48:10.32 logon Login failed for user 'sa'.
2005-07-20 16:48:14.75 logon Login succeeded for user 'sa'. Connection:
Non-Trusted.
By default, the SQL logs located in the folder "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG".
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to create SQL from XSD file

Hello,
I am looking for a way to build an SQL database from an XML schema.1. Try XSLT
2. Read XML using XML Dom, then do a loop -thru|||Also, you may want to look at the SchemaGen option of the SQLXML XML
Bulkload facility. It can give you a first cut at generating the table
definitions.
Best regards
Michael
"Neo" <pravinsable@.gmail.com> wrote in message
news:1144871606.564411.290310@.u72g2000cwu.googlegroups.com...
> Hello,
> I am looking for a way to build an SQL database from an XML schema.
>sql

How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(

how to create sql database in visual basic

First i am newbie in vb and Sql server...

Is possible to create and attaching, via vb classic or VBA, a sql
database in this instance:\\DVD377-14D9E48C\SQLEXPRESS...
I have Sql Express.

Database name: mydatabase
Database Table: mytable

fileds:
fiedl1 text format
fiedl2 text format
fiedl3 text format
fiedl4 date format
fiedl5 number format
fiedl6 text formatFirst i am newbie in vb and Sql server...

Is possible to create and attaching, via vb classic or VBA, a sql
database in this instance:\\DVD377-14D9E48C\SQLEXPRESS...
I have Sql Express.

Database name: mydatabase
Database Table: mytable

fileds:
fiedl1 text format
fiedl2 text format
fiedl3 text format
fiedl4 date format
fiedl5 number format
fiedl6 text format

You can execute sql statement from your code...its better to read a book on vb - database programming.|||Also it's best not to be creating tables from app code.

If your app logs in as a user that has rights to create tables, then it's logging in as a user with far too many rights - usually only an admin would be allowed to create tables. So this would ONLY be acceptable to me if your app was an administrative app, such as SSMS. :)

How to create sproc with unlimited number of parameters?

We have this kind of thing in C#, and I know the sp_executesql (or something like that) does it too. How do you create a stored procedure that has certain known parameters, followed by any number of additional parameters?

For example, you may have one that's called in any of these ways:

exec my_sp @.ID, @.text, @.P1, @.P2

exec my_sp @.ID, @.text, @.P1, @.P2, @.P3

exec my_sp @.ID, @.text, @.P4, @.P39087, @.P2

I can't find a syntax in the docs that really accommodates this.

Thanks in advance for your help!

Is something like this what you are looking for? This defines a procedure with M required parameters and N optional parameters:

create procedure dbo.xample
( @.pm_required_01 <type#01>,
@.pm_required_02 <type#02>,
...
@.pm_required_M <type#M>,
@.pm_optional_01 <type#01> = <defaultValue#01>,
@.pm_optional_02 <type#02> = <defaultValue#02>,
...
@.pm_optional_N <Type#N> = <defaultValue#N>
)
as
yourCode

|||Does this solution enforce a maximum number of parameters though? If so, that's not quite what I was after.

I guess the other problem too is that there has to be some way to iterate through all of the parameters as well.|||

Jeff:

It sounds to me like you want to use a "list" as an argument to the stored procedure; if that is the case examine this article by Erland Sommarskog and see if this is the correct orientation:

http://www.sommarskog.se/arrays-in-sql.html

Also, there is a response here from Jens Suessmeyer that has some more about using a "string list":

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

|||

TSQL has a hard limit of ~2K Parameters to any module. Thus, there is no way to do what your asking natively in TSQL (i.e, the best you can do is 2K sql_variant parameters with a default defined ).

People have used various techniques to get around this problem, most of which are nicely summarized in the links above.

How to create spool in PL/SQL

I want to create many different spools in PL/SQL programs, therefore i can't use 'spool' command for create a single in .sql file.
Can any one help me? Thx alot...Urgent!Originally posted by wwwdavy
I want to create many different spools in PL/SQL programs, therefore i can't use 'spool' command for create a single in .sql file.

Can any one help me? Thx alot...Urgent!
Perhaps the UTL_FILE package is what you need? This allows PL/SQL to open, read/write and close files on the server.|||Originally posted by andrewst
Perhaps the UTL_FILE package is what you need? This allows PL/SQL to open, read/write and close files on the server.

That's GOOD! Thx very much! Nice to meet u ^_^

How to create slideshow of a report?

Hi Expert!
I got a report showing the list of current sale volume by each office
branch. We got 20 offices. Is there a slideshow software package or ASP.NET
control out there to display my report as what PowerPoint does. I would like
to be able to pass in the report parameters.
Thanks in advance for any clue
ThanhOn Feb 26, 3:11 pm, Thanh Nguyen
<ThanhNgu...@.discussions.microsoft.com> wrote:
> Hi Expert!
> I got a report showing the list of current sale volume by each office
> branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> control out there to display my report as what PowerPoint does. I would like
> to be able to pass in the report parameters.
> Thanks in advance for any clue
> Thanh
One option would be to use SnagIt to record the parameter passing,
report execution, etc; and then play it back as video or use
screenshot frames. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks for your reply.
I would like to my slideshow to be real-time as much as possible, that means
once it reaches the end of the slideshow, it should be able to re-query again
and display the new sale volume in the next 1st slide. You can think of this
like stocks banner or something like that.
"EMartinez" wrote:
> On Feb 26, 3:11 pm, Thanh Nguyen
> <ThanhNgu...@.discussions.microsoft.com> wrote:
> > Hi Expert!
> >
> > I got a report showing the list of current sale volume by each office
> > branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> > control out there to display my report as what PowerPoint does. I would like
> > to be able to pass in the report parameters.
> >
> > Thanks in advance for any clue
> > Thanh
>
> One option would be to use SnagIt to record the parameter passing,
> report execution, etc; and then play it back as video or use
> screenshot frames. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||You can burn your powerpoint slideshow to dvd,
by this way you can a real-time and interactive dvd which can control each
silde play with a remote.
Play the DVD with a dvd player.
Burn the powerpoint slide show with Wondershare PPT2DVD
http://www.ppt-to-dvd.com/download.php?sid=4
--
PowerPoint software
http://www.ppt-to-dvd.com
PowerPoit free templates
http://www.ppt-to-dvd.com/free-templates.php?sid=4
"Thanh Nguyen" wrote:
> Thanks for your reply.
> I would like to my slideshow to be real-time as much as possible, that means
> once it reaches the end of the slideshow, it should be able to re-query again
> and display the new sale volume in the next 1st slide. You can think of this
> like stocks banner or something like that.
> "EMartinez" wrote:
> > On Feb 26, 3:11 pm, Thanh Nguyen
> > <ThanhNgu...@.discussions.microsoft.com> wrote:
> > > Hi Expert!
> > >
> > > I got a report showing the list of current sale volume by each office
> > > branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> > > control out there to display my report as what PowerPoint does. I would like
> > > to be able to pass in the report parameters.
> > >
> > > Thanks in advance for any clue
> > > Thanh
> >
> >
> > One option would be to use SnagIt to record the parameter passing,
> > report execution, etc; and then play it back as video or use
> > screenshot frames. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >sql

How to create Site Map based on Catalog table

Hi
I want to create site map with quick access to all reports in my intranet.
I have already static map without links, based on Catalog table and i want
to expand its funcionality by adding dynamic links.
Has someone already solved this problem?
thx5 minutes after sending my question i found solution. Maybe someone will find
it usefull :
all you need is to make the link to URL (using navigation tab) to expresion :
="http://reports.mycompany.com/Reports/Pages/Report.aspx?ItemPath="
+ Fields!Path.Value
that's all - it's working!

How to create simple insert trigger

I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:

Orders

ProductPrice ProductQuantity TotalCost
-- --
￡2.50 2
￡1.75 3
￡12.99 2

Can anyone please help me?You don't need a trigger, you need a computed column:

CREATE TABLE [dbo].[xxx](
[col_a] [int] NOT NULL default(2),
[col_b] [int] NOT NULL default(2),
[col_axb] as a * b,
) ON [PRIMARY]
GO|||Thank you for the help!

How to create server alias

Can anyone descrive in principle how a Web server alias is actually set
up? How do you get a server alias to point to a particular server with
a particular IP address within a large organisation? The outcome being
that when a user types the alias url into the address bar, the correct
page appears in the browser windowHi
Does it somehow relate to SQL Server?
<simonaj30@.yahoo.co.uk> wrote in message
news:1162895112.082029.220030@.i42g2000cwa.googlegroups.com...
> Can anyone descrive in principle how a Web server alias is actually set
> up? How do you get a server alias to point to a particular server with
> a particular IP address within a large organisation? The outcome being
> that when a user types the alias url into the address bar, the correct
> page appears in the browser window
>|||I would think so, as we use it in our organisation
Uri Dimant wrote:
> Hi
> Does it somehow relate to SQL Server?
>
> <simonaj30@.yahoo.co.uk> wrote in message
> news:1162895112.082029.220030@.i42g2000cwa.googlegroups.com...
> > Can anyone descrive in principle how a Web server alias is actually set
> > up? How do you get a server alias to point to a particular server with
> > a particular IP address within a large organisation? The outcome being
> > that when a user types the alias url into the address bar, the correct
> > page appears in the browser window
> >|||Well, If I understood you , take a look at Client Network Utility to add a
new alias to the server
<simonaj30@.yahoo.co.uk> wrote in message
news:1162896637.485781.85960@.m73g2000cwd.googlegroups.com...
>I would think so, as we use it in our organisation
> Uri Dimant wrote:
>> Hi
>> Does it somehow relate to SQL Server?
>>
>> <simonaj30@.yahoo.co.uk> wrote in message
>> news:1162895112.082029.220030@.i42g2000cwa.googlegroups.com...
>> > Can anyone descrive in principle how a Web server alias is actually set
>> > up? How do you get a server alias to point to a particular server with
>> > a particular IP address within a large organisation? The outcome being
>> > that when a user types the alias url into the address bar, the correct
>> > page appears in the browser window
>> >
>|||ok, many thanks
Uri Dimant wrote:
> Well, If I understood you , take a look at Client Network Utility to add a
> new alias to the server
>
> <simonaj30@.yahoo.co.uk> wrote in message
> news:1162896637.485781.85960@.m73g2000cwd.googlegroups.com...
> >I would think so, as we use it in our organisation
> > Uri Dimant wrote:
> >> Hi
> >>
> >> Does it somehow relate to SQL Server?
> >>
> >>
> >> <simonaj30@.yahoo.co.uk> wrote in message
> >> news:1162895112.082029.220030@.i42g2000cwa.googlegroups.com...
> >> > Can anyone descrive in principle how a Web server alias is actually set
> >> > up? How do you get a server alias to point to a particular server with
> >> > a particular IP address within a large organisation? The outcome being
> >> > that when a user types the alias url into the address bar, the correct
> >> > page appears in the browser window
> >> >
> >|||You right Uri...
Post this in question in Internet Information service(IIS) group. This is
done through IIS configuration.
This is SQL Server group.
vinu
<simonaj30@.yahoo.co.uk> wrote in message
news:1162896637.485781.85960@.m73g2000cwd.googlegroups.com...
>I would think so, as we use it in our organisation
> Uri Dimant wrote:
>> Hi
>> Does it somehow relate to SQL Server?
>>
>> <simonaj30@.yahoo.co.uk> wrote in message
>> news:1162895112.082029.220030@.i42g2000cwa.googlegroups.com...
>> > Can anyone descrive in principle how a Web server alias is actually set
>> > up? How do you get a server alias to point to a particular server with
>> > a particular IP address within a large organisation? The outcome being
>> > that when a user types the alias url into the address bar, the correct
>> > page appears in the browser window
>> >
>sql

How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it, I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for each
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file of
multiple backups to restore via script.
Any help much appreciated -Thanks
Amelia
Read this greate article
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks
|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:

> Amelia
> Read this greate article
> <http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> it, I
> DB
> each
> or
> of
>
>

How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it,
I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for eac
h
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file o
f
multiple backups to restore via script.
Any help much appreciated -ThanksAmelia
Read this greate article
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:

> Amelia
> Read this greate article
> <[url]http://vyaskn.tripod.com/ sql_server_administration_best_practices
.htm#Step1[/ur
l]
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> it, I
> DB
> each
> or
> of
>
>

How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it, I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for each
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file of
multiple backups to restore via script.
Any help much appreciated -ThanksAmelia
Read this greate article
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:
> Amelia
> Read this greate article
> <http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> > --administaiting best practices
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> > Instead of creating one file with the different Differential Backups in
> it, I
> > would like to create seperate files so I can script it easier - like the
> DB
> > Maintenance Plan wizard generates a job which creates seperate files for
> each
> > of the Full Backups.
> >
> > I cannot seem to find the option to specify this thru the GUI/Script for
> > Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> > create diff backup except via script?) you only get the choice of Append
> or
> > Overwrite Existing Media. Or alt, how do I specify which Backup in my file
> of
> > multiple backups to restore via script.
> >
> > Any help much appreciated -Thanks
>
>

How to create SELECT?

I have the two following tables:

tblProject:
ProjectID
5001
5002
6001
6002
7001
7002

tblProject_type:
ProjectTypeID ProjectIDFrom ProjectIDTo
A 5000 5999
A 7000 7999

I need to create a SELECT statement which shows all records from
tblProjects WHERE ProjectTypeID = A. In this case every project except
6001 and 6002.

Unfortunately I can't have the ProjectTypeID in tblProject because each
project can belong to many project types. I know it sounds crazy but
that's the way my customers company is organized.

I'm very grateful for help in the right direction!

Regards,

SCREATE TABLE tblProjects(ProjectID INT)
INSERT INTO tblProjects(ProjectID) VALUES(5001)
INSERT INTO tblProjects(ProjectID) VALUES(5002)
INSERT INTO tblProjects(ProjectID) VALUES(6001)
INSERT INTO tblProjects(ProjectID) VALUES(6002)
INSERT INTO tblProjects(ProjectID) VALUES(7001)
INSERT INTO tblProjects(ProjectID) VALUES(7002)

CREATE TABLE tblProject_type(ProjectTypeID CHAR(1),ProjectIDFrom
INT,ProjectIDTo INT)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
VALUES('A',5000,5999)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
VALUES('A',7000,7999)

SELECT ProjectID
FROM tblProjects
WHERE EXISTS (SELECT * FROM tblProject_type
WHERE ProjectID BETWEEN ProjectIDFrom AND ProjectIDTo
AND ProjectTypeID='A')|||sta...@.gmail.com wrote:
> I have the two following tables:
> tblProject:
> ProjectID
> 5001
> 5002
> 6001
> 6002
> 7001
> 7002
> tblProject_type:
> ProjectTypeID ProjectIDFrom ProjectIDTo
> A 5000 5999
> A 7000 7999
> I need to create a SELECT statement which shows all records from
> tblProjects WHERE ProjectTypeID = A. In this case every project except
> 6001 and 6002.

Try:

SELECT projectid
FROM tblProject AS P
WHERE NOT EXISTS
(SELECT *
FROM tblProject_type AS T
WHERE P.projectid BETWEEN T.projectidfrom AND T.projectidto
AND T.projecttypeid = 'A');

> Unfortunately I can't have the ProjectTypeID in tblProject because each
> project can belong to many project types. I know it sounds crazy but
> that's the way my customers company is organized.

Instead of what you posted, a more common solution would be:

CREATE TABLE tblProject_ProjectType
(projectid INTEGER NOT NULL
REFERENCES tblProject (projectid),
projecttypeid CHAR(1) NOT NULL
REFERENCES tblProjectType (projecttypeid),
PRIMARY KEY (projectid, projecttyypeid);

There isn't necessarily anything wrong with what you posted and your
version certainly could make for a smaller table but it's also hard to
avoid redundancy. Specifically, you would have to use a trigger to
prevent overlapping ranges of rows for the same type - otherwise you
could get duplicate rows out of joins, which could give you incorrect
results.

The benefit of my tblProject_ProjectType version is that there is no
redundancy and joins to the table are always equijoins.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank's a lot for excellent help!

Regards,

S|||Thank's a lot for excellent help!

Regards,

Friday, March 30, 2012

How to Create Store Procedure

How to create sql server login for workgroup computer?

How to create SQL Server Express server (INSTANCENAME) programmatically?

how to create sql server 2005 instance(after uninstalling sql server express)

How to create SQL Login audit log?

How to create SQL from XSD file

How to create sql for retrieving 10 records per time in .NET

how to create sql database in visual basic

How to create sproc with unlimited number of parameters?

How to create spool in PL/SQL

How to create slideshow of a report?

How to create Site Map based on Catalog table

How to create simple insert trigger

How to create server alias

How to create server alias

How to create server alias

How to create seperate Differential Backup Files

How to create seperate Differential Backup Files

How to create seperate Differential Backup Files

How to create SELECT?

copy database

Blog Archive

About Me