Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Turning IDENTITY_INSERT ON


Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT *
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
IDENTITY_INSERT Is Set To OFF
I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.
I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.
Any help would be most welcome. Many thanks in advance

View Replies !   View Related
Identity_insert?
I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
I am using:
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
but I get this error:
Error -2147217900


An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.

set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'

As I have turned ID_insert ON it must be the column list?...
not sure what to do next.

View Replies !   View Related
Set Identity_insert
hi

i need to set the identity_insert on and off to a remote table in order to insert rows into it

can anyone help me please

View Replies !   View Related
SET IDENTITY_INSERT
The SQL Server 7 documentation says "SET IDENTITY_INSERT permissions default to all users".

I run this command:

SET IDENTITY_INSERT database.dbo.tablename ON

I am getting an error

Server: Msg 8104, Level 16, State 1, Line 1
The current user is not the database or object owner of table

1) Is this an error in the documentation?

2) If so, is there a way I can grant rights to a non-dbo account to perform this?

Thanks

View Replies !   View Related
Set Identity_insert
Hi
--SQL SERVER 7.0
I have a table P1 which has one identity column and 9 other columns.
i need to put all the data from this table into another table with same structure as P1 called P2.

even though i used Set identity_insert P2 on
I get an error saying column list must be specified
Basically i was trying to do this

SET IDENTITY_INSERT P2 ON
go
insert into P2
select * from P1

could somebody throw light onto how to specify the column list,but at the same time inserting the entire data from P1 in one shot(using select * from...)?

Any help ragarding this is highly appreciated
Thanx
SK

View Replies !   View Related
IDENTITY_INSERT
I have 2 databases, HS and BoardAnalyst and the table TCompanies exist in both. I want to delete all of the records from HS.TCompanies and repopulate it with data from BoardAnalyst.TCompanies. I'm getting the error message "Table 'HS.dbo.CompID' does not exist or cannot be opened for SET operation." CompID is the PK for the table. I'm not sure what the problem is. Thanks



DELETE FROM HS.dbo.TCompanies
GO

SET IDENTITY_INSERT HS.dbo.TCompanies ON
GO

INSERT INTO HS.dbo.TCompanies
(
HS.dbo.AnnualMtg,
HS.dbo.BdMtgs,
HS.dbo.CompanyName,
HS.dbo.CompID,
HS.dbo.DirectorsTotal,
HS.dbo.Exchange,
HS.dbo.IndexFortune,
HS.dbo.Industry,
HS.dbo.LinkComp,
HS.dbo.MailAddress,
HS.dbo.MailCity,
HS.dbo.MailCountry,
HS.dbo.MailFax,
HS.dbo.MailingAddress1a,
HS.dbo.MailingAddress2,
HS.dbo.MailPhone,
HS.dbo.MailPostCode,
HS.dbo.MailState,
HS.dbo.MarketCap,
HS.dbo.ProxyDate,
HS.dbo.Revenues,
HS.dbo.StateHQ,
HS.dbo.Ticker,
HS.dbo.Updated
)

SELECT
BoardAnalyst.dbo.TCompanies.AnnualMtg,
BoardAnalyst.dbo.TCompanies.BdMtgs,
BoardAnalyst.dbo.TCompanies.CompanyName,
BoardAnalyst.dbo.TCompanies.CompID,
BoardAnalyst.dbo.TCompanies.DirectorsTotal,
BoardAnalyst.dbo.TCompanies.Exchange,
BoardAnalyst.dbo.TCompanies.IndexFortune,
BoardAnalyst.dbo.TCompanies.Industry,
BoardAnalyst.dbo.TCompanies.LinkComp,
BoardAnalyst.dbo.TCompanies.MailAddress,
BoardAnalyst.dbo.TCompanies.MailCity,
BoardAnalyst.dbo.TCompanies.MailCountry,
BoardAnalyst.dbo.TCompanies.MailFax,
BoardAnalyst.dbo.TCompanies.MailingAddress1a,
BoardAnalyst.dbo.TCompanies.MailingAddress2,
BoardAnalyst.dbo.TCompanies.MailPhone,
BoardAnalyst.dbo.TCompanies.MailPostCode,
BoardAnalyst.dbo.TCompanies.MailState,
BoardAnalyst.dbo.TCompanies.MarketCap,
BoardAnalyst.dbo.TCompanies.ProxyDate,
BoardAnalyst.dbo.TCompanies.Revenues,
BoardAnalyst.dbo.TCompanies.StateHQ,
BoardAnalyst.dbo.TCompanies.Ticker,
BoardAnalyst.dbo.TCompanies.Updated
FROM
BoardAnalyst.dbo.TCompanies
GO


SET IDENTITY_INSERT HS.dbo.CompID OFF
GO

View Replies !   View Related
Identity_insert
my SP worked on friday, then today i ran it, but it's not working and throws these errors. i googled the error, but i still can't fix it. can you help me?


Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 31
An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl2' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 57
An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl3' can only be specified when a column list is used and IDENTITY_INSERT is ON.

View Replies !   View Related
IDENTITY_INSERT
 

I have a table where IDENTITY_INSERT is set to OFF.  I also have one stored procedure that I need to run that requires IDENTITY_INSERT to be set to ON.  The only problem is the users who run the stored procedure are not owners of the table and are getting the error "The current user is not the database or object owner of table ...  Cannot perform SET IDENTITY_INSERT"  Is there a way to use the IDENTITY_INSERT command without being the owner of the object?
 

View Replies !   View Related
SET IDENTITY_INSERT
We have created an "AdminUser" with the bare minimum rights for the activites it will need to do.  One thing the "AdminUser" will need to do is a "SET IDENTITY_INSERT ON/OFF". 

I understand that for this user to be able to do this they will need ddl_admin rights.  But that gives them all kinds of permission that I don't want this user to have. 

Is there a specific permission I can give the user so they can do the "IDENTITY_INSERT"?

I tried "GRANT create table to AdminUser" but that didn't work.  I tried "GRANT alter any database DDL trigger to AdminUser" but that didn't work either.  I'm looking for something more specific than "ddl_admin".

Thanks.

Trish

View Replies !   View Related
SET IDENTITY_INSERT For SQL CE And SQL ME
 

Hi,
 
I have Product table in SQL Server 2005 with Primary key ProductID. This is column with Identity set so the values for this column is auto-generated. I also have Orders table has a key that is foreign key to table Products. Now my dev environment has SQL ME whereas the deployment will be done on SQL CE.
 
Now I have a requirement where we want to synchronize the master tables from SQL Server with the device. For this we want to have the same identity values on the device. When I try to issue SET IDENTITY_INSERT Products ON on SQL ME it does not work. Is there any way I can set the identity ON on the SQL Mobile Edition?
 
Also another question I have is: Is SET IDENTITY_INSERT supported on SQL Compact Edition?
 
Regards,
vnj

View Replies !   View Related
Turning A Null Into A Value
I am trying to return a 0 when there is a NULL as a result, I thought that Sum(ISNULL(Payment,0) would do the trick, I was wrong...any ideas:
 MonthlyCredit =
(Select
Sum(ISNULL(Payment,0)) from transaction as t
Where AccountNumber = @AccountNumber
and
Month(t.PayDate) = @Month
)
 

View Replies !   View Related
Turning Triggers On And Off.
Hi is it possible to run a certain SQL statement agaisnt SQL Server and ask it not to fire any triggers? Or is would it be better to disable the trigger and then reable it after ward? If so how? Thanks Ed

View Replies !   View Related
Turning Triggers Off
Is it possible to turn a trigger off in SQL Server as you would be able to do in oracle? If so, how?

Thanks
David

View Replies !   View Related
Turning Off The Log File
Is There any way to stop a database from writing to the log file when you alter the design
of a table.

View Replies !   View Related
Turning Age 65 Or 75 In May 2008
--Environment: SQL Server 2000
I am doing following query for who is reaching of Age 65/75 in May 2008 but Member_DOB's or Spouse_DOB's showing different month. Month should show '05' because I want to see results who is reaching of age 65/75 in May 2008 only.
Please help in this regard.
--Query:
Select m.empid, m.dob "Member_DOB",
  round(datediff(dd,m.dob,'05/30/2008')/365.25,1) Member_Age,
  d.dob "Spouse_DOB",
  round(datediff(dd,d.dob,'05/30/2008')/365.25,1) Spouse_Age
from  member m
left outer join (SELECT * FROM depend where depcode = 'S' and activestatus = 1)d
on  m.empid = d.empid
where (datepart(yy,m.dob) in (1933,1943) or
  datepart(yy,d.dob) in (1933,1943))
and   round(datediff(dd,m.dob,'05/30/2008')/365.25,1) in (65,75)
or    round(datediff(dd,d.dob,'05/30/2008')/365.25,1) in (65,75)
 
--Results:
Empid        Member_DOB                Member_Age   Spouse_DOB                  Spouse_Age
000000033 1931-12-07 00:00:00.000 76.500000       1933-06-16 00:00:00.000 75.000000
000000085 1933-05-23 00:00:00.000 75.000000       1938-03-10 00:00:00.000 70.200000
000000695 1933-06-10 00:00:00.000 75.000000       1934-07-08 00:00:00.000 73.900000
000000792 1931-01-15 00:00:00.000 77.400000       1933-06-05 00:00:00.000 75.000000
000002406 1933-05-27 00:00:00.000 75.000000        NULL                            NULL
000004149 1933-05-20 00:00:00.000 75.000000        NULL                            NULL


Desired results:
 
Member_DOB and Spouse_DOB's should show '05' i.e.  1931-05-07, 1931-05-15
 

View Replies !   View Related
Turning Off Validation
I need to turn off validation and I've seen some threads saying this is not possible but my situation has a twist.

A customer needs the package to connect to different modem dialup connections to connect to different servers (they use dialup for security reasons). We have written two VB script tasks at the beginning and end of a loop, with data flows in between. Before the loop the dialup connection info is read into a recordset along with Data Source connection information. The first script uses this information to dialup and the last script hangs up the connection. The problem is the package tries to validate the data connections and the package has not dialed up yet, so it fails.

We managed to confirm it works in a test environment by putting a break in the first script, manually VPNing into the test network (to allow validation of the data flow to work), and then manually disconnecting from VPN during the break. The script dials in and pumps the data. But this won't be an option in production.

So if anyone has figured out a way to turn off validation, great. Otherwise, any ideas to make this work? I was thinking about setting up a dummy connection that would be connected outside the package before running just for validation (and then the script would disconnect to begin, but I would prefer to handle all of this within SSIS.

Any help? While I see the point of validation it's a bummer that MSFT didn't put this in the hands of the user.

Thanks, Kayda

View Replies !   View Related
Turning RDA Tracking Off
I have a device application that simply needs to upload data to a server.  The preferred DB server is Oracle but I've made it work using RDA and SQL Server.  The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method.  The data arrives just fine, the first time.  With every subsequent upload all of the previous data is deleted fromt he server.  Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server. 

My question is:  Is it possible to prevent RDA from deleting data on SQL Server?  I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.

 

 

 

 

View Replies !   View Related
When IDENTITY_INSERT Is Set To OFF. -- LINQ
I'm new to ASP/VS/Linq and I'm having a small problem.
 I have one table setup in SQL Server Express 2005 through Visual Studio 2008.  The table name is "Users" and has three columns (accountID, userName, email).  AccountID is the primary key and set to auto incriment.  I've added a couple of records by hand and it works.
I have a single form with a button, a label, and two text boxes.  The button code is below.  After entering some fake data that does not already exist in the database and clicking the button I get this.
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.
I understand that it is trying to insert something into the accountID field but I don't understand why since I'm only providing a username and e-mail address to insert.
Your help is greatly appreciated.protected void Button1_Click(object sender, EventArgs e)
{
MyDatabaseDataContext db = new MyDatabaseDataContext();
var query = from u in db.Users
where u.email == txtEmail.Text
select u;

var count = query.Count();
if (count == 0)
{
//Create a new user object.
User newUser = new User();

newUser.username = txtUsername.Text;
newUser.email = txtEmail.Text;

//Add the user to the User table.
db.Users.InsertOnSubmit(newUser);
db.SubmitChanges();
}
else
{
Label1.Text = txtEmail.Text + " already exists in the database.";

 

View Replies !   View Related
Error : IDENTITY_INSERT Is Set To OFF !
Hi,
What is this error ? 
Error : " Cannot insert explicit value for identity column in table 'Comments' when IDENTITY_INSERT is set to OFF. "
Please help me .

View Replies !   View Related
IDENTITY_INSERT Problem
Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO2.8) in that I cannot insert a specific value to an identity field. (linesbelow with >>> are code lines. I am using Python, but the syntax should beabout the same as VBScript)First, I create an ADO Connection and create my table.[color=blue][color=green][color=darkred]>>> c = win32com.client.Dispatch('ADODB.Connection')[/color][/color][/color][color=blue][color=green][color=darkred]>>> dsn = 'DRIVER=SQL[/color][/color][/color]Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft DataAccess Components;SERVER=SERVERINSTANCE;"'[color=blue][color=green][color=darkred]>>> c.Open(dsn)[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql = 'CREATE TABLE Table_Name ('[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'Field_2 nchar(50) NOT NULL, '[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'[/color][/color][/color][color=blue][color=green][color=darkred]>>> c.Execute(sql)[/color][/color][/color]This works fine. Then, I attempt to allow insertion into the ID_Field.[color=blue][color=green][color=darkred]>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")[/color][/color][/color]This seems to work in that it does not throw an error and gives a returnof -1. Then I open a Recordset[color=blue][color=green][color=darkred]>>> r = win32com.client.Dispatch('ADODB.Recordset')[/color][/color][/color][color=blue][color=green][color=darkred]>>> r.Open('Table_Name', c, 2, 4)[/color][/color][/color]Last, I am attempt to add a record to the recordset with an explicit ID,[color=blue][color=green][color=darkred]>>> r.AddNew()[/color][/color][/color][color=blue][color=green][color=darkred]>>> r.Fields.Item('ID_Field').Value = 45[/color][/color][/color]but this fails with the error of"Multiple-step OLE DB operation generated errors. Check each OLE DBstatus value, if available. No work was done."Even worse, if I now try to set the identity field to allow inserts again,Updating() causes an error that I must use an explicit value for ID_Field,but if I try to give it one, it fails with the above error. I have todestroy the recordset object at this point to get any further.I am told that SET IDENTITY_INSERT only remains active for one statement andthus must be combined with the insert, but I do not know how to do this.There is a similar sounding bug w/ SQL 7(http://support.microsoft.com/defaul...b;EN-US;253157), but thereis no indication that it affects newer versions of the DB. Does anyone haveany suggestions or ideas?Thanks for any help,-d

View Replies !   View Related
SET IDENTITY_INSERT ON/OFF Error
I would like to insert an indentity column explicitly following error occurs:

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TableMarket' when IDENTITY_INSERT is set to OFF.

And when I am trying to SET IDENTITY_INSERT ON, it gives following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.

Anyone can help me please?

P.S. I am working on SQL Server 2000

Thanks,
Riaz

View Replies !   View Related
Setting The IDENTITY_INSERT
is there any way i can set IDENTITY_INSERT = ON
permenately for the whole DB

View Replies !   View Related
Identity_Insert Error
Hi all. This is my first post to these forums and I am a newbie. I am using SQL server 2005 and I have written a procedure to insert the data in new row. My procedure runs and executes well, but does not insert a record in my DB table. Instead I get a message in the debugger window that I need to turn ON the Identity Insert property for the tables. But after searching for a while in the IDE, I could not find any such property. So I need to know what went wrong here. Also, I am using ASP.NET to connect to the database and I found out an article on web explaining the way to set the property to ON through my ASP.NET application. But, I would also like to know how to set that property to ON through SQL management studio. Also, my primary key is not null; I am also passing the primary key value and it is unique. Please let me know a remedy for this.



Thank you.

View Replies !   View Related
Problem With SET IDENTITY_INSERT
Hi,
I am facing problem while inserting data in a table having identity seed.

DECLARE @varSQL VARCHAR(8000)
DECLARE @VARe VARCHAR(400)

SET @VARe = 'Tmp_r429_sig'
SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' ON'
EXEC (@varSQL)

INSERT INTO Tmp_r429_sig ([NAME],ID) VALUES('test',1)

SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' OFF'
EXEC (@varSQL)

The table name need to be a variable.

I ma getting following error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Tmp_r429_sig' when IDENTITY_INSERT is set to OFF.

Can someone throw some light on how to execute SET IDENTITY_INSERT as a dynamic query.

View Replies !   View Related
Problems With IDENTITY_INSERT
Hey Everyone,

I'm trying to copy the data from one table to another while preserving the identity keys, but I can't seem to get past this error message. Here's the SQL:

SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview ON

INSERT INTO dbo.Cms_MenuItems_Preview
SELECT Id, [Name], Url, Parent, IsActive, SortIndex
FROM dbo.Cms_MenuItems

SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview OFF


and the error message is:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Cms_MenuItems_Preview' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Any suggestions?

Thanks in advance!

View Replies !   View Related
SET IDENTITY_INSERT Tablename ON
I use IDENTITY(1,1)
and the help says:
SET IDENTITY_INSERT tablename ON

But if the table is an variable(virtual) table how to make it work?
 

Like this:
DECLARE @TEMPtable table ( ID int Identity(1,1), invar nvarchar(255)

 
It does not work with
SET IDENTITY_INSERT @TEMPtable ON
 
I get this

'Incorrect syntax near '@TEMPtable '
 
 

View Replies !   View Related
IDENTITY_INSERT Issue
INSERT        INTO dbo.my_table (table_key)
VALUES        (007)

-- Msg 544, Level 16, State 1, Line 1
-- Cannot insert explicit value for identity column in table 'my_table' when IDENTITY_INSERT is set to OFF.



SET IDENTITY_INSERT dbo.my_table ON
INSERT        INTO dbo.my_table(table_key)
VALUES        (007)
SET IDENTITY_INSERT dbo.my_table OFF

-- Msg 1088, Level 16, State 11, Line 2
-- Cannot find the object "dbo.my_table" because it does not exist or you do not have permissions.

I know for certain that I have read/write permissions on this server.  Is this somehow restricted to only the dba or something?

I appreciate all of this community's help with my basically non-stop flow of questions.  You're a huge chunk of help, and it's deeply appreciated!!

Jim Work

View Replies !   View Related
IDENTITY_INSERT On/Off In Sql Compact
Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @@IDENTITY_INSERT ]

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75



 

View Replies !   View Related
Unable To Set Identity_Insert Off?
Hi i am unable to set Identity_insert off in sql server compact? Can you please tell me if sqlce supports or not?

If it does not support what is the work around that can be done?

Please tell the workaround in detail i mean altering table for identity key e.g. Because i have seen some posts that talk about alter table but i am unable to understand how that can done?

Thanks

View Replies !   View Related
SET IDENTITY_INSERT Tablename ON
I know there has already been a thread on this, but I want to push some about it.

In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".

That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.

In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.

The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.

Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)

Thanks.

View Replies !   View Related
Turning A View Into A Table
I am in a scenario where my tables are refreshed every morning by a batch update.  I have built a few views off of one table.  To increase speed I would like to take all the rows from one of the view s and insert them into their own table.  I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate 

View Replies !   View Related
Turning Rows Into Columns
Say I have a table of data containing something likeRegion | County | Year | Month | Valuefor some sort of value (int). I want to re-arrange this data so that itcomes out like this:Region | County | Year | J | F | M | A | M | J | J | A | S | O | N | Dwhere the letters are obviously the months in order. How would I goabout this/what's the best way. I attempted to use 12 INNER JOINS onthe table itself, sadly that failed miserably. Also, this doesn't seemvery efficient?Before you ask I got rid of my original code (gave up!)

View Replies !   View Related
Turning SELECT Into A DELETE
I've constructed the SELECT statement to show the rows I want - and it shows 189 rows. Now I want to delete these rows. Here is the SELECT statement:

SELECT tblinqty.* FROM tblinqty LEFT JOIN tblmporder ON tblinqty.linkidsub = tblmporder.orderno WHERE tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'

If I change the statement to "select * from tblinqty where exists ()", putting the above command inside the (), it returns over 12,000 rows! My intention is to change the SELECT into a DELETE by replacing the "select *" with a "DELETE" - but if I do that it will delete the wrong rows. How is the easiest way to turn the above successful SELECT statement, which yields 189 rows, into a DELETE statement which also deletes the same 189 rows?

I've tried changing the statement to a WHERE, thinking it would be easier to change to a DELETE, but the following yields 0 rows:

SELECT tblinqty.* FROM tblinqty WHERE tblinqty.linkidsub = tblmporder.orderno AND tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'

View Replies !   View Related
Turning DB To NON-ARCHIVE Mode
Hi all,

I am pretty new MS SQL server,
I am actaully working as an SAP BASIS ADMIN and 1 of our client is on MS SQL 2005 with SP1 as the DB and Win 2003 as the server.

I just wanted to know

1) How can we login to the dbase from the command prompt?

2) How to alter the database to NON-ARCHIVE mode and back to ARCHIVE MODE ?

I just wanted to execute few SAP activities for which i dont want the database to generate the archive logs, so i need some assistance.

Hoping to START GOOD here in this forum...
Thanks a million in advance to all

Waiting to hear from all the SQL gurus.

Regards
Hunky

View Replies !   View Related
Turning Columns Into Rows
I have a summary table with a number of columns that give all the information I need to build a report. What I would like to do is create a view specific to a single report, that organizes the data so that each row represents one metric. The only way I know of to do this would be with a series of Union querries, but that would require querrying what is basicly the same data multiple times. Is there some way to gather the data in one pass and then split it up with multiple Union queries? Sicne I doubt I'm explaining this well I'll just try an exsample.

Let say I have a summary table with the following columns: Location, Severity, Date_Day, Number_Dispatch, Dispatch_Duration, Dispatch_Goal, Number_Dispatch_Met_Goal, and Dispatch_Met_Goal.

Now I want to turn this into a table with the following columns: Metric, Location, Severity, Goal, Value.

The only way I know how to do that is with the following SQL:

--Number Dispatched Yesterday
SELECT Tickets Dispatched Yesterday AS Metric
, Location
, Severity
, N/A AS Goal
, sum( Number_Dispatch ) AS VALUE
FROM Summary_Table
Where

UNION
--Average Dispatch Duration
SELECT Average Dispatch Duration AS Metric
, Location
, Severity
, Dispatch_Goal AS Goal
, sum( Dispatch_Duration ) / sum( Number_Dispatch ) AS Value
FROM Summary_Table
Where...

UNION
--Percent Dispatch Duration Met Goal
SELECT Percent Dispatch Duration Met Goal AS Metric
, Location
, Severity
, Dispatch_Met_Goal AS Goal
, sum(Number_Dispatch_Met_Goal ) / sum( Number_Dispatch ) AS Value
FROM Summary_Table
Where...

Now I dont have a problem writing a statement for each metric, but it seems like this would be a rather wasteful query, as each would have the same where statement. What Id like is some way to either do all of the above in one pass (some kind of CASE statement perhaps?) or some way to pull the data for all the UNION queries in one pass.

View Replies !   View Related
SSIS - Turning Unicode OFF
All:

 

When creating a package, SSIS assumes varchar columns as Unicode (DT_WSTR) so before loading data into the target tables, I have to perform a data conversion from DT_WSTR to DT_STR.

 

Is there any way to turn UNICODE off? So I do not need to do the conversion? Please advise...

 

Rohan

View Replies !   View Related
Turning Rows Into Columns
I have a denormalization question that seems fairly fundamental but I haven't found the answer in BOL.  I have data stored in a normalized transaction oriented database that I would like to denormalize to do some queries/analysis.  Many tables contain attributes that are virtual columns driven by configuration.  I am struggling with how to take those rows of data and turn them into columns of data.

Example Source:

Column1:  CustomerId
Column2:  AttributeType
Column3:  Attribute Value

Ex Data:

123, ShoeSize, 9
123, Age, 45
123, Gender, Male


I would like to turn that into a table with one row, many columns:

CustomerId, ShoeSize, Age, Gender

123, 9, 45, Male

 

Also, I have other tables that are keyed off of the CustomerId that I would like to append to my ouput table via more columns.  For example, a customer's address.

Example Source:
Column1:  CustomerId
Column2:  AddressLine1
Column3:  AddressLine2
Column4:  City
Column5:  State
Column6:  Zip


If I need to combine several tables, should I nest several merge transformations?

Thanks,

Craig

View Replies !   View Related
SSIS Performance Turning
Hello Everyone,
                         Can any one update me up performance turning of SSIS and what difference would it make if I change the default value of this two parameter in each Data Flow.
DefaultBuffermaxRows
DefaultBufferSize
 
Also update me on what is these parameters used for.
 
Thank you
 

View Replies !   View Related
Turning The SQL Server 2005
 
Hello,
           I€™m loading the Fact table of more then 8 million records. The SQL Server Database is taking hell lots of time to get this insertions and updations. Can any one guide me on turning the SQL Server 2005 Database.

 
 
Thank you

View Replies !   View Related
Turning Off Aggregation On A Certain Hierarchy
I have a multi dimensional cube. Among the dimensions I have, there is one dimension that has one hierarchy defined. When I view any of my measures on this hierarchy, I don't want the measures to be aggregated. I read some threads here having to do with semi additive behaviors, but Im not sure if it applies to my case. (or maybe it does and I'm just not getting it)
 
For example,
 
DimPerson
 
PersonID, PersonName, AnotherPersonID
1            , Person_A    , null   
2            , Person_B    , 1
3            , Person_C    , 1
 
The purpose of "AnotherPersonID" is to have a self join that describes a certain relationship. So, in this case, Person B and C are related to Person A. I describe this relationship using a hierarchy where A is a parent of B and C. However, I don't want the measures for Person_A to be replaced by the sum of the measures in Person_B and Person_C.
 
Why do I have a hierarchy if I don't want to sum the numbers? This is motivated by the need to dynamically report on A's numbers when B or C's numbers are reported. I'm just using persons as an example, but, in my case I have a bunch of members that can be paired with another member in that dimension. And when I report on a member that joins to another member in that dimension, I need to dynamically report on that member's measures as well.
 
Lastly, If the use of a hierarchy is not the best approach for this, would you recommend another approach?
 
 

View Replies !   View Related
Identity Field And IDENTITY_INSERT Is Off
ok, so i get this error,
Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'TBL_LAPTOP_BOOKINGS' when IDENTITY_INSERT is set to OFF.

i ran SET INDENTITY_INSERT TBL_LAPTOP_BOOKINGS ON before i ran my script... and i also checked, we are running SQL 2000 with service pack four, because of the cursor error....

so im just wondering, is there anyway to make identity_insert to always be on, or any other way to have a unique id in my booking_id field??

Thanks Guys, Justin

View Replies !   View Related
SET IDENTITY_INSERT: Devil In The Details...
Need to ask this, 'cause as ya'all know I'm a GUID sort of guy with a sever IDENTITY crisis.

Got a client that erroneously deleted some records from a table with an IDENTITY key. We have a backup that we can restore to a new db to isolate the records that need to be reloaded. Question is, when we set IDENTITY INSERT to ON temporarily to reload the affected records, does the db need to be in single-user mode, or will it go merrily on creating IDENTITY values for new records if the db is in use during our fix? It is a 24/7 production db with web users around the country/world.

View Replies !   View Related
Linked Server And IDENTITY_INSERT
I need to change IDENTITY_INSERT on remote server. I use Linked Server and everything is good except that I can not use IDENTITY_INSERT.
First I try with
IF (IDENT_SEED('[Server IP].[database].dbo.[Table]') IS NOT NULL )SET IDENTITY_INSERT [Server IP].[database].dbo.[Table] ON
but I recived error message:
The object name '....' contains more than the maximum number of prefixes. The maximum is 2.

Second I try
to make stored procedure (sp) at Linked Server which have only one line
IF (IDENT_SEED('[Table]') IS NOT NULL )
SET IDENTITY_INSERT [Table] ON
and I called this sp from sp which is on my local SQL Server. Everything is ok but IDENTITY_INSERT is still OFF. Probably there is a store procedure scope after which IDENTITY_INSERT is set automaticly to OFF.

Any help.

View Replies !   View Related
Identity_insert Error From Ms Access97
I need to append records from a linked table coming from two different servers. It says to do a set identity_insert <table_name> on via a sql passthrough query. How to do this when I open the said query?
Thank you.

View Replies !   View Related
IDENTITY_INSERT In MSSQL 2005
Hi ,

I use MSsql server 2005 with the compatability mode set to 2000.One of the tables, has an IDENTITY Column.I need to restore a earlier backed copy of the table.So, i did the following :

set IDENTITY_INSERT MYTABLE ON

insert into MYTABLE(ID,NAME) values(23,'XYZ')

However, i get the following error :

[Error Code: 544, SQL State: S0001] Cannot insert explicit value for identity column in table 'MYTABLE' when IDENTITY_INSERT is set to OFF.

Can anyone tell me why the set IDENTITY_INSERT does not work ? I need to disable the IDENTITY, do the restore and then enable the IDENTITY again.Also, i need to be able to do this only thorugh SQL issued via JDBC.Please help.

Thanks,

View Replies !   View Related
IDENTITY_INSERT In MSSQL 2005
Hi ,

I use MSsql server 2005 with the compatability mode set to 2000.One of the tables, has an IDENTITY Column.I need to restore a earlier backed copy of the table.So, i did the following :

set IDENTITY_INSERT MYTABLE ON

insert into MYTABLE(ID,NAME) values(23,'XYZ')

However, i get the following error :

[Error Code: 544, SQL State: S0001] Cannot insert explicit value for identity column in table 'MYTABLE' when IDENTITY_INSERT is set to OFF.

Can anyone tell me why the set IDENTITY_INSERT does not work ? I need to disable the IDENTITY, do the restore and then enable the IDENTITY again.Also, i need to be able to do this only thorugh SQL issued via JDBC.Please help.

Thanks,
Charu.

View Replies !   View Related
IDENTITY_INSERT, Is It A Good Idea?
Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

 

View Replies !   View Related
Help Turning This Sql Statement Into A Stored Procedure
Hello, I need a little help turning this:SELECT RequestNum FROM Tickets WHERE ReceiptDate>='" & FromDate & "' AND ReceiptDate<='" & ToDate & "'"into a sproc because of the two different values (FromDate and ToDate) for the ReceiptDate field in the database.I have this so far (problem areas are ??):Dim AuditConnection As New SqlConnection(ConnString)Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)AuditCommand.CommandType = CommandType.StoredProcedureAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = FromDateAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = ToDateAuditConnection.Open()Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()AuditGrid.DataSource = AuditResultAuditGrid.DataBind()AuditConnection.Close()and:CREATE PROCEDURE CreateAudit    ??    ??ASSELECT    RequestNumFROM    TicketsWHERE    ??AND    ??GOI know I'm an idiot and this should be something simple.  Arrrgh.  Any help is appreciated immensely!!!  :)

View Replies !   View Related
Turning Torn Page Detection ON
Are there any issues turning this ON?

I have inherited a database which started life under SQL 7 (where Torn page Detection was OFF by default), and I'd like to turn it on. Will this reshuffle all the pages to make room for the extra check-sum, or is that stored in a single block somewhere else such that it can easily be added?

Is the change going to block access for long? (DB = between 2~5GB)

Thanks

Kristen

View Replies !   View Related
Question Abt Turning On Svc Brker For Msg Delivery
hello,

I am trying to enable service broker by issuing this command:

USE master ;
GO
ALTER DATABASE msdb SET ENABLE_BROKER ;
GO

It is taking a while to do that and I am wondering whether msdb needs to be in single user mode? Some smaller dbs completed right away. Going through Surface area config I got a message that I need a service broker endpoint and I looked at my other db and that has dbmail functioning and same message saying this instance needs an endpoint in surface config, what do you think is wrong?

View Replies !   View Related
Turning Off Impersonation On A SQL Server Datasour
Dear SQL Server Reporting Services Support Guru,

How do we configure Reporting Services such that when it attempts to connect to a datasource (e.g. Microsoft SQL Server datasource), it connects as the service account under which it is running as opposed to the calling user?

From our research we were thinking we could try one of two things:

1) change the credential type on the datasource itself so that it does not use Windows Integrated Security and instead connects as itself or

2) change the <identity impersonate="true"> flag in the web.config of the ReportServer web service to false.

A high level description of our architecture is that we have a client application with an web browser control that displays a report in the ReportViewer web control on the server. The report uses a SQL Server datasource to connect to our own SQL Server database hosted on the same server as Reporting Services but we want it to turn off impersonation and have Reporting Services connect as itself, not as the calling user. We do not want to grant users in Production direct access to our SQL Server database; so instead we wanted to configure Reporting Services such that it resolves and connects to the datasource using its own credentials.

A related question...when does the ReportServer web service get called? For both retrieval of the .rdl files and connecting to the datasource for dataset retrieval? What exactly does that <identity impersonate="true"> flag affect?

Any help you can provide would be very much appreciated.

Thanks much!

-canuck81

View Replies !   View Related
Turning On SET Options Before Bulk Load?
 

I need to execute the following:
 

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
 
before I do a bulk load because the table I am inserting into has an indexed view created on it.  Whats the best way to set these options prior to a bulk load?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved