Recreating A View

Oct 19, 2007



I have some tables that are created as views in a database.
Everytime I modify table structure the view needs to be recreated.

I am developing a simple Stored Procedure which takes table name as parameter and drops teh view first and then cretaes it again.
The statements required would be

drop view viewname
create view viewname as
select * from ffship.dbo.viewname


The error I get is that

'CREATE VIEW' must be the first statement in a query batch

Any help appreciated.

Thanks,
Kiran.

View 4 Replies


ADVERTISEMENT

Recreating DB Log Files

Jul 25, 2004

I would like to be able to regenerate the log files for our SQL databases without impacting the data files. I tried:
- detaching the database,
- saving the datafiles to another drive,
- dropping and recreating the database with the same structure as before,
- detaching the database again,
- overlaying the data files with the ones I saved previously,
- then reattaching the database.

The reattach fails saying that the files can't be used for different databases.
If I try to bring in log files that are the same size as the database log files I'm trying to recreate, I get the same error only the log files are the ones in error.
I've tried recovering/recreating the database with the "for attach" parameter, but I get the same error.

More information... The problem I'm having that requires the log files to be regenerated is realted to an upgrade to Win 2003 EE. We upgraded the server last week and since then, the backups have been failing. I've tried reloading a backup of the database but the only backups we have are prior to the upgrade and I think, because the database logs are operating system files, the logs don't get loaded correctly and I still have the same backup problem. I logged a case with Microsoft, but they haven't been able to help.

View 3 Replies View Related

Problem Recreating The Database

Mar 25, 2008

Then I deleted an entire database from the "Enterprise Manager". And refreshed the Query Analyzer.
Then I again tried to create the database.  CREATE DATABASE ContactManager ON PRIMARY(NAME = ContactManager,FILENAME ='C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)LOG ON(NAME = ContactManager,FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 2MB)GoNow it says, database already exists.
I used 'EXEC sp_helpdb' to view all the databases, but the database 'ContactManager' was not there.
Where is the problem?

 

View 1 Replies View Related

Recreating Log File For Sql Server 7

Oct 2, 2000

I have a client who was cleaning up his server to free up disk space and actualy deleted de transaction log file for server database. I can't bring back the backup because it was also deleted (smart...). I still have the .mdf file. Is there a way to bring back the database? I tried to detach and reatach database without success, so the database is no longer attached.

Any help would really help.

Thanks in advance,
Stephane Jeffrey

View 2 Replies View Related

Recreating Indexes Dynamically

Apr 22, 2003

How to get all the index properties like unique, clustered of an index.

I need to recreate all the indexes in a table dynamically
1) need to get the list of indexes
2) drop them
3) change some properties and recreate the indexes, to do this I need to know the columns, uniqueness, clusteredness in step 1 above. How can I get to that?

In oracle we can depend on tables like index_columns, index_constraints....

thx

View 2 Replies View Related

Recreating A Corrupt Database!

Nov 3, 1998

Hello all,

I'm in a bit of a bind. We have a database that spits out a classic corruption error message whenever attempting to run scheduled maintenance:

Error:605, Severity: 21, State: 1
Attempt to fetch logical page ### in databbase 'db1' belongs to object 'table1' not object 'table2'

SQL Books Online says to check with DBCC checkDB and CheckAlloc asap, which I did, and CheckAlloc confirmed the corruption. Books Online's only advice at this point is to restore the DB from a clean backup.

Herein lies the problem. One I'm assuming has happened to many a DBA as well.

This particular message has been going on for months, and there is no "clean" backup of the database, because no-one here at the time knew there were any problems to take precautions. Now I'm here with a corrupt database, and no idea how one goes about reconstructing a DB from scratch. Does anybody out there know any good options?

Thanks in Advance

Fenderson

View 4 Replies View Related

Dropping Indexes And Recreating The Same

May 1, 2008

I would like to know if there is a way to drop/ disenable all the indexes in a maintenance plan?
Or is it better to write scripts for dropping indexes and recreatig the same?

Purpose: Need to drop indexes(not the Primary key) before loading data and recreate the same after loading

Looking forward for suggestions/Solutions!

Thanks,
Janani

View 2 Replies View Related

T-SQL And Dropping And Recreating A Table

Jun 11, 2008

Hi Guys,

I am trying to DROP a table and recreate the table in a stored procedure and then I am trying to INSERT records into the newly created table in the same stored procedure.

I know I don't have to DROP the table, but I am trying to just get it to work. The process runs without error, but when I refresh the tables, the table I created isn't there. The T-SQL is as follows:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ImportFilesPremier]

AS

DROP TABLE dbo.[PremierTest]

CREATE TABLE [PremierTest] (
[AYQ] nvarchar(6) null ,
[CYQ] nvarchar(6) null ,
[Description] nvarchar(50) null,
[PIP] [decimal] (17,2) ,
[BI] [decimal](17,2) Not null,
[PD] [decimal](17,2) Not null,
[COLL] [decimal](17,2) not null,
[COMP] [decimal](17,2) not null,
[DCRAT] [nvarchar](2) null ,
[Agent][nvarchar](3) null ,
) ON [PRIMARY]

begin transaction

insert into [PremierTest]
select *
from dbo.[new agt type tri 0804]
WHERE dir_ceded_ind = 'C'

commit transaction


Any information on how I can tweak my code so it works properly would be greatly appreciated. Thank you.

View 4 Replies View Related

Deleting Users And Recreating

May 7, 2006

In my database, I am currently trying to delete a user and recreate it for ownership. I get the following error message

An exception ocurred while executing a Transact-SQL statement or batch. The database principal owns a schema in the database and cannot be dropped. SQL Server Error 15138.

What do I need to do? Thanks

David

View 1 Replies View Related

Recreating Index After Restore--URGENT PLEASE

Sep 4, 2002

Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise.
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe

View 1 Replies View Related

Recreating ODBC Connection Strings

Jul 11, 2002

Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .

Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.

Thanks

View 1 Replies View Related

Recreating ODBC Connection Strings

Jul 11, 2002

Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .

Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.

Thanks

View 1 Replies View Related

Recreating Indexes After Restore. Urgent -- Please

Sep 4, 2002

Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise. I have also ran the update statistics on all tables but with no luck
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe

View 2 Replies View Related

Dropping And Recreating Stored Procedure?

Feb 16, 2015

Due to some SSQL application upgrade, some unnecessary columns have been added to the tables and the stored procedures. The values in these columns are NULL and these columns have been added at the end of the tables and some SP.

As my application doesn't require these columns thereby I have re-created the old tables using other environments where these columns were not present.

Can I do the same thing for stored procedures as well i.e. to drop the SP and recreate using other environment which are not having the unnecessary columns.

View 5 Replies View Related

Recreating The Data Publishing Wizard

Mar 27, 2008

Hi,
I was wondering if anyone had an idea if it's possible to run a stored procedure that would basically output a .sql file used to recreate the database elsewhere. kinda like the data publishing wizard does.

?
tx

View 1 Replies View Related

Recreating/reloading System Stored Procedures

Jul 21, 2006

A system stored procedure got accidentally deleted, and all backups aresince the stored procedure was deleted (wonderful!)Can the SQL for the stored procedure be extracted from another serverand loaded as opposed to removing everything and then rebuilding theserver?Thanks in advance!

View 1 Replies View Related

Merge Replication Failing After Recreating The Publication

Jun 7, 2006

I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:

1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.

2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.

3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.

4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'

I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?

Any help much appreciated.



View 4 Replies View Related

How To Add Tabls To Published Article Without Recreating Snapshot?

Jan 26, 2007

Hi experts:

We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?

By that is not possilbe, can we publish one new article on the same database?



Thanks in advance!

Ron

View 1 Replies View Related

Altering (or Recreating) A Stored Procedure Header

May 30, 2006

We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.

View 5 Replies View Related

Recreating The Functionality Of A Extended Stored Procedure That Uses Srv_impersonate_client

Mar 1, 2006

I need help rewriting an extended stored procedure as a CLR.

What this extended stored procedure does is to return the domain username of the person connected via named pipes.

This is accomplished by using the srv_impersonate_client and GetUserName functions from opends60.lib. I have tried rewriting this in CLR using Microsoft.SQLServer.Server.SQLContext.WindowsIdentity but have been unable to replicate the functionality to return the same values as the srv_impersonate_client.

If anyone knows how I can rewrite this as CLR, let me know, Also I am looking for where I can get a 64bit version of opends60.lib to run on an amd64

Thanks
Darryl

View 6 Replies View Related

How To Alter The Table With Delete/update Cascade Without Recreating The Table

Jul 26, 2004

I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.

Thanks


************************************************** ******
Contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

View 3 Replies View Related

Regarding ReCreating Catalog In SQL Server 2005 Which Was Existing In SQL Server 2000

Feb 24, 2008



Hello

I was using SQL SERVER 2000 ... In one table I've created FULL TEXT SEARCHING ( Full text catalog along with full text indexing)

Now we had to install our db in SQL SERVER 2005 standard edition. But while taking script it gave me two lines like:


if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name = N'DEV_CAS_DiagnosisCatalog')
exec sp_fulltext_catalog N'DEV_CAS_DiagnosisCatalog', N'create'
GO

so I used this in the new db creation script...

But I couldn't get where it actually is in SQL SERVER 2005 standard edition.

and also plz help how should we create if it doesn't exist...

What could be the problem....

Thanks In advance

View 3 Replies View Related

Recreating A Sql Server 2000 Database As A 2005 Database

Jan 25, 2007

 
I am working on a project that was written in Visual Studio 2003 using asp.net and C#.  The project accesses a SQL Server 2000 database. I need to test the application. What I want to do is to copy the database over to a test server. The problem is that the test server only has SQL Server 2005 on it. Would it be a problem if I backed up the database using SQL Server 2000 and then recreated it on the server as a SQL server 2005 project? Would my source code accept the new database even though it would now be in sql server 2005? 
 

View 2 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







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