I am work on SQL Server 6.5
When I want to drop a view then appear error message :
"Msg 3701, Level 11, State 1 Cannot drop the view 'EUL_ODBC_SCHEMAS',
because it doesn't exist in the system catalogs."
Has anybody else experienced this, and do you know what is causing the warning?
Thanks in advance for any replies.
Hello I am trying to run a select statement in Java against SQL Server 2K. But I keep getting an error saying that the table doesn't exist.
Is this a problem with my SQL statement?
select * from counting
I also tried
select * from dbo.counting
my connection url is
jdbc:microsoft:sqlserver://IS-0081526:1433;databaseName=bail;
I can run these queries from Query analyzer I am able to run them with the same account and login.
Hello!
Hopefully someone can help me.
I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.
Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK
BackupMedium::ReportIoError: read failure on backup device '\XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).
In my package, the first two tasks are as follows:
1)
IF EXISTS (SELECT name FROM sysobjects WHERE name = '<tablename>' AND type = 'U')
BEGIN
drop table dbo.<tablename>
END
2)
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = '<tablename>' AND type = 'U')
BEGIN
CREATE TABLE [dbo].[<tablename>](
col1,col2,col3......
)
end
so why am I getting the following error when trying to run the package?
Error Message
===================================
Package Validation Error (Package Validation Error)
===================================
Error at Get Fenics data to table [Load into FFFenics table [155]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.FFFenics'.".
Error at Get Fenics data to table [Load into FFFenics table [155]]: Failed to open a fastload rowset for "[dbo].[FFFenics]". Check that the object exists in the database.
Error at Get Fenics data to table [DTS.Pipeline]: "component "Load into FFFenics table" (155)" failed validation and returned validation status "VS_ISBROKEN".
Error at Get Fenics data to table [DTS.Pipeline]: One or more component failed validation.
Error at Get Fenics data to table: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount
[code]....
When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?
Thanks!
hi i am running this program
and its giving some problem while execution.
When the program tries to create the table expt1..its says
Msg 2714, Level 16, State 1
There is already an object named 'expt1' in the database.
and if i try to drop the table expt1 it says
Msg 3701, Level 11, State 1
Cannot drop the table 'expt1', because it doesn't exist in the system catalogs.
How should I resolve this problem...?
==========================
Declare @cmdstring varchar(255)
Declare @filename varchar(32)
DECLARE @v datetime
SELECT @v = GetDate()
Create table expt1(GMSKEY char,CONTRACT char,ORIGINALCONTRACT char,STATEMENTID char,
STATEMENTDATE char ,partner char ,ADDRESSSEQ char,BOOKINGAMOUNT char,
BOOKINGCURRENCY char,TAXEDIND char,TAXTYPE char,
DUEPERIOD char,ACCOUNTNUMBER char,PRODMONTH char,PRODYEAR char,BOOKINGUNIT char,
BOOKINGQUANTITY char,STATEMENTSEQ char,COSTID char,DELIVERYMETHOD char)
SELECT
DETAILTRANS.GMSKEY,
DETAILTRANS.CONTRACT,
DETAILTRANS.ORIGINALCONTRACT,
DETAILTRANS.STATEMENTID,
convert(varchar,DETAILTRANS.STATEMENTDATE,106) statementdate,
DETAILTRANS.partner,
DETAILTRANS.ADDRESSSEQ,
DETAILTRANS.BOOKINGAMOUNT,
DETAILTRANS.BOOKINGCURRENCY,
DETAILTRANS.TAXEDIND,
DETAILTRANS.TAXTYPE,
TITLECT.DUEPERIOD,
DETAILTRANS.ACCOUNTNUMBER,
DETAILTRANS.PRODMONTH,
DETAILTRANS.PRODYEAR,
DETAILTRANS.BOOKINGUNIT,
DETAILTRANS.BOOKINGQUANTITY,
DETAILTRANS.STATEMENTSEQ,
DETAILTRANS.COSTID,
TITLECT.DELIVERYMETHOD,
'' RECEND
INTO expt1
FROM DETAILTRANS,TITLECT
where DETAILTRANS.TITLEID=TITLECT.TITLEID
AND DETAILTRANS.CONTRACT IS NOT NULL
AND DETAILTRANS.CONTRACT!='N/A'
AND DETAILTRANS.EXPREV='E'
AND DETAILTRANS.PRELIMORFINAL='F'
AND DETAILTRANS.postedDATE IS NULL
ORDER BY DETAILTRANS.GMSKEy
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
Select @cmdstring = 'bcp GM_PROD..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
drop table expt1
===========
Thanks
Harish
I have a simple question I hope someone can verify for me. At my company we are sending 2 different response messages. One is a commit response and one is a finalize response. Currently the service that we are sending the responses to only cares about the commit but later on in development they will care about the finalize response. The service that we are sending the finalize message to simply does not have a finalize message type and if running the profiler on it an error occurs: "This message could not be delivered because the message type name could not be found." And the message is lost. This doesn't really matter to anyone because at the time no one cares about the message. But I want to make sure that that is expected. After all service broker is supposed to be reliable. So my question is: If a message_type doesn't exist on the target side, will the message disappear and is that normal service broker behavior. I would hate to one day find the transmission queue with thousands of messages in it.
View 5 Replies View RelatedI have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.
Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??
Hi, I not sure where my connection string is incorrect. I cant' access the SQL Server through Query Analyzer but the program can't. here is my connection string."ConnectionString" value="Data Source=xx.xx.xx.xx;Initial Catalog=xxx;UID=xxx;PWD=xxx;MAX POOL SIZE=5">
In the Query analyzer, i choose the SQL Server Authentication and provide login and password. I can access the database. Also, i can access the database at local computer. But, i can't access the database by other pc throught network.
do i need to provide any other in the connection string?
regards,
I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database?
Thanks!
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.
Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.
Product Location Time Sales
A X 1/04 200
B Y 1/04 100
A X 2/04 300
In the cube, if you were to look at product by location for just 2/04, you would see:
Product Location Sales
All Loc 300
A X 300
Y
All Loc
B X
Y
How do you get rid of the zero's or combinations that don't exist?
Thanks,
Doug
How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"
I have a stored procedure that I am writing that is giving me trouble dropping a temp table in SQL Server 2005. I have to use the SQL in Query 1, marked below, to get the temp table. I can find the table but I get the error listed in this message's subject. If I try to run DROP TABLE #ZipLatLong I will get an error telling me that the table already exists but then I can run CREATE TABLE #ZipLatLong ( ZIP INT, Latitude DECIMAL(18,6), Longitude DECIMAL(18,6)) and that will work...
In short, SQL Server 2005 is not letting me drop temporary objects. I am getting return messages telling me that an object already exists in the database by the name I am trying to delete and then it will let me create a table with the same name...
Figure 1
USE TEMPDB
GO
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'tempdb'
AND TABLE_SCHEMA = USER
AND TABLE_NAME LIKE '#ZipLatLong%'
GO
DROP TABLE #ZipLatLong
GO
Hi,
I am trying to have my old DTS packages working on SSIS. The first basic package I have, simply copied all tables/views and sp from one database to another one, quite like a backup/restore.
I converted this DTS package onto dtsx, tried to run it in VS2005, and it appears that the package just bcp from source to destination without dropping the dest table first.
I have checked that the parameter dropObjectsFirst was set to 'true' and ExistingData was set to 'replace'.
From the profiler, it seems that the data is appended each time in the table.
I am sure there's a small workaround to make that package working!
thank you in advance,
Hi all:
for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi");
if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like :
if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database");
I can't imagine I got the bool variable via exception...
thanks to all..
hello can anybody help me in this issue:-i got two sets of connection string modules, let us say (1) working (2) not working, and here is the code for the two sets:-set (1) which is working fine:-
private void OpenData(){System.Data.DataTable dt = new System.Data.DataTable();
// The connection string was not originally hardcoded but for some reason it was staying around when passed
// in through the constructor. It was always blank.
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(@"data source=localhost;initial catalog=truckorder;Integrated Security=SSPI;");
//System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(_cnString);
cn.Open();
new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM dealers WHERE DealerWeb like '%" + _url + "%'", cn).Fill(dt);
if(dt.Rows.Count > 0)
{_dealerid = Int32.Parse(dt.Rows[0]["dealerid"].ToString());_dealername = dt.Rows[0]["dealerName"].ToString();_dealerparent = Int32.Parse(dt.Rows[0]["dealerParent"].ToString());_usedMessage = dt.Rows[0]["usedMessage"].ToString();_rentalMessage = dt.Rows[0]["rentalMessage"].ToString();}
cn.Close();}----------------------------------------------------------------------------------------------------------------------------------------------------------------------set (2) not working fine:-private void OpenData()
{
System.Data.DataTable dt = new System.Data.DataTable();System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();conn.ConnectionString = "integrated security=SSPI;data source=FR011PC;persist security info=False;initial catalog=TruckOrder";
try{conn.Open();new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM dealers WHERE DealerWeb like 'yalevictoria.com.au'", conn).Fill(dt);
if(dt.Rows.Count > 0){_dealerid = Int32.Parse(dt.Rows[0]["dealerid"].ToString());_dealername = dt.Rows[0]["dealerName"].ToString();_dealerparent = Int32.Parse(dt.Rows[0]["dealerParent"].ToString());_usedMessage = dt.Rows[0]["usedMessage"].ToString();_rentalMessage = dt.Rows[0]["rentalMessage"].ToString(); _dealerWeb = dt.Rows[0]["dealerWeb"].ToString();}}
finally
{conn.Close();}THE DIFFERENCE IN BOTH THE SETS IS :- The Connection String (if you look at carefully) the one which is working has localhost and the one which is not working is having FR011PC (this code is working fine on FR011PC machine name and SqlServer Name) BUTwhen i transfer FR011PC code to the localhost machine, I mean i changed to datasource from FR011PC to localhost and run its giving me SQL SERVER DOESN'T EXIST OR ACCESS DENIED.Can any body help me on this issue has this is urgent for me please...................
I have TABLE1 and TABLE2
TABLE1 (ref,RESOURCE01,RESOURCE02,RESOURCE03,RESOURCE04,R ESOURCE05)
TABLE2 (ref,RESOURCE01,RESOURCE02,RESOURCE03,RESOURCE04,R ESOURCE05)
If TABLE2 has a data in resource 01-05 that isn't in resource01-05 of TABLE1 then I want to added it to the next free slot where ref is the unique key.
Note TABLE2 doesnt have to have all of TABLE1
eg if
TABLE1
01 AAA BBB
02 CCC DDD EEE
03 AAA DDD
TABLE2
01 AAA CCC
02 CCC DDD EEE FFF
03
I would like TABLE1 to be updated to appear like
TABLE1
01 AAA BBB CCC
02 CCC DDD EEE FFF
03 AAA DDD
Where do I even start here?
Hello,few users have problems with our application(ADP/MsAcces2000+SqlServer2000). Whenthey want to open a report which source is a stored procedure, they getmessage that "record source specified..... do not exist" - of course,it's not true.More details:- other users don't have this problem- they have WinXP- all users have the same permissions to SQL objects- I've checked Microsoft KB(http://support.microsoft.com/kb/243532/en-us) and they write about"dbo." before source name - I use that, so it's not a solution. Whatmore, in application there are lists which have SP as row source andwithout "dbo." and it works. (In lists a row source is defined as:"exec <sp_name>", in reports as: "dbo.<sp_name>").- computers have all service packs, new version of MDAC, etc.- I asked another user, who doesn't have this problem on his machine,to login on "bad" machine and he couldn't open reports too.Maybe someone could help? because I don't have more ideas.
View 1 Replies View RelatedI have a non-clustered index on a table. If I rebuild or reorganize it in SQL 2005, the total fragmentation percent reported by properties/fragmentation on the index stays at 33%.
Why doesn't the fragmentation go to 0% ?
If I totally drop/create the index, starts even higher, but beorg or rebuild simply goes to 33%. This even if using with use temp db for sort option.
I'm having some very frustrating 'syncing' problems with my report (currently in dev in visual studio) and what actually exists in the database. To start, I had my report definition open, and realized I needed a new sproc. So I went over to the db and created a new sproc, lets call it "Test1". After making a new dataset in ssrs, I can't seem to get SSRS to realize the stored proc actually exists. I continually get the error:
"The stored procedure 'Test1' doesn't exist. (System.Data)
I've shut down visual studio, re-opened, etc. Same thing. It looks like visual studio has some cached version of sys.procedures that only refreshes when the bloody thing feels like it. Any ideas?? I've been doing this for awhile, and I've minded the connection string, correct sproc syntax, etc. It runs fine on SQL Management studio.
Another, similar problem - When adding in a new column to a sproc, the dataset doesn't seem to pick up on it, no matter how many times the "refresh fields" button is hit.
I have a T-SQL Statement Task to create table every time the package run. It doesn't return error but it doesn't drop the table either. The data is appended every time. The code is working fine in SQL server query window.
IF OBJECT_ID (N'M020_Vendor', N'U') IS NOT NULL
DROP TABLE M020_Vendor
GO
SET ANSI_NULLS ON
GO
[code]....
After the incremental process and full process, SSAS doesn't drop the index files #.xxx.fact.map and #.xxx.fact.map.hdr in the file.0.dim folder. We now have all the versions from 3 to 5000 sitting in the folder. The DBA team only found this when the disk is running out of space recently.
We've already check the account running the SSAS has local admin to the server.
Is there any config setting that might cause this issue? If not, what could it be causing this issue.
I'd like to get some ideas for the following:
I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column)
So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it.
I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better.
Thanks!
I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like:
[AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
I'm trying to create a local copy of a popular CRM database calledSalesforce.com. Many of the tables in the DB have FOREIGN KEYreferences that I want to preserve, but I've run into a chicken and eggproblem. Table "A" has a reference to table "B," and table "B" has areference to table "A." So I can't CREATE one until the other exists.Is there a way to disable these checks until I've created all theschema?Here's what I see (error first, then SQL that caused it):Server: Msg 1767, Level 16, State 1, Line 1Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table'User'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES"UserRole"(Id), "RollupDescription" varchar(80),"OpportunityAccessForAccountOwner" varchar(40),"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime);CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,"Username" varchar(80), "LastName" varchar(80), "FirstName"varchar(40), "CompanyName" varchar(80), "Division" varchar(80),"Department" varchar(80), "Title" varchar(80), "Street" text, "City"varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime, "UserPermissionsMarketingUser" bit,"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);
View 2 Replies View RelatedI created a simple SSIS package that takes a Flat File Source (CSV file) and Imports it into a OLE DB Destination ([TestCSVImport].dbo.Table1). I have other CSV files I'd like to import, but I don't want to import entries where column "ordereID" (PK) are the equal. Just want to import the new data found in the CSV files. I tried adding a Lookup in-between the Flat File Source and the OLE DB Destination, but I'm not sure how to accomplish only importing new data.
View 2 Replies View RelatedSystem.Data.SqlClient.SqlError: Cannot open backup device '\.Tape0'. Operating system error 5(error not found). (Microsoft.SqlServer.express.Smo)
i have only one sql instance and tape is istalled successfully.
please help me to find solution for this error.
Thanks,
I have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.
Any ideas please?
The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.
Many Thanks
SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
View 4 Replies View RelatedWe have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
hello,
im creating a sp that creates a view from a query then bcp to csv file.
my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail
DROP IF EXISTS v_participantTrades
now, does that syntax not work for views?