Correlated Subquery

Hi,
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:

> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>

Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoiti'm not sure where the correlation comes in

these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:

select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies

I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it

;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.

The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).

NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)

select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery

Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoitplease don't cross-post

http://www.dbforums.com/showthread.php?t=1117027sql

Correlated query in SELECT clause

There is a problem when I include correlated query in the SELECT clause in
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?

Correlated and aggregate sql query

I have a table similar to the following:

ID | Name ID | Period From | Period To | Percentage |

-----------------------

Important - Each person can have more than one entry.

What I am trying to do is get the last percentage that each person obtained.

The only way I have been able to do this is by the following:

SELECT * FROM myTable

LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1

ON myTable.NameID = t1.NameID

WHERE myTable.PeriodTo = t1.PeriodTo

I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

|||This does not work. It finds the rows where the periodTo is equivalent to the maximum periodTo. I need to find the maximum periodTofor each nameID. Its is equivalent toSELECT ID, NameID, ..., MAX(PeriodTo)FROM myTableGROUP BY ID, NameID, ...|||

You can use ROW_Number function if you use SQL Server 2005:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(SELECT NameID, PeriodFrom, PeriodTo, Percentage,ROW_Number()OVER(PARTITIONBY NameIDORDERBY PeriodToDESC)as RowNum

FROM mytable)AS t

WHERE RowNum=1

|||

Can you post some sample data from the table and expected output? This makes it easier for us to see what should be achieved.

|||

if PeriodTo unique then it works

(

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

in the inner query it selects all the periodto which is max to the nameid >>>then it matches the PeriodTo from the outer query.
select Max(PeriodTo) from myTable group by NameIDthis portion returns the Max PeriodTo for each NameID which is unique

)

|||

Sorry Kamrul, but that is not what I am looking for. Maybe I have not explained the problem properly, so here is some sample data (obviously simplified).

An example of the table is (note that this is british dates - i.e. dd/mm/yy):

 ID | NameID | PeriodTo | Comments |---------------------- 1 | Mark | 01/01/01 | ComMark1 | 2 | Mark | 01/06/01 | ComMark2 | 3 | Mark | 01/11/01 | ComMark3 | 4 | Ken | 01/01/01 | ComKen1 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn1 |---------------------

And the result I am trying to get is :

 ID | NameID | PeriodTo | Comments |---------------------- 3 | Mark | 01/11/01 | ComMark3 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn |----------------------

Kamrul's code doesn't work because it will return every row.

The code I wrote does return this, but I was wondering whether there was a nice way of doing this kind of correlated aggregated query. I'm looking for the most efficient method. I should also point out that I am using sql server 2000.

Jagdip

|||

Hi,

Please try this.

SELECT *FROM MyTable OutMyTable
where PeriodTo = (select Max(PeriodTo)from MyTable InMyTableWHERE InMyTable.NameID=OutMyTable.NameID group by NameID)

Regards,

Gaurang Majithiya

|||

For SQL Server 2000:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(

SELECT NameID, PeriodFrom, PeriodTo, Percentage,(SELECTCOUNT(*)FROM mytable aWHERE a.NameID=b.NameIDAND a.PeriodTo>=b.PeriodTo)as RowNum

FROM mytable b)AS t

WHERE RowNum=1

correct syntax for ALTER DATABASE MODIFY FILE

Hi All,
I get an error "Incorrect syntax near FILE" when I try to execute the
following
ALTER DATABASE MODIFY FILE (NAME = MasterDB, NEWNAME = CompanyDB)
What could be wrong with this and how can I fix it.
ThanksYou need to specify the desired database name. Try:
ALTER DATABASE CompanyDB
MODIFY FILE (NAME = 'MasterDB', NEWNAME = 'CompanyDB')
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:%23XED$JXkFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I get an error "Incorrect syntax near FILE" when I try to execute the
> following
> ALTER DATABASE MODIFY FILE (NAME = MasterDB, NEWNAME = CompanyDB)
> What could be wrong with this and how can I fix it.
> Thanks
>|||Thanks Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%231iIzlXkFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You need to specify the desired database name. Try:
> ALTER DATABASE CompanyDB
> MODIFY FILE (NAME = 'MasterDB', NEWNAME = 'CompanyDB')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:%23XED$JXkFHA.4028@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I get an error "Incorrect syntax near FILE" when I try to execute the
>> following
>> ALTER DATABASE MODIFY FILE (NAME = MasterDB, NEWNAME = CompanyDB)
>> What could be wrong with this and how can I fix it.
>> Thanks
>

correct practices with SQL Server

I am hoping someone can give some advice on the following things:

I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done

using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could

be accomplished using either)? Will a sproc run faster than a view? Can any share any info?

Are sprocs best suited for data access and to enforce business rules?

I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is

in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005

Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?

...........................................thanks...........................................................

Views are generally better than stored procedures where they could be used interchangeably, especially considering that you can create indexes on views.

While it makes sense to want to use address for a customer's address, the meaning (in a computer) of the word is too general. It could mean a customer's home address, delivery address, the data address of some memory block on the client side, the IP address of another server, really almost anything. Ideally, you would want to follow naming conventions that enable other developers to immediately draw as much information as they can out of a table (e.g. customer name, customer address, customer ID. This will make it much easier to join tables (e.g. joining customer table with business table might have 2 address fields that need to be resolved). One possible naming convention is to consider the table name (e.g. customers) as an abstract object or schema for a data set and name everything by "<schema> <object name>" you could use underscores if you were more comfortable with unspaced column names. Ideally, this will make it much easier to read complex joins as the project grows and will make maintenance much less of a headache -- since there will be fewer questions like "which address do you mean: the one in customer data, business data, the address of the pointer, or the shipping address of the order in question?".

In general, all books teach both good and bad habits, unfortunately. Most SQL books published over the past 10 years have SQL Injection attacks available against all their examples, for instance. Like grade school -> high school -> college, we have to accept that some percentage of the information we learn in books or from teachers is just wrong. That doesn't mean it isn't practical to use as a learning tool, it is just not perfect in practice. This is sort of the scientific method, work with what you know until you learn something better, then paradigm shift and continue. In the long run, there is no perfect book for any given domain space. The only way to grow is to continuously seek out information, as you are doing now.

Hope that helps,

John

|||

So, it seems like the advantage of using a view with an index would be to increase performance. Should stored procedures be used only when there is a need to write to the database? Are these deductions correct?

I ended up doing what you suggested and changed it to CustAddress and EmpAddress.

........................................Thanks for the advice

correct me??

I've created C#.net program (behind code style).

when I run it in Internet explorer, the following error occurs in IE window.

pls instruct me how to handle and correct this error.

And how to initialize the connectionstring... Great thank!

Server Error in '/' Application.

------------------------

The ConnectionString property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: The ConnectionString property has not been initialized.]
System.Data.SqlClient.SqlConnection.Open() +809
CodeBox.BehindCode.getSubject() +80
CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +29
System.Web.UI.Page.ProcessRequestMain() +724

------------------------

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

Hi,
You haven't posted the code that you are trying to execute.
From the error, it seems that you havent set the connection string property for your connection object.
You can initialize the connection string as follows:-
SqlConnection objCon = new SqlConnection("put your connection string here");
Thanks.|||if you still have the problem show us the code !!|||

hi! thanks...
but I still have problem...
I want to show you my code and error message...
here is my code...
SqlDataReader getSubject(){
//create instance of connection and command object

//SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
constring ="DSN=fisca;User ID=sa;Password=admin;";
SqlConnection myconnection = new SqlConnection(constring);

SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}

here is error message. I sure that dsn is really existing and userid, password of sql server is true....
and I also sure that variable constring is declared.

Server Error in '/' Application.

---

Unknown connection option in connection string: dsn.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.ArgumentException: Unknown connection option in connection string: dsn.
Source Error:

Line 77: //SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);Line 78: constring ="DSN=fisca;User ID=sa;Password=admin;";Line 79: SqlConnection myconnection = new SqlConnection(constring);Line 80: Line 81: SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);

Source File:c:\inetpub\wwwroot\CourseRequisition.cs Line:79
Stack Trace:

[ArgumentException: Unknown connection option in connection string: dsn.] System.Data.SqlClient.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values) +673 System.Data.SqlClient.ConStringUtil.ParseConnectionString(String connectionString) +55 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +375 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +164 CodeBox.BehindCode.getSubject() in c:\inetpub\wwwroot\CourseRequisition.cs:79 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CourseRequisition.cs:36 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724

---

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0|||

here is my code...

SqlDataReader getSubject(){
//create instance of connection and command object
SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
but now I can find it out and I've corrected it myself.
cause I initialize connectionstring in web.config file. It can put away error.
so now I want to know that is there many ways to connect to sql server?...
how many way?
I just know two way,
1. Sqlconnection constring=new Sqlconnection("connection string");
2. SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
so pls let me know. cause I willingly want to know C#.net thoroughly.
I also learning it from book and creating C#.net application..
Have a bright and wonderful day!

|||the connection string that you provided is not correct !!!
look atwww.connstingSttings.com and see the proper contents of the connection string try to look the connetion string and if you still have problem get back to me

Correct format for set arithabort on

I am calling a stored procedure with the following syntax:

 Dim MyCommand1 As New SqlCommand("addprospcus", MyConnection)
 MyCommand1.CommandText = "set arithabort on"
 MyCommand1.CommandType = CommandType.StoredProcedure
 MyCommand1.Parameters.Add(New SqlParameter("@.Namecust", SqlDbType.NVarChar, 60))
 MyCommand1.Parameters("@.namecust").Value = txtProspName.Text.ToString
 MyCommand1.Parameters.Add(New SqlParameter("@.codeterr", SqlDbType.NVarChar, 6))
 MyCommand1.Parameters("@.codeterr").Value = Trim(TerritoryList1.SelectedItem.Value.ToString)
 MyConnection.Open()
 MyCommand1.ExecuteNonQuery()
 MyConnection.Close()

This is incorrect, but I can not find the correct syntax for calling my stored procedure but first setting "arithabort on".

Thanks in advance for your assistance.why wouldnt you set it inside the stored proc itself ?|||That does not work - I recieve the following error, "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'". There is alot of information about this and some of the advice states that the "set arithabort on" needs to be set before executing the stored procedure.

Currently I am getting around this error by using a nested sproc I call a sproc from my asp.net web form this stored procedure sets arithabort on and then executes a second sproc that actually does the insert.

the internal sproc runs correctly from Sql query analyser and when executed from another sproc as stated above, but not from my webform. Several articles have stated that the "set arithabort on" needs to be set from the application such as microsoft article ID: 305333

MyConnection.Execute "SET ARITHABORT ON"

but I could not get this to work.|||when you create the stored proc

SET ARITHABORT ON
GO
ALTER PROCEDURE yourproc ...
-- your stored proc code
SET ARITHABORT OFF
GO

hth|||I trie this procedure but it does not help. I still get the error. The only way, so far, that I have been able to use my stored procedure from my web application is to use a nested Sproc.

I did based on your advice try to change my original sproc using the procedure in your post. I alter the sproc and it will execute and insert records from query analyzer but not from my application. But if I nest my sproc with the calling sproc being executed from my application and the calling sproc set arithabort on, executes my nested sproc, and then set arithabort off; my records are correctly inserted into my table.

Copying the database

I realize that SQL Server is a big database and a big subject so perhaps I
should preface my question by saying the following. I am using SQL Server
2000 simply because Microsoft Visual Studio .net has so much market-share.
VS.net and SQL Server are a good match. I see that SQL Server is a quality
product, but frankly, except for the reason mentioned, it is overkill for
what we are doing. Prior to using SQL Server I was using Adaptive Server
Anywhere. The reason I mention this is that it will really help if I can
keep things as simple as possible where SQL Server is concerend. I am
running it as a back-end to a Windows Client/Server application in VS.net on
a local area network. My largest table is only about 75, 000 records. It
is a simple database by SQL Server standards.
That said, I need to develope a scheme for getting a copy of the database at
the Client's location and copying it (about weekly) to my office location.
The client's database is kept on a Windows 2000 Server. The database at my
office is on a stand-alone single machine. I have reviewed the material
extensively in a knowledgebase article titled "How to move databases between
computers that are running SQL Server" (314546). I think I understand most
of it but have a few questions relative to Detaching and Attaching, Logins
and Passwords, and Diagrams.
First - Detaching and attaching. When I first started copying databases
between the two locations I had SQL Server installed on a local machine at
my office, but also on a local machine at the clients. At that point in
time, it was not installed on their server. I did not know I needed to
detach and re-attach. So, I just copied the .mdf and .ldf files from
machine to machine. Funny thing is, it seems to work. That is, on the
destination machine I created a database called "Corp" and then simply
overwrote Corp.mdf and Corp.ldf with the files from the source computer. In
this case, both the source and destination machines had SQL Server installed
in exactly the same way with the same directories. This may account for why
it worked. I don't know. If in my case it is not really necessary to
detach and re-attach it would be nice not to have to do so. Just one less
step to have to worry about. Perhaps someone could clarify.
Second - Diagrams. the article implies that Diagrams do not get copied but
it appears to me they do. Did I mis-understand? On my target machine I do
see what appears to be the same diagram I had on the source machine.
Third - Logins and Passwords. 99% of the time I will be making a copy of
the database at the clients and coping it to my office, not the other way
around. I understand that Logins and passwords do not get copied. The way
the application is setup now all machines access the database in
Authentication mode. Is there anything to be aware of at my office with
regard to still being able to access the database? I read quite a bit about
orphaned users in the Knowledgebase articles but it is not clear to me how
much of a problem, if any, this might be to me. I don't know if I have
explained this well but perhaps somebody could give it a shot.
Sorry to be so wordy but don't know how else to ask what I need to know."Woody Splawn" <woody@.splawns.com> wrote in message
news:ewPrp4EtDHA.3496@.TK2MSFTNGP11.phx.gbl...
> I realize that SQL Server is a big database and a big subject so perhaps I
> should preface my question by saying the following. I am using SQL Server
> 2000 simply because Microsoft Visual Studio .net has so much market-share.
> VS.net and SQL Server are a good match. I see that SQL Server is a
quality
> product, but frankly, except for the reason mentioned, it is overkill for
> what we are doing. Prior to using SQL Server I was using Adaptive Server
> Anywhere. The reason I mention this is that it will really help if I can
> keep things as simple as possible where SQL Server is concerend. I am
> running it as a back-end to a Windows Client/Server application in VS.net
on
> a local area network. My largest table is only about 75, 000 records. It
> is a simple database by SQL Server standards.
> That said, I need to develope a scheme for getting a copy of the database
at
> the Client's location and copying it (about weekly) to my office location.
> The client's database is kept on a Windows 2000 Server. The database at
my
> office is on a stand-alone single machine. I have reviewed the material
> extensively in a knowledgebase article titled "How to move databases
between
> computers that are running SQL Server" (314546). I think I understand
most
> of it but have a few questions relative to Detaching and Attaching, Logins
> and Passwords, and Diagrams.
> First - Detaching and attaching. When I first started copying databases
> between the two locations I had SQL Server installed on a local machine at
> my office, but also on a local machine at the clients. At that point in
> time, it was not installed on their server. I did not know I needed to
> detach and re-attach. So, I just copied the .mdf and .ldf files from
> machine to machine. Funny thing is, it seems to work. That is, on the
> destination machine I created a database called "Corp" and then simply
> overwrote Corp.mdf and Corp.ldf with the files from the source computer.
In
> this case, both the source and destination machines had SQL Server
installed
> in exactly the same way with the same directories. This may account for
why
> it worked. I don't know. If in my case it is not really necessary to
> detach and re-attach it would be nice not to have to do so. Just one less
> step to have to worry about. Perhaps someone could clarify.
You're really beyond Detatching and Attaching. To make this work right,
you're better off using backups. Set up a maintence plan for the database
to back it up nightly (which you should do anyway). Set retention period
and file naming convention, etc. Just dump the backups to disk right on the
server.
Then you can instruct your customer to move move the backups off-server
nightly. For instance they can just do a flat-file backup of your DB server
nightly, and they will pick up the full backups.
Anyway, for getting the database back to your office, you can just look in
the backup folder, and pick the latest backup file and beam it back home.
Once there use RESTORE DATBASE XXX WITH MOVE to restore the client's
database onto your server.
> Second - Diagrams. the article implies that Diagrams do not get copied
but
> it appears to me they do. Did I mis-understand? On my target machine I
do
> see what appears to be the same diagram I had on the source machine.
Diagrams are stored in the msdb database. They will not be included.
> Third - Logins and Passwords. 99% of the time I will be making a copy of
> the database at the clients and coping it to my office, not the other way
> around. I understand that Logins and passwords do not get copied. The
way
> the application is setup now all machines access the database in
> Authentication mode. Is there anything to be aware of at my office with
> regard to still being able to access the database? I read quite a bit
about
> orphaned users in the Knowledgebase articles but it is not clear to me how
> much of a problem, if any, this might be to me. I don't know if I have
> explained this well but perhaps somebody could give it a shot.
As SA of your database, you will be DBO of the restored database. In other
words, don't worry about it.
David|||Thanks for responding.
> You're really beyond Detatching and Attaching. To make this work right,
> you're better off using backups.
If you think this is what I should do I am open to it. Having to go through
the restore seems slightly more cumbersome but maybe I'm being picky and am
just not use to it. If you don't mind, briefly, what is the advantage of
using backup and restore over detach and re-attach?
>Set up a maintence plan for the database
> to back it up nightly (which you should do anyway).
The applicaiton is not in production yet but it is my plan to do as you've
suggested once it is.
> Set retention period
> and file naming convention, etc. Just dump the backups to disk right on
the
> server.
I'm not sure what you mean when you mean when you talk about setting the
retention period and naming convention. However, I suppose what it boils
down to is to create a .bak file somewhere on the server and be sure it gets
backed up nightly. The backup tapes are moved off-site daily.
> Diagrams are stored in the msdb database. They will not be included.
Bare with me a minute. I am still new to this. I have been creating my
relationships between the tables by use of the Diagrams. It's easy to draw
lines there etc. If the diagram is gone that doesn't mean the link or
relationship between the tables is gone, does it? I suppose not. Further,
I suppose I should get in the habit of creating the relationship from within
design mode of the tables themselves. Y/N?
> As SA of your database, you will be DBO of the restored database. In
other
> words, don't worry about it.
That's nice to hear.
Thank You again.|||David:
Could I ask another question. When you restore a database and there is
already a database of the same name in the target directory of the target
machine, I suppose you must delete or remove that database before doing the
restore. Y/N? It will not just overwrite the database Y/N?|||"Woody Splawn" <woody@.splawns.com> wrote in message
news:eBI6WBItDHA.3144@.tk2msftngp13.phx.gbl...
> David:
> Could I ask another question. When you restore a database and there is
> already a database of the same name in the target directory of the target
> machine, I suppose you must delete or remove that database before doing
the
> restore. Y/N? It will not just overwrite the database Y/N?
>
This is the reason you want to use BACKUP and RESTORE intead of attach and
detach.
When you restore a dump, you extract the data files from the dump and put
them somewhere on your server. RESTORE gives you the option of renaming the
data files and either restoring over an existing database, or restoring as a
new database.
A database is made up of data files and log files. For a small database,
perhaps just one data file and one log file. Each of the files has both a
logical name and a physical name. So lets walk through what you would do.
Say your database is called MyApp.
ps
About the diagrams. The layout of the diagrams is stored in MSDB, but the
relationships are in the application database. The diagram is easilly
recreated, it's perfectly fine to use the diagram to design tables and
relationships.
Anyway below are samples of everything you will need. BACKUP and RESTORE
can be complicated, but only a DBA really needs to know most of it.
Here's everything a developer really needs to know:
David
--to create a database
create database MyApp
/*
The CREATE DATABASE process is allocating 0.63 MB on disk 'MyApp'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'MyApp_log'.
*/
--to dump the entire databse to disk
backup database myApp to disk='c:\MyApp.bak'
/*
Processed 80 pages for database 'myApp', file 'MyApp' on file 1.
Processed 1 pages for database 'myApp', file 'MyApp_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.240 seconds (2.734
MB/sec).
*/
--to get a list of the logical files in the dump:
--(if you always use the same 2 logical files, you can skip this step
restore filelistonly from disk='c:\MyApp.bak'
/*
LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize
MyApp,d:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp.mdf,D,PRIMARY,655360,35184372080640
MyApp_log,d:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log.LDF,L,,516096,35184372080640
(2 row(s) affected)
*/
--then to restore the dump to a new database called MyApp_customer1
restore database MyApp_customer1 from disk ='c:\MyApp.bak'
with
move 'MyApp' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_customer1.mdf',
move 'MyApp_log' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log_customer1.ldf'
/*
Processed 80 pages for database 'MyApp_customer1', file 'MyApp' on file 1.
Processed 1 pages for database 'MyApp_customer1', file 'MyApp_log' on file
1.
RESTORE DATABASE successfully processed 81 pages in 0.118 seconds (5.562
MB/sec).
*/
--to restore the dump and overwrite the MyApp_customer1 database
restore database MyApp_customer1 from disk ='c:\MyApp.bak'
with
replace,
move 'MyApp' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_customer1.mdf',
move 'MyApp_log' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log_customer1.ldf'
/*
Processed 80 pages for database 'MyApp_customer1', file 'MyApp' on file 1.
Processed 1 pages for database 'MyApp_customer1', file 'MyApp_log' on file
1.
RESTORE DATABASE successfully processed 81 pages in 0.118 seconds (5.562
MB/sec).
*/|||Hi Woody,
Thanks for using MSDN newsgroup. It's my pleasure to assist you with this issue.
As we know, detach/attach has the different mechanism from backup/restore. Detach/attach is
logical copying the database. The database files (.mdf, .ndf and .ldf) are not physically
moved and the server just references the attached database.
As you can see now, if the original database (or files) is damaged or corrupted, the attached
database will no longer be used until we use the backuped database to perform a restore
operation. Additionally, backup/restore database can also shrink the transaction logs and
prevent some volume limits for performance benefits.
=========For diagrams, their information is stored in the system table "dtproperties". If you use
backup/restore of detach/attach method, the diagrams will certainly not be lost as SQL Server
can get the needed information in that table.
However, if you try using DTS (describes in KB 314546 third method to move user database)
to move the database, the information of the diagram will be lost as well as some description
information for the table column. To work around this issue, you should perform a statement
"select * from dtproperties" to transfer all the information in the dtproperties table.
For more information, please reference the following article:
320125 HOW TO: Move a Database Diagram
http://support.microsoft.com/?id=320125
=========For the last question about restore database if it already exists, it's no need to delete it before
you restore back the database. When you arrive at the Restore Database Dialog Box, you
can navigate to the Option pan. There are three check boxes and radio options allowing you
to decide what restore mode you'd like to perform.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Copying tables between two databases

I am trying to copy the chart of accounts from Fabrikam to a newly created
company. I am running SQL 2005 and Dynamics GP 9.0.
I am following the steps from article 866332 in the knowledge base. This
process has the user copying some database tables from one database to
another database.
I perform the steps but when I try to execute the process, I get the following
errors:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc0202049: Data Flow Task: Failure inserting into the
read-only column "DEX_ROW_ID".
(SQL Server Import and Export Wizard)
* Error 0xc0202045: Data Flow Task: Column metadata validation
failed.
(SQL Server Import and Export Wizard)
* Error 0xc004706b: Data Flow Task: "component "Destination -
GL00100" (139)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
* Error 0xc004700c: Data Flow Task: One or more component failed
validation.
(SQL Server Import and Export Wizard)
* Error 0xc0024107: Data Flow Task: There were errors during task
validation.
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [FS].[dbo].[GL00100] (Stopped)
- Copying to [FS].[dbo].[GL00102] (Stopped)
- Copying to [FS].[dbo].[GL00103] (Stopped)
- Copying to [FS].[dbo].[GL00104] (Stopped)
- Copying to [FS].[dbo].[GL00105] (Stopped)
- Copying to [FS].[dbo].[GL40200] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Any idea what the problem is?
Thanks.
Jerry Flatto
On Mar 24, 2:04 am, Jerry Flatto
<JerryFla...@.discussions.microsoft.com> wrote:
> I am trying to copy the chart of accounts from Fabrikam to a newly created
> company. I am running SQL 2005 and Dynamics GP 9.0.
> I am following the steps from article 866332 in the knowledge base. This
> process has the user copying some database tables from one database to
> another database.
> I perform the steps but when I try to execute the process, I get the following
> errors:
> Operation stopped...
> - Initializing Data Flow Task (Success)
> - Initializing Connections (Success)
> - Setting SQL Command (Success)
> - Setting Source Connection (Success)
> - Setting Destination Connection (Success)
> - Validating (Error)
> Messages
> * Error 0xc0202049: Data Flow Task: Failure inserting into the
> read-only column "DEX_ROW_ID".
> (SQL Server Import and Export Wizard)
> * Error 0xc0202045: Data Flow Task: Column metadata validation
> failed.
> (SQL Server Import and Export Wizard)
> * Error 0xc004706b: Data Flow Task: "component "Destination -
> GL00100" (139)" failed validation and returned validation status
> "VS_ISBROKEN".
> (SQL Server Import and Export Wizard)
> * Error 0xc004700c: Data Flow Task: One or more component failed
> validation.
> (SQL Server Import and Export Wizard)
> * Error 0xc0024107: Data Flow Task: There were errors during task
> validation.
> (SQL Server Import and Export Wizard)
> - Prepare for Execute (Stopped)
> - Pre-execute (Stopped)
> - Executing (Success)
> - Copying to [FS].[dbo].[GL00100] (Stopped)
> - Copying to [FS].[dbo].[GL00102] (Stopped)
> - Copying to [FS].[dbo].[GL00103] (Stopped)
> - Copying to [FS].[dbo].[GL00104] (Stopped)
> - Copying to [FS].[dbo].[GL00105] (Stopped)
> - Copying to [FS].[dbo].[GL40200] (Stopped)
> - Post-execute (Stopped)
> - Cleanup (Stopped)
> Any idea what the problem is?
> Thanks.
> Jerry Flatto
DEX_ROW_ID seems to be timestamp column which can not be imported

Copying tables between two databases

I am trying to copy the chart of accounts from Fabrikam to a newly created
company. I am running SQL 2005 and Dynamics GP 9.0.
I am following the steps from article 866332 in the knowledge base. This
process has the user copying some database tables from one database to
another database.
I perform the steps but when I try to execute the process, I get the followi
ng
errors:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc0202049: Data Flow Task: Failure inserting into the
read-only column "DEX_ROW_ID".
(SQL Server Import and Export Wizard)
* Error 0xc0202045: Data Flow Task: Column metadata validation
failed.
(SQL Server Import and Export Wizard)
* Error 0xc004706b: Data Flow Task: "component "Destination -
GL00100" (139)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
* Error 0xc004700c: Data Flow Task: One or more component failed
validation.
(SQL Server Import and Export Wizard)
* Error 0xc0024107: Data Flow Task: There were errors during task
validation.
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [FS].[dbo].[GL00100] (Stopped)
- Copying to [FS].[dbo].[GL00102] (Stopped)
- Copying to [FS].[dbo].[GL00103] (Stopped)
- Copying to [FS].[dbo].[GL00104] (Stopped)
- Copying to [FS].[dbo].[GL00105] (Stopped)
- Copying to [FS].[dbo].[GL40200] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Any idea what the problem is?
Thanks.
Jerry FlattoOn Mar 24, 2:04 am, Jerry Flatto
<JerryFla...@.discussions.microsoft.com> wrote:
> I am trying to copy the chart of accounts from Fabrikam to a newly create
d
> company. I am running SQL 2005 and Dynamics GP 9.0.
> I am following the steps from article 866332 in the knowledge base. This
> process has the user copying some database tables from one database to
> another database.
> I perform the steps but when I try to execute the process, I get the follo
wing
> errors:
> Operation stopped...
> - Initializing Data Flow Task (Success)
> - Initializing Connections (Success)
> - Setting SQL Command (Success)
> - Setting Source Connection (Success)
> - Setting Destination Connection (Success)
> - Validating (Error)
> Messages
> * Error 0xc0202049: Data Flow Task: Failure inserting into the
> read-only column "DEX_ROW_ID".
> (SQL Server Import and Export Wizard)
> * Error 0xc0202045: Data Flow Task: Column metadata validation
> failed.
> (SQL Server Import and Export Wizard)
> * Error 0xc004706b: Data Flow Task: "component "Destination -
> GL00100" (139)" failed validation and returned validation status
> "VS_ISBROKEN".
> (SQL Server Import and Export Wizard)
> * Error 0xc004700c: Data Flow Task: One or more component failed
> validation.
> (SQL Server Import and Export Wizard)
> * Error 0xc0024107: Data Flow Task: There were errors during task
> validation.
> (SQL Server Import and Export Wizard)
> - Prepare for Execute (Stopped)
> - Pre-execute (Stopped)
> - Executing (Success)
> - Copying to [FS].[dbo].[GL00100] (Stopped)
> - Copying to [FS].[dbo].[GL00102] (Stopped)
> - Copying to [FS].[dbo].[GL00103] (Stopped)
> - Copying to [FS].[dbo].[GL00104] (Stopped)
> - Copying to [FS].[dbo].[GL00105] (Stopped)
> - Copying to [FS].[dbo].[GL40200] (Stopped)
> - Post-execute (Stopped)
> - Cleanup (Stopped)
> Any idea what the problem is?
> Thanks.
> Jerry Flatto
DEX_ROW_ID seems to be timestamp column which can not be imported

Copying tables between two databases

I am trying to copy the chart of accounts from Fabrikam to a newly created
company. I am running SQL 2005 and Dynamics GP 9.0.
I am following the steps from article 866332 in the knowledge base. This
process has the user copying some database tables from one database to
another database.
I perform the steps but when I try to execute the process, I get the following
errors:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc0202049: Data Flow Task: Failure inserting into the
read-only column "DEX_ROW_ID".
(SQL Server Import and Export Wizard)
* Error 0xc0202045: Data Flow Task: Column metadata validation
failed.
(SQL Server Import and Export Wizard)
* Error 0xc004706b: Data Flow Task: "component "Destination -
GL00100" (139)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
* Error 0xc004700c: Data Flow Task: One or more component failed
validation.
(SQL Server Import and Export Wizard)
* Error 0xc0024107: Data Flow Task: There were errors during task
validation.
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [FS].[dbo].[GL00100] (Stopped)
- Copying to [FS].[dbo].[GL00102] (Stopped)
- Copying to [FS].[dbo].[GL00103] (Stopped)
- Copying to [FS].[dbo].[GL00104] (Stopped)
- Copying to [FS].[dbo].[GL00105] (Stopped)
- Copying to [FS].[dbo].[GL40200] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Any idea what the problem is?
Thanks.
Jerry FlattoOn Mar 24, 2:04 am, Jerry Flatto
<JerryFla...@.discussions.microsoft.com> wrote:
> I am trying to copy the chart of accounts from Fabrikam to a newly created
> company. I am running SQL 2005 and Dynamics GP 9.0.
> I am following the steps from article 866332 in the knowledge base. This
> process has the user copying some database tables from one database to
> another database.
> I perform the steps but when I try to execute the process, I get the following
> errors:
> Operation stopped...
> - Initializing Data Flow Task (Success)
> - Initializing Connections (Success)
> - Setting SQL Command (Success)
> - Setting Source Connection (Success)
> - Setting Destination Connection (Success)
> - Validating (Error)
> Messages
> * Error 0xc0202049: Data Flow Task: Failure inserting into the
> read-only column "DEX_ROW_ID".
> (SQL Server Import and Export Wizard)
> * Error 0xc0202045: Data Flow Task: Column metadata validation
> failed.
> (SQL Server Import and Export Wizard)
> * Error 0xc004706b: Data Flow Task: "component "Destination -
> GL00100" (139)" failed validation and returned validation status
> "VS_ISBROKEN".
> (SQL Server Import and Export Wizard)
> * Error 0xc004700c: Data Flow Task: One or more component failed
> validation.
> (SQL Server Import and Export Wizard)
> * Error 0xc0024107: Data Flow Task: There were errors during task
> validation.
> (SQL Server Import and Export Wizard)
> - Prepare for Execute (Stopped)
> - Pre-execute (Stopped)
> - Executing (Success)
> - Copying to [FS].[dbo].[GL00100] (Stopped)
> - Copying to [FS].[dbo].[GL00102] (Stopped)
> - Copying to [FS].[dbo].[GL00103] (Stopped)
> - Copying to [FS].[dbo].[GL00104] (Stopped)
> - Copying to [FS].[dbo].[GL00105] (Stopped)
> - Copying to [FS].[dbo].[GL40200] (Stopped)
> - Post-execute (Stopped)
> - Cleanup (Stopped)
> Any idea what the problem is?
> Thanks.
> Jerry Flatto
DEX_ROW_ID seems to be timestamp column which can not be importedsql

Copying Tables

Hi,

I'd like a really simple way of making a replica of a table. The thing is i'd like the table name to be a variable. The following code doesn't work, any ideas??

Thanks in advance,

Alph

CREATE Procedure Test

@.vMonth as varchar(3)

As

SELECT tbl_Targets.* INTO @.vmonth
FROM tbl_Targets;
GOLook up sp_executesql and EXECUTE in the SQL Books Online.

Copying specific data from table in DB1 to table in DB2

I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB:
CompanyID, FacilityID, EmployeeID, FirstName, LastName,
[Password] = 'nippert', Role = 'Employee'

I tried the advice on this website but to no avail:
http://www.w3schools.com/sql/sql_select_into.aspHi Jason,INSERT INTO should fit your needs.

copying selective tables with SMO(VB)

Hi ,

I’m using following VB script (SMO) to copy tables between instances and it works fine. At the moment

xfr.CopyAllTables = True

but I would like to be able to be selective (list of table) that would be transferred.

Dim svr_connection As ServerConnection = New ServerConnection()

svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5"

'svr_connection.ConnectionString = "Data Source=TECH-907WL2J;Initial Catalog=CustomerDM_Control;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

dest_svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5_2"

'dest_svr_connection.ConnectionString = "Data Source=TECH-907WL2J;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("adventureworks")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "adventureworkscopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

xfr = New Transfer(db)

xfr.CopyAllTables = True

'xfr.ObjectList.Add("Shaunt")

xfr.DestinationDatabase = "adventureworkscopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2"

'xfr.DestinationLoginSecure = True

xfr.CopySchema = True

xfr.CopyData = True

xfr.CopySchema = True

xfr.Options.WithDependencies = True

'Script the transfer. Alternatively perform immediate data transfer with TransferData method.

'xfr.ScriptTransfer()

xfr.TransferData()

can you give me any idea?

Thanks

Moving thread...

Copying rows from multiple tables to a single table

Hi,

I have 3 tables with the follwing schema

Table <Category>

{

UniqueID,

LastDate DateTime

}

Assume the follwing tables with data following the above schema

Table Cat1

{

1, D1

2, D2

3, D3

}

Table Cat2

{

2, D4

3,D5

4, D6

}

Table Cat3

{

1, D7

3,D8

5,D9

}

I have a Master and the schema is as follows

Table master

{

UniqueId,

Cat1 DateTime, -- This is same as the Table name

Cat2 DateTime, -- This is same as the Table name

Cat3 DateTime -- This is same as the Table name

}

After inserting the data from all these 3 tables, I want the my master table to look like this

Table Master

{

UniqueId cat1 cat2 Cat3

- --

1 D1 NULL D7

2 D2 D4 NULL

3 D3 D5 D8

4 NULL D6 NULL

5 NULL NULL D9

}

Please remember the column names will be same as that of table names

can any one pelase let me know the query t o acheive this

Thanks for your quick response

~Mohan Babu

Try:

Code Snippet

insert into table_master([uniqueid], cat1, cat2, cat3)

select

[id],

[Cat1],

[Cat2],

[Cat3]

from

(

select [id], 'Cat1' as table_name, lastdate from dbo.Cat1

union all

select [id], 'Cat2' as table_name, lastdate from dbo.Cat2

union all

select [id], 'Cat3' as table_name, lastdate from dbo.Cat3

) as a

pivot

(

max(lastdate)

for table_name in ([Cat1], [Cat2], [Cat3])

) as pvt

go

AMB

|||

Can you use full outter join style?

CREATE Table #Cat1

(

id int,

val varchar(10)

)

insert into #cat1 (id, val) values (1, 'D1')

insert into #cat1 (id, val) values (2, 'D2')

insert into #cat1 (id, val) values (3, 'D3')

CREATE Table #Cat2

(

id int,

val varchar(10)

)

insert into #cat2 (id, val) values (2, 'D4')

insert into #cat2 (id, val) values (3, 'D5')

insert into #cat2 (id, val) values (4, 'D6')

SELECT

CASE

WHEN C1.Id IS NOT NULL THEN C1.Id

WHEN C2.Id IS NOT NULL THEN C2.Id

ELSE NULL

END AS [Id],

C1.val,

C2.val

FROM #Cat1 c1

FULL OUTER JOIN #cat2 C2 ON

(C1.Id = C2.Id)

DROP TABLE #Cat1

DROP TABLE #Cat2

|||

Code Snippet

Create Table #cat1 (

id int,

[LastDate] datetime

);

Insert Into #cat1 Values('1','01/01/07');

Insert Into #cat1 Values('2','01/02/07');

Insert Into #cat1 Values('3','01/03/07');

Create Table #cat2 (

id int,

[LastDate] datetime

);

Insert Into #cat2 Values('2','01/02/07');

Insert Into #cat2 Values('3','01/03/07');

Insert Into #cat2 Values('4','01/04/07');

SELECT c.id, c.LastDate, d.LastDate FROM (SELECT a.id, b.LastDate FROM (SELECT id FROM #cat1

union

SELECT id FROM #cat2) a LEFT JOIN #cat1 b ON a.id=b.id) c LEFT JOIN #cat2 d ON c.id=d.id

|||

Hi AMB and limon

Thank you bothe very much for your queries.

Both works perfect.

AMB: Can you pelase explain me why u have used Pivt and what is the impact of it in terms of performace and also can u please explain me why you have used max(lastUpdate) and what is the use of it

Limon : Though it works fine i feel the query is bit long.

My concern is that i may have N number of category tables and i have to merge them into a single table.

So i need to build the a query dynamically based on the number of the tables and merge them.

Can any one please explaine me which one executes faster as i might have millions of rows in the category table

Thanks for your time

~Mohan

|||

Dave,

This query inserts null if the ID doesn't exist in botht the tables.

Thanks for your time

~Mohan

|||

Hi mohandbabud,

why did I use pivot?

Because that is what you are doing. You are pivoting rows to columns. When using pivot operator, you need an aggregation function, and It does not make sense to use SUM, AVG, etc, with a [datetime] data type. you could use MIN also. BTW, I supposed that the [id] is unique in each category table.

About which one one will execute faster, I do not know, because both queries are doing heavy stuff. You can tell us when you have finished.

AMB

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copying named ranges or arrays from one sheet to another

Does anyone know how to achieve the following as my do loop contstructions
are taking quite a while to execute and this would be far faster I am sure
(i used to know how to do it but cannot for the life of me remember what I
did and I cannot find the answer on google)?
I have a column A containing dates which expand by one each day (funnily
enough). Basically I want to copy the dates from one sheet to another as
fast as possible) and then copy a column of numbers into the adjacent column
(I wnat to copy one column at a time so offset will probably do em when i
get the syntax). My idea is to define the column of dates as a named range
and then just say "the same range on shhet B is to equal the named range"
the idea being that just picking up the dates as a blocka nd plonking them
in the destination sheet is likeyl to be quicker than assigning the values
one at a time as i do now using a do loop construction.
Would appreciate any pointers, kind regards, MarkHi
When you talk of sheets I assume you are talking about excel and not SQL
Server? In which case there may be a more appropriate group to post to. If
you are using Excel as a linked server then it might be possible to somethin
g
like (untested):
INSERT INTO LinkedExcel..[Sheet2$](datecol1,col2,col3)
SELECT datecol1,col2,col3
FROM LinkedExcel..[Sheet1$]
WHERE datecol1 > '20050101'
AND datecol1 < '20050801'
John
"Mark Stephens" wrote:

> Does anyone know how to achieve the following as my do loop contstructions
> are taking quite a while to execute and this would be far faster I am sure
> (i used to know how to do it but cannot for the life of me remember what I
> did and I cannot find the answer on google)?
> I have a column A containing dates which expand by one each day (funnily
> enough). Basically I want to copy the dates from one sheet to another as
> fast as possible) and then copy a column of numbers into the adjacent colu
mn
> (I wnat to copy one column at a time so offset will probably do em when i
> get the syntax). My idea is to define the column of dates as a named range
> and then just say "the same range on shhet B is to equal the named range"
> the idea being that just picking up the dates as a blocka nd plonking them
> in the destination sheet is likeyl to be quicker than assigning the values
> one at a time as i do now using a do loop construction.
> Would appreciate any pointers, kind regards, Mark
>
>

Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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