0

I have a database on my student server, I need to go to my university if I want to access the database properties using the SQL server DBMS, it is a pain. I want to know if I can change the identity specification by using SQL on a specific table? I cannot insert to a table as this is not selected to yes, I want to be able to make these changes from home can I do this?

I was researching how to access tables and change some properties, and came across something like this:

Set IDENTITY_INSERT TableName ON

I want to be able to do this using a php script and some SQL, like this:

$query = "IDENTITY_INSERT customers ON ";

        $result = sqlsrv_query( $conn, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

        if( $result === false)
        {
            echo "Error in query preparation/execution.\n";
            die( print_r( sqlsrv_errors(), true));
        }

        else
        {
            echo'worked';
        }

will this work?

Here is my rough attempt at doing this:

<?php
$serverName = "servername";
    $connectionInfo = array("UID" => "username", "PWD" => "password",      "Database"=>"database");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    if( $conn === false ) // note the format of ‘equals’
    {
         echo "Could not connect.\n";
         die( print_r( sqlsrv_errors(), true));
    }



        $query = "SET IDENTITY_INSERT customers ON ";

        $result = sqlsrv_query( $conn, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

        if( $result === false)
        {
            echo "Error in query preparation/execution.\n";
            die( print_r( sqlsrv_errors(), true));
        }

        else
        {
            echo'worked';
        }

?>

Thank you.

Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
deucalion0
  • 2,422
  • 9
  • 55
  • 99
  • 1
    `SET IDENTITY_INSERT` does not change any table properties. It just means your session can insert an explicit value into an `identity` column. Are you trying to alter a column without `identity` to have `identity`? If so see [how to set auto increment after creating a table without any data loss?](http://stackoverflow.com/q/6084572/73226) – Martin Smith Apr 29 '12 at 11:57

2 Answers2

2

IDENTITY_INSERT is the correct syntax, although you appear to have missed off the SET keyword in your usage. See this line:

$query = "IDENTITY_INSERT customers ON ";

Change to:

 $query = "SET IDENTITY_INSERT customers ON ";
Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
  • Thanks for your answer! I tried what you said now I am getting this error Error in query preparation/execution. Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 8106 [code] => 8106 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'customers' does not have the identity property. Cannot perform SET operation. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'customers' does not have the identity property. Cannot perform SET operation. ) ) I have a customerID column which I need to set to change the identity to yes – deucalion0 Apr 29 '12 at 11:56
  • Ah okay, so you're actually wanting to make the column an identity column? I thought you just wanted to insert into a column that is an Identity. Let me know if that's what you want and I'll update my answer :) – Mathew Thompson Apr 29 '12 at 11:59
  • I have the database locally on my machine, the customers table is set to identity yes, so when I insert a new customer the customerID column auto increments. I think When I uploaded my tables onto my student web server using SQL server on a university machine, the customerID column maybe lost its identity specification property? I don't know, but when I try and insert a new customer into the database it is failing, that's the only thing I can think of is that the customerID column is not receiving a value, and it cannot be null. I hope that explains it! :) – deucalion0 Apr 29 '12 at 12:13
  • Hmmm, you could query whether or not customerId still is an identity by going into SQL Management Studio, right clicking on your Customer Table, then selecting Design, then click on the CustomerId field and in the properties window scroll down to Identity Specification and click on the plus, that will tell you if it still has it. Can you post the actual query in the `$query` variable. As long as you've got the customerID specified in the column insert list and you're passing it an actual value, it should be fine :) – Mathew Thompson Apr 29 '12 at 12:18
  • That is my problem, I can't see the on-line database as it can only be accessed by a special computer in a lab in my university, I need to keep going there every time there is a problem with this crappy database they gave to me. I want to instead be able to change this from home. I do have the same version on my own PC which is set to yes, but when I attached it to SQL server in the uni lab I think the identity specification may have reset? I'll add the whole query to my question as an edit. Thank you! – deucalion0 Apr 29 '12 at 12:25
1

I solved the problem, it was of course not set to identity specification yes. First I dropped the table column customerID, and the recreated it using this query:

$query = "Alter table customers add customerID INT IDENTITY(1,1) not null";

This created the column again but setting it to identity yes.

I couldn't have done it without your help.

Thank you.

deucalion0
  • 2,422
  • 9
  • 55
  • 99