Hi,
Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either.
Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???
Hi,
Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either.
Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???
himy self avicurrently i am developing one application in vb 6 and back end assqlserver 7 which is used on lan.i have some problem. like in my database i have one table salesvoucherwhich has 'voucherno' field. when 2-3 user will work on salesform at atime [since the softwrae will run on lan] then the same voucherno willsave for all users data which is wrong i need to save different vouchernofor each no. is there any way in sqlserver to apply some condition ondatabase or to set some its property[tables] so that at a time only oneusers data will save depend on firstcome first serve base or is there anyway to make changes in programme.plz help me regarding this i need it very badly.since its my first lan based software plz give me some books nameregarding this software for vb6 and sqlserver7
View 2 Replies View RelatedIf the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
Hi
I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?
Thanx for your support!!!
Diego Bayona
I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.
Say I have a table with items, prices, and dates.
PriceTable
ITEM PRICE DATE
A 1.00 5/5/2000
B 3.00 1/1/2000
A 2.50 6/5/2004
....
This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.
Now say I have a table of transactions with items, amount bought, and dates.
TransactionTable
ITEM AMOUNT DATE
A 5 6/6/2003
A 1 8/5/2003
A 2 8/5/2004
The total for A should come out to be 11.00. There are multiple Items and multiple price changes.
If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.
My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".
I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".
Hope that made sense, thanks.
I am trying to find a needle in a haystack.
One of the tables in this database has a column named column_xyz.
There are over 800 tables, each with numerous columns. If I know that the column I am looking for is "column_xyz" is there an easy query I can do to identify all tables that contain that column?
I hope.
Thanks
I have a data table I have adabted for my own use and want to add a row to the table. Can I do that with Visual Web developer Express?
I can add update modify the colums and data in the exciting table but I cannot figure out how to add a row.
This is just a simple datalist of names. firstname lastname. there are 18 of them and i want to add more names
This little handy algorithm provides a truth table for any range of numbers between 0 and 65535 of any base between 2 and 36!
As a twist, you can either return the digits concatenated or as separate columns.CREATE PROCEDURE dbo.uspTruthTable
(
@FromNumber SMALLINT = 0,
@ToNumber INT = 255,
@Step INT = 1,
@Base TINYINT = 2,
@Concat BIT = 0
)
AS
SET NOCOUNT ON
DECLARE @Temp INT,
@SQL VARCHAR(8000),
@b VARCHAR(12),
@d VARCHAR(2),
@Diff INT,
@Digits CHAR(36)
IF @FromNumber > @ToNumber
SELECT@Temp = @FromNumber,
@FromNumber = @ToNumber,
@ToNumber = @Temp
IF @FromNumber < 0
BEGIN
RAISERROR('The parameter FromNumber %d is less than 0 (base 10).', 16, 1, @FromNumber)
RETURN
END
IF @ToNumber > 65535
BEGIN
RAISERROR('The parameter ToNumber %d is greater than 65535 (base 10).', 16, 1, @ToNumber)
RETURN
END
IF @Base < 2
BEGIN
RAISERROR('The parameter Base %d is less than 2 (base 10).', 16, 1, @Base)
RETURN
END
IF @Base > 36
BEGIN
RAISERROR('The parameter Base %d is greater than 36 (base 10).', 16, 1, @Base)
RETURN
END
SELECT@SQL = '',
@Temp = FLOOR(LOG(@ToNumber) / LOG(@Base)),
@Diff = @ToNumber - @FromNumber + 1,
@Digits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
WHILE @Temp >=0
SELECT@b = CONVERT(VARCHAR, @Base),
@d = CONVERT(VARCHAR, @Temp),
@SQL = @SQL +CASE
WHEN @Concat = 1 THEN '+SUBSTRING(''' + @Digits + ''',1 + Number/POWER(' + @b + ',' + @d + ') % ' + @b + ', 1)'
ELSE ',SUBSTRING(''' + @Digits + ''',1 + Number/POWER(' + @b + ',' + @d + ') % ' + @b + ', 1) AS Digit' + @d
END,
@Temp = @Temp - 1
CREATE TABLE#Numbers
(
Number INT PRIMARY KEY
)
INSERT#Numbers
(
Number
)
VALUES(
@FromNumber
)
WHILE @Step <= @Diff
BEGIN
INSERT#Numbers
SELECT@Step + Number
FROM#Numbers
WHERENumber <= @ToNumber - @Step
SET@Step = @Step * 2
END
IF @Concat = 1
SET @SQL = 'SELECT Number, ''''' + @SQL + ' AS Digits FROM #Numbers ORDER BY Number'
ELSE
SET @SQL = 'SELECT Number' + @SQL + ' FROM #Numbers ORDER BY Number'
EXEC (@SQL)
DROP TABLE #NumbersYou can use the algorithm like these examplesEXEC dbo.uspTruthTable
EXEC dbo.uspTruthTable 0, 255, 1, 8
EXEC dbo.uspTruthTable DEFAULT, 15, 1, 2
E 12°55'05.76"
N 56°04'39.42"
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies View RelatedI have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies View RelatedAny SQL that can be used to identify all objects that depend on a particular table? For example, sprocs, functions, views, etc.
View 3 Replies View RelatedI've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,
[code].....
Is there any way to get the base table/column from a view by supplying the view name/column name_in_view? I know about INFORMATION_SCHEMA.VIEW_COLUMN_USAGE, but it doesn't always work. Try it on a regular database, not pubs. Any help is appreciated!
View 1 Replies View RelatedI am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedI am using MADC 2.8 to connect to Sql Sever 2000.
The Connection string uses a File DSN.
I get few records into a Record set using Select Command. Then I update one Col in the Recordset and Updates the database table. I open the recordset in
I am getting "Insufficient base table information for updating or refreshing" Error some times. This Code executes in a Multi user Environment.
I tried to recreate this error condition on my local system by Deleteing/Updating the Reocrds from the table and even locking the table.
I get this error "Row cannot be located for updating. Some values may have been changed since it was last read."
Are both of these errors connected? or is there any way i can get rid of this error
The Code is somethng like this
Set objRs = New ADODB.Recordset
objRs.CursorLocation = adUseClient
objRs.Open strSql, SQLCON, adOpenDynamic, adLockOptimistic
If (Not objRs.BOF) And (Not objRs.EOF) Then
objRs.Fields("FLAG") = 1
objRs.Update
VAR1 = objRs.Fields("0")
Else
MsgBox ("No Reocrds exist")
End If
Thanks in Advance for any help
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;
CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)
[Code] ......
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
View 4 Replies View RelatedI have converted a access database to sql server 2000 for some reason i keep getting this error base table not found.
I know the sql database is there and i also know the table is there. I'm not sure why i keep getting this error..
thanks
How can I easily identify who dropped a table?
View 8 Replies View RelatedI have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
View 1 Replies View RelatedI am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
I have an excel sheet that contain colummns as in a table in a sql database i want to transfer this data from the sheet to the table frombusiness logic code layer not from the enterprise manager by wizardwhat can i do?? ...please urgent
View 1 Replies View RelatedI need to get rid of the first char of data in a field f1 (in sql server)
I tried to use:
update myTable
set f1= substring(f1,1,f1.Len -1)
and get error "The column prefix 'f1' does not match with a table name or alias name used in the query.
Could anyone help? Thanks.
Hi,
How would I know that a dll component I built is in .NET framework 1.0 or 1.1??
annej
How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit
l've written it in layman's terms .Is it wise to use substring or they is a better method......
select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end
from table_Tran
Greetings.
I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.
However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )
Tipps, suggestions, help, all is very wellcome! :-)
DBCC SHOWCONTIG scanning 'TInsurance' table...
Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 1275
- Extents Scanned..............................: 225
- Extent Switches..............................: 224
- Avg. Pages per Extent........................: 5.7
- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]
- Extent Scan Fragmentation ...................: 74.67%
- Avg. Bytes Free per Page.....................: 520.0
- Avg. Page Density (full).....................: 93.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View 1 Replies View RelatedDoes anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
View 2 Replies View Related