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
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
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?
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.
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.
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?
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
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
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
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.
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
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
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
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
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
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.
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
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 RelatedI 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.
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
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 RelatedI 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
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)
);
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies View RelatedHi all,
I have a database name MyDatabase (SQL Server Express Dabase File). Is there anyway that I could convert it to SQL Server Compact Edition File?
By the way does anyone here got any problem with programming in SQL Server Compact Edition? It troubles me.
Thanks,
bombie
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies View RelatedDear all,
I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file with the same name already exists, that file isn't replaced. I would like the opposite to happen. I'm using the following code:
--Executable
set svr=http://w3sdwsqld1/reportserver
set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\"
set dest_fld="Associados"
set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld%
PAUSE
--rss Code
'
' Script Variables
'
' Variables that are passed on the command line with the -v switch:
'
' (a) parentFolder - corresponds to the folder that the script creates and uses
' to contain your published reports
' (b) reportP - corresponds to the report to publish
Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = ROOT + "/"+ parentFolder
Dim filePath As String = path
Dim report As String = reportP
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
Try
rs.CreateFolder(parentFolder, ROOT,Nothing)
Console.WriteLine("Parent folder {0} created successfully", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create shared data source
'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")
'Publish the sample reports
PublishReport(report)
End Sub
Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx")
Console.WriteLine(reportName)
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
Try
rs.CreateResource(reportName + ".xlsx", parentPath, True, definition, "application/x-excel", Nothing)
Catch e As Exception
Console.WriteLine(e.Message)
Console.WriteLine("Failed to publish report")
End Try
End Sub
--------------------------------------------------------------------------------------------------------------------
Any thoughts? Many thanks,
Pedro Martins
Portugal
I have the following SQL
IFOBJECT_ID('tempdb..#TempTable_Lockbox_File_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_File_Header_Record
;
IFOBJECT_ID('tempdb..#TempTable_Lockbox_Batch_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_Batch_Header_Record
;
[code]....
When I send my query results to a file in SQL Server Management Studio, how come I'm seeing the following in Notepad++? FH TEST "FH" which I thought should be in a CHAR(2) data column is there but "TEST" seems to start in Column 6...not column 3 as I would have expected. I was expecting... FHTEST.
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang