Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.With no backups you have to hope the data is recoverable from the transaction logs with a tool likewww.lumigent.com offers, but even this does not offer 100% certainty of recovering the table.
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Is it possible to read data from a table with exclusive lock ?
Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.
Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.
Your next option is to add a transaction to the process and set the isolation level of the transaction to "readuncommited". This will allow you to 'by pass' the exclusive lock, but the RISK is that you may get obsolete (incorrect) data.
|||Hello,
Just to confirm you it's seems to work and this a great new for our users :)
Thank you very much for you help
regards
Nico
|||hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo 
Seriously, glad to help, would you please remember to mark a question as "answered" once you are satisfied. It will help others find answers, and allow answerers to move on to other unanswered questions.
Is it possible to read data from a table with exclusive lock ?
Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.
Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.
Your next option is to add a transaction to the process and set the isolation level of the transaction to "readuncommited". This will allow you to 'by pass' the exclusive lock, but the RISK is that you may get obsolete (incorrect) data.
|||Hello,
Just to confirm you it's seems to work and this a great new for our users :)
Thank you very much for you help
regards
Nico
|||hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo
Seriously, glad to help, would you please remember to mark a question as "answered" once you are satisfied. It will help others find answers, and allow answerers to move on to other unanswered questions.
sqlis it possible to pass a report table column in sql reporting serivces
Hi,
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh
Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.
Hope that helps,
-Lukasz
is it possible to pass a report table column in sql reporting serivces
Hi,
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh
Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.
Hope that helps,
-Lukasz
sqlIs it possible to output a Field Value based on another field value?
I would like the data value of a text field in a table to based on the
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>
is it possible to number the records?
I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>
is it possible to number the records?
I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>
is it possible to move table(s) from one db to another?
thanks in advance.
Use the import/export functionality in Enterprise Manager.
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>
|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>
|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
>
|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves
...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts[vbcol=seagreen]
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same
> in
> the
>
|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the
Use the import/export functionality in Enterprise Manager.
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>
|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>
|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
>
|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves
...HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts[vbcol=seagreen]
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same
> in
> the
>
|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves
...> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the
is it possible to move table(s) from one db to another?
thanks in advance.Use the import/export functionality in Enterprise Manager.
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > thanks in advance.
> >
> >
>|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves :) ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > By move you mean you want the data to be copied right ? If yes, then you
> > have:
> >
> > 1. Create a DTS package has a wizard approach that will transfer the
same
> in
> > a few clicks.
> > 2. Script out all the data using available third party tools and script
> the
> > data and import the same. One such script present at :
> > http://vyaskn.tripod.com/code/generate_inserts.txt
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD, MCSD
> > http://www.extremeexperts.com
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> >
> >
> > "Tin" <oe@.fawcette.com> wrote in message
> > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > thanks in advance.
> > >
> > >
> >
> >
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves :) ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > It seems tin needs to move the table from one database to another
> database
> > in the same server. In this case he can select * INTO statement.
> >
> > For eg:
> >
> > You have 2 databases- 1 is HR and the 2nd is Finance..
> >
> > Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> > execute the below command from Query Analyzer.
> >
> > select * into FINANCE..Employee from HR..Employee
> >
> > Once the tabe is successfully copied then you can drop the table from
> source
> > database. But ensure that full data is copied to destination.
> >
> > Note:
> > If you need the indexes then you have to create the indexes by scripting
> > them using the Enterprise manager -- Databases-- Alltasks-- Generate
> Scripts
> > by selecting the current database and table.
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> > news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > > By move you mean you want the data to be copied right ? If yes, then you
> > > have:
> > >
> > > 1. Create a DTS package has a wizard approach that will transfer the
> same
> in
> > > a few clicks.
> > > 2. Script out all the data using available third party tools and script
> the
> > > data and import the same. One such script present at :
> > > http://vyaskn.tripod.com/code/generate_inserts.txt
> > >
> > > --
> > > HTH,
> > > Vinod Kumar
> > > MCSE, DBA, MCAD, MCSD
> > > http://www.extremeexperts.com
> > >
> > > Books Online for SQL Server SP3 at
> > > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> > >
> > >
> > > "Tin" <oe@.fawcette.com> wrote in message
> > > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > > thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > thanks in advance.
> >
> >
>|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves :) ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > By move you mean you want the data to be copied right ? If yes, then you
> > have:
> >
> > 1. Create a DTS package has a wizard approach that will transfer the
same
> in
> > a few clicks.
> > 2. Script out all the data using available third party tools and script
> the
> > data and import the same. One such script present at :
> > http://vyaskn.tripod.com/code/generate_inserts.txt
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD, MCSD
> > http://www.extremeexperts.com
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> >
> >
> > "Tin" <oe@.fawcette.com> wrote in message
> > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > thanks in advance.
> > >
> > >
> >
> >
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves :) ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > It seems tin needs to move the table from one database to another
> database
> > in the same server. In this case he can select * INTO statement.
> >
> > For eg:
> >
> > You have 2 databases- 1 is HR and the 2nd is Finance..
> >
> > Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> > execute the below command from Query Analyzer.
> >
> > select * into FINANCE..Employee from HR..Employee
> >
> > Once the tabe is successfully copied then you can drop the table from
> source
> > database. But ensure that full data is copied to destination.
> >
> > Note:
> > If you need the indexes then you have to create the indexes by scripting
> > them using the Enterprise manager -- Databases-- Alltasks-- Generate
> Scripts
> > by selecting the current database and table.
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> > news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > > By move you mean you want the data to be copied right ? If yes, then you
> > > have:
> > >
> > > 1. Create a DTS package has a wizard approach that will transfer the
> same
> in
> > > a few clicks.
> > > 2. Script out all the data using available third party tools and script
> the
> > > data and import the same. One such script present at :
> > > http://vyaskn.tripod.com/code/generate_inserts.txt
> > >
> > > --
> > > HTH,
> > > Vinod Kumar
> > > MCSE, DBA, MCAD, MCSD
> > > http://www.extremeexperts.com
> > >
> > > Books Online for SQL Server SP3 at
> > > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> > >
> > >
> > > "Tin" <oe@.fawcette.com> wrote in message
> > > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > > thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
is it possible to move table(s) from one db to another?
thanks in advance.Use the import/export functionality in Enterprise Manager.
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
>|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same[vbcol=seagreen]
> in
> the
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having sai
d
> that this also copies the data not moves ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
>|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves
...HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same[vbcol=seagreen]
> in
> the
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having sai
d
> that this also copies the data not moves
...> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the
Wednesday, March 28, 2012
Is it possible to merge date from 2 or 3 tables into a View?
Suppose I have three tables Type1Events, Type2Events, and Type3Events where
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>
Is it possible to make "proxy tables" win SQL-server 2000?
Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
Is it possible to make "proxy tables" win SQL-server 2000?
Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per
Monday, March 26, 2012
Is it possible to insert a PDF File into a SQL 2k Table?
Hello,
I want to develop a small PDF Management System for our Web Insurance
Systems and Im wondering if I can use SQL Server to save my generated PDF
Documents. Is it possible? If so is it suggested? Are there any other
alternatives?
Jorge Luzarraga C
Fidens S.A.
321 7610 Anx 23
"I can do it quick. I can do it cheap. I can do it well. Pick any two."Jorge Luzarraga Castro wrote:
> Hello,
> I want to develop a small PDF Management System for our Web Insurance
> Systems and Im wondering if I can use SQL Server to save my generated PDF
> Documents. Is it possible? If so is it suggested? Are there any other
> alternatives?
>
It would be better to store the filenames to the PDF files in the
database. Otherwise, the database could become too large, or if the
database was to be corrupted, all pdf-files could be lost.
Steven|||> Is it possible?
Yes.
> If so is it suggested?
Typically, no.
http://www.aspfaq.com/2149
> Are there any other alternatives?
Yes, store the files in the filesystem, and their paths and other
information about them in the database.
A
I want to develop a small PDF Management System for our Web Insurance
Systems and Im wondering if I can use SQL Server to save my generated PDF
Documents. Is it possible? If so is it suggested? Are there any other
alternatives?
Jorge Luzarraga C
Fidens S.A.
321 7610 Anx 23
"I can do it quick. I can do it cheap. I can do it well. Pick any two."Jorge Luzarraga Castro wrote:
> Hello,
> I want to develop a small PDF Management System for our Web Insurance
> Systems and Im wondering if I can use SQL Server to save my generated PDF
> Documents. Is it possible? If so is it suggested? Are there any other
> alternatives?
>
It would be better to store the filenames to the PDF files in the
database. Otherwise, the database could become too large, or if the
database was to be corrupted, all pdf-files could be lost.
Steven|||> Is it possible?
Yes.
> If so is it suggested?
Typically, no.
http://www.aspfaq.com/2149
> Are there any other alternatives?
Yes, store the files in the filesystem, and their paths and other
information about them in the database.
A
Is it possible to have SSIS flatten XML data?
Hi,
I have to take a hierarchical XML file and store it into a table.
I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.
I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.
Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.
Thanks,
-dhideal
The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.
-Jamie
|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.
4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)
1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.
2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.
3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb
4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb
5. Load it into SQL server and use XQuery (not an SSIS solution)|||
jaegd,
Thanks for the pointers.
It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):
1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.
2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.
3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.
4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.
5) We're trying to avoid this particular solution for now if we can.
Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.
-dhideal
|||It seems you understand it quite well. To flatten, though must join and/or aggregate.Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.
Now, I have tested all five of these approaches, and used two of them
(XPath,XmlSerializer, leaving out XQuery for the moment). They all
require you to essentially do XML "joins" via custom code, whether that
code is a set of XPath expressions in a custom adapter, a set of
property "gets" in in a custom adapter.
Had Microsoft shipped a client side XQuery implementation, I would have
recommended that, since XQuery allows you to do XML joins, which is
basically what you're looking for.sql
Is it possible to have SSIS flatten XML data?
Hi,
I have to take a hierarchical XML file and store it into a table.
I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.
I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.
Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.
Thanks,
-dhideal
The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.
-Jamie
|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.
4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)
1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.
2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.
3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb
4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb
5. Load it into SQL server and use XQuery (not an SSIS solution)|||
jaegd,
Thanks for the pointers.
It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):
1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.
2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.
3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.
4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.
5) We're trying to avoid this particular solution for now if we can.
Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.
-dhideal
|||It seems you understand it quite well. To flatten, though must join and/or aggregate.Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.
Now, I have tested all five of these approaches, and used two of them
(XPath,XmlSerializer, leaving out XQuery for the moment). They all
require you to essentially do XML "joins" via custom code, whether that
code is a set of XPath expressions in a custom adapter, a set of
property "gets" in in a custom adapter.
Had Microsoft shipped a client side XQuery implementation, I would have
recommended that, since XQuery allows you to do XML joins, which is
basically what you're looking for.
Is it possible to have SQL send an email out based on a value in a table?
Hi all,
I'm working on a trouble ticket application and would like to add one more bit of functionality to the system:
When a new trouble ticket is created via the application front end (ASP.NET web application) the system sends an email to an appropiate party indicating that a new ticekt has been issued and needs to be responded to. The user must then log into the system to see the new ticket. The ticket is of course stored in the database (SQL Sever 2000). My question is, whether it is possible to tell SQL server to automatically send emails on a set time interval as long as their are tickets in the database that have a status of new?
ThanksThere is a SQL Server extended stored procedure, xp_Sendmail, that will allow your SQL code to send mail (presuming the SQL Mail agent is configured and running).|||You can also use CDONTS from a stored procedure if you don't have SQL Mail installed - http://support.microsoft.com/view/tn.asp?kb=312839|||Thanks all. I have a procedure that uses CDONTS and it works fine. now I gotta figure out hot to schedule it to run based on a field in a database. But hopefully that should be easy.
Wendell|||Hi
This KB is not anymore available online. Can you provide the details for this?
Cesar|||Here'sanother article on the same topic.
Regards,
Xander
Is it possible to have more than one table footer rows ?
Well, we can add another table footer row by right-clicking on the footer row of the table and select, Inser Row Above, or, Insert Row Below.
But, I want to set different values to same properties of rows. Such as;
Row3.RepeatOnNewPage = False
Row4.RepeatOnNewPage = True
What do you say ?
-SMang the Frustrated Developer
SMang,
You can't hide your table rows independently with the RepeatOnNewPage, but you can set the rows "hidden" property to true or false. The catch here is that you will need to tie it to a toggle item.
Ham
Is it possible to generate alter Table statements using SMO
Hi
I'm trying to modify existing tables in a database.
How can I create alter Table scripts using SMO/DMO
Thank you
Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:Server s = new Server(".");
s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql
//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql
//s.ConnectionContext.CapturedSql.Text; //Get the Text
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks again Jens
I'm trying the follwong code
Server server1 = new Server(".");
Database db= server1.Databases["master"];
server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
foreach (Table Tbl in db.Tables)
{
tabl.Alter ();
}
db1.Refresh();
//writing to a file
writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");
But it is not generating Alter statments.
But if I use Create(), in place of alter(), it's generating Create statments.
|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?
In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.
Server s = new Server(".");
s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;
Table t = s.Databases["SMOTest"].Tables["TestTable"];
t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));
t.Alter();
foreach (string st in s.ConnectionContext.CapturedSql.Text)
{
Console.WriteLine(st);
}
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens
Got it.
while comparing a table in one database to other table (identical) in other database,
if the Source table has some modified(altered) columns and need to be modified in the target table.
How to solve this problem.Any Idea.
I generated the alter scripts manually for each column.
Like
ALTER TABLE [dbo].[wo]
ADD [requested-time] varchar (8 ) NULL
Thank you
|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Thanks alot Jens.
I'll try for this
Subscribe to:
Posts (Atom)