bcp still more errors

I managed to coorect the earlier error but now it looks like this
master..xp_cmdshell 'bcp ##ttable
OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
the new error is
NULL
Enter the file storage type of field state_id [int-null]:
(2 row(s) affected)
I have 2 fields in the temp tableHi,
Instead of -C can use -c (small case). The -C is for code page.
-c does not prompt for each field; it uses char as the storage type
--
Thanks
Hari
MCDBA
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:u8U6iVWaEHA.3016@.tk2msftngp13.phx.gbl...
> I managed to coorect the earlier error but now it looks like this
> master..xp_cmdshell 'bcp ##ttable
> OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
> the new error is
> NULL
> Enter the file storage type of field state_id [int-null]:
> (2 row(s) affected)
> I have 2 fields in the temp table
>

BCP problem

Exec Master..xp_CmdShell 'bcp "exec mydb..SP_test ''200603''" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'

when i run this code in java , the file is created , but data not pump in , anyone know why is it so ?

it can run in query analyzer and no problemsorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data|||sorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data out

bcp only runs on master database

Hello,
I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.
Please help
ThanksOriginally posted by markfraser
Hello,

I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.

Please help
Thanks
Here is an example how to do it with VB (youll find this example at the help of MS SQL Server and will help you to do it from within Query analizer too I guess.

Peter

The following Microsoft Visual Basic code uses the BulkInsertTask object to insert data from file D:\DTS_UE\BCPData\Payroll.txt into table Payroll of database DTS_UE.

Public Sub Main()
'initialize Payroll table in DTS_UE db with bulk data
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objBulkCopy As DTS.BulkInsertTask

Set objPackage = New DTS.Package

'create database connection
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'create step and task, specify data file and format
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSBulkInsertTask")
Set objBulkCopy = objTask.CustomTask
With objBulkCopy
.Name = "BulkInsTask"
.DataFile = "D:\DTS_UE\BCPData\Payroll.txt"
.ConnectionID = 1
.DestinationTableName = "DTS_UE..Payroll"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With

'link step to task to package, run package
objStep.TaskName = objBulkCopy.Name
objStep.Name = "BulkInsStep"
With objPackage
.Steps.Add objStep
.Tasks.Add objTask
.FailOnError = True

.Execute
End With
End Sub|||Originally posted by markfraser
Hello,

I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.

Please help
Thanks

do you have an admin on your team other than yourself? post your bcp.|||True, post the code used and BCP is a command link utility and not pertained to default to any database.

BCP in stored procedure

I have a stored procedure, which loops through a database and runs a BCP
statement (with changing criteria), as follows:
exec master..xp_cmdshell 'bcp "SELECT myfields FROM Database..Viewname WHERE
criteria ORDER BY criteria" queryout "C:\File.txt" -S ServerName -T -c'
When I run this, I get the following error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'MY DATABASE NAME' in sysservers. Execute sp_addlinkedserver to add the
server to sysservers.
I'm telling it which server to use, but it's reading the database name as a
server, for some reason. The server, however DOES show in sysservers. I've
also tried running this with -U sa -P ... with the same results. What would
be causing this? Thanks for advance for any help you can offer.
BariYou have to register that Server on the server you are running the query on.
You register the Server by executing
sp_addlinkedserver YOURSERVERNAME
Otherwise it won't be in systables.
Pain if you wanna distribute this code.

bcp error: Unable to open BCP host data-file

I get this error message:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

if I try to run

EXEC master..xp_cmdshell
'bcp TestDB.dbo.Employees out \\Ioana\Export\Employees.txt -c -t"," '

from Query Analyzer (or a store procedure).

If run the same command in cmd or run window (of course without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...

If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...

Thanking in AdvanceMy guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.|||Originally posted by MCrowley
My guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.

SQL Server is install on SQLSERVER machine and Ioana is my machine.
I want to run this script on SQLSERVER machine (in Query Analyzer) and to export the file in Export directory on my machine. The Export directory on my machine is shared with all the rights.

If run the bcp from cmd window on SQLSERVER machine is working fine. From Query Analyzer on the same machine (SQLSERVER) is not working even if I am logged in as system administrator with windows authentication. The administrator of SQLSERVER have full permision on my shared directory.

Thanks again

Basic questions on Service Master Key

Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...

Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions...
__

The database engine uses the Service Master Key to encrypt...
- Linked server passwords (example?)
- Connection Strings (example?)
- Account credentials (example?)
- All database master keys

You should back up the Service Master Key and store it offsite.
(Even if I never plan to use encryption?)

ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key.
(Is it building a completely different key, or regenerating the original?)
(Why would I want to regenerate it?)
(Does this void all database master keys encrypted by the original Service Master Key?)
(If so, do I regenerate all database master keys voided by the regeneration?)

A database master key is optional.
(So if I have Server A with encrypted database A, that I want to restore to Server B,
I need the following items to perform restore?
- a backup of database A
- the Service Master Key of A at time of backup
- associated password of Service Master Key at time of backup)

Just repeating what it is mentioned in BOL:

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.

Also http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx fyi for more information.

|||

A few corrections:

The Service Master Key (SMK) has two encryptions, both made using DPAPI - one uses the service account credentials, the other one uses the machine credentials. Note that neither of these encryptions is necessarily protecting the SMK from a machine administrator.

Regenerating the SMK will only require re-encrypting the entities encrypted by it: linked server login passwords (the ones you specify when you set up a linked server), credentials (as created by CREATE CREDENTIAL), and database master keys.

Coming back to the original questions:

It is not necessary to backup the SMK if you do not plan to use it.

REGENERATE creates a new key. It can be used as part of a key rotation policy or if you have reason to believe the old key was compromised. You can also use this to generate a new key if the old one was corrupted and you have no backup around. Regenerating the SMK doesn't invalidate any database master key (DbMK) - it just re-encrypts them with the new key.

For restoring a database to another server, see last paragraphs of http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx. You don't need to move the SMK to the new server, you just need to open and reencrypt the DbMK with the new server's SMK, and you only need to do this if you had the same setup on the original server.

Thanks
Laurentiu

|||

Thanks for taking the time to explain some concepts. I haven't had any experience with encryption, and now feel further behind with SQL 2005's new concepts.

I'm having problems understanding the extent the SMK is capable of.... you wrote about the SMK being compromised, but I don't see how you could compromise it, or what you could do with it once you have it?

- You have to be a SysAdmin to restore a SMK
- You would somehow have to get the SMK backup file onto the server
- You would have to have local access to the SMK backup file to restore it
- You would have to know the password of the restored SMK

And even if all that occurs:
- The linked servers all still show "********" for a password
- You can't decrypt data with a SMK (can you?)
- You can't decrypt a Database Master Key with the SMK (can you?)

|||

The SMK allows you to decrypt all that is encrypted beginning with it - database master keys, initially, then anything encrypted by the database master keys, and so on, towards the data itself.

As you've noticed, there are good protections set up around accessing the SMK, so my mention of "compromising" is more theoretical than practical - you could "loose" the SMK by loosing an SMK backup and the password protecting it, for example.

Having the SMK and a copy of a database with encrypted data (where the encryption protection is rooted at the SMK) allows you to decrypt the data.

Have a look at the encryption hierarchy in Books Online, to understand what key protects what information. The main point is that this is a hierarchy, so the key at its root unlocks everything below. You can detach protection of some data from this hierarchy by using password encryption for certain keys, which uses TripleDES encryption to protect those keys.

http://msdn2.microsoft.com/en-us/library/ms189586.aspx

Also listen to the presentation of encryption in SQL Server 2005 that Rob Walters gave at PASS this year. You can access Rob's presentation here:

http://cmcgc.com/Media/WMP/261115/

Also, there were no encryption features in SQL Server 2000. These features have been introduced in SQL Server 2005.

Thanks
Laurentiu