DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataRow in a CLR Stored Procedure

I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
created a Database project where I've written some simple CLR Stored
Procedures. I can deploy and call the simple CLR Stored Procedures from my
host WinForm application. This all works great.
I'd now like to write a CLR Stored Procedure with a parameter of type
System.Data.DataRow. Doing this compiles just fine, but when I attempt to
deploy, I get the following error:
Cannot find data type DataRow.
Any suggestions on what I might do to get a CLR Stored Procedure with a
DataRow parameter to compile AND deploy?
Thanks,
--
Randyexamnotes <randy1200@.newsgroups.nospam> wrote in
news:4A6B0CA3-733E-4E43-AA94-CD9D98B295C2@.microsoft.com:

> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
>
Can not be done. Your CLR procedures can only have params of types that are
T-SQL compatible.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||randy1200 (randy1200@.newsgroups.nospam) writes:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
You can't do that. A CLR stored procedure can only take parameters
that maps to types used in SQL Server, and DataRow is not such a type.
Keep in mind that even if the stored procedure is implemented in the CLR,
there is still is a CREATE PROCEDURE statement which looks just like
the CREATE PROCEDURE statement for a T-SQL procedure as far as the
parameter list is concerned.
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|||You could serialize the DataRow and use a VarChar or VarBinary parameter.
Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
"Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML Dat
a
Serialization." You could use an XML representation, however there is a
particularly interesting example of serializing/deserializing a DataTable an
d
the DataRows it contains using an efficient custom binary representation on
pages 424-428. The sample code is in C# and there isn't much code needed.
Hope this helps.
Jack Whitney
"randy1200" wrote:

> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
> Thanks,
> --
> Randy|||I have been playing around with Dino Esposito's example of custom binary
serialization of a DataTable and the DataRows it contains. (This is the
example that I mentioned in my earlier posting on this thread.) It is very
nicely implemented. The code to serialize a DataTable to a file and
deserialize the file back to a DataTable, including a worker class definitio
n
and comments is 78 lines of code. Dino provides a sample Windows applicatio
n
that connects to a Northwind database. You enter SQL in a textbox like
"SELECT * FROM [Order Details]" and the application gets the data from the
database into a DataTable, serializes the DataTable to a file, deserializes
the file back to a DataTable, and renders the DataTable in a DataGrid. He
actually serializes the DataTable using two methods and compares the output
for size efficiency. The two methods are ordinary .NET framework binary
serialization and his custom binary serialization. The custom binary
serialization produces much smaller output. The code was written for .NET
1.1, but I just converted it to .NET 2.0 and accessed SQL Server 2005 with n
o
hitches. If you are interested in pursuing this, you should definitely take
a look at this sample.
Note that to serialize just a DataRow, you would need to serialize some
properties of the containing DataTable object, minimally the column names an
d
types, in addition to the values in the DataRow. Dino's example demonstrate
s
this.
Hope this helps.
Jack Whitney
"Jack Whitney" wrote:
> You could serialize the DataRow and use a VarChar or VarBinary parameter.
> Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
> "Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML D
ata
> Serialization." You could use an XML representation, however there is a
> particularly interesting example of serializing/deserializing a DataTable
and
> the DataRows it contains using an efficient custom binary representation o
n
> pages 424-428. The sample code is in C# and there isn't much code needed.
> --
> Hope this helps.
> Jack Whitney
>
> "randy1200" wrote:
>

DataReader is blocking my tables

Hello Newsgroup,

I have written a very short program to get information from a whole
table out of a database. the problem is that other people couldn't
work on this table during the process. It seems that my program locks
the whole table.
I used the DataReader from the .NET Framework. Can you please take a
look at the code and give me any solution? Thank very much, Nils

Dim strSQL As String = "SELECT * FROM TESTTABLE"
Dim Conn As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection("Data Source=1.1.1.1;
User ID=sa;Password=secret;Persist Security Info=True;
Initial Catalog=TestDB")
Conn.Open()
Dim SqlCmd As SqlCommand = New SqlCommand(strSQL,Conn)
Dim DR As System.Data.SqlClient.SqlDataReader
Try
DR = SqlCmd.ExecuteReader
Do While DR.Read()
<only reading with DR.item("columnname")>
Loop
Catch ex As Exception
errorhandler(ex.ToString)
Finally
If DR.IsClosed = False Then DR.Close()
SqlCmd.Dispose()
End TryNils Pommerien (fishinet@.gmx.de) writes:
> I have written a very short program to get information from a whole
> table out of a database. the problem is that other people couldn't
> work on this table during the process. It seems that my program locks
> the whole table.
> I used the DataReader from the .NET Framework. Can you please take a
> look at the code and give me any solution? Thank very much, Nils
> Dim strSQL As String = "SELECT * FROM TESTTABLE"

Well, a SELECT * from a table without any WHERE condition will require
the entire table to be locked while you get the data. Other people
should still be able to read from the table, but updates will not
be possible.

If the table is small, this is not much of an issue, because unless
you go do some huge processing for each row. But if the table is big,
you will held the locks for quite some time. In such case I would
question the wise in getting so much data to the client.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
does not care if the data changes.

Oscar...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93ED17B437BFYazorman@.127.0.0.1...
> Nils Pommerien (fishinet@.gmx.de) writes:
> > I have written a very short program to get information from a whole
> > table out of a database. the problem is that other people couldn't
> > work on this table during the process. It seems that my program locks
> > the whole table.
> > I used the DataReader from the .NET Framework. Can you please take a
> > look at the code and give me any solution? Thank very much, Nils
> > Dim strSQL As String = "SELECT * FROM TESTTABLE"
> Well, a SELECT * from a table without any WHERE condition will require
> the entire table to be locked while you get the data. Other people
> should still be able to read from the table, but updates will not
> be possible.
> If the table is small, this is not much of an issue, because unless
> you go do some huge processing for each row. But if the table is big,
> you will held the locks for quite some time. In such case I would
> question the wise in getting so much data to the client.|||Oscar Santiesteban Jr. (oscarsantiesteban@.worldnet.att.net) writes:
> Erland,
> Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
> does not care if the data changes.

No that would not work:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nolock'.

But:

SELECT * FROM tbl WITH (NOLOCK)

would of course the remove the locking problems. I didn't mention this
possibility, because I had a feeling that he his real problem one of:

1) He's getting far more rows than he has use for.
2) He's doing something long-winding between the retrieval of each row.

So the NOLOCK would only be a band-aid on a poor design.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, a question for you, I've noticed that the NOLOCK statement
generates more logical IO than selecting from the entire table, do you
know the cause?

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Ray Higdon (rayhigdon@.higdonconsulting.com) writes:
> Erland, a question for you, I've noticed that the NOLOCK statement
> generates more logical IO than selecting from the entire table, do you
> know the cause?

Eh, could you provide a repro?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

database written in Natural and bck data format

does anybody know, is there any possible way to connect to database written in Natural ?? After evaluating everyday backups on that database all i can see is BCK data format. im trying to create datawarehouse running on sql server 2000 and gather all operational data from the systems that are working in my firm. I just managed to get data originating from Interbase and these with dbf and txt format . unfortunatelly my problem with bck data format is still open.
As you can see im a beginner with that issue so if anybody could help i would be very gratefull.http://www.attunity.com/data_integration/adabas_xml.htm

Database with dash job run as dbo

I have written a script that needs to run as dbo and is triggered by a 9002 alert and it runs fine on about 500 databases, however I can not get it to run on databases that have a dash in their name (Nor if they have a space).

I am running 8.00.534 SQL Server 2000 SP2 November 30th, 2001 Standard Edition.

To reproduce the problem:

#1 Create a database named "test-dash"
#2 Create a job "testjob" with owner sa
#3 in enterpise manager under job steps click "new"
#4 General tab:
Step Name: testing
Type: TSQL
Database: test-dash
Comand: select 'hello world'
#5 Advance tab:
Run as user: dbo
#6 click ok and click ok

It returns:
Error 170: Line 3: Incorrect syntax near '-', the job was not saved,

Apparently it is dying on line 148 of msdb.dbo.sp_verify_jobstep
EXECUTE(N'DECLARE @.ret INT
SELECT @.ret = COUNT(*)
FROM ' + @.database_name_temp + N'.dbo.sysusers
WHERE (name = N''' + @.database_user_name_temp + N''')
HAVING (COUNT(*) > 0)')

Which should generate:
SELECT COUNT(*)
FROM [test-dash].dbo.sysusers
WHERE (name = N'dbo')
HAVING (COUNT(*) > 0)

But is missing the brackets

Has anyone seen a microsoft patch to fix this?I was able to replicate your problem. I haven't seen a patch for this but I am curious why you set the Run As = dbo when the scripts is running under the sa login?|||Like Paul, I did the test and came down with the same error. However as Paul stated, if the job is owned by sa why set RunAs to dbo. When you remove that option you can save the job.|||Thank you for the replies.

The directions were a "simplified" example to try to find the problem.

I was writing a script to automate the install of the maintenance jobs on 500+ databases.

I am trying to move away from "Mixed Mode" authentication, so actually I was planning to have the jobs owned by the same user that SQL Server is running as which may not have the needed privileges. Can a job be owned by and run as SA when you are using NT Authentication?

The user that a certain application is running as owns many of the databases. I was hoping that running as dbo would simplify the install.

I know I can look at the owner of each database and set that as the owner of the job, but the job might break when the owner of a database is changed.

The script does currently work if I have the jobs all owned and run by my personal account, but I did not think that was a good solution. Occasionally servers get set to deny access to all domain administrators, so I was hoping the scripts would still continue to run when that happens.

Bob S.|||You are headed in the right direction, and yes a job can be owned by and run as SA.

If the NT account used by your SQL Server Agent is alias to SA your life will be much easier. I personnaly have worked for only one company that had a problem with this and we resolved it by switching to a server side authenticated id.|||I got the error on line no 248 (rather than 148) when doing the same in T-SQL, see below.
WHy does it say that "dbo" doesn't exist, when I know it does?

use msdb

EXEC sp_add_job @.job_name = 'testingtsql',
@.owner_login_name = 'sa'

EXEC sp_add_jobstep @.job_name = 'testingtsql',
@.step_name = 'selecthello',
@.subsystem = 'TSQL',
@.command = 'select hello-world',
@.database_name = 'test-dash',
@.database_user_name = 'dbo'

Server: Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 67
The specified @.name ('testingtsql') already exists.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '-'.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 248
The specified @.database_user_name ('dbo') does not exist.|||Okay, I bet you USD100.00 that you DON'T have a user named 'dbo', but do have a role called db_owner commenly refered to as dbo.

In your job deffinition you do not need to specify @.database_user_name when the job owner is sa, by default the job will run as dbo.|||You're probably right.

Anyway, this bug about the dash-name occurs regardless of user, I guess?