I am trying to pass a datename in to a database backup. The date is to be retrieved from the 'monthname' column which is stored in the 'tblbill' table. I have included my ridiculous bit of code below (which funnily enough does not work!!) just so that you can see what I am trying to do. Can anyone help?
DECLARE @name varchar(10)
backup database Bill to Disk ='c:@name.dat' with init
@name =select distinct monthname from tblbill where monthname is not null
how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.
I'm not sure if this question should be in this thread but anyway...
I have a form (Mricosoft Access front end) in which the user enter a Pending Date in one of the fields. The date is populated into my backend (SQL Server) withouht problems. Whenever the issue which had the pending date in the field is resolved I'd like to delete this date from this field and also from the back end. At the moment I'm not being able to pass the "value" Null to my back end (probably because it's a datetime type). I've searched the net throughout and I couldn't find a way around it. Only found solution to when the data is not date but numbers/letters, etc... Any ideas on how can I sort this out?
After several hours of trying, I trow the towel in the ring and come here to ask a question.
Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).
What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable
I try to do this in 2 sql execute tasks:
sqltask 1: "select @@DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?
DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
INT64 and UINT64 don't work error message that types for column and parameter are different
STRING works
Then I want to store this variable back in a table of a different data source
sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...
if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ???
if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.
Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?
How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?
I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this.
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I have a table in sql server, i need to import this table to another database in same sql server using DTS, In the table, we have a field called 'qualDate', I need to import the record that the qualDate is in the date of today and back off four years, for example, today is 8/10/2004, back off four year should be 8/10/2000, so i need only the record that qualDate is between 8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.
Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH
Hi, I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt FROM tblCalendar AS c WHERE (@WorkingDays = (SELECT COUNT(*) AS Expr1 FROM tblCalendar AS c2 WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1) AND (IsHoliday = 0)
-- Return the result of the function RETURN convert(varchar(12),@WorkingDate,106)
I'm trying to get a list of items by there last invoice date, but I keep getting errors. I have to join two table to get all the information needed to match a date with an item number.
select i.[Invoice-date], l.[Item-no] from [Invoice] as i join [invoice-line] as l on i.[Seq-no] = l.[Seq-no] (Select MAX(I2.[Invoice-date]) as LastDate, l2.[ITEM-No] from [invoice] AS I2 JOIN [invoice-line] AS L2 on i2.[Seq-no] = l2.[Seq-no] GROUP BY L2.[Item-no]) ITEMNUMBER WHERE i.[invoice-date] = lastdate.[invoice-date] and l.[item-no] = itemnumber.[item-no]
However, I keep getting this..
Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'.
I have an ASP.Net page that needs to sometimes pass a null parameter value to the querystring. It bombes out with an error that a date cannot be "" and that i need to supply the date.
Has any1 been able to get this right? How does one pass a null date through the querystring?
I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".
Create PROCEDURE sampleprocedure @tablename AS VARCHAR(4000), @date as date AS BEGIN declare @table varchar(1000)
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD') When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results. But when I run the report from Preview, I get no results at all no matter what format I use in the function. Any chance any of you have seen this and know how to work with it?
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission ... ... @IncludeOrderDateAsCriterion int, @OrderDateRangeStart date, @OrderDateRangeEnd date as BEGIN ... ... WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and (@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:
4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)
which displays correct data.
However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:
7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)
The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).
I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?
I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.
I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.
Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.
Thanks for your help,
Jonathan.
Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 7.0.5730.11 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
Hi everyone, I am working in a company where data is changed daily (IT department of a shopping center) where item prices change everyday, the quantity available in stock vary daily,...What is the scenarios available for the back up on SQL Server 2000 (I am a developer not a database administrator, but I have a task to do which is to make a back up for the critical data) . could u help me with some links or idea.I was thinking of doing a full back up one time everyweek and daily I will do a differential backup?is that the best solution?Thanks.
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
Hi, i have one database question here. Let me describe my situation first:
i have two different hard disk drives, here i call it HDD1 and HDD2. i want to format my HDD1, but all my database tables are stored in HDD1. i want to know is there any way for me to backup my database table to HDD2, and restore it back later to HDD1.
I am trying to back up database to a different server. I am able to map the serve to my current ones, however, when I go to Enterprise Manager - back up DB, I can not see the mapped drive from back up file location, I wasn't able to see any other disk location but the local drives.
Can somebody tell me how can I back up database to a different server from Enterprise Manager or any syntax?
Hi all, I'm getting the following error message in NT event view:
Error: 9002, Severity: 17, State: 2 The log file for database 'db_sys' is full. Back up the transaction log for the database to free up some log space.
But I don't know how to back up the transaction log for the database. Do I need TRUNCTE LOG (in E.M) to free up some log space? I only know back up all the database using ALL TASKS -> BACKUP DATABASE in E.M. Please help me. Thanks in advance. TH