Hi friends,
I am backing up the database using the Database Maintenance Plan.
Everything is in place. I want to save the file name in the format yyyy-mm-dd_finbck.bak. how can i assign the date from here itself ? this should be done through the DB maintenance plan only.
is it possible ? can we assign the date to the backup file through the DB maintenence plan ?
kindly guide.
Regards,
Amit
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
Hi,
Is there a format function in MDX which will help me solve the following
scenario:
One of my measures is Net Sales and I have tried all different formats
in Analysis Services so that it shows without the decimal places i.e.
instead of showing the Net Sales for any selected combination of
Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my
requirement. However, in my front-end (Microsoft Data Analyzer), I am
still getting it as 123,456.04 (even when the same in Analysis services,
i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data
Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me
get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Many TIA
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
View 3 Replies View RelatedI've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";
SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231");
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;
myConn.Open();
dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
View 16 Replies View RelatedI want to make simple database application but I want that I make just one transaction with the database..
If I have , say 10 insert queries, i want to transact with the database just once.
Somebody told me this could be done by 'containers' or 'data transfer objects'
So please, somebody help..
Hi,
I made a DTS which appends data coming infrom a view to an exisiting table.So far no problem and all goes well.
I am facing a problem due to the format of the date that is coming in (getting appended) and while going through BOL, came across the following topic:
mk:@MSITStore:C:Program%20FilesMicrosoft%20SQL%2 0Server80ToolsBookshowtosql.chm::/ht_dts_trns_97ou.htm
I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).
Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.
Many TIA
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
select @xml = bulkcolumn from openrowset(bulk 'C:Documents and
SettingsKasiDesktopewsrss.xml' , single_blob) as channel
here after bulk instead of giving path, we have to give parameter so that the paramter takes the value from the table.
the tables contains paths of xml files
there is a prblem with data in pivoting the table.
problem is like this--
there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..
i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.
this data is loaded from excel sheet to sql sever table.
wht can i do for this problem.
is there any method to truncate the indivisual data, i m using nvarchar datatype for this.
Dear All,
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
Thanks in Advance.
Is it possible to retrieve all tables that a given one is related tovia foreign keys?
View 2 Replies View RelatedWe had been running SQL Server without any control of security (sincethe company is very small -100 employees). All of us know the adminpassword and has been accessing the database as admin. Our databaseserver crashed due to hardware failure twice last month and we lost alot of important data. Now the management is taking the control ofserver access seriously.SQL Enterprise manager is installed on many PCs and any one can deleteany database with a right click.My question is:1. Can the enterprise manager be installed on client's PC with alimited right (or as a user not as admin)?We need to limit the user's access of using the Enterprise Manager.In other words, how can we set this up for different users.2. How can we keep running SQL Server if one server fails?Clustering or Replication or Mirroring? OI would highly appreciate if you could direct me to any website orresources on how to set up security of SQL Server (2000 with the latestservice pack).Thanks a million in advance.Best regards,Mamun
View 2 Replies View Related
Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please
Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
Hi,
I have a data including "year", "month" and "day" columns and I'm using these columns as parameter to create a report. I'm using the parameters successfully in my report. I import all possible year-month-day combinations but in my data there are some unmatched combinations. (For ex, for 2007 there's just last 3 months of the year, and some days are missing) To make the report more affective, I wanna make the parameters dynamic; when I choose 2007 in year-combobox I wanna see just the related months (not all 12) and the related days after I choose the month. Is it possible in reporting services?
Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.
The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.
I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.
Thanks.
Regards,
Sumis
I have 2 related tables I want to include in a single full text search. How would I go about setting this up?
View 1 Replies View RelatedHere is the first table:CREATE TABLE "Games"("ID" int,"Title" varchar(30),"Popularity" int)Here is the second table:CREATE TABLE "Related"("ID" int,"rID" int)Here is the data for the first table:INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)Here is the data for the second table:INSERT INTO Related (ID, rID) VALUES (1,2)INSERT INTO Related (ID, rID) VALUES (2,1)INSERT INTO Related (ID, rID) VALUES (1,3)INSERT INTO Related (ID, rID) VALUES (2,3)INSERT INTO Related (ID, rID) VALUES (3,1)INSERT INTO Related (ID, rID) VALUES (3,2)INSERT INTO Related (ID, rID) VALUES (6,7)INSERT INTO Related (ID, rID) VALUES (7,6)Now, lets say a person wants to grab the top three results:SET ROWCOUNT 3Select * from Game ORDER BY PopularityResult Set:1 Tag Dodgeball 102 Freeball 103 Dr. Dodgeball 10But,
what if this person doesn't want to play three related games in a row?
This is my dilemma. How do I get the following result set:1 Tag Dodgeball 102 Kickball 83 Fooseball 8The
related table tells which games are related to which, but how do I get
the select statement to realize that it should not select any games
that are related once it already has one in the result set?I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.Pseudo-Code might look something like this:SET ROWCOUNT 3Select * from Game ORDER BY Popularity (where ID !=rID)Please help. I've banging my head against this one for quite some time.Respectfully,David.
Hello EveryOne,I have a program which insert the Data into table Fine. But the problem is that whenever expecption is raised the auto increament id is increases..i want that user enter the record and without error the auto increament id is 1 and when second user enter the record on table and exception raised and next time he entered again then his record auto increament id is 3 why? But record is not entered only the expecption is raised.How to resolve this problem to make cosistency in record of other tables.? Help me
View 2 Replies View RelatedPlz help me out making a SELECT for my imagegallery.
Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all.
Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId}
In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath]
To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle]
To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select
And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID()
-But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
This is the error i get every now and then when i attempt to browse to a page. It seems that i am getting this error as its trying to connection to a sql express database??? Only i have set my project up to use sql server 2000 and i am using that DB but every now and then i get this error. Im not sure what else i need to do? Any ideas?
Hi All,
I've created a DTS package with 2 connections to 2 servers. The operation transfers a db in server1 to server2. I have to set the sa password for each of the server (when setting up the connections).
Our policy is to change the server password regularly. This creates problem as I have to change the password setting at each of the connections. If there are 100 connections, I have to change it 100 times. Is there a way to avoid this so that we can avoid this tedious work.
TIA
Rgds,
scyeo
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter
Set counter = currentpage
If counter > lastpage
Goto task exit loop
Else perform task - (insert string into table)
Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int
DECLARE @Current string
DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage
SET @Count= dbSL.tblPaging._colPagingCount
SET @Last= dbSL.tblPaging._colLastPage
WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN
PRINT 'Hello World'
SET @Count = @Count +1
END
Any help much appreciatted.
Thanks
nme
Hi all,
Can a table be created within a trigger? I looked in books online and found the list of transact-sql statements that are not allowed in a trigger. Create table is not one of them, so I assume that it can be done within a trigger. Correct? Thanks for your help.
Hi,
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28
I would like to know the Package name related to.
Any help would be very appreciated.
Regards
I have a couple questions about replication (for both 2000 and 2005 servers):
1. which system tables/dmvs/system sprocs can I look at to determine which columns of a table are being replicated?
2. which system tables/dmvs/sprocs can I call to get metadata about publishers and subscribers?
Thanks!
I have a Transactional table containing IDs that relate to each other. The table highlights the transactions that move one ID to another, by storing an ID and a IDNew (Basically from ID to ID).
My requirement is to create a Inline Table Valued Function that will efficiently return a single ID column to show all related IDs by looking at the ID and the IDNew fields.
I need to pass in a single ID to get all relations or a list of IDs through xml.
I have managed to achieve this by making use of two recursive cte's and then combining the results with union statements to create a distinct list of values.
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
[code]....
hi this is probably a dumb question with an easy answer but does anyone know how to do this...
2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)
basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?
do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?
hi,
Can someone give me the sql query to find out data that has been added in the current month.(table contains a field 'date' of type datetime.)
rthsh.wst