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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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