|
No, you can choose whether you want to export schemas, tablespaces, tables or the whole database.
Data Pump Export[^]
|
|
|
|
|
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. We are not using any third party tool to take backup.
Thanks,
SREE
|
|
|
|
|
|
Hi ,
I am getting this message on SQL log but i am not initiated any SQL job maintenance.
Please help me what this msg indicates.We are taking Drive backup.
Msg:
Database backed up. Database: abc, creation date(time): 2015/04/17(08:07:47), pages dumped:56589001, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{622759E005}7'}). This is an informational message only. No user action is required.
Thanks,
Sree
Thanks,
Sree.
|
|
|
|
|
|
I have to do an upgrade of MSSQL database like this :
I have folders that each contain an sql script
I must have a sql or batch script that allows me to upgrade my database by running scripts that exist in these folders depending on the version of the current database:
Example:
folders
1 Folder has the name: v2.00
2 Folder has the name: v3.00
3 Folder has the name: v4.00
the initial version of the database: v3.00
the version of the final database: v4.00
So I have to have a script (or batch) that allows me to:
execute just the proper scripts (in this case v3.00 and v4.00)
|
|
|
|
|
How do you determine the "version" of the database?
|
|
|
|
|
there is a table which contains this information
Table "TVersion" and field "FVersion"
|
|
|
|
|
khaliloenit wrote: I must have a sql or batch script In that case you'll need to write one.
You could try to do it completely from SQL, but that requires the xp_cmdshell sproc. Alternatively you write a batch-file for DOS, executing your scripts using the isql command.
Easiest way might be a powershell-script.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Have you an example how to do it ?
Best,
|
|
|
|
|
Which of the three versions?
..but no, not for that specific scenario.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
You can use the sqlcmd utility from a windows batch file e.g.
@ECHO OFF
FOR /F %%i IN ('sqlcmd -S YourDBInstance -Q"set nocount on;select CONVERT(DECIMAL(10,2),FVersion) from TVersion"') DO SET VNO=%%i
call .\v%VNO%\script.bat
@echo done.
Things to note
- you'll have to insert your own information for the -S parameter
- Do not put spaces around the = sign in SET VNO=%%i
- The conversion in the query is necessary to ensure the .00 is returned correctly
- you will need to substitute your script name for script.bat
|
|
|
|
|
I'm looking for a way of joining two tables using a function returning a ranking value where the rows joined would be decided on the returning value of a function.
Assume I'm joining table X and Table Y using a function f(X,Y)
If the values returned by the function from a cartesian join would be:
I would want this "excluding functional join" to return:
Y1,X3
Y2,X1
Y3,X2
Is this at all possible without using procedural code?
<edit>What I want is something similar to the Gale-Shapley[^] algorithm but using a special text search instead of simple preferences.
It's easy enough to do in procedural code, but I want to avoid an RBAR solution</edit>
modified 17-Jun-16 2:34am.
|
|
|
|
|
Jörgen Andersson wrote: without using procedural code I do doubt it.
One way might be to create a separate view that generates the ranking (can't see the PK field) and use the view in the join.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: can't see the PK field That's because they're of no use in this case.
I'm trying to map two tables in two different databases on their description fields, and some descriptions are basically the same but with added information for some rows, and those are the big problem.
I'm going to try to do it using a recursive CTE.
|
|
|
|
|
Jörgen Andersson wrote: m going to try to do it using a recursive CTE. why am I not surprised by that. Your cte skills seem to be quite adequate. Let us know if you achieve a solution
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I believe I have found one, the proper test will be on Monday. But as it is done using a recursive CTE, it's not the most effective one.
I'm going to have another look at Richards version tonight.
|
|
|
|
|
In the end I ended up using a variant of Richards suggestion.
|
|
|
|
|
How about something like this:
- Cross-join the two tables;
- Generate the ranking value for each pair;
- Generate a
ROW_NUMBER , partitioned by the primary key of Y, and ordered by the ranking value (descending); - Select the rows where the row number is
1 ;
WITH cteRanked As
(
SELECT
X.PK As XPK,
X.OtherColumnsFromX,
Y.PK As YPK,
Y.OtherColumnsFromY,
RankingFunction(X.Value, Y.Value) As R
FROM
X CROSS JOIN Y
),
cteOrdered As
(
SELECT
XPK,
OtherColumnsFromX,
YPK,
OtherColumnsFromY,
ROW_NUMBER() OVER (PARTITION BY YPK ORDER BY R DESC) As RN
FROM
cteRanked
)
SELECT
XPK,
OtherColumnsFromX,
YPK,
OtherColumnsFromY
FROM
cteOrdered
WHERE
RN = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Not bad, but it's not going all the way. I need to partition it by both X and Y.
|
|
|
|
|
I must be missing something - surely, if you partitioned by both X and Y, you'd end up with the Cartesian product of the two tables?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well, that, is exactly my problem!
|
|
|
|
|
The data turned out to be a lot less ordered or rather more asymmetrical than expected (just like I should've expected )
And the mapping between the databases also turned out to be important in only one direction.
So I ended up using a variant of your solution, but using RANK instead of ROW_NUMBER.
Now I'm working on refining the ranking function for whenever I get duplicate matches
|
|
|
|
|
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)
// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);
I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data.
Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pv
modified 12-Jun-16 9:19am.
|
|
|
|
|