|
Use the FTP task to copy the file to an accessable directory, then you can create a data flow task which uses a flat file source, processes the file and then puts the data in the appropriate data flow destination.
Your package designer should resemble this:
<br />
FTP Task --> Data Flow Task<br />
Open the Data flow task (double-click) and it should resemble this:
<br />
Flat File Source --> [intermediate transformations you may need (if any)] --> Sql Server Destination<br />
Just drag the items mentioned to the designer surface, open each item and set the properties needed.
|
|
|
|
|
Hi,
Can I total a detail column at group level on reprting services. I can see how I would put a footer in and total from the SQL but I need things totalling down the page at a smaller level.
e.g.
Sales
Product Type
House Product Uk Sales India Sales
DVD 6 10
Oven 7 12
Total 13 22
Garden Trees 10 15
Fences 34 10
Total 44 25
Any help welcome !
Thanks in advance
|
|
|
|
|
Hi
i am creating dataTable at runtime and binding rows at runtime . how to insert the all the rows in dataTable to database
|
|
|
|
|
Create a Command object which defines the INSERT command then create a DataAdapter object and assign your Command object to the adapter's Insert command. Make sure when you define the data table object to map the datatable's column names to the parameter objects in the insert command you created.
|
|
|
|
|
the code in my application is:
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "UPDATE tblUsers SET Password=? WHERE UserName=?";
com.Parameters.Add("UserName", OleDbType.WChar);
com.Parameters.Add("Password", OleDbType.WChar);
com.Parameters["UserName"].Value = this.UserName;
com.Parameters["Password"].Value = this.txtPassword1.Text;
-------------------------------------------------------------------------------
When I run the application , it gives the syntax error in update query.
I am unable to find where I am going wrong
Kindly help me!
Sadaf
|
|
|
|
|
Why not you try this:
com.CommandText = "UPDATE tblUsers SET Password='" + this.UserName.Text + "' WHERE UserName='" + this.txtPassword1.Text + "'"
Do good and have good.
|
|
|
|
|
Silent Eagle wrote: com.CommandText = "UPDATE tblUsers SET Password='" + this.UserName.Text + "' WHERE UserName='" + this.txtPassword1.Text + "'"
Shouldn't that be:
com.CommandText = "UPDATE tblUsers SET Password='" + this.txtPassword1.Text + "' WHERE UserName='" + this.UserName.Text + "'"
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks, Yes you are right. It is written mistakenly but i think it conveys the concept.
Do good and have good.
|
|
|
|
|
Thanks all for ur kind help but its still giving the same error!
Sadaf
|
|
|
|
|
There are two major flaws in your code:
1. Generating SQL statements on the fly is generally considered bad practice. It leaves you exposed to SQL injection attacks. Instead, consider writing a stored procedure to perform your update and call it from your .NET code.
2. You appear to be storing your user's passwords in clear text. This is very insecure. You should consider encrypting your password using a 1-way salted hash algorithm.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
try this code
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "UPDATE tblUsers SET Password=? WHERE UserName=?";
com.Parameters.Add("UserName", OleDbType.WChar);
com.Parameters.Add("Password", OleDbType.WChar);
com.Parameters["UserName"].Value = this.UserName.Text;//Error was here
com.Parameters["Password"].Value = this.txtPassword1.Text;
|
|
|
|
|
try this
com.CommandText = "UPDATE tblUsers SET [Password]='" + this.txtPassword1.Text + "' WHERE [UserName]='" + this.UserName.Text + "'"
I Love SQL
|
|
|
|
|
No. Don't do this. Haven't you heard of SQL Injection Attacks? Have a read through the many wonderful articles before you find out why this is wrong, oh so wrong.
|
|
|
|
|
Sure I have attention about SQL injection attacks...
I Love SQL
|
|
|
|
|
If you are aware about SQL Injection Attacks, then why do you recommend something that is wide open to such an attack? It's your responsibility when posting an answer to give good advice and not to post something that is such blatant bad practice.
|
|
|
|
|
but it's not my responsibility to teach others about SQL injection all of programmers must know about SQL injections...
Why you not gonna explain him about SQL injections?
I Love SQL
|
|
|
|
|
Hi,
I have a database called MyDatabase and I am looking to create another datebase called MyDatabase_Dev with the exact same tables and data in it, I want no primary keys that are lost and no auto numbering that it not set.
I can't remember how to do this. I tried Import data on the MyDatabase_Dev, but I loose primary keys and auto numbering. I am using SQL Server 2005.
Thanks
Brendan
|
|
|
|
|
Backup your database, then restore it under a different name.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I tried that but it gave me an error. But then I tried it again and saw that I did not change the destination file name to my new name, and then it worked.
Thanks
|
|
|
|
|
Just another quick question about the database restore. After the new database is restored, I right clicked on the database and clicked on properties. I then clicked on the files tab, and to the right there is a column with a name called "Logical Name". And the logical name is still MyDatabase, the original database that I did the backup from. Wasn't this supposed to have changed when I changed the file names when I did the restore? I don't want it to have an relationship the original database, I want it to be on it's own. I hope you understand.
|
|
|
|
|
You can just change this on the file tab if you want to. The physical names of the database files should be different from the original database and follow the name of your new database. The database you have restored from the backup doesn't have any relationship with your original database, so you don't need to worry about this.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
What about copy database wizard??
In your SQL management studio, right click on the database, select Tasks -> Copy database.
Regards,
Mehroz
|
|
|
|
|
i m using sqlserver 2000
i have a table that has the primary key set as identity
i want to make that off insert one row and value that column that is set as identity and then again make that on and i want to do that through sql script.
i try this code
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
And have got this error always:
Incorrect syntax near the keyword 'IDENTITY'.
Why I got this?
|
|
|
|
|
Not quite clear on what it is you are trying to do, but it isn't allowed. I've included the ALTER COLUMN description from BOL below.
If you need to perform this kind of change you'll need to create a table which matches your customers table (MyCustomers_copy) AND has the new definition for the identity column. Then you'll need to copy all the data from the customers table to the copy table. Then drop the original customers table and rename (sp_rename stored procedure) the copy table to the name of the original customers table.
If all you're trying to do is insert an explicit identity value instead of letting SQL Server do it for you, then you just need to use IDENTITY_INSERT
<br />
SET IDENTITY_INSERT MyCustomers ON<br />
<br />
INSERT INTO MyCustomers(CustID, CompanyName) VALUES(202, 'A. Datum Corporation')<br />
<br />
SET IDENTITY_INSERT MyCustomers OFF<br />
<br />
That will allow you to insert any value you like for CustID. However, if the value already exists you'll get a primary key violation error. Usually this is not used from application code, only for coping data from one database to another.
ALTER TABLE Documentation from BOL:
ALTER COLUMN
Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).
The altered column cannot be any one of the following:
A column with a timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.
The data type of text, ntext and image columns can be changed only in the following ways:
text to varchar(max), nvarchar(max), or xml
ntext to varchar(max), nvarchar(max), or xml
image to varbinary(max)
Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.
The data type of a column of a partitioned table cannot be changed.
|
|
|
|
|
I've searched everywhere and can't seem to find a solution for this one.
I have a C# program that uses an Access DB. The program works fine on all of the Win XP and Vista machines that I've tried, but on a Windows 2000 Professional machine, I get an error when it tries to open the DB connection created with an OleDBConnection object. I get the same error on WinXP if I change the program properties to run in Windows 2000 compatibility mode.
The error is reported as "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
Here's the code snippet that is causing the problem:
<br />
try<br />
{<br />
db = new OleDbConnection(connStr);<br />
db.Open();<br />
}<br />
catch (OleDbException e)<br />
{<br />
MessageBox.Show("DB Error: " + e.Errors[0].Message);<br />
}<br />
The error is generated trying to execute the db.Open() statement.
The database file looks fine using Access 97, 2003 and 2007 on all machines. Apparently it has something to do with the way the connection is created, but I don't understand why it works on a normal XP and Vista machine, but not when run under the Win 2000 compatibility mode. What is different between, say normal XP and running under Win 2000 compatibility on XP?
Any ideas will be appreciated.
The PetroNerd
modified on Saturday, March 15, 2008 11:02 PM
|
|
|
|