copy file

hi guys,

anybody know the syntax in SQL to copy file to other server.

we want to copy our db backup files automaticaly to other server.

thanks

-wei

Hi

It wud be helpful if u can be more specific, like if u wanna back up ur entire DB or just copy data from one table to another table in another DB

|||

hi,

I want to copy file to other server using SQL.

I found out that we can use xp_cmdshell but I can make it work.

my syntax is " exec xp_cmdshell 'copy c:\folder\test.xxx

\\server1\folder1, no_ouput ;

on the result pane it said succesful, but when I look to the destination folder there is no file that I copied.

|||

hi dedy,

hi try mapping "\\server1\folder1" to qa drive letter

to map

1. open windows explorer.

2. click tools.

3. clcik on map network drive

4. enter necessary permission

you can assign a specific user (using different username option) to your mappings

to make sure you wont run on a permission problem with NTFS

run

exec xp_cmdshell 'copy c:\folder\test.xxx x:' <--where x: is the mapped network drive

regards,

joey

|||

hi joey,

I have tries your suggestion.

but there is an error that said "The system cannot find the drive specified"

I'm using SQL 2005.

I already map the folder using administrator account.

please advice.

Copy Date Dimension

Hello Guys and Galls,

I have a fact table with two different keys referring to the same date dimension table. In a query you would use table aliassing to join the date dimension twice.

In AS I created a DSV with two aliasses of the date dimension table. Lets give um names:
- dim_ActivityDate
- dim_OrderDate

Now, I have created a dimension "Activity Date", based on the dim_ActivityDate dimension. I want to create a second dimension, "Order Date", which is a copy of the "Activity Date" dimension, but based on the dim_OrderDate alias from the DSV.

Is there an easy way to do this, or do I have to create the whole dimension using the wizard again?

Regards, Jeroen

Hi Jeroen,

No, you don't need to create two separate physical dimensions. You just need to create one dimension and add it to the cube more than once - a 'role playing dimension'. In Visual Studio just double click on your cube to edit it and assuming you have the dimension already added to the cube once, you just need to right-click on Dimensions box in the bottom-right-hand corner of the 'Cube Structure' tab and select 'Add Cube Dimension'. Add the date dimension again and you'll find that it gets added with a new name, which you can change to Order Date, and then if you go to the Dimension Usage tab you'll find you can join it to your measure group on the dim_OrderDate key column.

HTH,

Chris

|||Splendid! Thnx Chris.

Copy Database Problem SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the SERVER to any place in my laptop in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Check your duplicate post here.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Okay. I just didn't know which one is the right place to post this question.

Thanks anyway.

Copy Database Error using SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the net server called "SERVER" to any place in my laptop called "MOSHE-DELL" in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Hi,

There are a few things to check to help you along abit.

1) Connect to your SQL server via the "SQL Server Configuration Manager" and check services and that "Client Protocols" have TCP/IP and named pipes configured.
2)You should also ensure yiu are connecting via the correct machie and instance name be default it will be MOSHE-DELL\MSSQLSERVER.

CU

tosc

|||

Hi Tosc,

Where should I configure my SQL SERVER 2005 (in the local machine) in order to allow remote connections?

|||check the firewall on the Moshe-Dell server and verify that the correct ports are open.|||I have tried following the indications in: http://www.west-wind.com/WebLog/posts/4493.aspx and: http://support.microsoft.com/kb/914277 but this time getting the Error below: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent is not currently running so it cannot be notified of this action. (.Net SqlClient Data Provider)
|||

Hi,

By default, the SQL Server Agent service is disabled when SQL Server 2005 is installed unless the user explicitly chooses to autostart the service

By using the SQL Server 2005 Surface Area Configuration you could configure the SQLSERVERAGENT service!

Configuring SQL Server Agent

cu

tosc

|||

Using SSMS, on the left pane (Object Explorer), right click on SQL Server Agent. Select Start.

Then again right click on SQL Server Agent, select Properties. Verify that the AutoStart boxes are checked for both SQL Server and SQL Server Agent.

copy content of txt file

Hi Guys,

What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?

Example of the text file's content:

Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90

In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.

Regards,

Lars

Hi,

You could use DTS for this. Define a new DTS package, with your database in source, and a text file for output.

You can use an SQL order to filter your Data (..Where Date = "20060101" AND ....).

The result will be formatted as a CVS file by default. If you need to personalize your output format, you can use ActiveX scripts in transformation tasks to do this

ex :

Function Main()
DTSDestination("Date") = "Date, " & DTSSource("Date")
Main = DTSTransformStat_OK
End Function

|||

hi,

if you know the location of the row you want to copy you can use

the -f anf -l switch of BCP which stands for

-f = the first row

-l = the lastrow

regards,

joey

copy content of a text file into table

Hi Guys,

What approach should I use to copy content of a text file.

Example of the text file's content:

Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90

In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.

Regards,

Lars

Hi Larry,

If the text file is not very large, in that scenario you can simply iterate through the file line by line checking for those lines which contain your desired content. Extract the content and fire and insert against the table.

I am feared that you may not be able to find any ready-made solution for it.

|||

Larry Surat wrote:

Hi Guys,

What approach should I use to copy content of a text file.

Example of the text file's content:

Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90

In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.

Regards,

Lars

Read the file in as a single-columned dataset. Use Conditional Split to get only the rows you are interested in. Then use Derived Column to parse out all of the columns.

-Jamie

converting to numeric (6,3)

Hey guys,

I am trying to achive the following:

Truncate the values 1680.390000000000000000

to numeric (6,3)

All works ok until i hit a value over 1000, (as above)

The code i was using is:

cos1 = 1680.390000000000000000000

CONVERT (NUMERIC(6 , 3), ROUND(COS1, 3))

I have tried a few others, but nothing seems to work..

I get the following error. "Arithmatic Overflow"

Has anyone got any suggestions?

Thanks guys,

Scotty

A numeric(6,3) would only accept a number as large as 999.999 -six digits total, three digits to the right of the decimal.

Your value, 1680.xxxx exceeds the maximum size and creates an overflow condition -resulting in an error.

Perhaps you need a larger number, like maybe, just guessing here, numeric(8,3)?

|||

Brilliant!!!!

thanks vry much Archie.. this has been confusing me all day,, the way they explain it looks to me as 6 to the left, and 3 to the right...

Thanks again

Scotty

Converting text output of execution plans to graphical

Hi Guys,
does anyone know of any nice tools that will take the text output of a
profiler trace (with show execution plan traces on) and turn it into
the graphical version that you see through a query analyser?
Cheers
WillWill (william_pegg@.yahoo.co.uk) writes:
> does anyone know of any nice tools that will take the text output of a
> profiler trace (with show execution plan traces on) and turn it into
> the graphical version that you see through a query analyser?
If you are talking SQL 2000, I don't know any such tool.
However, in SQL 2005 you can capute the even Showplan XML, and as I reacall,
you can save that plan to disk, and then load into Mgmt Studio.
Nevermind that Query Analyzer has a better graphical presentation of
query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx