copy database: 2012

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,

How to create SELECT QUERRY FOR "A CHANGE ORDER FORM" in ASP?

Hello.
Hope you are all well and having a good day.
I've got a problem i would like you to help me out with. My
company has got a client who sells meat and my senior tech lead
has customized the cart to fit their needs and have created a
despatch table with a "simple asp form" that allows them
to scan their products in with a wi-fi scanner.

The despatch table has the following fields:
1. OrderID
2. Product
3. Quantit
4. sellbydate
5. traceabilitycode and
6. Weight
Question 1: how do i create a form in asp that allow a user to
view the order no, product and quantity
celibate,traceabilitybycode and Weight after scanning the
details into the simple form created?
Question 2: How can i create an asp form that allows users to
search for the Order that populates the orderid, Product,
quantity?
Question 3: How can i create an asp for that allows users to
search for Product with orderid,traceabilitycode and quantity?
Question 4: How can i create an asp form that allows users to
Trace: Orderid, traceabilitycode, sellbydate and Quantity?

Please find below the source code of both the:
despatchLotsRowsSimpleForm.asp that allow them to scan details
into the table and the despatchLotsExec.asp:

THE FORM:
<!#include file="../includes/settings.asp">
<!#include file="../includes/getSettingKey.asp">
<!#include file="../includes/sessionFunctions.asp">
<!#include file="../includes/databaseFunctions.asp">
<!#include file="../includes/screenMessages.asp">
<!#include file="../includes/currencyFormat.asp"
<%
on error resume next

dim mySQL, connTemp, rsTemp

' get settings
pStoreFrontDemoMode =
getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pAuctions = getSettingKey("pAuctions")
pAllowNewCustomer = getSettingKey("pAllowNewCustomer")
pNewsLetter = getSettingKey("pNewsLetter")
pStoreNews = getSettingKey("pStoreNews")
pSuppliersList = getSettingKey("pSuppliersList")
pRssFeedServer = getSettingKey("pRssFeedServer")

%>
<b><%=getMsg(10021,"despatch")%></b>
<br>
<%=getMsg(10024,"Enter despatch ...")%
The despatchExec.asp Page
<%
' WebShop 3.0x Shopping Cart
' Developed using ASP
' August-2002
' Email(E-Mail address blocked: See forum rules) for further information
' (URL address blocked: See forum rules)
' Details: add e-mail to newsletter list
%






<!--#include file="../includes/sendMail.asp"--
<%

on error resume next

dim mySQL, connTemp, rsTemp, pEmail

' get settings

pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pEmailSender= getSettingKey("pEmailSender")
pEmailAdmin= getSettingKey("pEmailAdmin")
pSmtpServer= getSettingKey("pSmtpServer")
pEmailComponent= getSettingKey("pEmailComponent")
pDebugEmail= getSettingKey("pDebugEmail")
pGiftRandom= getSettingKey("pGiftRandom")
pPercentageToDiscount= getSettingKey("pPercentageToDiscount")
pStoreLocation = getSettingKey("pStoreLocation")

' form
pidOrder = lcase(getUserInput(request.form("idOrder"),50))
pProduct = lcase(getUserInput(request.form("product"),50))
pQuantity = lcase(getUserInput(request.form("quantity"),50))

pPriceToDiscount = 0

' insert despatch

mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('"&pidOrder& "', '" &pProduct& "', '" &pQuantity&"')"

call updateDatabase(mySQL, rstemp, "despatchExec")

pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf&getMsg(311,"To opt-out click at:") & " (URL address blocked: Seeforum rules)="&pEmail

response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed"))

call closeDb()
%
Thank you for your help in advance..
Regards,
philip

That's ASP, not ASP.NET, and you definately aren't using the SqlDataSource control. You may have better luck asking your question in a different forum.

|||

Thanks Motley...

sql

How to create second publisher record on the server?

Hello...
What we are trying to accomplish is to be able to replicate from Sql Server 2000 to some laptops running MSDE across the internet. These laptops are NOT part of our domain. I have set up a publication to allow for anonymous pull subscriptions for transa
ctional replication. Everything works fine now...except that for my test I had to setup/configure the laptop with a Hosts and LMHosts file so that it could resolve the server's name from its fully qualified name. This will be a pain for us to implement
and deploy....as we will have hundreds of client machines connecting to our server. The problem has to do with a sql server table that stores the name of the server publisher & distributor.
I noticed (via the sql server profiler tool) that when replication begins, there is some intial login/handshaking that occurs and one of the principal commands that gets executed is something like: exec sp_helpdistpublisher N'ServerName'. This stored pr
oc queries the MSDistPublisher table in the MSDB database. The stored proc returns a single row of data which includes our server's name...as it has been setup...it's machine name. This table does not include (obviously) a record that has the server's fu
lly qualified domain name (ServerName.Domain.Com).
My question is: is there a way to add an additional row of data and use the fully qualified domain name?
thanks for any help.
- DW
in your application, can't you hard code the publisher name with the FQDN
for the publisher/distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DW" <DW@.discussions.microsoft.com> wrote in message
news:62391DE0-137B-46EE-9024-9E679829FD84@.microsoft.com...
> Hello...
> What we are trying to accomplish is to be able to replicate from Sql
Server 2000 to some laptops running MSDE across the internet. These laptops
are NOT part of our domain. I have set up a publication to allow for
anonymous pull subscriptions for transactional replication. Everything
works fine now...except that for my test I had to setup/configure the laptop
with a Hosts and LMHosts file so that it could resolve the server's name
from its fully qualified name. This will be a pain for us to implement and
deploy....as we will have hundreds of client machines connecting to our
server. The problem has to do with a sql server table that stores the name
of the server publisher & distributor.
> I noticed (via the sql server profiler tool) that when replication begins,
there is some intial login/handshaking that occurs and one of the principal
commands that gets executed is something like: exec sp_helpdistpublisher
N'ServerName'. This stored proc queries the MSDistPublisher table in the
MSDB database. The stored proc returns a single row of data which includes
our server's name...as it has been setup...it's machine name. This table
does not include (obviously) a record that has the server's fully qualified
domain name (ServerName.Domain.Com).
> My question is: is there a way to add an additional row of data and use
the fully qualified domain name?
> thanks for any help.
> - DW

how to create sdf file in c#

hi ,

i just want to create a sdf file using Sql server 2005 CE. I am very new to CE.

can anybody tell me how to create that file?

thanx in advance

Look into SqlCeEngine class.

|||San,

I have a whole series on my blog on how to use SSCE.
http://arcanecode.wordpress.com/arcane-lessons/

Lesson 2 talks about how to create the sdf, lesson 3 the table, and so on.

Hope it helps,

Arcane
|||

this is in vb.net just convert it

to C#

Imports System.Data.SqlServerCe

Public Class Form3

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim repl As New SqlCeReplication()

repl.InternetUrl = "http://ServerName/snapshot/sqlcesa30.dll"
repl.InternetLogin = "<...>"
repl.InternetPassword = "<...>"
repl.Publisher = "ServerName"
repl.PublisherDatabase = "Merchandising_Dev"
repl.PublisherSecurityMode = SecurityType.DBAuthentication
repl.Publication = "DataPublication"
repl.Subscriber = "Sub1"
repl.HostName = "1" ' This is a prameter for filter the puplication'
repl.PublisherPassword = "password"
repl.PublisherLogin = "Sub1"

repl.SubscriberConnectionString = "Data Source=""\program files\aa.sdf"";Max Database Size=128;Default Lock Escalation =100;"
Try
repl.AddSubscription(AddOption.CreateDatabase)
repl.Synchronize()

Catch err As SqlCeException
MessageBox.Show(err.ToString)
End Try

End Sub
End Class

how to create sdf file in c#

hi ,

i just want to create a sdf file using Sql server 2005 CE. I am very new to CE.

can anybody tell me how to create that file?

thanx in advance

Look into SqlCeEngine class.|||San,

I have a whole series on my blog on how to use SSCE.
http://arcanecode.wordpress.com/arcane-lessons/

Lesson 2 talks about how to create the sdf, lesson 3 the table, and so on.

Hope it helps,

Arcane
|||

this is in vb.net just convert it

to C#

Imports System.Data.SqlServerCe

Public Class Form3

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim repl As New SqlCeReplication()

repl.SubscriberConnectionString = "Data Source=""\program files\aa.sdf"";Max Database Size=128;Default Lock Escalation =100;"
Try
repl.AddSubscription(AddOption.CreateDatabase)
repl.Synchronize()

Catch err As SqlCeException
MessageBox.Show(err.ToString)
End Try

End Sub
End Class

How to create scripts (drop then create) in sql 2005

I'm using sql 2005. One thing I could do in sql 2k which I don't see in sql
05 is when I script objects in EM it would check to see if the object exists
first, and if so, it would drop it before creating it. I don't see such an
option in sql 05 SSMS. Its a much better scripting wizard but seems to be
missing the most basic functions.
any advice?
Thanks.
moondaddy@.noemail.noemailAs far as I know there is no such choice in the wizard, you would need to
run it twice, one for 'Generate DROP statements only' and one for 'Generate
CREATE statements only'.
Note that the SP1 description still shows 'Generate the script as DROP
statements, CREATE statements, or DROP statements followed by CREATE
statements."
Ben Nevarez, MCDBA, OCP
Database Administrator
"moondaddy" wrote:

> I'm using sql 2005. One thing I could do in sql 2k which I don't see in s
ql
> 05 is when I script objects in EM it would check to see if the object exis
ts
> first, and if so, it would drop it before creating it. I don't see such a
n
> option in sql 05 SSMS. Its a much better scripting wizard but seems to be
> missing the most basic functions.
> any advice?
> Thanks.
> --
> moondaddy@.noemail.noemail
>
>|||Check out http://www.sqlteam.com/item.asp?ItemID=23185 and
http://weblogs.sqlteam.com/billg/ar...1/22/8414.aspx.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"moondaddy" <moondaddy@.noemail.noemail> wrote in message
news:uLMUBQWdGHA.3712@.TK2MSFTNGP03.phx.gbl...
> I'm using sql 2005. One thing I could do in sql 2k which I don't see in s
ql 05 is when I script
> objects in EM it would check to see if the object exists first, and if so,
it would drop it before
> creating it. I don't see such an option in sql 05 SSMS. Its a much bette
r scripting wizard but
> seems to be missing the most basic functions.
> any advice?
> Thanks.
> --
> moondaddy@.noemail.noemail
>

How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...sql

How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?
Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJ
Try something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] =
'temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:

>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJTry something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] =
'temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:

>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJTry something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] ='temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:
>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

how to create rule which update other columns

Hi,

I have a table with the following columns:
ID INTEGEDR,
Name VARCHAR(32),
Surname VARCHAR(32),
GroupID INTEGER,
SubGroupOneID INTEGER,
SubGroupTwoID INTEGER

How can I create a rule/default/check which update SubGroupOneID &
SubGroupTwoID columns when GroupID for example is equal 15 on
MSSQL2000.

It is imposible to make changes on client, so I need to check
inserted/updated value of GroupID column and automaticly update
SubGroupOneID & SubGroupTwoID columns.

Sincerely,
Rustam BogubaevYou haven't explained what value(s) you want the subgroup columns updated
to.

If the two subgroup columns are solely determined by the the Groupid then
the answer is not to put those columns in the table at all because to do so
destroys normalisation in your schema. Put the subgroups in a separate,
related Groups table.

Rules and Check constraints don't actually change data - they just validate
it. Use a trigger update dependent columns when data is inserted or updated.
See CREATE TRIGGER in Books Online for details.

--
David Portas
----
Please reply only to the newsgroup
--|||You can do this in a trigger, assuming the primary key value is never
changed. For example:

CREATE TABLE MyTable
(
ID int
CONSTRAINT PK_MyTable PRIMARY KEY,
Name varchar(32),
Surname varchar(32),
GroupID int,
SubGroupOneID int,
SubGroupTwoID int
)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE t
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM MyTable t
JOIN inserted i ON t.ID = i.ID
WHERE i.GroupID = 15
GO

INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
'surname', 1)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
'surname', 2)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
'surname', 15)
SELECT * FROM MyTable
UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
SELECT * FROM MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
news:20046852.0401202332.386aa33c@.posting.google.c om...
> Hi,
> I have a table with the following columns:
> ID INTEGEDR,
> Name VARCHAR(32),
> Surname VARCHAR(32),
> GroupID INTEGER,
> SubGroupOneID INTEGER,
> SubGroupTwoID INTEGER
> How can I create a rule/default/check which update SubGroupOneID &
> SubGroupTwoID columns when GroupID for example is equal 15 on
> MSSQL2000.
> It is imposible to make changes on client, so I need to check
> inserted/updated value of GroupID column and automaticly update
> SubGroupOneID & SubGroupTwoID columns.
> Sincerely,
> Rustam Bogubaev|||Hi dan

This problem was one I am grappling with, and despite its apparent
simplicity, is not touched on in any simple way in SQL books online, or
other 3rd party books, or rarely in Deja archives.

Could I impose on you a little more to elaborate on a couple of points in
this eample please ?

> For example:
> CREATE TABLE MyTable
> (
> ID int
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Name varchar(32),
> Surname varchar(32),
> GroupID int,
> SubGroupOneID int,
> SubGroupTwoID int
> )
> GO

Yup, I can handle that, lets make a table

> CREATE TRIGGER TR_MyTable
> ON MyTable FOR INSERT, UPDATE
> AS
> UPDATE t
> SET
> SubGroupOneID = 1,
> SubGroupTwoID = 1
> FROM MyTable t
> JOIN inserted i ON t.ID = i.ID
> WHERE i.GroupID = 15
> GO

Making a trigger i can handle, but ..
Bits that puzzle me
UPDATE t, can you explain the reason for and the use of 't' ?
Is that a temporary table where data is stored in the process?

the MyTable t bit, assuming t is a table, should that be MyTable, t -
joining two different tables ?

> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> 'surname', 1)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> 'surname', 2)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> 'surname', 15)
> SELECT * FROM MyTable
> UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> SELECT * FROM MyTable
> GO
I presume this next bit is an alternative method, where we insert 3 records,
and then do a bulk update to make
the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand the
"GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
online, as it rarely gives me any usefull results from my inquiries :-)

The other bit that puzzles me is why the "SELECT * FROM MyTable" is needed.
Does the UPDATE row not process all records automatically when the GO is
encountered?

Many thanks for any help you can provide.

> "Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
> news:20046852.0401202332.386aa33c@.posting.google.c om...
> > Hi,
> > I have a table with the following columns:
> > ID INTEGEDR,
> > Name VARCHAR(32),
> > Surname VARCHAR(32),
> > GroupID INTEGER,
> > SubGroupOneID INTEGER,
> > SubGroupTwoID INTEGER
> > How can I create a rule/default/check which update SubGroupOneID &
> > SubGroupTwoID columns when GroupID for example is equal 15 on
> > MSSQL2000.
> > It is imposible to make changes on client, so I need to check
> > inserted/updated value of GroupID column and automaticly update
> > SubGroupOneID & SubGroupTwoID columns.
> > Sincerely,
> > Rustam Bogubaev|||> > CREATE TRIGGER TR_MyTable
> > ON MyTable FOR INSERT, UPDATE
> > AS
> > UPDATE t
> > SET
> > SubGroupOneID = 1,
> > SubGroupTwoID = 1
> > FROM MyTable t
> > JOIN inserted i ON t.ID = i.ID
> > WHERE i.GroupID = 15
> > GO
> Making a trigger i can handle, but ..
> Bits that puzzle me
> UPDATE t, can you explain the reason for and the use of 't' ?
> Is that a temporary table where data is stored in the process?
> the MyTable t bit, assuming t is a table, should that be MyTable, t -
> joining two different tables ?

The 't' is simply an alias declared for MyTable so that I didn't need to
specify the full table name when qualifying column names. The following is
functionally identical. Both examples join MyTable with the inserted table
in order to identify newly inserted or updated rows.

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE MyTable
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM inserted
WHERE MyTable.ID = inserted.ID AND
inserted.GroupID = 15
GO

> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> > 'surname', 1)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> > 'surname', 2)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> > 'surname', 15)
> > SELECT * FROM MyTable
> > UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> > SELECT * FROM MyTable
> > GO
> I presume this next bit is an alternative method, where we insert 3
records,
> and then do a bulk update to make
> the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand
the
> "GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
> online, as it rarely gives me any usefull results from my inquiries :-)

'GroupID IN(1, 2)' is equivalent to 'GroupID = 1 OR GroupID = 2'. You can
find details of 'IN' in the SQL 2000 Books Online. I was able to find the
BOL topic by clicking the index tab, typing 'IN' and double-clicking on the
'IN' keyword in the list. I then selected the 'IN' title from the topic
list.

> The other bit that puzzles me is why the "SELECT * FROM MyTable" is
needed.

The SELECT statements before and after the UPDATE are to display the data
before and after the UPDATE. These are only for illustration.

> Does the UPDATE row not process all records automatically when the GO is
> encountered?

GO is a batch separator. Tools like Query Analyzer execute the preceding
batch of SQL statements when a GO is encountered in the script. The insert,
update and select statements are executed sequentially as part of the same
batch.

--
Hope this helps.

Dan Guzman
SQL Server MVPsql

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

How to Create Reports?

Hi --
We have SQL Server 2000 and I just found out that it comes with Reporting
Services. How do I create reports?
Thanks
RichRead the SQL Server Help File to get started, there are a lot of good
tutorials.
"Rich Morey" <rwmorey71@.hotmail.com> wrote in message
news:uV%23a1doCIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi --
> We have SQL Server 2000 and I just found out that it comes with Reporting
> Services. How do I create reports?
> Thanks
> Rich
>

How to create reports dinamically?

Hello. Can I make a report for example with all the columns and make a
program in asp.net in which the user can select the columns he wants to see?
Thanks.Yes of course, unless RDL is a open Language you can stick your parts
together as the User wants. But I would keep in mind that there are already
thrid party tools to do that, therefore looking at these will eventually
save you time and money fordeveloping that on your own.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hello. Can I make a report for example with all the columns and make a
> program in asp.net in which the user can select the columns he wants to
> see?
> Thanks.
>|||Your page doesnt work
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> escribió
en el mensaje news:elyDct0dFHA.220@.TK2MSFTNGP12.phx.gbl...
> Yes of course, unless RDL is a open Language you can stick your parts
> together as the User wants. But I would keep in mind that there are
already
> thrid party tools to do that, therefore looking at these will eventually
> save you time and money fordeveloping that on your own.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
> news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> > Hello. Can I make a report for example with all the columns and make a
> > program in asp.net in which the user can select the columns he wants to
> > see?
> >
> > Thanks.
> >
> >
>sql

how to create report without Report wizard

Hi,

Whenever I tried creating reports under-->BUsiness Intelligence Projects-->REport wizard or Report type.Everytime Report wizard opens.What i am trying to say is I want to build manually(Like in crytsal reports).But I don't see any option.One more Question---Once after Designing the table in Report Wizard,like Page fields,Group fields,Diaplay Fields,Is there any option that I can add more fields OR Change Existing fields.Do I have to do in Report designer Layout??I don't see any option for doing in SSRS 2000.Sorry for small questions,I am very new to SSRS2000 ,and programming too.Thank you.

HI,srijyothi:

You can check out this article about:

Generate Ad-hoc Reports with Microsoft Reporting Services 2005

http://www.devx.com/dbzone/Article/28047/1954?pf=true

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a

convenience to you. Microsoft does not control these sites and has not tested any software or information found on these

sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or

information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions

you to make sure that you completely understand the risk before retrieving any software from the Internet.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI,srijyothi:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

How to create report using WebService as DataSource.

Hi,

We created a simple web serivce having a Web Method which accepts a string parameter and returns "Hello World" concatenated with parameter value. We tested this web service by browsing it through IE and it was working fine by accepting a parameter and displaying the "Hello World " concated with the value given through the parameter when clicked on "Invoke" button.

We now created a Reporting Services 2005 project using BI having a report for which the data source will be the web service which is mentioned above. We defined a parameter for the report which will pass the value to the webservice and the report will display the returned value from the web service.

When we have done the report and executed by giving a value to the parameter and clicking on view report button it was displaying the "Hello World" but the parameter which we passed to the web service is not getting concatenated. When we tried to trace the application we could see that the value passed to the web service web method was comming as null. So kindly help me where I went wrong in developing such a kind of report which is not passing a parmeter to webservice.

Kindly provide us the solution in order to make this issue to be resolved.

Thanks in Advance.I can concur with VDeevi, I was able to recreate the exact same issue. In my case I'm passing parameters to a web service that is returning a dataset. When I set a default value in the webmethod it returns a dataset successfully. My problem is also that the parameters are not getting passed to the web service.

What I think is strange is that I've done the BOL sample with the SSRS web service Listchildren method and it works successfully. Does that mean that the problem is not in SSRS but in my implementation of the web service?

This works:

<WebMethod()> _
Public Function GetTestDataset() As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(1)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

This doesn't work:

<WebMethod()> _
Public Function GetTestDataset(ByVal ID As Integer) As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(ID)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

|||

It's probably not your implementation, but in the parsing specifics of the SoapAction and Namespace.

For example, if your SoapAction is http://tempuri.org/HelloMyNameIs, we split it into http://tempuri.org and HelloMyNameIs throwing away the separating / character.

But if you look at the way webservice is defined, the namespace is actually http://tempuri.org/ - with a trailing /.

So in this case, when method name/namespace can not be correctly derived from SoapAction and when SoapAction can not be correctly derived from method name (in the above case we would get http://tempuri.org//HelloMyNameIs) you should use both <Method …/> and <SoapAction …/>, e.g.

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloMyNameIs"/>
<SoapAction>http://tempuri.org/HelloMyNameIs</SoapAction>
</Query>

We are working on updates to the documentation.

|||Thanks Brain, it really helped us to solve this issue.

Thanks once again,|||Worked for me as well. Thanks a lot!

How to create report parameter programmatically?

Hi,

I'm working on custom report manager. It manages "report entities" and

"report templates" (actually, RDLs uploaded on the server) and stores

one-to-many relation between them. The task is to store

"MasterEntityID" report parameter in every RDL and keep it up in actual

state whether RDL is being assigned to another entity or new RDL is

being uploaded and assigned. I've covered the first issue with

SetReportParameters() web method, but how should I deal with the second

one? Uploaded RDL may be short of the param, so I have to add it

programmatically while uploading.

Thanks,

AnatolyWhat exactly is the second issue? it looks like you have a custom application layer which can have complete control over the parameters.|||Yes, but actually I'm interested in adding new report parameter via web service. My application should do this if a developer of the uploading report hasn't defined necessary parameter manually. Is it possible?|||

The new parameter must be added to the report definition under the <ReportParameters> element. SetReportParameters won't let you configure the new parameter if it is not prior added to the report definition. This makes sense considering that you will probably need also to link the parameter to the report query. In fact, you won't need SetReportParameters for new parameters b/c once you add the parameter to RDL and call SetReportDefinition, the parameter will be created automatically for you.

As a side note, if you your application requires extensive reading/writing to RDL, you may benefit from buidling an object model on top of RDL that knows how to deserialize/serialize from/to RDL to avoid tedious XMLDOM programming and XPATH references.

|||

Teo, thanks for your clear reply. I'd think of object model. Mostly because I'm going to generate some rdl templates (with datasources, datasets and queries definitions) programmatically in future.

Thank you once again and Happy New Year!)

|||Happy New Year to you too.

how to create report from multiple tables

hi all,
i want to create report from 4 to 5 tables by providing one value
through which i move from one table to other.
how that is possible . can any body help me .
looking for reply
good bye
umarWhen creating the report, add all the required tables and field and link them by common column|||hi
thanks for reply but i want to create report via code by providing
one value at run time and clicking button to generate report.

Thanking u for ur help
good bye
umar|||hi
try like this

Dim cr As New SaleBalanceReport
da = New SqlDataAdapter("select s.*, d.* from saleorder s, dispatchdetails d where d.Sale_note_no=s.saleorderno and s.saleorderno='" & txtsalenote.Text & "'", con)
da.Fill(ds, "salebalance")
cr.SetDataSource(ds.Tables(0))
SBRViewer1.ReportSource = cr
SBRViewer1.Refresh()
ds.Clear()

happy programming
kameswararao

how to create report by month from vb

hi all, i m using this code to display report of month selected from combo1
it shows records but only single of current month and moreover if there is no record of a month it shows previous result.
plz help me soon
bye

///////////
mon = Combo1.Text
cmd.CommandText = "Select * from travel"
rs2.Open cmd, , adOpenStatic, adLockReadOnly
Do While Not rs2.EOF
mo = Format(rs2.Fields("report_date"), "mmm")

If mo = mon Then

crxreport.RecordSelectionFormula = "{travel.travel_id} = " & rs2.Fields("travel_id")
CRViewer1.ReportSource = crxreport

End If

rs2.MoveNext

Loop
rs2.Close

CRViewer1.Zoom (100)
CRViewer1.ViewReportu cn do it easily by inserting the required field of that month into a temp table.

& then link the temp table to ur report

i think it will do|||thanks for response,
sorry i not understand what u mean by temp table
and how i can pass month name from combo box in vb to crystal report for displaying record of that month

waiting for reply
bye|||insert a combobox containg all the month in the form where u u load ur report .
Then make a query(for ur defined month from the combobox) 2 select the data from the original .insert these data 2 a temp table which is linked 2 the crystal report.

Then load the report.

bye.|||thanks
please tell me what is problem in my code given above
or give some sample code
to pass month name from vb form
to report
thankssql

How to Create reference for Composite key

Hi All,

Can anyone tell me how to create a reference for composite key.

For ex, I have created tblEmp table successfully.

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

constraint pk_add

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) references tblEmp(empId),

RegId varchar(100) references tblEmp(RegId)

)

But it gives error like

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Could anyone please let me know how to create reference for composite key.

Thanks in advance,

Arun.

The below code will do:

create table tblEmp( empIdvarchar(100), RegIdvarchar(100), empnamevarchar(100),constraint pk_addprimary key(empId, RegId) )create table tblAccount( acctIdvarchar(100)primary key, empIdvarchar(100) , RegIdvarchar(100),constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId) )

In case you've already created both tables, you can use below query to set the foreign key in the account table.

alter table tblAccountadd constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId)

Hope this will help.

|||

Hi,

We can also do it by

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) ,

RegId varchar(100) ,

foreign key(empId,RegId) references tblEmp(empId,RegId)

)

It works perfect

|||

arunkumar.niit:
primary key(empId, RegId)

arunkumar.niit:
foreign key(empId,RegId) references tblEmp(empId,RegId)

Hey, your queries are exactly the same as what I've posted except one thing that I've given my own names to the constraints and you've left it over the SQL Server.

|||

Hi,

Thanks for your reply.

Thanks & Regards,

Arun.

Friday, March 30, 2012

Blog Archive

About Me