« Previous Next »

Thread: Auto-increment primary key in SQL tables

Last post 11-01-2007 1:08 PM by gian.laurenzi. 2 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (3 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 10-22-2007, 7:07 AM

    Auto-increment primary key in SQL tables

    Reply Contact

     Hello, I'm trying to create and populate some SQL Tables from log file using LogParser COM API.

     I use SQL Output format with -createTable option enabled and i need an auto-generated identity column (an auto-increment primary key!)

    I tried

    SELECT 1, SendTime, Facility, Severity, MessageText ;
            INTO [" + IPAdd.ToString() + "] "
            FROM '" logfile + "' ";
            WHERE SourceAddress LIKE ' " + IPAdd.ToString() + " '"

     but I obtained

    Error executing query: Error creating table
    SQL State: 42000
    Native Error: 102
    Error Message: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 1.
    Most likely you're attempting to create a table with invalid column names, such as names containing the '-' character, or names that are illegal for the database you are connecting to (e.g. 'timestamp'). Rename the columns in the SELECT clause using the AS statement. [Unknown Error]

    OK! so I change my query in

    SELECT 1 AS LogID, SendTime, Facility, Severity, MessageText ;
            INTO [" + IPAdd.ToString() + "] "
            FROM '" logfile + "' ";
            WHERE SourceAddress LIKE ' " + IPAdd.ToString() + " '"


     but I always obtain 1 in the first column, when i needed an identity column!

    As you can see in my other post, i tried to create tables at run-time with SQLCommands embedded in my C# code, but it generates another error!

    Please help me!

    Thanks in advance,

    Giancarlo Laurenzi.
     



     
    Tags:

  • 10-26-2007, 7:41 AM In reply to

    Re: Auto-increment primary key in SQL tables

    Reply Contact

    Hi again,

    I just see this thread. And what is the status now?

    http://forums.iis.net/p/1146695/1858394.aspx#1858394

    Zhao Ji Ma
    Sincerely,
    Microsoft Online Community Support

    “Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. ”

  • 11-01-2007, 1:08 PM In reply to

    Re: Auto-increment primary key in SQL tables

    Reply Contact

    Thank you, but this is another problem related to create a new SQL table!

    Please somebody help me, how can i create a SQL table with an auto-increment (IDENTITY(1,1)) primary key, a datetime field and others text fields?

    I have some logs (from router/firewall) with this format:

     SourceAddress ; SendTime ; Facility ; Severity ; MessageText

    192.168.1.1; 01/11/2007 18.00.00; local7; Alert; Some text...

    I want to create a SQL table (I'm using SQL Server 2005 Express Edition) using this query:

    SELECT 1, TO_TIMESTAMP (SendTime, 'dd/MM/YYYY?hh.mm.ss' ) AS Send_Time, Facility, Severity, MessageText
    INTO [ IPADDRESS ]
    FROM 'LogFile_*.csv'
    WHERE SourceAddress = 'IPADDRESS' (note that IPADDRESS is a variable)

    quite easy, isn't?

    But I encountered several problems:

    I am unable to create an auto-increment primary key.

    I am unable to create a datetime field with the function TO_TIMESTAMP (note that i use italian date format), without the TO_TIMESTAMP it creates a String field. 

    Please help me!!

    Thanks in advance! 
    Tags:
Page 1 of 1 (3 items)
Microsoft Communities