显示标签为“local”的博文。显示所有博文
显示标签为“local”的博文。显示所有博文

2012年3月25日星期日

BCP to txt file

I am trying the following but it only works on a local database I want to ru
n
this on a remote server and have the file sent to my PC.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'You have to create a shared folder in your pc and give write access to sql
agent services account. Then use that shared folder in the bcp command.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout
"\\your_pc_name\shared_folder\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'
AMB
"Lontae Jones" wrote:

> I am trying the following but it only works on a local database I want to
run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -
S
> server1 -U sa -P sapwd -c'
>|||It works only if your servers are linked.
Try using tabe name directly (instead of using query) it should work
bcp "TestDB_data..titles" OUT c:\test_08-25-05.txt ....
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:80C1C9DA-2C3F-4048-9B55-7F849FD6B662@.microsoft.com...
>I am trying the following but it only works on a local database I want to
>run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout
> "c:\test_08-25-05.txt" -S
> server1 -U sa -P sapwd -c'
>

2012年3月6日星期二

bcp error

I am getting an error that I can't open the host file when running bcp
through a tsql statement. When I set the path to a local drive on the sql
server the utility runs. However, when I set the path to a unc path that th
e
server and service account both have permission to then it won't run.
Any ideas on what I should try to fix this problem?Who is the owner of the job? If it is not sa then you will use the account
setup for the Proxy. See this: http://www.support.microsoft.com/?id=269074
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>I am getting an error that I can't open the host file when running bcp
> through a tsql statement. When I set the path to a local drive on the sql
> server the utility runs. However, when I set the path to a unc path that
> the
> server and service account both have permission to then it won't run.
> Any ideas on what I should try to fix this problem?|||I don't know if this helps answer your question, but it does not run as a
job. It is running through a stored procedure. DBO is the ower of the
stored procedure.
"Andrew J. Kelly" wrote:

> Who is the owner of the job? If it is not sa then you will use the accoun
t
> setup for the Proxy. See this: [url]http://www.support.microsoft.com/?id=269074[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>
>|||Whether it is a job or not still has the requirement of who is trying to
execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
that user is a member of the sa role it will use the account that SQL Agent
is running under. If not it will attempt to use the account that is set for
the Proxy account of SQL Agent. The proxy account most likely does not have
the right permissions. From BOL under xp_cmdshell:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...[vbcol=seagreen]
>I don't know if this helps answer your question, but it does not run as a
> job. It is running through a stored procedure. DBO is the ower of the
> stored procedure.
> "Andrew J. Kelly" wrote:
>|||I executed the following:
xp_sqlagent_proxy_account n'get'
It returned an account. I checked the account and it was a member of the
sysadmin role.
To me, it seems like it should be working.
"Andrew J. Kelly" wrote:

> Whether it is a job or not still has the requirement of who is trying to
> execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
> that user is a member of the sa role it will use the account that SQL Agen
t
> is running under. If not it will attempt to use the account that is set f
or
> the Proxy account of SQL Agent. The proxy account most likely does not ha
ve
> the right permissions. From BOL under xp_cmdshell:
> When xp_cmdshell is invoked by a user who is a member of the sysadmin fixe
d
> server role, xp_cmdshell will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent prox
y
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail. This is true only for
> Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
> impersonation and xp_cmdshell is always executed under the security contex
t
> of the Windows 9.x user who started SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...
>
>|||That does not mean it has permissions to access the file share or even the
server. Is that account a domain account with permissions to that share?
The sa part has to do with who is running the command and not the proxy
account. Please read the entry in BOL for xp_cmdshell.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...[vbcol=seagreen]
>I executed the following:
> xp_sqlagent_proxy_account n'get'
> It returned an account. I checked the account and it was a member of the
> sysadmin role.
> To me, it seems like it should be working.
>
> "Andrew J. Kelly" wrote:
>|||The account is a domain account and does have permissions to the share.
"Andrew J. Kelly" wrote:

> That does not mean it has permissions to access the file share or even the
> server. Is that account a domain account with permissions to that share?
> The sa part has to do with who is running the command and not the proxy
> account. Please read the entry in BOL for xp_cmdshell.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...
>
>

2012年2月13日星期一

Batch insert

I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
MarkI am confused:confused: What is it that you need?

If you're talking about being able to do BULK INSERT from a stored procedure, you can do that, but you'll have to grant the User ID that will execute it Bulk Insert Administrator server role (bulkadmin.)|||Sorry
I have 2 tables
table 1 - remote, cant use an SP
table 2 - local, can use a SP

I do a select on Table1 and it returns the results,

I want to insert all these results into table 2

whats the best way to do that? (most efficient)

Cheers
Mark|||The problem is that the first table is really slow to access, I have a string of booking Id's i need to pass it and it keeps timing out...its the DB! its on rubbish HW and is badly designed but I cant do anything about that so I have to use the most efficent method of getting the data out, or simply copying the required records between the 2 tables

remember the rubbish DB is not local and I only have read access to it!

whats the most efficent method I should use?

thanks
Mark|||Create a stored procedure on the remote server that retrieves rows via SELECT, and call it instead of doing SELECT from your local server.

batch file to copy file and append date

The Sql Server database can only see the local drive.

I would like to set up a batch file that will copy a SQL Server

backup file from the local drive to the network drive. I would

like to append the file date to the end of the copied file. I

assume a batch file can accomplish this but I am new to batch

file writing. Does anyone have code that they already created

for this sort of task??

Thank you!You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?

I just want a plain copy within a batch file, but one that can append the file date (ie BackupFile_mm_dd_yyyy). Currently this copy has been set up within windows Task scheduler. I want to do the same but run the batch file in the task. Fyi: Because SQL Server cannot see the network, the task was set up with Windows tasks scheduler.

Let me know your thoughts..

Thank you.|||You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.

Thank you for your suggestion. I have not worked with VB Script so was hoping to accomplish this within a batch file. But thank you for your response. I will look into this as another option.|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP

Can you please explain the %1 and %~t1, will this append the file date?
Thanks!|||Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatPIf the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.|||I have a job which save databases structure into files. It create directory 'year-month-day' and dump structure there. This is code:
----------
DECLARE @.command varchar(1000);
-- create local directory for dump
SET @.command='mkdir C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--create dump
SET @.command='xp_cmdshell ''"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s TESTERS /d ? /P 1 /f C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+'\?.sql''';
exec sp_MSforeachdb
@.command1 = @.command,
@.replacechar = '?'

--create remote dir
SET @.command='mkdir \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--copy databases structure to remote computer
SET @.command='copy '+'C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+' \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;
----------
I hope this help you|||If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
Can you suggest how to append mmddyyyy to end of filename in a backup command within a SQL SERVER job?|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP
The results is close but beacuse of the slashes in the date, the output is going into subdirectories not a single filename. Any suggestions?|||I figured out how to copy a file and append a date to the copy. Here is the code for the batch file:

:: COPY FILE AND DATE
::
@.ECHO OFF

FOR /F "tokens=2,3,4 delims=/ " %%a IN ('DATE /t') DO SET mydate=_%%a_%%b_%%c

::ECHO The value is "%mydate%"
copy "c:\readme" "L:\DATABASES\PROJECT REVIEW\readme%mydate%" /Y|||I am sure you did, and so did I, after reading this (http://www.computerhope.com/batch.htm#5)and similar pages ;)