I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go

insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go

drop table submain
go
drop table main
go
drop table combined
go

I want to select the SECOND newest record in a table,....is this possible?

Hi!
I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.
Can I do that?
/Johan Ch

You could do it like this:

SELECT TOP 1
*
FROM
(SELECT TOP 2 * FROM myTable Order by myDateTime DESC) AS A
ORDER BY
myDateTime ASC

i want to import a database from SQL Server to Oracle

hi
i am amit from india
i am new to this field
can you help to import a database from SQL Server to Oracle9i
Message posted via http://www.droptable.comUse DTS from SQL Server
"amit mota via droptable.com" wrote:

> hi
> i am amit from india
> i am new to this field
> can you help to import a database from SQL Server to Oracle9i
> --
> Message posted via http://www.droptable.com
>|||Hi,
There are many options
1. DTS
2. BCP OUT the data in sql server and use SQLLDR to load the data into
Oracle.
Thanks
Hari
SQL Server MVP
"Venu" <Venu@.discussions.microsoft.com> wrote in message
news:662F2701-F182-4783-8B7F-48C3EEC59280@.microsoft.com...[vbcol=seagreen]
> Use DTS from SQL Server
> "amit mota via droptable.com" wrote:
>|||thanks hari
its working by SQLLDR
Message posted via http://www.droptable.com

i want to import a database from SQL Server to Oracle

hi
i am amit from india
i am new to this field
can you help to import a database from SQL Server to Oracle9i
Message posted via http://www.sqlmonster.com
Use DTS from SQL Server
"amit mota via SQLMonster.com" wrote:

> hi
> i am amit from india
> i am new to this field
> can you help to import a database from SQL Server to Oracle9i
> --
> Message posted via http://www.sqlmonster.com
>
|||Hi,
There are many options
1. DTS
2. BCP OUT the data in sql server and use SQLLDR to load the data into
Oracle.
Thanks
Hari
SQL Server MVP
"Venu" <Venu@.discussions.microsoft.com> wrote in message
news:662F2701-F182-4783-8B7F-48C3EEC59280@.microsoft.com...[vbcol=seagreen]
> Use DTS from SQL Server
> "amit mota via SQLMonster.com" wrote:
|||thanks hari
its working by SQLLDR
Message posted via http://www.sqlmonster.com

i want to create database

Hi
i have installed sql server and i didn't find any GUI tool like sqlserver 2000 to create database or list the avilable databases or run sql statements ,,,

can you help me

thanks in advance.

Hi Seco,

You can download Management Studio Express from the SQL Express download page.

Mike

|||Thanks mike for reply

is this tool come with workgroup edition? what it's name or the one you gave me can work with all versions of sql server?

2- how can i make relation between 2 tables ?

thanks in advance.|||

hi,

seco wrote:

Thanks mike for reply

is this tool come with workgroup edition? what it's name or the one you gave me can work with all versions of sql server?

quite, but not really... the "full" editions of SQL Server 2005 provides "SQL Server Management Studio", including futher "wizards" and the like being "the full tool" implementation...

SQL Server Management Studio Express is the "lite" version, available for free...

2- how can i make relation between 2 tables ?

you can use the "Diagram" feature of SQL Server Management Studio Express or, (and you should go that way for learning purpose), you should dig into the Data Definition Language statements like CREATE TABLE or ALTER TABLE statements where you can define this kind of constraint..

regards

|||

You might be interested in the learning resources that I posted in this thread, there are a bunch of videos that teach you how to use the tools and how to create the standard database objects such as tables, views and stored procedures.

Mike

I want all the Table names

hi

I am using Sql Server 2000 as Back end. i am using Visual Basic as front End. i want all the Table Names from the particluar database(i Know the Database name) and load it into an Recordset. how to do that. is there any stored Procedure for that to get all the tables names.

Thanking you

Regards

GandhiYou can issue the following select statement to any database in SQL Server...

Select Name from Sysobjects Where type= 'U'|||Originally posted by pcmbalaji
You can issue the following select statement to any database in SQL Server...

Select Name from Sysobjects Where type= 'U'

Thank u it is working fine

Regrds
gandhi

I receive MSG 7707 when trying to split a partition for the second time. Why ?

Hi

I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem.
I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.

At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"

Here's what i do:

/*-*/
CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/

CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/

CREATE TABLE [dbo].[Letture](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data)
) ON schemaPartizioneLive(data)

ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))

CREATE TABLE [dbo].[LettureStorico](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data)
) ON schemaPartizioneStorico(data)

ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))

/*-*/

Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:

/*--*/
SET @.NewBoundary = dateadd(dd,-1, @.dateOfToday)

--this new partition contains the rows i want to switch
ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)
--this new partition is empty
ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@.NewBoundary)

--this works fine, rows are moved
ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2

--these two merges lead to two tables partitioned in two partitions each
ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@.OldBoundaryLive)
ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@.OldBoundaryStorico)

/**/

Till now, everything is working as expected.
Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)" i receive a 7707 error message:
"Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."

How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?

Someone can help me on this, please ?

Many thankx

Wentu

Hello,

I've experienced the same problem. Even if you map all your partitions to the PRIMARY filegroup when you create your scheme, each time before you split your range, you have to call

ALTER PARTITION SCHEME schemapartizioneLive NEXT USED [PRIMARY].

However, why it works the first time is still a mystery to me. Probably when you mal all your partitions, the "next used" is probably also set. As you haven't done it when you split the first time, you got the error the second time.

Greetings,

Adriano

i need to connect python to sql2005

hi
i need to connect
a small program written in Python
to a SqlServer 2005 database
how con i do it ?
can anyone link me to a resource ?
thanks
You should be able to connect with ODBC jsut like any other language. I
would start by looking at the Python web site:
http://www.python.org/
but a simple google search for python odbc gives a lot of hits. It's amazing
what a few keystrokes can buy you these days.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> hi
> i need to connect
> a small program written in Python
> to a SqlServer 2005 database
> how con i do it ?
> can anyone link me to a resource ?
> thanks
>
|||of course, Andrew
i googled that...
( does exist anybody
that don't find answers on internet first ?! :-)
i promisse i did it,
what i found is an old adodbapi
developed near 2003, that is
before sql2005 being released
i wonder if there is other ways
thanks & regards!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> You should be able to connect with ODBC jsut like any other language. I
> would start by looking at the Python web site:
> http://www.python.org/
> but a simple google search for python odbc gives a lot of hits. It's
amazing
> what a few keystrokes can buy you these days.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||If you google for "python odbc sql 2005" the very first hit is a 3rd party
that has 2005 drivers. There are several other links that appear useful as
well. Hopefully one of those will do the trick.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
> of course, Andrew
> i googled that...
> ( does exist anybody
> that don't find answers on internet first ?! :-)
> i promisse i did it,
> what i found is an old adodbapi
> developed near 2003, that is
> before sql2005 being released
>
> i wonder if there is other ways
> thanks & regards!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> amazing
>
|||well, i'm looking for something """free"""
but you are right,
i'll search in depth
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you google for "python odbc sql 2005" the very first hit is a 3rd party
> that has 2005 drivers. There are several other links that appear useful as
> well. Hopefully one of those will do the trick.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>
|||OK well that may narrow the search down a bit.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:Oz$N3X9bHHA.1216@.TK2MSFTNGP03.phx.gbl...
> well, i'm looking for something """free"""
> but you are right,
> i'll search in depth
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||I just googled it and found a free one on the first hit - a
download from sourceforge:
About pymssql
This module provides access to Microsoft SQL Servers from
Python scripts. It's the most efficient method of accessing
MS SQL Server's data from Python scripts.
http://pymssql.sourceforge.net/
And in the first few hits there was this:
Microsoft SQL Server Express / Python HOWTO
http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
-Sue
On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
<bajopalabra@.hotmail.com> wrote:

>well, i'm looking for something """free"""
>but you are right,
>i'll search in depth
>thanks
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
>news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||come on, hit me...
anyway, thanks both
for reply with respect
you are very kind
|<aiser Soze
"Sue Hoegemeier" <Sue_H@.nomail.please> escribi en el mensaje
news:jfmg03toi97nkhslost93k5ir0s5ina8jc@.4ax.com...
> I just googled it and found a free one on the first hit - a
> download from sourceforge:
> About pymssql
> This module provides access to Microsoft SQL Servers from
> Python scripts. It's the most efficient method of accessing
> MS SQL Server's data from Python scripts.
> http://pymssql.sourceforge.net/
> And in the first few hits there was this:
> Microsoft SQL Server Express / Python HOWTO
>
http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html[vbcol=seagreen]
> -Sue
> On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
> <bajopalabra@.hotmail.com> wrote:
party[vbcol=seagreen]
as[vbcol=seagreen]
mensaje[vbcol=seagreen]
language. I
>

i need to connect python to sql2005

hi
i need to connect
a small program written in Python
to a SqlServer 2005 database
how con i do it ?
can anyone link me to a resource ?
thanksYou should be able to connect with ODBC jsut like any other language. I
would start by looking at the Python web site:
http://www.python.org/
but a simple google search for Python odbc gives a lot of hits. It's amazing
what a few keystrokes can buy you these days.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> hi
> i need to connect
> a small program written in Python
> to a SqlServer 2005 database
> how con i do it ?
> can anyone link me to a resource ?
> thanks
>|||of course, Andrew
i googled that...
( does exist anybody
that don't find answers on internet first ?! :-)
i promisse i did it,
what i found is an old adodbapi
developed near 2003, that is
before sql2005 being released
i wonder if there is other ways
thanks & regards!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> You should be able to connect with ODBC jsut like any other language. I
> would start by looking at the Python web site:
> http://www.python.org/
> but a simple google search for Python odbc gives a lot of hits. It's
amazing
> what a few keystrokes can buy you these days.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||If you google for "python odbc sql 2005" the very first hit is a 3rd party
that has 2005 drivers. There are several other links that appear useful as
well. Hopefully one of those will do the trick.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
> of course, Andrew
> i googled that...
> ( does exist anybody
> that don't find answers on internet first ?! :-)
> i promisse i did it,
> what i found is an old adodbapi
> developed near 2003, that is
> before sql2005 being released
>
> i wonder if there is other ways
> thanks & regards!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> amazing
>|||well, i'm looking for something """free"""
but you are right,
i'll search in depth
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you google for "python odbc sql 2005" the very first hit is a 3rd party
> that has 2005 drivers. There are several other links that appear useful as
> well. Hopefully one of those will do the trick.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>|||OK well that may narrow the search down a bit.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:Oz$N3X9bHHA.1216@.TK2MSFTNGP03.phx.gbl...
> well, i'm looking for something """free"""
> but you are right,
> i'll search in depth
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||I just googled it and found a free one on the first hit - a
download from sourceforge:
About pymssql
This module provides access to Microsoft SQL Servers from
Python scripts. It's the most efficient method of accessing
MS SQL Server's data from Python scripts.
http://pymssql.sourceforge.net/
And in the first few hits there was this:
Microsoft SQL Server Express / Python HOWTO
http://www.time-travellers.org/shan...thon-HOWTO.html
-Sue
On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
<bajopalabra@.hotmail.com> wrote:

>well, i'm looking for something """free"""
>but you are right,
>i'll search in depth
>thanks
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
>news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||Lines: 102
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: line40.comsat.net.ar 200.47.78.40
Xref: leafnode.mcse.ms microsoft.public.sqlserver.connect:1884
come on, hit me...
anyway, thanks both
for reply with respect
you are very kind
|<aiser Soze
"Sue Hoegemeier" <Sue_H@.nomail.please> escribi en el mensaje
news:jfmg03toi97nkhslost93k5ir0s5ina8jc@.
4ax.com...
> I just googled it and found a free one on the first hit - a
> download from sourceforge:
> About pymssql
> This module provides access to Microsoft SQL Servers from
> Python scripts. It's the most efficient method of accessing
> MS SQL Server's data from Python scripts.
> http://pymssql.sourceforge.net/
> And in the first few hits there was this:
> Microsoft SQL Server Express / Python HOWTO
>
http://www.time-travellers.org/shan...thon-HOWTO.html
> -Sue
> On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
> <bajopalabra@.hotmail.com> wrote:
>
party[vbcol=seagreen]
as[vbcol=seagreen]
mensaje[vbcol=seagreen]
language. I[vbcol=seagreen]
>

I need the function to return a srting

Hii every one
When i use the function of (select) from the data bass it return dataset or some thing else
But I need it to return string or the data element which in the query not all the query

like

I dont need that
_____________
| Id | Name |
------
| 1 | Bill |
-------
I dont need All of that to display But I need to display the name only in Label or textbox
like
Bill

Thanks
MaroRead about "OUTPUT paramaters"|||I think the easiest way for you to get the value you're looking for, would be to capture the output results of your function to a dataset and then set the value of your label or textbox to an item in the dataset.

Dim myDataset As New Dataset()
myDataset.Datasource = yourFunctionName(parms)

Label1.text = myDataset.Tables(0).Rows(0).Item("myColumnHeaderName")|||Thanks that is what I need
Maro