Hello out there,
I am very new to SQL.
A user in my group created a batch of tables - now belonging to his id.
I have permissions to use all those tables - however I must type the owner/table to use those objects.
Is there any method of only typing in the table/object name instead of owner/table?
Thank you - in advance for your response <:)
I thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO
View 2 Replies View Related
Hi,
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object..
Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file)
So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
Thanks...
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
View 5 Replies View RelatedHi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies View RelatedI have created a profile object which is a list to store photoId into an array. (kinda like a shopping cart) which will persist in the list untill manually deleted by the user.I want to use the photo Ids stored in this object in my profile as a parameter for an sql select statement to be displayed into a data grid. What is the best way to go about this... and suggestions... I know you can use a profile property as a parametere in a sqldatasource but Im not sure how to do this for an array.HELP
View 1 Replies View RelatedMsg 225, Level 16, State 1
Cannot run query--referenced object (name NAME NOT RECOVERABLE) dropped during query optimization.
can anyone tell me how to solve this problem
thanks
Ali
I am brand new to SQL , and have been asked to come up with a solution for the following error when running a query - Invalid Object Name 'Despatches'
Is it as straightforward as it looks, it because the query is being run on an object that does not exist.
I'm getting the following error when I try to add a new record to the db:System.Data.SqlClient.SqlException: Invalid object name 'Pub_Points'. My table is called Pub_Points. I've had an insert working before.This is the SQL string that I'm trying to send:INSERT INTO Pub_Points ('PPName','Encoder_URL','Connect_Type','Archive','Creation_Date') VALUES ('fu','barr','local ','19/26/2005 13:35:27')Yet updating an existing record during the same run works fine:UPDATE Pub_Points SET PPName='foo ', Encoder_URL='bar', Connect_Type='remote ', Archive='0' Where ID='114' So it can't be a problem finding the table itself.Only two things I can see that are different:- the Insert procedure first creates and attaches the formatted date string (which you see already inserted in the Insert query)- the database has an auto-generating key field 'ID' (which you see being referenced in the UPDATE query)but I don't see how either of those things would give me this error.Ideas?
View 1 Replies View RelatedHey all,
I've a querie which inserts into a table, while joining on it. Something like:
insert into table1
(columnA,
columnB
)
select (columnA,columnB)
from table2
where columnA NOT EXIST(SELECT 1 from table1,table2 where table1.columnA!=table2.columnA)
As a rule of thumb, I avoid subselects and negative logic. However, sql2k seems to pick the fastest execution plan using this query, using a left anti semi join on that subselect. My question is this; what are the repercussions of inserting into a table when sql2k has already picked a query plan based on its stats? Is deadlocking possible? I haven't been able to find any evidence one way or the other. My objects are several million rows, so I am expecting some sort of side effect.
Any help would be greatly appreciated.
Regards,
-Kilka
Hi, I am trying to generate a report and I am using the query builder feature in SQL Server Reporting Services.
I just entered a simple query (actually the query builder generated it for me):
SELECT ID, pname
FROM jira393.project
and it seems to run fine but when I click "OK" to go to the next step, I keep getting an invalid object name error: 'jira393.project'
I don't know what is wrong right now since when I run it, it seems to display the correct information but trying to go to the next step displays that invalid object error.
Does anyone have any idea what is wrong?
Is it a way to return Jason object from SQL query?
This is my store proc that I like to return Jason object.
Create PROCEDURE [dbo].[Get_Payment_Gateway]
AS
BEGIN
SET NOCOUNT ON;
SELECT [Payment_Gateway_ID]
,[Payment_Gateway_Name]
,[Payment_Gateway_URL]
,[Payment_Gateway_Description]
FROM [CustomerPortal].[dbo].[Payment_Gateway] PG WITH (NOLOCK)
END
I'm new to SQL from Access and trying to run an update in the Query Analyzer. I created the following Select query in the Enterprise Manager and it works fine:
SELECT CVTotalswSalesperson.[Item Number] AS Item, CVTotalswSalesperson.[Sales ID] AS SalesID
FROM dbo.Inventory INNER JOIN
CVTotalswSalesperson ON dbo.Inventory.ItemNumber = CVTotalswSalesperson.[Item Number]
WHERE (dbo.Inventory.PlantID = 4)
I then tried to replace the Select with an Update in the Query Analyzer ...
UPDATE dbo.Inventory
Set SalesPersonID = CVTotalswSalesperson.[Sales ID]
FROM dbo.Inventory INNER JOIN
CVTotalswSalesperson ON dbo.Inventory.ItemNumber = CVTotalswSalesperson.[Item Number]
WHERE (dbo.Inventory.PlantID = 4)
and I get "Invalid object name 'CVTotalswSalesperson'."
What am I doing wrong? Thanks.
Hi,
I have a Dimension Dim_Customer with these fields:
CustomerKey (mandatory)
CustomerAccount
CompanyKey (mandatory)
I used the lookup object to compare if the data from the working table (Dim_WCustomer) is existing in the Dim_Customer.
The reference column is Customer Account. However, the records with CustomerKey 1 to 5, which have special function when linked to fact table, have no customer account (NULL) that's why i want to filter that out from the lookup.
This is the select statement in my lookup: Select CustomerKey, CustomerAccount where CustomerKey > 5. When I previewed this, i don't see the 1-5 customerkey. However, when I ran the data flow, it gives me this error:
OnError,,,Add new records to Dim_Customer,,,25/02/2008 6:43:52 PM,25/02/2008 6:43:52 PM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Customer'. Cannot insert duplicate key in object 'dbo.Dim_Customer'.".
When I removed the 1-5 customers in the database, it worked well. It seems "where" clause in lookup doesn't function well....
anyone who can help?
che
declare @MinVoter varchar(20)
declare @field varchar(20)
declare @sql1 varchar(100)
declare @tableName varchar(20)
set @tableName = '00221'
print @tableName
print '======================================='
select @field = ad_str1
from @tableName
where id_voter = @MinVoter
Server: Msg 137, Level 15, State 2, Line 15
Must declare the variable '@tableName'.
can someone fix this syntax.
Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
We have several databases but one is behaving differently in SQL Query Analyzer. Please assume we have selected the correct db in the the pull-down and assume database name is DB1 and table name is Table1
SELECT *
FROM db1.[table1]
works fine.
SELECT *
FROM [table1]
returns "Invalid Object Name Error".
This problem causes some complex queries not to work so I'd like to know if this db has some special setting that requires explicit db name in each query.
We have also tried..
use [db1]
SELECT *
FROM [table1]
It still gives the same error.
Any help would be appreciated.
Thansk
Notalian
I have a database owned by the sa. In the database are some tables owned by id1. When I login via SQL Analyzer as id1 and try to select from
the tables owned by id1, I get an error message 208 that the object does not exist. If I query 'select * from id1.table', I get my data. I thought that
if I owned the table I could always access it. Additional facts, id1 is defined as a login, id1 is defined as a user for the database with db_owner role.
Any ideas?
Thanks,
Jen
My applications group wants to do a table transfer from one server to another as a means of refreshing the information on the target on a scheduled basis. That means that reports could be running against the target table during the transfer. This has introduced erroneous reports because the object transfer initially truncates the table, then transfers the data. If reports are running during the transfer, they might retrieve no data or only part of a table. I wrote a procedure to make a copy of the table to be transferred, then transfer that new table to the target machine, then drop the target table, then rename the transferred table. Now, I have the slightly smaller problem of doing a DROP TABLE when users are accessing the table. I can't set the database to 'dbo use only' because there are other tables in the database that must be available. Is this really a replication application?
Cindy Rutherfurd
SQL Server DBA
ZC Sterling Corp.
Hi. I tried the following alter statement:
Code:
alter table userphotos
add constraint IX_userid unique (userid)
and got the following error:
Quote:
Msg 2714, Level 16, State 4, Line 3
There is already an object named 'IX_userid' in the database.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
I previously created the same constraint in a different table (using IX_userid). Is it not allowed to use the same naming
scheme for constraints in different tables? What am I doing wrong?
Thanks.
sorry for short subject, it allows very less characters, seems like varchar(50)
Hi,
My questions is...
How to identify an object using its objectid. In profiler and activity monitor we see objects like
1:12345
1:2345:453454
and so on...
I am sure they are not tables or indexes and I guess they are something related to pages, rowids etc.,
how do i interpret these objects (ids) and point back to the table (if it belongs to any table)
to summarize what I want
how to identify the type of object is being shown in profiler/activity monitor
how to identify to which table/index that object belongs/associated
where can i get object name for any object id that is shown in profiler/activity monitor. some time it shows some object ids in 100s of thousands for which i don't see any entry in sysobjects/sysindexes
Thanks in advance
Good Day guys, sorry i'm just new in SQL Server.
My problem is like this:
I want to pass Table Object to a declared variable and make View.
Usually we make like this:
"Select * from Table1"
I want like this:
"Select * from @Table1"
I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,
[Code] ......
I just found an odd bug and was wondering if anyone else has seen this.Any templpate file in the directory 'C:Program FilesMicrosoft SQLServer80ToolsTemplatesSQL Query Analyzer' that has the NTFScompression turned on (that is, colored blue in Windows Explorer) doesnot display in the object browser's template tab of query analyzer.Control-Shift-Insert works fine to insert a template, however.What's up with that?
View 2 Replies View RelatedHi,Using VB.net I have created a custom object (hope my terminology is correct here), it’s just a class that defines a few string, integer, and hash table variables. After creating an instance of this class and populating it with data I need to be able to store this instance of my object in a sql server data base table. How do I do this? I saw an article that used the image data type to achieve this (code was in java unfortunately) is this the correct approach. Could you please give me some code examples
Thanks
I have a problem with multiple databases on a server. I originally read another post that went into detail on this same error. After reading the post I decided to re-index the table to see if that would fix my issue. I started on Saturday by re-indexing a table on database 'a' - the process completed after that; I received another error on database 'b' on Monday i subsequently re-indexed all indexes on this database; today I have received the same message on database 'c' and 'd'. All post that I have read seem to point to an issue with only one database and not multiple databases. I have approximately 100 databases on this cluster - SQL 2000. The physical server logs only error shown is '000009ac.00001744::2007/12/29-15:12:58.848 ERR [CP] CppReadCheckpoint unable to copy file U:Disk12Partition1d61e68e-a60f-4ca6-b9ae-df3b893e4bb2 0000001.CPT to C:DOCUME~1mscsLOCALS~1TempCLS302F.tmp, error 3
000009ac.00001744::2007/12/29-15:12:58.848 ERR [CP] CppReadCheckpoint - Was that due to quorum resource not being up ???
' before corruption of database 'a'.
Here is the entire error if you think it will help. The error has been the same on all databases: 12/28/2007 6:23 AM A possible database consistency problem has been detected on database ‘ substituted_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'substituted_name'..
When I run the DBCC CHECKDB command I got these errors on Substite name database:
DBCC results for 'FVD_A'.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 165575628, Index ID 2. Keys out of order on page (1:12743984), slots 97 and 98.
There are 72920059 rows in 2281375 pages for object 'FVD_A'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FVD_A' (object ID 165575628).
DBCC results for 'FVD_CVS322'.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 181575685, Index ID 2. Keys out of order on page (1:7250519), slots 30 and 31.
I am at a loss. I believe we may have a bigger issue, but the SAN Administrator and Server Admin are saying there is nothing they can do. Do I have a true SQL Server issue or something else.
HELP!
Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :
I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks
I have an application where the columns in a datatable stored in a variable (as a dataset with one table in it) are dynamic. This means that the number of columns and their data types will vary. Also, I would like to access the column names. For these reasons it is not very practical to map the columns to variables.
Is there a way to use a Script Task within a For Each Loop (with ADO enumeration) to read the data row by row (presumably as a datarow) getting the column names and coloumn values by numeric (column) index?
Thanks,
*** Campbell
Hi,
I have placed one table object to show my datarows in reporting services report.
Depending upon the Parameter i want to change the location of this table in report. Is it possible or any other way we can do this. please help on this.
The purpose is to show the table horizontally centered after hiding the visibility of some column depending upon the parameter.
Thanks in advance.
With regards,
Sathish Raja S
After Upsizing a table to sql I linked to that table using access db
Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!
Can you help!
Hi all,
We have sql sever 2000 as our backend database system. on the front, we have vb medical application screen for users to do the data entry.
for 7th screen, it is assoicated with table Case_Custom. user enter data througth 7th screen to populate Case_Custom table.
There is one column called Case_Custom.CaseID ( a foreign key to to parent table Cases) was populated automatically instead of user input.
However, I could not find which stored procedure, views or trigger or application code to populate this field? I have queried INFORMATION_SCHEMA.ROUTINES, dbo.syscomments and did not find anything updating Case_Custom.CaseID.
I have contacted our app vendor and have got any answer from them yet.
Any ideas?
Thanks,
Jane