automatically import csv to MsSQL

Hi is there a way to emulate the MSSQL import wizard with a command prompt so I can shedule an automatic import form a csv file?
Thansk!Look up BCP (bcp in) and bulk insert in BOL. There are several command line parameters that can be used. Sometimes you can get by with filename and table table.

Bill|||If you're using the import wizard, just save it as a DTS package (option available at the end). You can then schedule the DTS package to run whenever you want. Look at www.sqldts.com for information on DTS.|||Thanks! I didn't notice you could save the DTS.

I'll try that, since the file and table will be fixed, I'm sure it will work!

Thansk again.|||Better look closely at the options you select.

Automatic Creation of LDF file

I was wondering how the automatic creation of a LDF (log)
file worked in MSSQL Server 2000. Is there a special
utility to use? I only have an MDF file and I try
to "Attach" it as a database. I get the following error
message:
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1813: Could not open new database 'DBName'. CREATE
DATABASE is aborted.
Device activation error. The physical file name 'C:\SQL
Data\DBName_log.ldf' may be incorrect.
--
OK
--
It's looking for the ldf file and it won't create it. How
do I get it to create a new log file? I tried creating a
new blank database with the same name and using it's ldf,
but somehow it knows they're not from the same database.
ThanksDanny,
May be there were some open transactions and its searching the ldf file for
matching.Was this database properly detached before?A prerequisite for
sp_attach* is that the database should be properly detached using
sp_detach_db.Either you can RESTORE from a valid and latest database backup
or try the workaround mentioned in :
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
The archived newsgroup thread
http://tinyurl.com/m071
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks|||Hi,
- Change your DB Status to Emergency Mode (32768).
- Stop SQL Server / Start SQL
- In query analyser, execute dbcc rebuild_log. REMEMBER: Rename old File Log
before.
- Change Your DB Status to Normal (0)
- Stop SQL Server / Start SQL
This error is because your GUID of file Log not match with definition of
your DB.
Regards,
Fabiano Maciel
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks

AutoGenerate

Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.

CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)

Good luck!
-Gabian-|||Thanks to both of you gentleman.

One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:

Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb

Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...

I usually use varchar (^_^)

auto_increment_offset

I need to setup a way to manage auto_inc keys on MSSQL without the
distributor handling it. I am using MSSQL 2005 transactional replication
with updateable subscriptions.
I have done this with MySQL using auto_increment_offset
see article:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
if you want more info about the process.
Can MSSQL do anything like this?
Thanks
You can use dbcc checkident for this. It is better to let replication handle
it through automatic identity range mangement. Here is an article on it.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sintel Silverblade" <sintel@.segamer.com> wrote in message
news:e5%235uVhqHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I need to setup a way to manage auto_inc keys on MSSQL without the
> distributor handling it. I am using MSSQL 2005 transactional replication
> with updateable subscriptions.
> I have done this with MySQL using auto_increment_offset
> see article:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> if you want more info about the process.
> Can MSSQL do anything like this?
> Thanks
>

Auto_Increment?

Hi all.

I'm trying to transfer a mysql file to mssql. Is there a field type of auto increment in mssql.
If not is there anyway to make a field auto increment in the SQL Studio express?

Thanks in advance for help with this!
Ron

In Microsoft SQL, you set the column's "Identity Specification" equal to Yes

Auto numbering field similar to

I've been using MS Access 2000 for a while and have recently switched to MS
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com

auto increatment

how can i do that in mssql ?
e.g. existing data
Column 1, Column 2
A 001
B 001
B 002
C 001
then Column 1 and Column 2 are primary key.
how can i set the column propertis for column 2 such that i can get
auto-number B003 while entering B, and auto-number C002 while entering C ?calvin wrote:

> e.g. existing data
> Column 1, Column 2
> A 001
> B 001
> B 002
> C 001
> then Column 1 and Column 2 are primary key.
> how can i set the column propertis for column 2 such that i can get
> auto-number B003 while entering B, and auto-number C002 while
> entering C ?
Create a trigger to do this, you cannot do it with standard Identity
column.
HTH,
Stijn Verrept.|||can u give a sample trigger thx ?|||Hi
I'd prefer to write a stored procedure rather using triggers
Look at my exmple does the job for you
CREATE TABLE #Test
(
row_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
col1 CHAR(1),
col2 VARCHAR(30)
)
DECLARE @.par CHAR(1),@.err INT
SET @.par ='A'
BEGIN TRAN --If an insertion is failed rollback entire transaction
INSERT INTO #Test (col1)VALUES (@.par)
SET @.err =@.@.ERROR
IF @.err >0 ROLLBACK TRAN
UPDATE #Test SET col2=(SELECT '00'+CAST(COUNT(*)AS VARCHAR(3))
FROM #Test WHERE col1=@.par)
WHERE row_id =(SELECT MAX(row_id) FROM #Test WHERE col1=@.par)
SET @.err =@.@.ERROR
IF @.err >0 ROLLBACK TRAN
COMMIT TRAN
SELECT * FROM #Test
"calvin" <admin@.newsgroup.com.hk> wrote in message
news:ua1GgFvBGHA.3980@.TK2MSFTNGP14.phx.gbl...
> can u give a sample trigger thx ?
>|||calvin wrote:

> can u give a sample trigger thx ?
CREATE TRIGGER SetID ON [dbo].[Table]
FOR INSERT
AS
Update Table set Column2 = (select IsNull(max(TB2.Column2), 0) + 1 from
Table TB2 where INS.Column1 = TB2.Column1)
from Table TB inner join inserted INS on INS.Column1 = TB.Column1
By using a trigger you are sure that this is handled whatever way data
is inserted into your table.
HTH,
Stijn Verrept.|||>> then Column 1 and Column 2 are primary key.
If both columns form the key, you'd want to make sure there is no dependency
between one column to another. So the question becomes, why would you want
to do something like this?
Anith|||Here's what I did:
1. Primary keys do not allow null values in any of it's columns. Therefore,
I had to add another column, assign identity value and make it the primary
key.
2. I had to allow null values into column 2 as you cannot insert a row with
null value otherwise. (SQL Server checked that first before running the
insert trigger.)
3. I could not get the trigger posted by Stijn to work. (It could be
something I was doing wrong.) Plus, it looked to me that his trigger would
update all rows with the new value.
4. It looked like you wanted character-based sequence numbes (i.e. 001, 002,
003, etc.) so I added code to zero pad it to 6 characters (i.e. 000001,
000002, etc.). If you don't want that, simply remove the right padding and
converting to varchar.
Here's my version of the trigger that is tested in SQL Server 2000.
CREATE TRIGGER SetID ON dbo.[Table]
FOR INSERT
AS
update [Table]
set Column2 = (select right('000000' + convert(varchar,
IsNull(max(TB2.Column2), 0) + 1), 6)
from [Table] TB2 where TB2.Column1 = INS.Column1)
from [inserted] INS
where [Table].Column1 = INS.Column1
and [Table].Column2 is null
Hope that helps,
Joe
"calvin" wrote:

> how can i do that in mssql ?
> e.g. existing data
> Column 1, Column 2
> A 001
> B 001
> B 002
> C 001
> then Column 1 and Column 2 are primary key.
> how can i set the column propertis for column 2 such that i can get
> auto-number B003 while entering B, and auto-number C002 while entering C ?
>
>