Creating a view with multiple join criteria

This is a silly question but i must present it.
I am creating a view between two tables. Both tables are identified by
two pieces of data:
User ID and Product Name
fooshly those who created the tables did not create the Product name to
be similar. Now i'm trying to join on dissimilar information. I
cannot change the original tables, and i'm stuck making reports trying
to validate information with these joins.
one piece of information. If i can restrict product names to the first
three characters then i can do a successful table join. I can't think
for the life of me how to do it.. any suggestions?Try this:
CREATE VIEW vw_ViewName
WITH SCHEMABINDING
AS
SELECT A.[FieldName], B.[FieldName], (etc.....)
FROM TableA A
JOIN TableB B
ON Left(A.ProductName, 3) = Left(B.ProductName, 3)
AND (A.UserID = B.UserID)
"epikto" <epikto@.gmail.com> wrote in message
news:1142636261.915610.295790@.i39g2000cwa.googlegroups.com...
> This is a silly question but i must present it.
> I am creating a view between two tables. Both tables are identified by
> two pieces of data:
> User ID and Product Name
> fooshly those who created the tables did not create the Product name to
> be similar. Now i'm trying to join on dissimilar information. I
> cannot change the original tables, and i'm stuck making reports trying
> to validate information with these joins.
> one piece of information. If i can restrict product names to the first
> three characters then i can do a successful table join. I can't think
> for the life of me how to do it.. any suggestions?
>|||Excellent. I knew it was simple... Just couldn't find the right syntax
with SQL. Thank you!sql

Creating a VIEW with multiple DATATABLES

I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.

Hi johncosmas

check this link you will get what you want, with code

http://www.java2s.com/Code/CSharp/Database-ADO.net/PopulateaDataSetobjectwithmultipleDataTableobjects.htm

hope it helps

Creating a unique constarint on a multiple null column

HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

Yes.

Since a Primary Key CONSTRAINT requires NOT NULL values, a UNIQUE index is the best alternative method to force a constraint on columns that can contain NULL values.

Creating a formula on a table column

Hi.
Some background first...
The tables are in SQL server 2000 and the front end is an Access
project.
I have a table with multiple ID fields which is populated by several
forms and other tables. The Main ID has a autonumber function on it. 1
other is made from various components from the row in the table.
The column i am having trouble with, all i want to do is increment it
by 1 ( just to prevent any duplication of numbers ) but i want to do
this on the table,
I could do it on the form when data is inputted, but due to large
number of forms etc... if it could be done on the table it would be
easier.
There is a formula function but i can't get it return the Max column
value from a table. Is this type of thing possible.
I can retun values, but how do i define a query within this function?
Many thanks for any help / advice
john."john" <j.w.horlock@.leeds.ac.uk> wrote in message
news:8456800a.0310020656.c97d681@.posting.google.com...
> Hi.
> Some background first...
> The tables are in SQL server 2000 and the front end is an Access
> project.
> I have a table with multiple ID fields which is populated by several
> forms and other tables. The Main ID has a autonumber function on it. 1
> other is made from various components from the row in the table.
> The column i am having trouble with, all i want to do is increment it
> by 1 ( just to prevent any duplication of numbers ) but i want to do
> this on the table,
> I could do it on the form when data is inputted, but due to large
> number of forms etc... if it could be done on the table it would be
> easier.
> There is a formula function but i can't get it return the Max column
> value from a table. Is this type of thing possible.
> I can retun values, but how do i define a query within this function?
> Many thanks for any help / advice
> john.
On the SQL table define the column thus:
ALTER TABLE tblFlibble(
Col1 int Identity (1,1) NOT NUL,
col2,
col3, etc)
If you already have data in the table and want to preserve the existing
values, change the IDENTITY to
IDENTITY(*number higher than your highest existing number*,1)
To find the number you have just created when you ad a row, select the
@.@.IDENTITY variable
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

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

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

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

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating 1 backup for multiple DB

is there a way to create one backup from multiple databases ?

Hello,

If you mean being able to append multiple database backusp to a single backup file, then yes this is possible. You do this via NOINIT:

backup database Database_A to disk = 'C:\DBBackupFile.BAK'

backup database Database_B to disk = 'C:\DBBackupFile.BAK' WITH NOINIT

Then, the file C:\DBBackupFile.BAK will contain two backup sets. To reference either of the backup sets, you then need to use the FILE clause of the restore command as such:

restore filelistonly from disk = 'C:\DBBackupFile.BAK' with FILE=2 (this referrs to Database_B)

Cheers,

Rob

|||

If what you are looking for is a backup at one point in time for multiple databases, so that they can all be brought back to a common point in time, the way to accomplish that is with marked transactions.

There is not a mechanism to create a single backup spanning multiple databases.

You can, however use the full recovery model to roll a group of databases forward to a single point in time.

The mechanism for making sure that this point is exactly coordinated across databases is to use the

BEGIN TRANSACTION 'MyTransaction' WITH MARK

statement to begin a marked transaction. If that is a distributed transaction (accomlished by touching each of the databases within the transaction), then the mark will be saved in each database's log.

You can then restore each database independantly, and apply logs using the STOPATMARK = 'MyTransaction' clause to cause all databases to roll forward to exactly the same point in time.

Creating "columns" from transaction data

Hi,
I have a transaction table that basically has the following fields
RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e.
quantities in multiple periods)
I need to convert an entire table of these records to one that looks like
this...
RedId , P1Qty, P2Qty, P3Qty, P4Qty etc...
Which has one row per RecId and places the quantity (quantities) in the
appropriate "period" column(s) based on the value(s) of "Period" in
the transaction file for each record.
I've done this before in Access, using the IIF function for each of P1...P4
columns (IIF(Period = 1, Quantity, 0), IIF(Period = 2, Quantity, 0) for
each of the columns of the derived table I was making. This doesn't seem to
work for SQL Server. IIF exists, but I can't get the computed columns to
work properly (Syntax error near "=").
So, I've thought about...
1) Use CreateTable to create my derived table with periods as columns,
2) Write a series of INSERT queries that reads the transaction file for
each possible individual value for "Period" and populates the appropriate
column in the derived table
3) Sum the derived table on every column by RecId
4) Run the whole batch as the SelectCommand of my DataAdapter. The last
command in the batch is Select * from DerivedTable and this is the table
that the DataSet gets.
There has to be a better way to do this?
Thanks.
BBMYou can use CASE expressions instead of IIF. But why would you ever create a
table like this? What you are asking for is a report not a table. Any
reporting tool will construct a cross tab report for you.
David Portas
SQL Server MVP
--|||Do you have any idea what First Normal Form is? You might want to
learn about RDBMS before you write any code.|||Thanks David, CASE was just what I'm looking for.
In this instance, this result set is used as one of the tables in
multi-table DataSet used on a fairly complex display.
Thanks again.
BBM
"David Portas" wrote:

> You can use CASE expressions instead of IIF. But why would you ever create
a
> table like this? What you are asking for is a report not a table. Any
> reporting tool will construct a cross tab report for you.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yes, in fact I do. I simplified the underlying data structure in my questio
n
to hopefully make it easier to reply to. I was only using the "extra" table
,
because I couldn't figure out how to get the result set I wanted in one pass
.
Thanks for your response anyway.
"--CELKO--" wrote:

> Do you have any idea what First Normal Form is? You might want to
> learn about RDBMS before you write any code.
>

Create view with data from multiple servers

Hi everyone,

I have 5 servers, all with identical databases just different data. I
have a rather lengthy SQL statement (in a View) to hit one database and
pull-in certain data, but I'd like to somehow run this same SQL
statement within the view but hit all 5 servers so we don't have 5
different versions of this data to mess with.

I'm not opposed to creating an update query in a stored procedure to
hit all 5 databases and update a table or even do this within a DTS,
but I'd prefer to keep it as simple as possible and as dynamic so the
users can simply run the view and get live data anytime based on all 5
tables.

Is this possible ?

Thanks,

rlanglySee "Creating a Partitioned View" in BOL.

AMB

--
Message posted via http://www.sqlmonster.com|||Ringo Langly (rlangly@.gmail.com) writes:
> I have 5 servers, all with identical databases just different data. I
> have a rather lengthy SQL statement (in a View) to hit one database and
> pull-in certain data, but I'd like to somehow run this same SQL
> statement within the view but hit all 5 servers so we don't have 5
> different versions of this data to mess with.
> I'm not opposed to creating an update query in a stored procedure to
> hit all 5 databases and update a table or even do this within a DTS,
> but I'd prefer to keep it as simple as possible and as dynamic so the
> users can simply run the view and get live data anytime based on all 5
> tables.

While you could set up a distributed partitioned view of the servers,
as suggested by Alejandro, but distributed partitioned views is
not really for the faint of heart. Anyway, it requires that the
tables has a partitioning column.

You can still up a view over the tables with UNION ALL, but the view
may not be updatable, and it sounds like you want to do this for an
update operation, but I might be misunderstanding something.

It's a little difficult to say that much more with that small amount
of information.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create view from cursor

I have multiple locations that I want to create views for each
individual location.

I am using a cursor to create the views for each location. So, the
cursor grabs site #1 then <should> create view_site_#1, then grab site
#2 and <should> create view_site_#2.

For some reason it doesn't like the view name with the @.site in it.
Any ideas of how to get this done?

Here's the cursor...

declare @.site varchar(5)

declare c_site cursor for
select station from VHAISLCAUDIA.VISN_SITE
order by station

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

CREATE VIEW Site_All_Data_+ @.site
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE (Site = @.site)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site
return

endThis is actually one of the few times that a cursor and dynamic SQL can
be useful; this administrative scripting is a great target for this
sort of stuff.

Anyway, you need to use dynamic SQL for this:

DECLARE @.tSite TABLE (site varchar(5))
INSERT INTO @.tSite
SELECT 'ABCDE'
UNION ALL
SELECT 'FGHIJ'

declare @.site varchar(5)
DECLARE @.SQL nvarchar(2000)

declare c_site cursor for
select site from @.tsite

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

SET @.SQL = 'CREATE VIEW Site_All_Data_' + @.site + '
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE Site = ''' + @.site + ''''

exec (@.SQL)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site

HTH,
Stu|||Worked like a charm!

Thanks for helping a developer that forgets the 'simple' stuff
sometimes.

db55

Create variables with same format in one single step?

I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below. Thanks a lot!

--Current Query--
SELECT cast(0.0 as money) as balance_1
,cast(0.0 as money) as balance_2
,cast(0.0 as money) as balance_3
,cast(0.0 as money) as balance_4
,cast(0.0 as money) as balance_5
,cast(0.0 as money) as balance_6
,cast(0.0 as money) as balance_7
,cast(0.0 as money) as balance_8
,account_no
,XXX
INTO table1
FROM account

Can I do something like this? This one didn't work.

SELECT balance_1 to balance_8 (cast 0.0 as money)
,account_no
,XXX
INTO table1
FROM accountIt's not a single step but this might work for you:

This might work for you:

declare @.balance_1 money,
@.balance_2 money,
@.balance_3 money,
@.balance_4 money,
@.balance_5 money,
@.balance_6 money,
@.balance_7 money,
@.balance_8 money

set @.balance_1 = 0
set @.balance_2 = 0
set @.balance_3 = 0
set @.balance_4 = 0
set @.balance_5 = 0
set @.balance_6 = 0
set @.balance_7 = 0
set @.balance_8 = 0

SELECT @.balance_1,
@.balance_2,
@.balance_3,
@.balance_4,
@.balance_5,
@.balance_6,
@.balance_7,
@.balance_8,
account_no,
XXX
INTO table1
FROM account|||I'm confused. There are no variables in the code you posted - you are
creating a table with eight columns. There is only one step in a query
- the whole query operates as one logical unit.

What you seem to be looking for is a syntax shortcut that saves you
typing out the column names. The best shortcut is probably to click and
drag a list of names from the Object Browser in Query Analyzer then
search and replace the rest. I expect you could do that in much less
time than it took to write out this question. :-)

Your query itself looks a little strange. Are you sure you can't
calculate the balances with a single query rather than SELECT followed
by an UPDATE / INSERT?

--
David Portas
SQL Server MVP
--|||(rong.guo@.gmail.com) writes:
> I would need to create multiple variables with the same format for
> future update, what I did is listing them separately, is there an easy
> way to combine them in one step? please see query below. Thanks a lot!
> --Current Query--
> SELECT cast(0.0 as money) as balance_1
> ,cast(0.0 as money) as balance_2
> ,cast(0.0 as money) as balance_3
> ,cast(0.0 as money) as balance_4
> ,cast(0.0 as money) as balance_5
> ,cast(0.0 as money) as balance_6
> ,cast(0.0 as money) as balance_7
> ,cast(0.0 as money) as balance_8
> ,account_no
> ,XXX
> INTO table1
> FROM account
> Can I do something like this? This one didn't work.
> SELECT balance_1 to balance_8 (cast 0.0 as money)
> ,account_no
> ,XXX
> INTO table1
> FROM account

You could build an string with the SQL statement by iterating from
1 to 8, and use EXEC() to execute that statement. However, this is
definnitely not recommendable.

I have no idea what you are up to, but columns are usually distinct
entities. If you find that you need balance_1 to balance_8, maybe
you should make them rows instead. That is how you work with array
data in SQL, rows with pairs (index, value).

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To answer the question I think you are trying to ask: No, there are not
arrays in SQL.

You create a table with a CREATE TABLE statement and you have to know
what it looks like before you type in the command.

You need to put the column names in the SELECT list. Using the SELECT
* option is a really bad idea for production code.

You need to give every column a meaningful name from your data model.
But fromthe look of this, you do not have a data model and are writing
code on the fly.|||Thank you ALL so much!!

I do have a bunch of other variables in the table (about 20), I only
listed account_no as an example of the variables.

Doing the calculation in a single step instead of using update is a
great idea, but for some reason, it created duplicates. I haven't found
out if it is due to the database duplicates or because of my query. I
will do further research... Thanks again...

--CELKO-- wrote:
> To answer the question I think you are trying to ask: No, there are
not
> arrays in SQL.
> You create a table with a CREATE TABLE statement and you have to know
> what it looks like before you type in the command.
> You need to put the column names in the SELECT list. Using the
SELECT
> * option is a really bad idea for production code.
> You need to give every column a meaningful name from your data model.
> But fromthe look of this, you do not have a data model and are
writing
> code on the fly.