Can I Amend A View Through VS?
Dec 10, 2007
Hi,
I have a SQL database consisting of tables and views. As there as remote users, I am not able to directly amend their databases, so need to do it through my VS application.
If I want to amend one of the tables, I do it like this:
=============================================
Dim connectionstring As String = My.Settings.ConnectionString
Dim eSQL As String = ""
Using connection As New System.Data.SqlClient.SqlConnection(connectionstring)
Dim cmd As New System.Data.SqlClient.SqlCommand(eSQL, connection)
connection.Open()
eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND COLUMN_NAME = 'Company' ) " _
& "BEGIN " _
& "ALTER TABLE [Client] " _
& "ADD Company int " _
& "END "
cmd.CommandText = eSQL
cmd.ExecuteNonQuery()
End Using
=============================================
However I've now come to a point where I have no option other than to change a view (I need an extra field in it). Is there any way I can do this programmatically?
The view looks like this in SQLMSE:
====================================
SELECT dbo.Client.ID, dbo.Client.Title, dbo.Client.First, dbo.Client.Last, dbo.Client.Street, dbo.Client.Town, dbo.Client.Postcode, dbo.Client.CleanerID,
dbo.Client.Cleaner, dbo.Client.Status, dbo.Client.StatusDate, dbo.Cleaner.Tel, dbo.Cleaner.Mobile, dbo.Cleaner.WorkPhone, dbo.Client.hours,
dbo.Client.frequency, dbo.Client.BlitzDate, dbo.Client.RegStart, dbo.Client.interviewdate, dbo.Client.PayStatus
FROM dbo.Client INNER JOIN
dbo.Cleaner ON dbo.Client.CleanerID = dbo.Cleaner.ID
====================================
I just want to add an extra field to the select list.
Thanks
View 3 Replies
ADVERTISEMENT
Jan 28, 2008
Please could someone tell me how to modify a sp in 2005. I r/h click on the sp, choose modify and do my changes, but when I save it it saves it under a different name.......
View 8 Replies
View Related
Jul 30, 2007
I am trying to amend an SQL insert statement but it is not working.
It is as follows can anyone tell me what i am doing wrong.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" DeleteCommand="DELETE FROM [useridTest] WHERE [userID] = @original_userID AND [userName] = @original_userName AND [listCopy] = @original_listCopy"
InsertCommand="INSERT INTO [useridTest] ([userID], [userName], [listCopy]) VALUES (<% user.ProviderUserKey.ToString() %>, @userName, @listCopy)"
View 1 Replies
View Related
Nov 20, 2007
Hi,
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
Any help would be much appreciated.
Thanks in advance,
Paul
View 3 Replies
View Related
Jul 23, 2005
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 Related
Jul 24, 2012
Write 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]...
View 2 Replies
View Related
Aug 24, 2007
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!
View 3 Replies
View Related
Mar 9, 2006
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 Related
Aug 14, 2000
I 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.
View 1 Replies
View Related
Feb 17, 2006
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
View 4 Replies
View Related
Oct 27, 2006
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
Feb 21, 2006
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie
View 2 Replies
View Related
Mar 29, 2007
I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year.
tblPayments (ClientID, PmtID, PmtDate, PmtAmt)
How would I set the criteria for this?
View 1 Replies
View Related
Feb 7, 2008
How do I set up a sql statement to use as a view?
I want to use two tables but they are both from different databases.
From the Offices database I want to use a table called Office Code and only the Name column
The other database is called Library and I want to use a table called Requestors and join it to others.
I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database.
Create view GetDataview
select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name], Titles.title as Title from requestors
join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column?
I hope this makes sense
View 6 Replies
View Related
May 18, 2008
how can i make a view in my sqlexpress 2005 database that get data from oracle database or from RDB (old oracle database)
View 6 Replies
View Related
May 18, 2001
I am trying to write a view to retrieve data from
two different servers..
create view as
select c1 from ser1.database1.dbo.table1
union
select 1 from ser2.database2.dbo.table1
First it is not accepting to write it from EM, hence I wrote it from QA,
the problem when I am trying to execute it is giving following error
"Could not find server 'hercules' in sysservers"
Help me out what needs to be done?
View 2 Replies
View Related
Jan 12, 2001
i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.
ur help is appriciated.
View 1 Replies
View Related
Sep 21, 2000
I have a view which I am supposed to run manually first thing tomorrow morning but I will not be
in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.
View 1 Replies
View Related
May 7, 2002
How many maximum number of tables I can used to create the view in SQL 6.5..?
View 1 Replies
View Related
Oct 24, 2007
When I ran this SQL Query, I got an unexpected result.
//Sql Query
Code:
SELECT distinct Year FROM vw_EMD_Options_Pricing WHERE (Year >= '2003') AND (Year <= '2008')
The view is the "vw_EMD_Options_Pricing". The request I got is the year that go from 1981 to 2008. This is for MS-SQL 2000. Why does the SQL Query does that?
View 2 Replies
View Related
Oct 20, 2004
I have a database with a view to another table in another database which I created a form out of. I was wondering when if this rule applies
"Data Entry" property of the form is set to True. The default value for this property is "False" which means that Access opens the form and shows the existing records. However, if set to True, the Data Entry property of the form specifies that the form will only show a blank record.
The reason I am asking is because you cant see the records after you close the form and reopen it again.
View 1 Replies
View Related
Apr 6, 2007
Hi,
I have 2 databases, db1, db2 in the SQL Server. In db1, I created the view to link one table from db2. When I run it, it is just read-only, I want to edit/insert data in the view. Can we edit/insert the data in the view from db1(which linked table from db2)?
View 1 Replies
View Related
May 18, 2004
Hello Everyone,
When I write a sql joining more than views how will be executed?.
Does SQL Server execute each view and join the result set or interpret into one final sql and execute?
Thanks
Masanam
View 3 Replies
View Related
Apr 22, 2008
Hello All,
My sample data looks like this in SQL Server 2000
SubGr,Trno,Glcode,Amount,DrCr
----------------------------
Bank,1000,100001,1000.00,D
Other,1000,100012,1000.00,C
Other,1001,100010,1500.00,D
Other,1001,100010,1500.00,D
Bank,1001,100002,3000.00,C
Bank,1002,100001,2000.00,D
Bank,1002,100003,2000.00,C
Other,1003,100051,5000.00,D
Other,1003,100051,5000.00,C
I want a view which gives a output like this for those SubGr with 'Bank'.
Output
SubGr,Trno,Glcode,Amount,DrCr,Bank
----------------------------------
Other,1000,100012,1000.00,C,100001
Other,1001,100010,1500.00,D,100002
Other,1001,100010,1500.00,D,100002
Bank,1002,100001,2000.00,D,100003
Bank,1002,100003,2000.00,C,100001
Thanks in anticipation
Nirene
View 4 Replies
View Related
May 5, 2008
I want to create a view that has an if then statement within it, how is this done?
View 5 Replies
View Related
May 16, 2008
Hi,
I have a view in my database which returns:
ID Date Area
1010712008F
1020712008F
1030712008F
I should change this view’s output in such a way that for each Id I should have 3 record:
IDDateAreaType
1010712008FLO
1010712008FPO
1010712008FSO
1020712008FLO
1020712008FPO
1020712008FSO
1030712008FLO
1030712008FPO
1030712008FSO
LO ,PO and SO are constant.
How should I do that?
Thanks.
View 5 Replies
View Related
Jun 3, 2008
i have a view that calls 4-5 functions and many joins,
the time to display data is around 10-11 mins,
how can i improve performance?
View 4 Replies
View Related
Jun 10, 2008
I have a small doubt.
Can we update a table through view.
Is there any limitation to upadate
View 1 Replies
View Related
Oct 31, 2005
Hi
How can i save a query as a view if query contains the case statement
asm
View 2 Replies
View Related
Mar 31, 2006
I've never worked with views before though I have a vaugue idea what they're for :)
I have a table that is quite large and growing fast and my primary use for the data is to run lots of aggregate functions on it.
See my previous post for an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63748
Queries like that are taking 10 seconds to run.
So what I'm looking for is a way to store the results of a query like that, run the actual query maybe once an hour, and any time a user needs that data they get the results of the last run.
My first thought was to just have "summary" tables that get updated by a Stored Procudure that runs on a schedule under "Jobs". Is there a better way?
Thanks!
Edit: Some of the "summary" data would be based on date, so the summarized data would only need to be processed once. Don't know if that is something to consider.
View 2 Replies
View Related
Sep 19, 2006
Shahbaz writes "Hi
1.Can we delete the record using view.?
2.Can we insert and update the record through view?
Thanks and Regards,
Shahbaz"
View 5 Replies
View Related
Feb 2, 2007
Hey Guys,
I am trying to get 5 days back date and in my table the following 3 records are there but my query is not pulling. Could you please tell me what is wrong with my query? Thanks
select * from
dbo.tblTransaction
WHERE Performance_dm >= dateadd(day, datediff(day, 0, getdate()), - 5) AND Performance_dm < dateadd(day, datediff(day, 0, getdate()), -4)
order by Performance_dm
These are the records in the table but it is not pulling. How should i pull these records please?
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
View 2 Replies
View Related
Feb 11, 2007
I have tried Books online and have queried this forum for assistance in creating a SQL view and have spent much time trying to solve on my own.
I am working with only one table:
Sales: SOP10200
SOPTYPE ITEMNMBR ITEMDESC QUANTITY
3 Widget special 4
3 Widget special 7
2 Gadget special 1
2 Widget special 12
3 Gadget special 5
I want to create a view that summarizes the data based upon SOPTYPE and ITEMNMBR as follows:
ITEM_NUMBER ITEM_DESC QTY_INVOICED QTY_ORDERED
Widget special 11 12
Gadget special 5 1
I am struggling whether to use a select statement or a CASE statement.
I am able to summarize one SOPTYPE as follows:
CREATE VIEW [dbo].[Tec_SOP10200_INVOICE_Qty]
AS
SELECT ITEMNMBR as ITEM_NUMBER, ITEMDESC as ITEM_DESC, SUM(QUANTITY) AS QTY_INVOICED
FROM SOP10200
WHERE SOPTYPE = 3
GROUP BY ITEMNMBR, ITEMDESC
But I need more than one soptype in same view
I tried this CASE statement -
Create tec_sales_view
as
SELECT SOP10200.ITEMNMBR as ITEM_NUMBER
, SOP10200.ITEMDESC as ITEM_DESC
, SOP10200.QTY1 'QTY_INVOICED'
, SOP10200.QTY2 'QTY_ORDERED'
, (CASE
WHEN SOP10200.SOPTYPE = 3 THEN SUM(QUANTITY) QTY1 ELSE 0)
(CASE
WHEN SOP10200.SOPTYPE = 2 THEN SUM(QUANTITY) QTY2 ELSE 0)
END as QTY_Type
FROM SOP10200
GROUP BYITEMNMBR, ITEMDESC, QTY1, QTY2
But I get error message doesn't recognize QTY1 (invalid column name)
Thank you in advance for any assistance.......
View 2 Replies
View Related