Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?
Thanks =)
I set up a test database to try and understand table partitioning. Since deleting the partition schemas, functions and all relevant info on the database properties page, I am getting the following errors during the weekly database integrity check:
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:223) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Can anyone suggest why this might be happening?
Thanks
Hi experts,
i am asked to check data driven errors while importing data from an excel workbook to a sql server table.
what are the data driven errors expected and how to introduce that in this sql code.
SELECT a.* FROM OPENROWSET(BULK 'Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database = C:DataExTestVista_Logo_Product_List_20080204.xls',
'SELECT * FROM [RAW Data$]'
, FORMATFILE = 'D:format.fmt') as a
Thanks in advance.
I have written a utility that checks our stored procedures forpotentially incorrect code (eg = null as opposed to is null, unusedvariables/parameters etc). It also ensures that parameters/variablesetc match our naming conventions and data types/sizes are the sames asthe corresponding type in the database (if appropriate)Are there any other utilities that do this sort of thing?
View 1 Replies View RelatedI can't get the download to work...i've tried regular sql server express 2005, the express for sp2 and the express with advanced services. I keep getting this message after the native client and support files are installed.."This application has failed to start because XOLEHLP.dll was not found. Re-installing the application may fix this problem."Then right after that message is says... "Failed to load SQLSpars.dll"
View 1 Replies View RelatedI recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
how to check the errors when a database is configured in Always on in sql 2012?
View 1 Replies View RelatedI am not a DBA but have responsibility for a particular MSSQL 2008 R2 file server running a particular application.how to solve a database consistency check problem.The database fails dbcc checkdb with multiple 8903 errors. Unfortunately this was not discovered until well after any good backups were deleted. The good news is that the DB otherwise seems fine. We have experienced zero problems with the DB or the applications. Running the checkdb with the "repair_allow_data_loss" option does not fix the problem.
However, I would still like to fix the problem. Using a popular SQL recovery product I am able to recover the database.The original, vendor designed and supplied DB, has 2 file groups, and three files (MDF, NDF, LDF). The output of the recovery process produces 1 file group and 2 files (MDF and LDF). Vendor says they cannot support me since the recovered DB is 'non-standard' according to their design.
I am able to set up a new, blank version of the vendors database on another dev system with the proper file and filegroup structure. How can I get the data moved/copied from the recovered (MDF/LDF) database into the dev database (MDF, NDF, LDF). I've tried the import/export function but it fails (I can rerun and give details if necessary).
I am using execute sql task and my sql statement contains
EXEC test ?
procedure test has 2 parameters. I added parameters using parametermapping tab. When I run i get an error message
Multiple-step OLE DB operation generated errors. Check each OLE DB status value
Hi all,
We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit...
Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.."
Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed..
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The
external request has completed.
Start Time: 2007-11-09 09:41:25
End Time: 2007-11-09 09:41:25
End Log
Error: 2007-11-09 09:41:25.95
Code: 0xC0202009
Source: Package_name loader Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error
s. Check each OLE DB status value, if available. No work was done.".
End Error
Log:
Name: OnError
Source Name: Data Flow Task
Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0}
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
. The AcquireConnection method call to the connection manager "SourceConnection
Excel" failed with error code 0xC0202009. There may be error messages posted be
fore this with more information on why the AcquireConnection method call failed.
Any help..?
Hi,
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
dbinit.AddProperty(DBPROP_INIT_CATALOG, (const char *)bDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (const char *)bServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_TIMEOUT, (short)150);
hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
if (FatalError(hr, "db.Open", buf))
{
*iErrorCode = hr;
return S_OK;
}
Any help, appreciated.
Thanks,
Satish
can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates lkie this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX
Hello,
I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.
- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of
"output column "ErrorCode" (79)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...
Any help would be appreciated...
Thanks,
David
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
View 2 Replies View RelatedHi ALL,
Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
Please help me...........
Thanks,
Pracheer
I have procedure. say for example...
begin
select * from table_abc
end;
suppose, i get errors i.e table not found etc etc. I want to save the errors in a file or even in a table will do.
Paul writes "Hi,
We have run the following command...
DBCC CHECKDB ('db_name')
... and 12'622 consistency errors were found (all on the same index on the same table).
So next we ran...
DBCC CHECKTABLE ('table_name', REPAIR_ALLOW_DATA_LOSS)
... and this fixed SOME of the errors, but not all...
12586 consistency errors in table
Does anyone have any ideas?
Just to confirm this is SQL Server 2000.
I do have both of the results logs available if that would help.
Many Thanks,
Paul."
Hi all,I am in the process of creating a page that checks to see if a particular user exists in the database and if it does then send to welcome.aspx if not then accessdenied.aspx. The userid is requested from the query string.I have done some research and cannot find anything directly related, I have tried to add bits of code into what i think is the right place but I dont think what i am doing is correct. Can someone help and show me where my code is going wrong? Also is there a better/more efficient way to do what I am doing?Thanks in advance. default.aspx.csusing System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { string UserID = Request.QueryString["uid"]; //string TransferPage; if (UserID != null) { //initiate connection to db SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); string sql = "SELECT COUNT(*) FROM members WHERE UserID = '" + UserID + "'"; SqlCommand query = new SqlCommand(cmd, test); int count = (int)query.ExecuteScalar(); int aantal = -1; // some default value if can't insert record if (count == 0) // no existing record for username { Server.Transfer("accessdenied.aspx"); } else { Session["UID"] = UserID; Server.Transfer("welcome.aspx"); } } }}
View 7 Replies View RelatedHi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
View 1 Replies View RelatedHi...
Is there any way to check previous row in SQL Query?
I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes
I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.
Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD
In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.
Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.
Hi,
Could someone shed some light on the best way to check a table recordcount before run of DTS pkg.
We have a table that is populated in MS SQL from an Oracle database, and the contents of that table are the basis of a DTS package that we run.
Sometimes Oracle transfer bombs out and the table is empty but our process runs anyway. It screws everything up.
Can we check the recordcount of a table on the server, and if the recordcount > 1 then the process runs, if not, then the process aborts?
Thanks,
Dominic
Hello, everyone:
I got a new database from client. How to check which tables are seed table and truncatable? Thanks
ZYT
Hi,
This my first time using the link server to read the files directly from within SQL2005, so issues the following link:-
EXEC sp_addlinkedserver
@server = N'MYVFPSERVER', -- Your linked server name here
@srvproduct=N'Visual FoxPro 9', -- can be anything
@provider=N'VFPOLEDB',
@datasrc=N'"C:PROGRAM FILESMICROSOFT VISUAL FOXPRO 9Samplesdata estdata.dbc"'
After that i can open query and do the import issues, but how can check if the table exists before issues the query.
Best regards
Hi,
I can check the existency of the attribute in particular table. But is it possible for me to check whther my table is exist or not in that particular database?
I am using JSP
Please help!
thank you
Hi,
i wanna know how can i check a table has datas inside, because i wanna use delete from table and if table is empty i dont wanna run this statement.
thanks in advance
Hello, there,I am trying to find out if a table is used by any of the storedprocedures or functions.I can generate all the scripts and look for it. But is there an easyway?THXJohn
View 4 Replies View RelatedHi SQL Professionals ,
I have a requirement like this,
I have a Live Database as well as the Test database with the same Definition
now i need to write a SQL to check identity of these two database tables,
i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ?
In the same way how do i check for the Stored peocedures ?
how do i write a SQL for this ?
regardssuis
i have large ssis package which is scheduled daily. The package uses a table on sql server as source and because the execution time is pretty long i just want to execute the whole package only if the data in the source table has changed since the last execution. unfortunatley in the source table there is no timestamp or something like that available. so i thougt about querying some logging information. is there a system table where the time of the last table transaction is stored?
View 7 Replies View RelatedHi all!
I need to check data changes in some tables from specified date. Can it be done without triggers for each table?
SQL Server Management Studio always says "Data was changed" if another user updates data and you try to update old version. How it checks data modification date? I found only this:
USE [ScheduleDB]
GO
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TTest]') AND type in ('U')
but it can only show structure modification date.
CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))
[Code] ....
I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.
What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.
My failed attempt:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
[Code] ....
We recently restored a database from a...
windows 2000 x86 sp4
sql 2000 enterprise x86 sp4 stand-alone
all drives hosting sql databases are SAN attached, write-caching is enabled
server, to a...
windows 2003 enterprise x64 sp1
SQL 2005 enterprise x64 sp2 2-node active/passive
all drives hosting sql databases are SAN attached, write-caching is enabled
server.
We are experiencing data corruption on different tables in different databases. So far, we have always been able to quickly resolve the issue by simply running a DBCC DBREINDEX against the table. However, we are unable to resolve the issue with the latest occurrence. Any help would be greatly appreciated. Here is the output (or partial output in some cases) of various commands:
DBCC CHECKDB ('DATABASE_NM')
DBCC results for 'main_notes'.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0.
Msg 8929, Level 16, State 1, Line 1
Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0)
There are 1 rows in 1 pages for object "main_notes".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587).
** NOTE ** Index_ID 1 is the clustered index which is on a single-column PK of int type.
DBCC CHECKTABLE ('Main_Notes')
DBCC results for 'main_notes'.
Msg 8961, Level 16, State 1, Line 2
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0.
Msg 8961, Level 16, State 1, Line 2
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0.
Msg 8929, Level 16, State 1, Line 2
Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0)
There are 1 rows in 1 pages for object "main_notes".
CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DATABASE_NM.dbo.main_notes).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 2487, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 34174, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC DBREINDEX ('Main_Notes')
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
UPDATE STATISTICS Main_Notes WITH FULLSCAN
Command(s) completed successfully.
select * from main_notes
Msg 7105, Level 22, State 6, Line 1
The Database ID 17, Page (1:2487), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
select * from main_notes (nolock)
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
hi
Here are the two tables again.
1)PATIENT(PATIENT_ID,NAME,CITY)
2) DISEASES(DISEASE_ID,NAME)
I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.
Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.
i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.
please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.
your help and insight is highly appreciated.
regards
Raju
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
View 2 Replies View Related