Views Scripted In The Wrong Order
Dec 19, 2005Is there a way to make EM script all views of a database in the order in which they depend on each other?
View 11 Replies
ADVERTISEMENT
Wrong 'Order By'
May 15, 2003Does anyone know any bug relationed with memory paging and order by clause?
I try to explain:
I have the following code inside a SP:
create table #tempA (colA char(2), colB numeric(7), colC numeric(3))
create table #tempB (colA char(2), colB numeric(7), colC numeric(3))
)
insert into #tempA
(...)
(...)
insert into #tempB (colA,colB,colC)
select colA,colB,colC
from #tempA
order by
colB,colA,colC
select * from #tempB
While using the reserved memory for the SQL Server, the last 'select' works pretty well. After the memory swapping starts, the 'select' returns data in incorrect order.
Any Ideas?
Thank you for all support than you can give me.
:confused:
Convert Datetime But Order By Is Wrong
Sep 25, 2006I am trying to convert my datetime to 107 ex: Dec 11 2006 ,but when sorting I get the months sorted correctly but not by year????
ex:
Jan 2004
Feb 2001
Mar 2006
Wrong Date Sort Order In Enterprise Mgr
Feb 3, 2006When clicking on the header for the Date Created column in EM (SQL Server 2000) , in the tables list for a database, I noticed that the tables don't get sorted by created date at all - looks like random order, without looking too close.
Is this is a bug (probably not??) or some kind of collation-related problem?
Order By Cluase Cause Wrong Results To Be Returned.
Sep 10, 2007I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1
Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1
Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1
Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1
Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....
OLEDB To Flat File Destination - Comes Out In Wrong Order
Jun 8, 2007Hello,
My OLE DB Source is getting data from the following column types:
ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10)
My OLE DB Source outputs my information in the following order when I click Preview:
ID Name Date Currency Cost
When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get:
Currency DT_STR Length: 3
ID DT_STR Lenght: 50
Name DT_STR Lenght: 100
Date DT_DBTIMESTAMP
Cost DT_NUMERIC
What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source:
ID Name Date Currency Cost
Thank you very much!
SS2005 Standard (RTM && SP1 CTP1) Evaluates Sub-queries In Wrong Order
Mar 24, 2006We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.
However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.
My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.
However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.
CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')
-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15
--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15
-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error
DROP TABLE #Problem
I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!
Views Ignoring Order By Clause
Sep 7, 2007I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes
Create Views In The Order Of Their Dependency
Feb 5, 2007In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.
View 1 Replies View RelatedIncorrect Processing Order For Views
Jun 4, 2007I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2
Essentially the problem is as follows:
1) Initial state is that merge replication of table and views is working fine
2) I then alter one view which references a new view in the same publication
3) Synchronization processes the view scripts in the wrong order regardless of the processing order
4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted
The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order
Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs
I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct
Is there an known problem in this area?
Any suggestions would be much appreciated
aero1
SQL 2012 :: Re-order Views Fields Name By Alphabet
May 20, 2014There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
Transact SQL :: Output Views In Dependency Order
Jun 25, 2015Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
View 2 Replies View RelatedSQL 2012 :: Using Partitioned Views In Order To Manage Table Sizes
Oct 13, 2015I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.
I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]....
ORDER BY Clause Is Invalid In Views / Inline Functions / Derived Tables / Subqueries
Sep 25, 2013The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error.
Output:
1234567890000043321092513 00050020
Select DATA from(
select '12345678'+
left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
cast (CONVERT(int,(INV_AMT*100)) as varchar) +
left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
CAST (MONTH(DATE) as varchar(2)) +
left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
CAST (day(DATE) as varchar(2))+right(cast
(year(DATE)
[code]....
Scripted Delete Rows
Jul 23, 2005Hi,I need to delete rows from my user tables dependant upon there nonexistence from another table:delete studentwhere student_id not in (select student_id from tblStudent)The reasons is convoluted, simplest explanation is that our operationalsystem allows the change of business keys. This wreaks havoc in thedata warehouse.So, I'm look for help on how I can delete rows from tables that have acolumn STUDENT_ID. I'd like the script to search for the tables, thenperform the delete.I don't know where information about user tables are stored, nor how toloop through the results to do the delete.Any Ideas are appreciated.
View 2 Replies View RelatedScripted CREATE INDEX
Mar 13, 2007I have SQL Server 2005 x64. I found in the studio I can generate allsorts of scripts, including the creation of indexes.I works, however the script contains the CREATE TABLE statements aswell. I want the scripts to recreate the scripts on another filegroup(testing a theory from previous post).Am I missing a switch in the wizard to exclude CREATE TABLE?I did create the script, but then I manually deleted all the CREATETABLE statements, which was a pain.ThanksRob
View 1 Replies View Related[help] Scripted Database Creation
Jul 13, 2007Hi all :)My apologies if I posted in the wrong groups, but I just jumpedin MS SQL waters, so any guidance will be appreciated.What I'm trying to do is the following process:[1] present operator with a web page (script)[2] once filled with db name and username, script will create..sql file[3] osql.exe will be called with, i presume, -i file.sql andcreate a databaseI have limited SQL knowledge, but I got the information fromEnterprise Manager when I ran 'All Tasks -Generate SQLScript' on how the .sql file should look like.I realized what are the commands that would create a fresh DB(I ran this for newly created DB), and figured my .php scriptshould create such a file.It's fairly basic, and I'm almost sure all of you know howoutputed .sql file looks like, but anyway here it is:Script is called with parameters 'six' as database name and'magarac' as user name:---------------------------------------------------------------IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'six')DROP DATABASE [six]GOCREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:Databasepathsix_Data.MDF' , SIZE = 1, MAXSIZE = 20,FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:Databasepathsix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH= 10%)COLLATE SQL_Latin1_General_CP1_CI_ASGOexec sp_dboption N'six', N'autoclose', N'false'GO....use [six]GOif not exists (select * from dbo.sysusers where name =N'guest' and hasdbaccess = 1)EXEC sp_grantdbaccess N'guest'GOif not exists (select * from dbo.sysusers where name =N'sinisam')EXEC sp_grantdbaccess N'magarac', N'magarac'GOexec sp_addrolemember N'db_owner', N'magarac'GO---------------------------------------------------------------I managed to get an exact replica of .sql file that EnterpriseManager created. It leads me to believe that this way ofautomated database creation is indeed possible.Really sorry for making you to go through all this text, butafter I get a green light on this from you guys, I'll have abit more problematic question.Is there any reason why this should not be used, or would fail?Thanks in advance :)P.S.Just as a heads-up, next part of my problem is automatedcreation of new MS SQL server login to use with new DB.
View 3 Replies View RelatedCan A DTS Package Be Scripted Or Transferred To Another Server?
Jul 20, 2005I have production SQL Server database that must be moved to a newmachine. There is a fairly complex DTS package on the original serverthat is used to handle the weekly updates to the database.Is there a way to export this DTS package in order to set it up on thenew machine as well?Best Regards,Warren WrightScorex Development Team
View 3 Replies View RelatedSaving Scripted Output From Enterprise Manager
Aug 14, 2002Does anyone know where to declare the target directory for scripted output from Enterprise Manager?
Every time I get EM to script Tables I want the output not to go to c:mssql7inn . . but somewhere else. Trouble is I can't find where to set this variable. It won't work in a shortcut - I've tried that, and I can't find an ini file either.
Seems like a simple problem to fix if you just know where to look !
Schedule A Scripted Restore On 2005 Express From .bak File.
Oct 28, 2007Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.
View 2 Replies View RelatedScripted Backup/Restore To Remote Server Failing
May 16, 2008FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.
First I'll give a little background on our situation.
Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.
In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].
Now for the code. Props to Tara and the code she's put online.
Any help would be appreciated and I'll be glad to help answer questions related to what I've got.
1st Step:
Agent Job scheduled to call stored procedure
EXEC sp_backup_user_dbs3
2nd Step (The code for that stored procedure is):
CREATE PROC sp_backup_user_dbs3
AS
SET nocount ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @DBName SYSNAME
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @loglogical VARCHAR(1000)
DECLARE @datalogical VARCHAR(1000)
DECLARE @restoreData VARCHAR(255)
DECLARE @restoreLog VARCHAR(255)
DECLARE @backupFile VARCHAR(255)
DECLARE @physicalNameData VARCHAR(255)
DECLARE @physicalNameLog VARCHAR(255)
DECLARE @physicalNameDataStripped VARCHAR(255)
DECLARE @physicalNameLogStripped VARCHAR(255)
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL VARCHAR(1000)
DECLARE @restoreToDataDir VARCHAR(255)
DECLARE @restoreToLogDir VARCHAR(255)
DECLARE @path VARCHAR(100)
SET @path = 'I:ackupMoveTo14'
--we need to delete all the old backup files from the I:ackupMoveTo14 folder
-- Build the del command
SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd,
NO_OUTPUT
CREATE TABLE #whichdatabase
(
dbname SYSNAME NOT NULL
)
INSERT
INTO #whichdatabase
(
dbname
)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ( 'db1', 'db2')
ORDER BY [name]
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname
WITH truncate_only
END
-- Backup the database
BACKUP database @DBName TO disk = @filename
DELETE
FROM #whichdatabase
WHERE dbname = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR delay '00:00:05'
END
DROP TABLE #whichdatabase
SET nocount OFF BEGIN
SET @cmd = ''
SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd
END BEGIN
EXEC [RemoteServer].master..usp_restoreDbsFromDir2
END
RETURN 0 GO
3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):
CREATE PROCEDURE usp_restoreDbsFromDir2
AS
SET NOCOUNT ON
DECLARE @dbname varchar(255)
DECLARE @loglogical varchar(1000)
DECLARE @datalogical varchar(1000)
DECLARE @physicalName varchar(255)
DECLARE @physicalFileName varchar(255)
DECLARE @restoreData varchar(255)
DECLARE @restoreLog varchar(255)
DECLARE @backupDisk nvarchar (255)
DECLARE @physicalNameData varchar(255)
DECLARE @physicalNameLog varchar(255)
DECLARE @physicalNameDataStripped varchar(255)
DECLARE @physicalNameLogStripped nvarchar (255)
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL varchar(1000)
DECLARE @spidstr varchar(8000)
DECLARE @cmd sysname
DECLARE @bkpFile nvarchar(1000)
DECLARE @sql nvarchar(4000)
DECLARE @restoreDir varchar(255)
DECLARE @PhysicalDataPath varchar(255)
DECLARE @PhysicalLogPath varchar(255)
SET @restoreDir = 'F:MSSQLBACKUP'
-- Get files sorted by date
SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'
CREATE TABLE #Dir
(DirInfo VARCHAR(7000)
) -- Stores the dir results
CREATE TABLE #BackupFiles
(BackupDate varchar(10),
BackupFileName nvarchar(1000)
) -- Stores only the data we want from the dir
CREATE TABLE #RestoreFileListOnly
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
[Size] numeric(20,0),
[MaxSize] numeric(20,0)
)
INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd
INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
SET @cmd = @restoreDir + @bkpFile
INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
--get the dbname from the bkpFile name
--SET @strSQL = CHARINDEX('_db_', @bkpFile)
--SET @dbname = LEFT(@bkpFile, @strSQL - 1)
SET @strSQL = CHARINDEX('.bak', @bkpFile)
SET @dbname = LEFT(@bkpFile, @strSQL - 1)
--PRINT @dbname
--IF @@ROWCOUNT <> 2
-- RETURN 3
SET @backupDisk = @restoreDir + @bkpFile
SELECT @datalogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @loglogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @PhysicalDataPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @PhysicalLogPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'DROP database ' + @dbname
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
--restore the database
SELECT @strSQL = ''
SELECT @strSQL = @strSQL + 'RESTORE DATABASE ' + @dbname + CHAR(10)
SELECT @strSQL = @strSQL + 'FROM DISK = ''' + @backupDisk + '''' + CHAR(10)
SELECT @strSQL = @strSQL + 'WITH' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'REPLACE'
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE '''+ @datalogical + ''''+ ' TO '''+ @PhysicalDataPath + ''''
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE ''' + @loglogical + '''' + ' TO '''+ @PhysicalLogPath + ''''
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'alter database ' + @dbname + ' set online with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @restoreDir + '' + @bkpFile + ' /Q /F'
--PRINT @cmd
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
END
--This is supposed to remove the row once done
DELETE FROM #BackupFiles
WHERE @bkpFile = BackupFileName
-- Get the database to be backed up
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
--Wait a couple of seconds before starting the next one
WAITFOR delay '00:00:30'
END
Drop TABLE #Dir
Drop TABLE #BackupFiles
Drop TABLE #RestoreFileListOnly
SET NOCOUNT OFF
RETURN 0
GO
Transact SQL :: How To Find All Dynamic Scripted Stored Procedure In Database
Jul 2, 2015Is there any way/Process that we can find out all stored procedures from database where dynamic script has used in script.
View 6 Replies View RelatedSql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?
Feb 19, 2006Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedAre Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow 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 RelatedLarge Views Vs Multiple Small Views
Sep 6, 2007Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedRecompiling Views That Reference Other Views
Jun 28, 2007Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Quicky : Views Of Views Of Views
Feb 22, 2007Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Jan 7, 2007Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
Conditional Order By - Sort Result Set By Employee Number Ascending Order
Sep 24, 2012In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?
May 19, 2015I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Calculate Total Amount Of Order Details Based On Particular Order
Apr 10, 2014I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
Default Sort Order - Open Table - Select Without Order By
Mar 27, 2008Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Jan 4, 2008Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!