SQL 2005 Breaks Writeable Multi-table Views
Sep 25, 2006
Hi,
I have some simple views that bind to an Access forms for use in the detail side of Master/Detail forms.
The views are of the style:
SELECT *.OrderDetails, Products.TotalUnitsInStock AS UsefulReferenceInfo
FROM OrderDetails LEFT OUTER JOIN Products ON ..........
Using a database in SQL 2000, opening the view to directly insert values into the OrderDetails columns works perfectly, and Access correctly determines that there is an IDENTITY column that is the Primary Key. Insert works perfectly, form work perfectly.
Using the same SQL script to build a database in SQL 2005 the view doesn't detect that the column is an IDENTITY column so any attempt to insert data fails with the error "Invalid input parameter values. Check the status values for detail." Insert fails, form also doesn't work.
I have tested this in Access 2002, 2003 and 2007 B2TR and all give the same results so it seems to be a breaking change in SQL 2005.
It is easy to see when there is going to be trouble because the tell-tale '(Auto Number)' or more recently '(New)' text is missing from the appropriate identity column.
This is fairly inconvenient and breaks quite a lot of my Master/detail forms that use this design pattern.
Can anybody from the SQL team help shed some light on this for my.
Cheers Simon
View 11 Replies
ADVERTISEMENT
Aug 31, 2007
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
View 4 Replies
View Related
Sep 8, 2015
I am new to programming trying hard to learn. I made a code for Views in mssql for the 2 tables below:I am wondering if I could make it 3, 4 or more tables at the same time. How can incorporate in the following codes I made.
Create View vwEmployeeInfoByGender
as
Select FirstName, MiddleName, FamilyName, Gender, BirthDate,
MaritalStatus
from tblEmployeeInfo
join tblGender
on tblEmployeeInfo.GenderID
= tblGender.GenderID
View 26 Replies
View Related
Jun 30, 2006
Hello,
I'm working on a custom dataflow destination component. It makes use of the External Metadata Collection. I also use Custom Properties with the external metadata collection.
When I open the destination component using the Advanced Editor, and select an External Metadata Collection and change the Custom Property it always changes back to the original value.
Additionally the method SetExternalMetadataColumnProperty never gets called.
Here is a little Test Component that surfaces the problem:
[DtsPipelineComponent(ComponentType=ComponentType.DestinationAdapter, DisplayName="Test Destination")]
public class Class1 : PipelineComponent
{
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
ComponentMetaData.InputCollection.RemoveAll();
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Name";
input.ExternalMetadataColumnCollection.IsUsed = true;
IDTSExternalMetadataColumn90 ext = input.ExternalMetadataColumnCollection.New();
ext.Name = "ExtName";
IDTSCustomProperty90 customProp = ext.CustomPropertyCollection.New();
customProp.Name = "Custom Property";
customProp.Value = "Hallo";
}
public override IDTSCustomProperty90 SetExternalMetadataColumnProperty(int iID, int iExternalMetadataColumnID, string strPropertyName, object oValue)
{
return base.SetExternalMetadataColumnProperty(iID, iExternalMetadataColumnID, strPropertyName, oValue);
}
}
Any ideas how to make that work?
Georg
View 2 Replies
View Related
Sep 14, 2007
Greetings all:
I'm having a bit of an issue with a report that I've been working on for the last several days. The report is set up to use a table to print out information grouped by customer, with each customer's information showing up on a new page. This is all fine and well, but it seems that the table is generating page breaks after each row; in other words, since the table has twelve rows (10 group header rows, one details row, and one group footer row), twelve pages get printed, with the data from each row showing up at the top of the page, and nothing else.
The odd thing seems to be this: when I turn off page breaks (initally set to occur at the end of each group), it continues to
insert these breaks, as long as the details row forces the group footer onto another page. This doesn't happen every time the report is generated. Short of getting into the XML that makes up the report definition, I've tried every trick I can think of to get this to stop.
Any suggestions?
Edit: I should also mention that the report previews just fine, but only spits out the 12+ pages when printed or exported.
View 16 Replies
View Related
Aug 30, 2006
BooksOnline indicates that sp_rename can still be used to rename constraints such as primary keys, foreign keys, defaults, etc. in SQL Server 2005. However, when I try to do so I get this:
"Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong."
After looking at the code for sp_rename, it looks like this problem might be related to another bit of apparent misbehavior on 2005's part: when I execute "select object_name()" with the Id number of a constraint, it returns the name, but when I execute "select object_id()" with the name of that same constraint, it returns null. Why is that?
Thanks,
Ron
View 6 Replies
View Related
Aug 16, 2005
Using an MS A2K front end running on WinXP and ODBC connections to SQLServer 7 on Win 2K server; there are about 10 users who access thefront end via Terminal Server and use the same front end; there areabout another 10 users who run a copy of the db on their local machine.SQL server has 2 databases, with approx. 20 tables between them (oneis for processing ckts from a remote sql server for local use, theother is a site database).There is one user that always has to refresh the same single table eachtime a release of the db is copied to his local machine. Once thetable is refreshed in link manager, the front end is good to go...untilthe the next release is made (about every 2 months--for changes inbusiness rules). We have gone and checked his WINS and DNS settingsand at this point, the only difference between other users and this onewho can't get the table normally, is that he is using a different brandmachine (he uses a toshiba satellite, all others use a ibm thinkpad).Since purchasing a new laptop for him is out of the question, doesanyone have a suggestion for correcting this problem?TIA for the help,EricO.
View 4 Replies
View Related
May 7, 2008
I had a report that exported to PDF just fine before installing SP2 and now it is completely messed up. The report consists of a table that has two header rows (the report is basically a calendar). First Header Row is Month - merged across all 7 columns, Second header row is day of week. The next row is a row containing the date and finally the last row contains the actual data that is populated into each day. It is a sub-report in each cell of the row. It used to export fine but after applying SP2 it exports to pdf like this:
1st page is the first header row (Month) - Nothing else on the page
2nd page is the 2nd header row (Day of Week) - Nothing else on this page.
3rd page is the 1st row of actual dates - Nothing else on the page.
4th page is the data that goes to that 1st row of dates on the 3rd page, this page all all following pages are fine and show the row with the date directly above the data row with no page breaks in-between.
There is nothing in any of the controls forcing a page break (remember, it worked fine up until SP2 was applied).
What did the SP break? I am unable to modify my report now so that it exports to PDF correctly. What the heck???
View 4 Replies
View Related
Jan 23, 2008
Is there something I can do in layout mode to cause line breaks to only occur when data changes? I am creating a list of students and their degrees, but some students have multiple degrees and some only have one. If I place the information in a table with a blank row for a break, it breaks after every degree, but without the break there are no breaks at all.
Here's what I want:
Student
degree
degree
Student
degree
Student
degree
degree
degree
But what I have is:
Student
degree
degree
Student
degree
Student
degree
degree
degree
Is there any way to fix this, perhaps with an expression of some kind?
View 3 Replies
View Related
Oct 15, 2007
Hello,
Since SQL 2005 Express doesn't appear to let you paste multiple lines of data into a cell -- a task as "basic" as the Commodore 64 -- I'm forced to enter the data for 2 records directly. I'm trying to insert line breaks, which SQL 2000 allowed you to do by simply pressing the Enter key. However, 2005 just moves you to another record. How do I do this extremely simple task?
Many thanks for a speedy reply!
View 6 Replies
View Related
Mar 7, 2007
I have several nested sub-total groups that are all coded to "page break at end". I don't know how to tell RS to suppress the page break if one sub-total immediately follows another. The initial HTML output seems to be smart enough to do this suppression on its own, but when I export to PDF, all the page breaks appear. This causes several pages at the end of the report with one line per page. Perhaps, if I could explicitly suppress these page breaks, the PDF would render properly. How can I do this?
View 1 Replies
View Related
Feb 14, 2008
Fit an intere table in same page without page break for save the excel export.
My table has a Group for order my dates.
I need to have the intere table in the same page, i don't care about blank space at the end of the page.
I can't use the page break beacuse i need an excel export in a unique sheet..
I have tested.. every page break..you'll have a different sheet in your excel export
I need something like this
page 1
Zone
1
2
3
4
5
6
7
7
8
9
..
page 2
Zone
1
2
3
4
5
6
7
7
8
9
..
but an unique sheet in the excel export
View 4 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
May 21, 2015
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
View 13 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
May 9, 2007
Hi, I have created some views of the select join query in sql server 2005. I want to make use of this views with the asp.net 2005 with C# code. I m using SQLDATASOURCE to retrieve data from sqlserver. Can anyone help me how to write in C#VIEW SELECT CAST(LEFT(SalesReturnDate, 11) AS datetime) AS Date, SUM(TotalAmount) AS Salesreturn
FROM dbo.dfh_SalesReturnHeader
GROUP BY CAST(LEFT(SalesReturnDate, 11) AS datetime) This is the sample view how to connect it with asp.net C#,sqldatsource of 2005Thanxs in adv.
View 1 Replies
View Related
Jan 17, 2008
So, my SQL server was recently upgraded from SQL 2000 to SQL 2005. Everything seemed to go smoothly and look good, but now it seems that a view that had been created in SQL2000 isn't working as it should, and I can't for the life of me figure out why.
Here's the deal - I have a phone/email directory page & database that I maintain, and this view is supposed to pull data from 2 different tables and combine it into one view - specifically matching people with their associated organizations (and pulling the needed data on each organization), and then this should be sorted first by the OrgID, and then by the PersonID. The OrgID sort is working just fine, but for some reason it's not necessarily then sorting by PersonID anymore. I tried creating a whole new view using the designer in SQL Server Management Studio, but it has the same problem.
I'm including the queries for each of the views below - any help would be much appreciated!
Thanks
Kaiti
Original View:USE [DirectorySQL]GO/****** Object: View [dbo].[OrgView] Script Date: 01/17/2008 12:07:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER VIEW [dbo].[OrgView]ASSELECT TOP 100 PERCENT *FROM dbo.OrgList INNER JOIN dbo.Merge ON dbo.OrgList.OrgID = dbo.Merge.MergeOrgIDORDER BY dbo.OrgList.OrgName, dbo.Merge.MergePersonID
New View:USE [DirectorySQL]GO/****** Object: View [dbo].[NewOrgView] Script Date: 01/17/2008 12:08:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER VIEW [dbo].[NewOrgView]ASSELECT TOP (100) PERCENT dbo.Merge.MergePersonID, dbo.Merge.MergeOrgID, dbo.Merge.MergeTitle, dbo.OrgList.OrgID, dbo.OrgList.OrgName, dbo.OrgList.OrgType, dbo.OrgList.OrgAssociation, dbo.OrgList.OrgOffice, dbo.OrgList.OrgCampusMail, dbo.OrgList.OrgFax, dbo.OrgList.OrgInfoPhone, dbo.OrgList.OrgInfoEmailFROM dbo.Merge INNER JOIN dbo.OrgList ON dbo.Merge.MergeOrgID = dbo.OrgList.OrgIDORDER BY dbo.OrgList.OrgID, dbo.Merge.MergePersonID
View 6 Replies
View Related
Jun 17, 2008
can anyone tell me why when I create a view if I do this in my select statement "CAST(CrewNo AS char(10)) AS CrewNo" and then save the view the above CAST statement will be changed to this "CAST(CrewNo AS char(10)) AS Expr1". After I have it saved and then go back and change it back to "CAST(CrewNo AS char(10)) AS CrewNo" it will stay that way. Just looking for a reason why this is happening as I don't know if I'm doing something wrong or not.
View 2 Replies
View Related
Sep 10, 2007
Hello all!
I've wrote a small query for SQL 2005 and it's doesn't seem to work.
I have a table that contains two columns (X and Y), X is an int and Y is an nvarchar(50). I've populated this table with some data where Y contains numbers and some strings (e.g. "1", "2", "foo", etc). I've then got a view which only returns the rows where Y is numeric - now, I then query this table stating I only want numbers greater than 0 (i've casted the column) but this throws an error stating "foo" can't be casted. This is strange because the view doesn't return that.
What's going on? All of this works fine in SQL 2000 but not in SQL 2005 - looks like it's looking at the underlying table rather than the view. Sample code below to help you all out: -
Create Table
============
CREATE TABLE [dbo].[tblTest](
[X] [int] NOT NULL,
[nvarchar](50) NOT NULL
) ON [PRIMARY]
Insert Data
===========
INSERT INTO tblTest(X, Y) VALUES(1, '1')
INSERT INTO tblTest(X, Y) VALUES(1, '2')
INSERT INTO tblTest(X, Y) VALUES(2, 'foo')
INSERT INTO tblTest(X, Y) VALUES(2, 'bar')
Create View
===========
CREATE VIEW [dbo].[vwTest]
AS
SELECT X, Y
FROM dbo.tblTest
WHERE (ISNUMERIC(Y) = 1)
Finally
=======
SELECT X, Y
FROM dbo.vwTest
WHERE (CONVERT(int, Y) >= 0)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'foo' to data type int.
Thanks in advance!
View 10 Replies
View Related
Dec 16, 2005
hi all
i am having problem in finding the max value of name in my views table
i have names stored in my table as view1,view2 etc, so what i wanted to do is i want to find the next max value of the name which obviously 3 so my new name will be view3
the problem is if some one already created a name as 'view of john' then i am running in to problem by using this statement
Code:
SELECT @viewcount=ISNULL(max(CAST(SUBSTRING(Name,5,len(Name)) AS Integer)),0)+1
FROM Views
WHERE Name LIKE 'View%'
SET @ViewName='View'+cast(@viewcount as varchar(8))
here is the sample data
Code:
create table Views(Name varchar(50))
insert into Views(Name)
values('View1')
insert into Views(Name)
values('View2')
insert into Views(Name)
values('View of John')
i hope any genius can guide me in solving the problem.
thanks in advance
View 1 Replies
View Related
Feb 24, 2006
Hi,
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greets,
Ivo Klerkx
View 3 Replies
View Related
May 26, 2004
Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?
What is the alternatives??
I have to match two tables up and use records from one to update the other and creating a view isn't working....
View 5 Replies
View Related
Feb 7, 2002
I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.
--------------------------------------
select c1.contact ,ca.enddate ,ch.ondate
from ca
join c1 on ca.accountno = c1.accountno
join ch on ca.accountno = ch.accountno
where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------
View 1 Replies
View Related
Sep 12, 2014
I have some SQL experience, but nothing past basic commands. I'm trying to take some data held by an application to use as CSV import into another application.I have two tables from an application, one holds references made in another.The first tables holds details about a person:
field1=name field2=age field3=country
Joe,50,1
Country is held as a number, then there is another table that holds all the countries:
field1=id field2=description
1,USA
2,France
3,Germany
I want to do a lookup where it returns:
Joe,50,USA
View 1 Replies
View Related
Jul 9, 2007
I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.
Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @keyName)
SELECT * FROM Table1
WHERE (Key = ?)
SELECT * FROM Table2
WHERE (Key = ?)
SELECT * FROM Table3
WHERE (Key = ?)
SELECT * FROM Table4
WHERE (Key = ?)
SELECT * FROM Table5
WHERE (Key = ?)
TIA
Ian
View 4 Replies
View Related
Oct 21, 2006
Hi,
I am writing Stock inventory and POS application.
POS will be used at multiple branches to collect sales, whereby Stock Inventory will be used in HQ for product info maintenance, stock in and etc.
I need to send all of the branch sales records back to HQ and send updated product info such as cost to branches everyday (could be multiple times per day).
I am thinking to use SQL Server Express 2005 at branches and SQL Server 2005 standard at HQ.
Do you think it is workable? What is the mechanism I can use to "transfer" these data?
Please advice.
Thank you
View 1 Replies
View Related
Aug 11, 2004
Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?
View 2 Replies
View Related
Feb 2, 2006
Hi
I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
View 1 Replies
View Related
Apr 24, 2008
Are there any disadvantages in respect to performance in using table valued functions instead of using a view.
Thanks...
View 3 Replies
View Related
Feb 26, 2008
Hi Experts,
We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).
Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.
Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.
1. Will SQL server table paritioning help in this scenario ?
Or
2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?
Any help would be greatly appreciated.
Thanks in advance,
Hariarul
View 4 Replies
View Related
Aug 8, 2005
I have a partitioned view sitting over several tables and I'm slowlyapproaching the 256 number. Can anybody confirm if there is such alimit for the maximum number of tables that a partitioned view canhold?If this is true, does anybody have any suggestions or ideas to workaround this max limit?TIA!
View 4 Replies
View Related
Apr 20, 2006
Hi champs!
Is it possible to make a database View over two different database installations on different machines?
Many thanks
kurlan
View 4 Replies
View Related
Aug 20, 2007
Hi everybody.
I need to find all the views that depend on a table in a different database, in order to refresh them once the table is altered.
In the BOL I found the following script that is very useful, but I can't use it if the view and the table are in different database.
Where can I find the dependencies in this case?
Thank you very much.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9ce1d07c-ee66-4a83-8c73-cd2cc104dd08.htm
Creating a script that updates all views that have dependencies on a changed object.USE AdventureWorks;
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('Person.Contact')
View 1 Replies
View Related
