Determining Last Entry ID In Table
Not sure if this is the right place to post this but hopefully someone can help me. I would like to determine what the last automatically incremented ID is in a table I need to return it as a variable to a VB.NET program but I'm not sure what the SELECT statement would look like for this. Any help would be greatly appreciated.
View Complete Forum Thread with Replies
Related Forum Messages:
Determining If A Table Exists
How do I find out if a temporary table named '##test' exists? I have a stored procedure that creates this table and if it exists another stored procedure should do one thing, if it does not exist I want the SP to do something else. Any help as to how I can determine if this table exists at the current time would be greatly appreciated. Thanks in advance for you help, Jon
View Replies !
Determining Last Time A Table Was Updated (6.5, 7.0, 2k)
I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows. I am a DBA of several hundred databases and want to retire those no longer being used. Is there a column of a system table that has this info.
View Replies !
How Do I Enter NULL In A Table Cell In The Enterprise Manager UI For Table Data Entry?
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View Replies !
Custom Stored Procs: Determining Source Table Name
Hello all ... I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks. It looks like I can get the data values passed as parameters just fine. I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target. I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database. A handy trick or technique would be helpful! Thanks! DB
View Replies !
Overwriting Entry In Table
Task: To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry. What's the quickest/cleaniest way to do this ? thanks, Clayton
View Replies !
Table Entry Problem
While i execute the below query ,Entry is put in status_report table.But the same if i put in scheduling (Sql Agent) no entry is made in the table and the message success is shown in "Agent Job Activity". Purpose of the sp is to transfer data from One Server to Another Server. But if see in the Destination Tables No entry is made. CREATE procedure RBI_Control_sp as begin set nocount on begin try BEGIN TRANSACTION insert into fin_wh..Status_report ( [object_name], row, st_date ) select 'Data Tranfer', 'Inprogress--', getdate() --Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)] exec fin_ods..RBI_Data_Transfer_sp --temp table Table population,Fetching data from the fin_ods[erp Table] exec FIN_wh..RBI_SPExecution_sp Update fin_wh..Status_report set row= 'Success', [error_message] = 'No Error', end_date= getdate() where row= 'Inprogress--' COMMIT TRANSACTION end try begin catch ROLLBACK TRANSACTION Update fin_wh..Status_report set row= 'Failure', end_date= getdate(), [error_message]= cast(error_number() as varchar) + '***' + error_message() where row= 'Inprogress--' end catch set nocount off end Txs in advance
View Replies !
How To Prevent Doublicate Entry To A Table
hi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that, I am using sql server 2000. thanks Ali
View Replies !
Preventing Duplicate Entry For A Given Foreign Key In Db Table
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View Replies !
Table With Duplicate Entry With Primary Keys
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View Replies !
The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312
Hi, I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error: The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'. All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . . Any ideas as to what is causing this error?
View Replies !
Determining Your Worth
Where does one DBA find a reliable benchmark for determining his salary. I like where I work but the investors seem to be ignorant to the value of technology professionals and I could use the extra $$$. I have a B.A. in MIS and achieved MCP in SQL 7.0 admin (940/1000). Working on the MCDBA at home. 2 yrs exp in VB development and <1 year in DBA position? Average Salaries?? Doug D.
View Replies !
Determining If A Server Is 32 Bit Vs 64
Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. I need to know this to pick the correct upgrade version. -Kyle
View Replies !
Determining If A Server Is 32 Bit Vs 64
Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. -Kyle
View Replies !
Determining What To Input As Server
I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon
View Replies !
Determining Relationships Among Tables
I am trying to design my first database using SQL-Server 2000. The database is to track a company's referrals, who are basically individuals who are not a part of the company but refer customers to the company for commission. The database needs to track when these referrals come to pick up their commission, the name of the customer they referred and information about the customer's contract, the company employee who gave out the referral commission, and the amount of money the referrals receive for commission. I designed five tables for this project and I am having some trouble determining the relationships among them. (Sorry for the formatting-- couldn't figure out how to paste tables directly). If anyone could help me figure out the relationships among the tables, I would greatly appreciate it, as I'm new to all this and I'm working on this project on my own. Thanks! Employees ======= Column Name - Data Type - Length - Allow Nulls -------------------------------------------------------------------------------- EmployeeLastName - varchar - 20 - EmployeeFirstName - char - 10 - EmployeeId - varchar - 11 - __________________________________________________________ Receipt ====== Column Name - Data Type - Length - Allow Nulls ----------------------------------------------------------------------------------- ReceiptID - char - 10 - [Date Paid] - datetime - 8 - Receipt# - char - 10 - Completed_By - varchar - 40 - Comments - ntext - 16 - X _____________________________________________________________ Contract ====== Column Name - Data Type - Length - Allow Nulls -------------------------------------------------------------------------------------- ReferralID - varchar - 11 - ReceiptID - char - 10 - [Date] - datetime - 8 - ContractID - char - 10 - Customer_Name - char - 10 - Number_of_Days - char - 4 - Rate - money - 8 - Comments - ntext - 16 - X ______________________________________________________________ Customers ======= Column Name - Data Type - Length - Allow Nulls ------------------------------------------------------------------------------------- CustomerLastName - char - 10 - CustomerFirstName - char - 10 - ContractNumber - char - 10 - Rate - money - 8 - Comments - text - 16 - X CustomerID - char - 10 - Referral_id - varchar - 11 - _____________________________________________________________ Referral ====== Column Name - Data Type - Length - Allow Nulls ---------------------------------------------------------------------------------------- Referral_id - varchar - 11 - Referral_Lastname - varchar - 20 - Referral_Firstname - char - 10 - X Employer_Name - varchar - 20 - Home_phone - char - 12 - X Cell_phone - char - 12 - X
View Replies !
Determining Unique Children
Hi, I need to be able to identify if a parent has the same child names as another parent. Have a look at my data (in pic) Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names? So you would get something like: Bob | 2 Bill | 1 Glen | 2 thanks, david.
View Replies !
Determining Database Size
Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database. My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full. I'd appreciate any insights. Kamen
View Replies !
Determining DB Type/Character Set
Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set? Any help will be greatly appreciated. Thanks a lot!!
View Replies !
Determining Appropriate RAM For Application - SQL7
Given that I know the database, the number of clients who will access the database and the other processes NT will be running, where might I find a formula for determining the correct amount of RAM to install on a new NT4 server? Any help will be appreciated and thanks in advance. -- Ty
View Replies !
Determining Database Size
Is there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.
View Replies !
Determining Is A Database Is A Subscriber
Hi, can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!! I want to provide a small app that creates a merge publication but only if the database isn't a subscriber. Thanks for your help Graham
View Replies !
Determining The OS Version That SQL Is Running On
I am writing a client application that offers an UI that allows an administrator to remotely add/delete/update user accounts accross many different SQL Servers running on XP and up. When the operating system is W2K3 or higher I want to take advantage of the "check_expiration, check_policy, must_change' arguments to create login and exclude those features when the host OS does not support them. Is there an easy way to determine if those arguments are supported? Thanks Mark
View Replies !
Determining Which Version Of SQL Is Running
hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is. thank you for your help
View Replies !
Determining If SQL Statement Is A Query
Hi, I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it. There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..): try { reader = OpenReaderCursor(statement) } catch { updatedCount = ExecuteNonQueryCommand(statement) } Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it. My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code? Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago) Thanks for your time, all ideas appreciated Jim
View Replies !
Determining Null Values In Columns
How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example: int count = (int)row["Count"]; and int count = row["Count"] as int; the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
View Replies !
Determining A MySQL Servers Status.
I am using vb.net and the myoledb provider.I've run into a problem where what I need to do is open a databaseconnection to a mysql server, easy. The problem arrises in that I wantto, if the server cannot be found, open from a locally storedencrypted XML file.Works fine when the sever is present, but when I go to open theconnection and the database cannot be found, a dialog box pops up as tofill in the connection string information. I do not want this, is thereany way I can intercept this dialog or skip past it so my try/catchhandles it?Thanks.--Posted via http://dbforums.com
View Replies !
DETERMINING WHICH HIERARCHY OF A DIMENSION IS SELECTED
I am trying to use MDX to dynamically determine which hierarchy of thetime dimension is selected. I have a calculated member which is usingthe last non-empty descendant of the [Time].[Calendar] dimension, butI want it to work even if Fiscal is selected instead of Calendar.Here is the codeSUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [CalendarDate]), isEmpty([Measures].[Employee Count]) = False), 1),[Measures].[Employee Count])What can I put in place of [Time].[Calendar].CurrentMember to take thecurrent hierarchy of the time dimension instead of the Calendarhierarchy? Thanks!
View Replies !
Determining If Foreign Key Constraint Exists
Hello,I'm working with a number of databases. I need a way to determine if aforeign key with given name exists and determine what tables andfields are linked to it. Any ideas?I know I can issue the following statement to get a list of the fieldsthat are constrained by a foreign key constraint.SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEHow can I get a list of fields pointed to by the constraints?Thanks,WillJoin Bytes!
View Replies !
Determining MDAC And Service Pack
I am new to MS SQL 2000 and have a couple of questions. First, how do you check the version of MDAC that Windows 2003 server is running and second, how do you determine what service pack that SQL is at. I am having issues of extreme slowness using ODBC. I checked to see that tracing was not enabled and found that it wasn't. I am using TCP/IP for the connection. Thanks
View Replies !
Determining What Service Pack Is Installed...
Hi, How can I determine what SQL Server 7.0 service pack is installed on a Windows 95 developers workstation (not the server)? If I run "select @@version" from Query Analyzer, it returns me what is installed on the server but I need to know what service pack is installed on the workstation. Thanks, Darrin
View Replies !
Determining If A Local Variable Exists
Is there a way to determine if a local variable exists or not? There's a parameter I often use in code called @guid_batch that is usually declared in the parameter of a stored proc, but when in debugging it would be nice to have it available without having to change code. Is there something that I could do similar to the following Code: IF VARIABLE_ID('@guid_batch') IS NULL BEGIN DECLARE @guid_batch UNIQUEIDENTIFIER SELECT @guid_batch = NEWID() END Thanks in advance, -MBirchmeier
View Replies !
Determining The Nature Of Partitioned Tables
I apologize in advance if this is something obvious I've missed ... fresh eyes/brain and all that. If I have a table that is using a particular partition scheme/function, is there a quick and easy way to determine which column of that table is being used for partitioning? We're examining a number of legacy structures and we're hoping to reduce the time it's going to take us to get the report management wants. Thanks.
View Replies !
Determining Default Values Automatically
I am building code to look into the database and create "CRUD" stored procedure scripts automatically for each table. I am in the finishing stages as my procedures take into consideration all necessary parameters and keys wheter they are natural or identity. It also takes into consideration User Defined Types. I am working on the section which defines the parameters and their default values. ie. CREATE PROCEDURE [dbo].[TransactionHistoryInsertOne] ( @ProductID Int = 0, @ReferenceOrderID Int = 0, @ReferenceOrderLineID Int = 0, @TransactionDate DateTime = '1/1/1900', <-- Possibly arbitrary @TransactionType nchar(1) = '?' <---- How to get this. @Quantity Int = 0, @ActualCost Money = 0, @ModifiedDate DateTime = '1/1/1900' ) AS... I have three problems remaining. 1. Using the AdventureWorks database as a data model, I run into the TransactionType field in the TransactionHistory table, a non-nullable nchar(1). How do I determine and use any default values that may be defined for this object? 2. Do UDTs have default values already defined? 3. How do I make sure I am not arbitrarily setting the default values? Here i my select statement into the system tables: SELECT C.*, T.name as xtypename, U.name as xutypename, S.*, so.* FROM syscolumns C left join systypes T on (C.xtype = T.xtype) and (T.xtype = T.xusertype) left join systypes U on C.xusertype = U.xusertype left join sys.all_columns S on (C.id = S.object_id) and (C.name = S.name) left join sysobjects so on S.default_object_id = so.id WHERE (C.id = [TableId])"; Thanks for any help
View Replies !
Determining The Server Memory Required For SQL Ser
Hi everybody, I am trying to determine the optimum amount of server memory that would be required for my SQL Server application. According to a book, it is calculated as follows: Minimum memory required = System memory + User memory + Database process memory I have thought about ways to determine each component of memory. 1) I could determine the System memory utilized from the Task manager (Total - Available), when the SQL Server service has been started, and no applications are running. 2) I could determine User memory by ascertaining the number of concurrent users during peak usage time of the application and then multiplying it with 0.5 MB. 3) I could determine Database process memory by summing up the "memusage" column in the SysProcesses table, at the time of peak usage and multiplying it with 8. Please let me know whether they are correct.
View Replies !
Problem Determining If IDTSComponentMetaData90 Is A DataReader Source
I'm attempting to iterate package components of a package created in the SSIS designer, but have run into a problem identifying the DataReader Source managed component. For instance, if iterating the dataflow task components like so: Application app = new Application(); MainPipe pipe = taskHost.InnerObject as MainPipe; foreach (IDTSComponentMetaData90 component in pipe.ComponentMetaDataCollection) { if (component.ComponentClassID == app.PiplineComponentInfos["DataReader Source"].ID) { // THIS IS NEVER REACHED } } The ComponentClassID of the component is a GUID, but the PipelineComponentInfo.ID AND PipelineComponentInfo.CreationName are both the fully qualified assembly name of the data reader source adapter, which is: "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" From reading another post i attempted to get the ProgID of the component from the class ID (ole32 ProgIDfromCLSID) but that only yields a ProgID of DTS.ManagedComponentWrapper.1 How can I properly identify this type of dataflow component? I am running SQL Server SP2 (Developer Edition) on XP Pro SP2.
View Replies !
Need Assistance In Determining Expiring (and Soon To Expire) Sql Accounts
I have a question regarding notification of password expirations. Background: My workplace will soon be enforcing the password policy and password expiration facilities in SQL Server 2005. My understanding (and correct me if I am wrong) is that the period of validity for a password before expiration occurs comes from either active directory, or where there is no AD, the local machine security policy. Because we are on a Novell network our SQL2K5 servers are all mixed authentication. For development purposes we are each given a separate account. Our applications, too, use sql accounts (not windows accounts) for database access. Having a development account expire unexpectedly is no problem; we'll change those passwords as they need to be. What I am concerned with is the accounts our applications use to access the database server; like most IT shops, our applications need to be available with interruptions in service as minimal as possible. I would like to create and schedule a report to run every morning listing those accounts that are about to expire, or have expired, so that those passwords can be changed with little to no interruption in service. Question: Where do I pull this data from? I couldn't find anything in the BOL, or online (so far), giving the details of how to determine when a particular account is nearing expiration, or has expired. I would appreciate any advice anyone has to offer. Best, B.
View Replies !
Determining Database Role Of Querying User
I know that I can use: IS_MEMBER('MyRoleName') = 1 to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like: SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME) I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role. Thanks, Kayda
View Replies !
Determining Public Access To Xp_regread And Xp_instance_regread
I am trying to write a query to determine if public role has access to both of these. I need this to satisfy auditing requirements. If anybody knows how to write the t-sql to turn these on, I should be able to use the sql to find out if public role has access to these. Or if you just have a script already or a sp that would be fantastic also. Thanks Kyle
View Replies !
|
TRACKER
