When I try to do a pull using a view I'm getting the following error message:
€œThe query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME.viewname ]€?
but when I do a 'select col1 from viewname' I get my data. The view has some inner joins included in it, could that be the issue?
NOTE: this is from my handheld device,
When I try to do a pull using a view I'm getting the following error message:
€œThe query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME.viewname ]€?
but when I do a select col1 from view I get my data. The view has some inner joins included in it, could that be the issue?
NOTE: this is from my handheld device, its not SQL Server related. I posted this earlier in this forum but it was moved to SQL Server.
I get the error on my handheld device and handheld device only
I have tried using the SqlClient class to connect to the database, but I haven't had much success. Here is my setup:
Desktop/Workstation has Windows XP, ActiveSync, .NET Framework 2.0, and SQL Server 2005.
The production Workstation will have 2000; so I do want to ensure SQL Server 2000 compatibility with my solution.
Handheld device is an Intermec 751g with docking station, hooked to PC host using USB connector cable and ActiveSync, Windows CE 4.2, .NET Compact Framework 2.0 with SP1 and latest patch.
Development tools consist of Visual Studio 2005 and C# .NET language.
Note that when I attempt to ping my desktop from the handheld command prompt I get "transmit failed, error code 11010".
Also, I am able to access the Internet from my handheld device.
My error is encountered when I execute this fill code:
int returnValue = this.Adapter.Fill(dataTable);
Please note that all of my code is generated by the VS 2005 IDE at this juncture, as I am simply trying to get the plumbing to work.
Here is the error that I encounter:
System.Data.SqlClient.SqlException was unhandled
Message="SqlException"
Class=20
LineNumber=0
Number=6
Procedure="ConnectionOpen (Connect())."
Server="09Q-GHAZEL2\SQL2K5"
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError()
at System.Data.SqlClient.SqlInternalConnection.OnError()
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Connect()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.AdventureWorksDataSetTableAdapters.ContactTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Program.Main()
Thanks again for your insight. Sincerely, -Dotnetfellow dotnetfellow@yahoo.com
dear friends,
It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...
Thanks
Hello all,
does anyone know if it's possible to call a stored procedure from a view.
Thnx,
Patrick
Is it possible to call a soted procedure in view?.
If so, how to call?.
Please post syntax.
Thanks
I am working on SSRS. Based on parameter value I needs to call the related view. Below are my current expression query. but it has some error.
=IIF(Parameters!RacServiceOfferingsOnly.Value=0, SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_N WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate) AND
(:TransferType IS NULL OR :TransferType = "Direction"),SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_Y WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate))
I'm able to pull down all my tables needed, but I need to push a table from my handheld database that was not pulled. Is this possible?
The table is used to save data on some input screen on my handheld application and I need to push it to a 'staging' table on my main sql server before it is inserted into the correct tables.
Is there a way to do this?
Hi,
While creating an indexed view with the command :
create unique clustered index idx_atrid on account_transactions (policy)
there is a check constraint on the policy column of the tables used in this view.
The following error is encountered
Cannot index the view 'test.dbo.account_transactions'. It contains one or more disallowed constructs.
Can anyone help?
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
View 2 Replies View RelatedI have some issue when replicating a view (transactional replication).I have following objects on publisher:
Table: [Metadataschema].[Entity]
View: [dbo].[EntityLogicalView]
the view definition is:
create view [dbo].[EntityLogicalView] as (SELECT * FROM [Entity] WHERE OverwriteTime = 0)
this is what I get when I run "Script View as Create". The same query is used by the replication.Unfortunately this query fails on the subscriber "Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber". I cannot even runt his query manually on the subscriber, it only works when I change it like this:create view [dbo].[EntityLogicalView] as (SELECT * FROM [Metadataschema].[Entity] WHERE OverwriteTime = 0) Is there any way to get this working? Can I change the creation scripts used by the replication or force publisher to include schema names in all objects?
Hello,
I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:
(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))
it always return '0'.
Here is a very cut down version of the view, only selects the uid! from a single table
IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL
DROP VIEW GetMessageQueueDetails ;
GO
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW GetMessageQueueDetails
WITH SCHEMABINDING
AS
SELECT Uid
FROM dbo.MyTable
GO
I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :
"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."
Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?
Or am I doing something else wrong?
Can anybody offer any help?
Thanks
Hello,
We are in need of some help around the use of SQL Server 2005 Mobile Edition.
The problem comes in when we have to import a large list of equipment ID€™s into the database.
The way that things work today is that the database only runs on the handheld computer, so we are having to process a large amount of data on the handheld therefore taking a lot of time.
If there were a way to have a €œseed€? database on the server side (running on Windows Server 2003 or similar) and import all of this data on the server side it would be more efficient and require less time.
Once the data was imported on the server side we can simply transfer this seed database down to the handheld
Could you please throw some light on the above queries.
Thanks in advance.
Jeeves
Hi;
We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.
Any advice on this issue would be appreciated
Thanks,
Hakan Gümüs
I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
View 4 Replies View RelatedI created a view with a simple join query
there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as
"select orderid from joins where orderid=NULL"
Query :
create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid
We have a maintenance plan in place for updating the statistics on daily basis. Now, i would like to remove the view from maintenance plan. How can i remove that from Update statistics task?
View 8 Replies View RelatedHi,
I have couple of Web pages linking to my RDLs using report viewer Web controls. I have no problem running and viewing them in VS2005 debug mode and report manager until I published them to local folder and set the local folder as an application on my IIS7. When running the published version of the Web pages, all my calendar image buttons (which happen to be part of the report parameter fields) turned to red 'X':
http://dyzimw.bay.livefilestore.com/y1pwxE9mLpX4JV7e438WA4DeGwu6YHQOkRoC20r_EhQ5uoXkt15dqs99LmiDSTauSvUCno9HnOwYKR34rPpFYA_lQ/ssrs.jpg
When clicking the View Report button, the animated progress icon not appearing at all. Before the occurance of this problem, I did face the problem whereby the 'NT AUTHORITYNETWORK SERVICE' does not have enough permission to view the report. After referring to some threads, I finally be able to get rid of this error by assigning Browser role to 'NT AUTHORITYNETWORK SERVICE' from the report manager. I wonder could this solution be related to the problem that I'm facing now. Any help would be appreciated.
Thank you.
Regards,
Antonio
Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
I've written a T-SQL stored procedure that I want to call other stored procedures. The opening part of it looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spArchive] @dtArchiveBefore DateTimeASEXEC spArchiveAHD @dtArchiveBeforeGOEXEC spArchiveContract_History @dtArchiveBeforeGOEXEC spArchiveContracts @dtArchiveBeforeGO But I'm getting this error repeatedly: Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@dtArchiveBefore".I've searched for this term but can't figure out what's wrong with my code.Any ideas?Robert W.
View 4 Replies View RelatedHi, There,
Do you know when should I use extend sp to call a dll file, and when should I use sp_OACreate to call dll. It seems only dll created by C++ can be called from extend sp, how about VB created dll?
Thank you very much
Tony
MS SQL 2005
I developed several SP that update tables
If I execute them one by one in the SQL Sever Management Studio, they work ok
Now,
I want to execute them all inside another SP
I wrote it (attached code)
Execute it and give me a message that runs successfully but that is not true
May anyone of You tell me the right syntax to do it?
[Code]
USE REPORTES
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE dbo.Astral_sp_Acumula
AS
--
EXECUTE [REPORTES].[dbo].[Astral_sp_AcCpasArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcDevArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcVtasArtV]
GO
-- More sp executions
[Code]
Thanks
JG
i have made a .sql file in which i want to call another .sql file
plz tell me the syntax for it
Hi all,
I have 2 tables like that
CREATE TABLE tab1 (
id int IDENTITY (1, 1) NOT NULL ,
master varchar (50) NULL
) ON PRIMARY
GO
CREATE TABLE tab2 (
id int IDENTITY (1, 1) NOT NULL ,
student_name varchar (50) NULL ,
master_id int NULL
)
Now i want to create a Sp
which is
create proc inner_sp(@s1 varchar(50))
as
insert into tab1(master) values (@s1)
and i want to call it from another sp
create proc outer_sp2(@s2 varchar(50),@s1 varchar(50))
as
declare @r int
exec inner_sp @s1
set @r=Scope_Identity()
insert into tab2(student_name,master_id) values(@s2,@r)
exec outer_sp2 'Vivek1','Test'
But in table tab2 master_id column contain NULL value....
Please fix it...
Ujjwal
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies View Related