Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts

Wednesday, March 28, 2012

How to use Xcopy & User Instance to copy 3 dbo tables from the database of SQL Server Mana

Hi all,

I have read/studied (i) Working with Databases in Visual Web Developer 2005 Express inhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/vwd.aspx, (ii) Xcopy Deployment (SQL Server Express) inhttp://msdn2.microsoft.com/en-us/library/ms165716.aspx, (iii) User Instances for Non-Administrators inhttp://msdn2.microsoft.com/en-us/library/ms143684.aspx, and (iv) Embedding SQL Server Server Express in Applications inhttp://msdn2.microsoft.com/en-us/library/ms165660.aspx. I do not understand the concepts and procedures to do Xcopy and User Instances for non-administrators completely-I do not know how to connect to databases and create database diagrams or schemas using the Database Explorer. I have a stand-alone Windows XP Pro PC. I have created a ChemDatabase with 3 dbo tables in the SQL Server Management Studio of my SQL Server Express and a website of my VWD Express application with an App_Data folder. I am not able to proceed to use Xcopy and user instance to bring the 3 dbo tables of ChemDatabase to my App_Data folder. Please help and give me some detailed procedures/instructions to bring the 3 dbo tables of ChemDatabase (or ChemDatabase itself) from the SQL Server Management Studio Express to the App_Data folder of the website of my VWD Express project?

Thanks in advance,

Scott Chang

I am sure those documentation entries were good intentioned but all of the above is not used with standard SQL Server so it is not very useful because you cannot xcopy a regular SQL Server database. The SQL Server team have a blog entry that will help you connect your user instance so you can use it in Management Studio. You could choose to do it per those docs which is not applicable to regular SQL Server or take the time to read up on how SQL Server really work. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||

Hi Caddre, Thanks for your response.

I read the article "SQL Server Express Weblog" you suggested in your response. I studied that article and Xcopy Deployment (SQL Server Express) in the Microsoft Library very hard for the last 5 days. I think I know more about Xcopy and User Instance. But I still do not know where and how to get my Xcopy deployment started in the website of my VWD Express application!!!? I have the Micosoft "Northwind" Database in the SQL Server Management Studio Express of my SQL Server Express that is installed in my stand-alone Windows XP Pro PC. My VWD Express program is also installed in the same stand-alone Windows XP Pro PC. I think that User Instance in my SQL Server Express is established. Suppose that I want to get the .mdf file of Northwind Database in the App_Data folder of a website of my VWD Express project. Where and how can I get Xcopy and Use Instance started? Please help me and kindly give me the key steps/instructions for obtaining the .mdf file of Northwind Database to the App_Data folder of the website of my VWD Express project via the Xcopy procedures.

Many Thanks in advance,

Scott Chang

|||

Hi Scott,

I found the MSDN thread below you have posted there that is very good since I don't think xcopying a SQL Server database is a valid operation I will let that forum provide you the means to implement it. However I have created a faq of the correct ways to move a SQL Server database so I will also post that thread, when you choose to know a SQL Server database as half of your application I will be here to help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1090046&SiteID=1

http://forums.asp.net/thread/1454694.aspx

Wednesday, March 21, 2012

how to use select statement in insert query

hi my self avi
i want to copy data from one table to other table,by giving certain
condition and i want o use insert statement .in this i want to pass some
value directly and some value from select statement , if i try i ll get
error i.e all column of destination table (i.e in which i want to insert
data) should match with all columns in values column some thing like
this.
plz give me some helpful suggetion on this"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:f9b4f44fb15b1beabe792c1f314e7f86@.localhost.ta lkaboutdatabases.com...
> hi my self avi
> i want to copy data from one table to other table,by giving certain
> condition and i want o use insert statement .in this i want to pass some
> value directly and some value from select statement , if i try i ll get
> error i.e all column of destination table (i.e in which i want to insert
> data) should match with all columns in values column some thing like
> this.
> plz give me some helpful suggetion on this

Something like this?

insert into dbo.Table1
(col1, col2, col3, col4)
select 'A', co11, 7, col8
from dbo.Table2
where...

In the SELECT statement, you can mix constant values ('A' and 7) with column
values (col1 and col8) - I think this is what you want to do, if I
understood you correctly. If this doesn't help, then I suggest you post
CREATE TABLE statements for both tables, and the INSERT statement you're
trying to make work, as that will avoid any confusion and guesswork.

Simon

Friday, February 24, 2012

How to use CDO to send mail with attachment

I have a stored proc thats basically a copy of the script from MS to create
sp_send_cdosysmail.
This work fine using our mailrelay, but there seems to be no clear way to
add an attachment.
I've tried a simple:
EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
but that just causes an unknown name error.
I've also tried creating an attachment object but I can't seems to assciate
it with the original cdo.message:
EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iATT
I've tried variations on creation of the original object and methods, but I
basically just guessing!
I've read somewhere that the file has to be accessable as a http path. This
is purely a SQL 2000 server, no extras like IIS or any form of exchange
client.
Has anyone sent attachments using SQL server and CDO? Can you please offer
some assisance.
Thanks in advance.Typically, I've found out what I need to know about 30mins after this post.
Why doesn't it happen 30 minutes after you first start looking!
Ha|||Hi, Phil
Would you mind sharing your idea about 'CDO sending mail with attachment'?
1. What is the syntax of CDO with attachment?
2. How to use the result of query as attachment?
Thanks a lot!
Tianya
"Phil_Edney" wrote:

> I have a stored proc thats basically a copy of the script from MS to creat
e
> sp_send_cdosysmail.
> This work fine using our mailrelay, but there seems to be no clear way to
> add an attachment.
> I've tried a simple:
> EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
> but that just causes an unknown name error.
> I've also tried creating an attachment object but I can't seems to assciat
e
> it with the original cdo.message:
> EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
> EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
> EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
> EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
> EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
> EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iA
TT
> I've tried variations on creation of the original object and methods, but
I
> basically just guessing!
> I've read somewhere that the file has to be accessable as a http path. Thi
s
> is purely a SQL 2000 server, no extras like IIS or any form of exchange
> client.
> Has anyone sent attachments using SQL server and CDO? Can you please offer
> some assisance.
> Thanks in advance.|||I found a solution at the following web site.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
"Tianya" wrote:
> Hi, Phil
> Would you mind sharing your idea about 'CDO sending mail with attachment'?
> 1. What is the syntax of CDO with attachment?
> 2. How to use the result of query as attachment?
> Thanks a lot!
> Tianya
>
> "Phil_Edney" wrote:
>

how to use bulk copy?

hi..

how to use bulk copy in MS SOL Server?

Please seehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_6_040_101f.asp for more details information about bulk copy...

Lan

How to use a vriable in DTS Package

Hello,

I have set up a simple DTS Package to copy a table (backup) in my database. I have specified the name of the table, however I want the naming of the new table to be dynamic and use the current timestamp, but I am not sure if and how I can define a variable and use it in the package.

Any thoughts? Do you need any more info?

Thanks.

RodneyHere is my SQL for the new table creation. I want the following name to be dynamic "CON_D_EmployeeInfo_backup20040526" Maybe use "CON_D_EmployeeInfo_{CurrentDateTime}".

CREATE TABLE [ancosalesdm].[dbo].[CON_D_EmployeeInfo_backup20040526] (
[EmployeeID] varchar (15) NULL,
[EmployeeTitle] varchar (46) NULL,
[EmployeePosition] varchar (46) NULL,
[EmployeeCostCenter] varchar (4) NULL,
[EmployeeDivision] varchar (256) NOT NULL,
[EmployeeStatusFlag] varchar (3) NULL,
[curr_ind] varchar (1) NOT NULL,
[last_up_date] datetime NOT NULL,
[eff_start_date] datetime NOT NULL,
[eff_end_date] datetime NULL,
[key] int NOT NULL
)