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

Amend SP In SQL 2005

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

Is It Easy To Amend An Insert Statement.

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

Cannot Amend Or Delete Subscription And Cannot Delete Report.

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

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

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

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

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

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

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

Different Query Plans For View And View Definition Statement

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

Alter View / Create View

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

Updating My View Changes My View Content

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

View Works, But The Sql From The View Does Not

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

Selecting A View And Selecting FROM A View Is Wildly Different

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

Need Help With A View

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

Need Help Using A View.

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

Sql View Help

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

View ???

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

View S

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

View

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

View

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

VIEW Bug??

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

View

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

View

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

View

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

Help For A VIEW

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

If Then Within A View

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

View

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

View

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

View

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

View

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

Is This What A View Is For?

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

View

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

Need Help With The View

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

SQL View

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







Copyrights 2005-15 www.BigResource.com, All rights reserved