Obtaining Count For Identity

Jul 23, 2005

Is there a way to query a SQL server such that it will return the
current counter value of a field that increments automatically (an
identity field)? Using a MAX query might not necessarily give you the
correct information because it is possible that the high value records
were deleted. I checked the system tables and none of them appear to
have what I need. I suppose it would be possible to maintain a table
that lists all the incrementing columns, but ideally there would be a
way already built into SQL Server.

Thanks.

View 3 Replies


ADVERTISEMENT

OLE DB Destination - Obtaining Identity Values

Apr 29, 2008



Is there any way to get the new identity values created after an OLE DB Destination has finished its inserts?

Basically my problem is this...

My source database has its primary and foreign key columns as a GUID datatype. In my conversion, my destination database will be using INT (identity) columns for primary/foreign keys. How do I propagate the foreign key relationships to the destination database in an SSIS package? So obviously I can use an OLE DB Destination to generate the data with identities for the parent table, but after that I am stuck in how to create the relationships for the child tables.

I know how to do this in TSQL fairly easily (cursor or temp tables), but it doesn't seem so simple in SSIS.

thanks in advance.

View 9 Replies View Related

Row Count/Identity Type Processing In SSIS

Dec 5, 2007

I've got a falrly simple task I'd like to achieve in SSIS without scripting if possible.

I have a table with three columns, Cust ID, Address and Group ID
I want to update the Group ID with a counter that increments every time the Address changes.

So currently the table looks like

Cust ID Address Group ID
62 ABC
57 DEF
79 DEF
42 GHI
11 JKL
20 JKL

and I want to end up with

Cust ID Address Group ID
62 ABC 1
57 DEF 2
79 DEF 2
42 GHI 3
11 JKL 4
20 JKL 4

I have created a view over this table that counts and Groups by address. This looks like

Count Address
1 ABC
2 DEF
1 GHI
2 JKL


What I'm thinking of doing is reading the View in Address order and updating the table (joining on Address) with a counter that increments for every row of the View read.

What I'm not sure of is how to increment this counter without creating a script. One thought I had was to create a new table from the view, and add an Identity column somehow (is this feasible in SSIS?)

Any advice appreciated

View 5 Replies View Related

Obtaining The Last Record

Jul 23, 2007

Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamen

View 6 Replies View Related

Obtaining Disk Space

Mar 23, 2006

Hi,
I'm new to these forums (and to SQL Server), so please be gentle with me.

I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.

I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.

I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)

I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.

Many thanks

View 4 Replies View Related

Obtaining PATH To Master DB

Aug 31, 2006

I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLMSoftwareMicrosoftMSSQLServerSetup

It seems that a SQL2005 installation does not have the same default
directory.

Is there a way to query this from the server itself so I don't have to read the registry?

View 1 Replies View Related

Obtaining 2nd Last Record From A Table

Jan 6, 2012

What is the query to get 2nd last record from a table on a specific note_id key?

This is the data I have:

Note_id ======From Date===== =======To Date======
892481====2010-07-27 00:00:00===2010-07-27 00:00:00.000
892481====2010-07-27 00:00:00===2010-07-27 23:36:30.063
892481====2010-11-01 00:00:00===2010-11-02 00:15:23.027
892481====2011-03-08 00:00:00===2011-03-08 13:18:37.873 <====== This is the result i want
892481====2011-04-21 00:00:00===2011-04-21 07:57:20.567

View 11 Replies View Related

Obtaining Management Studio?

Jun 4, 2007

I'll be working on a web project that utilizes sql server 2005. Our web host will be providing the server however I'll need a version of Sql Server 2005 management studio to work with it.

I believe the studio software costs around $50 however the express edition software is free.

So my question is, is the express edition software compatible with the full version of sql server 2005 or only sql server 2005 express edition?

Thanks.

-Goalie35

View 1 Replies View Related

Obtaining The Version Of ADO Installed On XP

Jul 23, 2005

How is it possible to tell which version of ADO is installed on an XP workstation?Thanks

View 1 Replies View Related

Obtaining Timezone Offset In T-SQL.

Jul 23, 2005

In MS SQL 2000, I would like to obtain the timezone offset from UTC fora given date. For today's date, I can doDATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a futuredate may not be the same as today because some countries go in DaylightSaving mode. Can you suggest a way to obtain the timezone offset forany given date?ThanksYash

View 3 Replies View Related

Obtaining Audit Information

Jul 5, 2007

Hi People
We want one trigger which captures the data as mentioned below.





Table Name

Date and Time

User

type

Mode of modification


TABLE_ABC

03/07/2007 12:00:04

XCSRaoa

Update

Procedure


TABLE_DEF

03/07/2007 12:00:34

XCSRaoa

Insert

Class Integration SSIS Package


TABLE_GHI

03/07/2007 12:01:04

XCSRaoa

Insert

Procedure


TABLE_GHI

03/07/2007 12:01:34

XCSRaoa

Update

XCSRaoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.
Could someone help me achieving this?

Regards
Abhi

View 5 Replies View Related

Obtaining Login ID From Page

Oct 3, 2006

Hello,

By using DBCC Page I am able to exemine data entered to row and other changes related to the rows. What I want to know is; is it possible to find out (and how) which Login ID commited these change directly from pages or where do I need to look to find it out?

Any guidance is appreciated....

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Timeout When Obtaining A Connection From The Pool

Oct 6, 2006

Hi All,I'm having a bit of a problem with a c# program which creates and index file/table. The number of records I need to process is around 3500, the main loop. Each of thoes records I need  to look at a 2 text fields and create an entry in an index table for each valid word found in thoes fields. I call a stored proc to save the key word. So this stored proc get called numerous time per record.What seems to be happening is proceese about 400 main rcords the errors out with the following error message: {"Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached." }Has anyone had a similar problem? if so any suggestions on how to fix, I've been looking at this problem and recoding for the last 4 days! I need some help! Using SQL Server 2000 & .net v1.1Thanks in advance Davej 

View 1 Replies View Related

Obtaining Data From Different Tables In The Same Database

Dec 31, 2006

Hi
I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.
Thanks

View 1 Replies View Related

Obtaining The Number Of Records In A SqlDataReader

Feb 26, 2004

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?

View 8 Replies View Related

Need Help Obtaining Reporting Services For SQL Developer

Apr 23, 2004

I'm hoping someone can shed some light on a reliable way to get Reporting Services if you don't have an MSDN subscription.

I have registered copies of SQL Server 2000 Developer and SQL Server 2000 Standard that I bought late last year.

I've had a backorder with MS Parts since January to get updated CDs with Reporting Services on them. Each month, the back order is pushed up another month. The disks are now due sometime in late May. I called the fulfillment center and the operator end didn't seem too enthusiastic about it coming in stock anytime soon.

All 3 versions (Developer, Standard, and Enterprise) are on a long back-order.

I thought I could get by temporarily by using the 90-day trial copy of Reporting Services, but I'm now concerned it might run out before I get my own non-trial version.

Isn't there a way for legit users to get a hold of this product? I'd be happy to download it, but it's not available unless I want to pay add'l money for an MSDN subscription.

_ANY_ suggestions or help appreciated.

Thanks.

dotnetFool

View 2 Replies View Related

Obtaining Detail Of DTS Package Contents

Dec 17, 2002

I have quite a few DTS's to work through while analysing current SQL databases for a client. Problem is I can't seem to find a way in SQL (Enterprise Manager) to print the contents of a DTS package. This is quite frustrating in that I have to go an "Design" the package, then click on the properties for each step and copy and paste the contents into Notepad/Wordpad. Surely Microsoft have something to ease the pain?

If anyone can help me on this one, I would be extremely grateful.

View 4 Replies View Related

Obtaining A Table Lock In SQL Server

Apr 9, 2003

I need to lock a table in SQL Server. The reason is that I need to insert a value into an identity column (rather than just taking the next sequence number). I have a program that:

1. Sets identity insert on
2. Inserts into the table
3. Sets identity insert off

The problem is that I believe I should lock the table before I turn identity insert on (otherwise, other transactions will fail). I need to ensure that no other operations will take place on the table while I am doing all three of these steps.

I believe I can set the isolation level, but I cannot find any way to establish the lock without executing the update statement itself - which is a nanosecond too late to prevent another user from getting an error.

View 6 Replies View Related

Obtaining The Most Current ODBC Driver

Nov 10, 1998

I've been having some strange problems lately upon connecting to SQL Server via the Enterprise Manager, or via MS Access. The response time is MUCH slower than usual. The only change I'm aware of is that additional drives were added to the server.

At any rate, when logging in to Access my login (not my query) times out. I get SQL State: S1T00; SQL Server Error: 0; MS ODBC SQL Server Driver: Timeout Expired.

I'm not sure where to begin to troubleshoot this. The version of SQL Server ODBC driver I have is 2.65.0252. Perhaps this one is corrupt. How would I ascertain what the latest version is? And, if it's appropriate to replace it, how do I go about it? Is it a simple overwrite of the file?

I'm using SQL Server 6.5 SP2 on NT 4.0.

Thanks,

Lisa

View 1 Replies View Related

Obtaining Foreing Keys Dependences

Jan 6, 2006

I want to know how to obtain the relationships of foreing keys when they have diferent names in diferent tables using SQL querys from VB.net in SQL Server 2000.

Example:

Table Person
------------
IDPerson
Name
Address


Table Customer
--------------
IDCustomer
.
.
.


Table Employee
---------------
IDEmployee
.
.
.


The dependencies are:

IDPerson--->IDCustomer
IDPerson--->IDEmployee

View 2 Replies View Related

Timeout / Obtaining A Connection From The Pool - Please Help!

Jul 20, 2005

Dear GroupSorry for posting this here. I'm desperate for a solution to thisproblem and thought some of you might have come across it with .NETand SQL Server.Let's assume I've the following code:Private Sub Button4_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles Button4.ClickDim sqlConnection As New System.Data.SqlClient.SqlConnectionsqlConnection.ConnectionString = "workstation id=THEINTREPIDFOX;packetsize=4096;user id=sa;data source=""(local)"";p" & _"ersist security info=False;initial catalog=TestDB"sqlConnection.Open()sqlConnection.Close()sqlConnection = NothingEnd SubWhen I click 100 times on the button the page finally times out with:Timeout expired. The timeout period elapsed prior to obtaining aconnection from the pool. This may have occurred because all pooledconnections were in use and max pool size was reached.Of course I could increase the max pool size but this isn't thesolution to this problem. I wonder what's wrong with my code?According to MSDN, if the connection is closed it's released back intothe pool. Also tried to dispose but nothing helps. It looks like thatit keeps connections and doesn't release them. I'm grateful for anyhints, ideas, suggestions on this problem as I'm very desperate tosolve this.Thanks very much for your time & efforts!Martin

View 5 Replies View Related

Obtaining Snapsot Via FTP Using SQL Express Client

Aug 12, 2006

Hi All,

I really need some help here! I have spent the last few days upgrading a clients system to SQL 2005, part of which involved upgrading their sales people's laptops to Sql 2005 Express and setting up Merge Replication. I have configured the Server to use Web Synchronisation, and have tested that the replisapi.dll?diag all works ok and it does.

I am running into problems now with getting the laptops to synchronise. I have set up the Replication task, and am getting an error when I run the Wndows Synchroniser:

====================

The schema script '\PTC42ParklandsWebsiteV2ftpPTC42_PARKLANDS_PARKLANDS20060812131209' could not be propagated to the subscriber.

The process could not connect to FTP site 'mail.parklands.net' using port 21.

A connection with the server could not be established
==============

I have tested independantly connecting to the FTP server using Windows explorer and it works fine using the same username and password. I am at a loss here!!! I am going in circles looking for help too as there doesnt seem to be much info on this in SQL 2005 yet. I have re-read all the books online articles for Replication, Replication in SQL 2005 Express, and also gone back & reset up the Server & the Client several times - with no luck!



The client needs to get these sales guys laptops back ASAP and I am starting to really stress out here!! (can you tell I havent had much sleep in the last few days??) Any help would be so wonderfully apreciated!!!

Thanks so very much,

Catherine

View 1 Replies View Related

Obtaining Values On Datasource Inserting Event

Nov 27, 2007

Hi,
I want to be able to spot when the same employee name gets added to my grid.  This is to ensure that I cannot not have the same firstname and lastname (i.e. cannot have 2 John Smiths).
It is kind of like spoting for duplicates but they are not PKs.  I was hoping if there was a way you could identify the the feild values on the "inserting" event of the datasource so I could put some logic in.  The reason for placing it there is because we have the e.cancel = true command.
Thanks in advance,
 Jon

View 5 Replies View Related

Obtaining Collation Length Of Declared Variable Within SP

Jun 12, 2008

Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length.  I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB'  this needs to be four characters long so want to pass it into a function and return 'AB  'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom

View 1 Replies View Related

Obtaining Column Values Separated By Comma

Sep 26, 2006

How do I get the values of a column from a table separated by a comma.

For example

Suppose I have a table with column Levels (below), I want the values of the corresponding column separated by a comma, so that I can use this in a different query to pull these values from a different table

Levels
Level1Name
Level1Value
Level2Name
Level2Value

Result should look like
Level1Name, Level1Value, Level2Name, Level2Value


Thanks
Suresh

View 4 Replies View Related

URGENT: Obtaining Actual Name Of ErrorColumn Instead Of Its LineageID.

Mar 21, 2007

Hi,

In a dataflow, I've an OLE DB source before a script component.
When I link the Red Output of the source to the script component, I want to get the COLUMN NAME (which is the source of error), instead of the default ErrorColumn (LineageID). I'm not able to succeed with the following code snippet.

My code is something like this:

Dim temp_Int_EmpID As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn90
temp_Int_EmpID = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumnCollection90.GetInputColumnByLineageID(Row.ErrorColumn)

Pls. let me know if it's possible to achieve what I require & if so, how.

Kindly reply at the earliest !

Regards,
Natesh PV

View 1 Replies View Related

T-SQL (SS2K8) :: Obtaining Counts From Historical Data After Given Date

May 12, 2015

I'm looking to get counts on historical data where the number of records exists on or after May 1 in any given year. I've got the total number of records for each year worked out, but now looking for the number of records exist after a specific date. Here's what I have so far.

SELECT p.FY10,p.FY11,p.FY12,p.FY13,p.FY14,p.FY15
FROM
(
SELECT COUNT(recordID) AS S,
CASE DateFY

[code]...

View 2 Replies View Related

Obtaining Business Hours Based On Start And End Date

Jul 23, 2005

I have a transaction log that tracks issues from a call center. Eachtime an issue is assigned to someone else, closed, etc. I get a timestamp. I have these time stamps for the beginning of an issue to theend of an issue and I'd like to determine how many business hours theseissues were open.Issue BeginDt Enddt Total hours1 3/29/05 5:00 PM 4/1/05 2:00 PM 69Basically, this is the type of data I'm looking at and my hours of workare from 7:30 - 5:00 weekdays. I need to come up with a way to removeall nonbusiness hours, weekends, & holidays from the difference of thetwo dates. Issues can span for 2-3 days or 20-30 days.Please let me know if anyone has any ideas or has done something likethis before.Thanks!

View 5 Replies View Related

Reporting Services :: Obtaining Column Names From Data?

May 16, 2015

Got a table like:

Reporting_DateCustomerSales...

In such a case need a view with custom headers with a prefix of Latest date found in the first field of Reporting_Date.

In other words if Reporting_Date consists data like following six entries:

1-JAN-201031-DEC-2013NULL14-AUG-2014NULL31-MAR-2015

Then, could there be anyway to achieve a View with the headers (i.e. complete data without any filtration) in the following form:

Mar312015_Reporting_DateMar312015_CustomerMar312015_Sales...

View 4 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?



TIA,



Barkingdog

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?



TIA,



barkingdog



P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View 12 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved