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 a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

Converting T-SQL *= OUTER JOINS to ANSI-92 syntax

Hello all. I have an application that has worked smoothly using the following query syntax:
FROM tbl_participation, tbl_adult, tbl_month, tbl_school_year
WHERE tbl_participation.adult_ID =* tbl_adult.ID

AND tbl_participation.month_ID =* tbl_month.ID
AND tbl_participation.year_id =* tbl_school_year.ID

AND tbl_adult.ID = 8
AND tbl_school_year.ID = 5
It works just fine, as I want the results to include a table with one column containing the month name, the table headed by the adult's name/school year. It needs to still return a table even if there have yet been no records in tbl_participation for that adult/month/year.
However, the following is my best shot at making the query ANSI-92 compliant, as when I implement SQL Server 2005 I don't want to have to go back and change compatibility modes:
FROM ((tbl_adult

LEFT OUTER JOIN tbl_participation ON tbl_participation.adult_ID = tbl_adult.ID)

RIGHT OUTER JOIN tbl_month ON tbl_participation.month_ID = tbl_month.ID)

RIGHT OUTER JOIN tbl_school_year ON tbl_participation.year_id = tbl_school_year.ID

WHERE tbl_adult.ID = 8
AND tbl_school_year.ID = 5
This query works fine if there is any data in tbl_participation for adult 8 and school year 5. But if nothing has yet been entered, it returns nothing. I need it to work like the older T-SQL iteration, and still return a list of the 12 months and the adult's name even if no participation data has been entered.
Thanks - this one has got me pulling my hair out.

With the JOIN clause, you should logically think of the ON clause as being evaluated first followed by WHERE clause, GROUP BY and HAVING clause. Note that with inner joins the optimizer can evaluate predicates in the WHERE and ON clause together. But when you outer joins the WHERE clause is always evaluated after the ON clause. So this will essentially prevent non-matching rows from being produced based on your example. Additionally, it also depends on whether you filter the rows before the outer join. So in your example, you need to move the tbl_adult.ID check to FROM clause using a derived table like:

FROM (( (select * from tbl_adult where ID = 8) as a
LEFT OUTER JOIN tbl_participation as p ON p.adult_ID = a.ID)
RIGHT OUTER JOIN tbl_month as m ON p.month_ID = m.ID)
RIGHT OUTER JOIN tbl_school_year as y ON p.year_id = y.ID
WHERE y.ID = 5

Converting Rrom Access To Sql Syntax

Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

Case Statement might be the solution but i could not do it.

Your input will be appreciated

Thank you

you rightly said that CASE Statement is the right choice...

check this....
select ID, FName, LName,

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

Madhu

|||Thank you for you respond. This won't really work for one simple reason.

It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

But it is not equal to this

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

This does not work i can't have two things happening after "Then"

Do you think i can convertt this to sql Syntax?

thanks

Converting Rrom Access Syntax To Sql Syntax

Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

Case Statement might be the solution but i could not do it.

Your input will be appreciated

Thank you

you rightly said that CASE Statement is the right choice...

check this....
select ID, FName, LName,

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

Madhu

|||Thank you for you respond. This won't really work for one simple reason.

It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

But it is not equal to this

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

This does not work i can't have two things happening after "Then"

Do you think i can convertt this to sql Syntax?

thanks

Converting Oracle Encode Function into T-SQL syntax

I am looking for the correct syntax to convert Oracle encode function into T-SQl syntax. Please provide specific syntax and examples.
Thank you.Don't you mean 'decode' function?|||did you see my reply on this thread (http://dbforums.com/showthread.php?s=&threadid=444782) ?

rudy