Database backup

I tried to backup a database but failed:
In EM, right click Northwind, select All Tasks, Backup a database...
Enter Description, select complete backup, Add a destination to a location,
set the shedule.
But there is not backup after the schedule time.
Anything I missed ?
Check inside Management, SQL Agent, Jobs and see if there is a n error
associated with the job
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>I tried to backup a database but failed:
> In EM, right click Northwind, select All Tasks, Backup a database...
> Enter Description, select complete backup, Add a destination to a
> location,
> set the shedule.
> But there is not backup after the schedule time.
> Anything I missed ?
>
>
|||Ah, may be the SQL Server Agent has not started.
"Mary Bray" <no@.spam.com> wrote in message
news:%231pAMcspEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Check inside Management, SQL Agent, Jobs and see if there is a n error
> associated with the job
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>

Database backup

I tried to backup a database but failed:
In EM, right click Northwind, select All Tasks, Backup a database...
Enter Description, select complete backup, Add a destination to a location,
set the shedule.
But there is not backup after the schedule time.
Anything I missed ?Check inside Management, SQL Agent, Jobs and see if there is a n error
associated with the job
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>I tried to backup a database but failed:
> In EM, right click Northwind, select All Tasks, Backup a database...
> Enter Description, select complete backup, Add a destination to a
> location,
> set the shedule.
> But there is not backup after the schedule time.
> Anything I missed ?
>
>|||Ah, may be the SQL Server Agent has not started.
"Mary Bray" <no@.spam.com> wrote in message
news:%231pAMcspEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Check inside Management, SQL Agent, Jobs and see if there is a n error
> associated with the job
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
> >I tried to backup a database but failed:
> >
> > In EM, right click Northwind, select All Tasks, Backup a database...
> > Enter Description, select complete backup, Add a destination to a
> > location,
> > set the shedule.
> >
> > But there is not backup after the schedule time.
> >
> > Anything I missed ?
> >
> >
> >
>

DataAdapter.Update updates zero records

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?I found the source of the problem. It was a result of a conflict elsewhere. So, if anyone else is trying to use a combination of stored procedures and normal sql queries, it will work fine, even if the statements do not match up perfectly with the dataset. The queries simply need to meet any requirements of the database.

DataAdapter.Update Method Question.

Hi,

I am trying to use DataAdapter.Update to save a file stream into SQl Express.

I have a dialog box that lets user select the file:

openFileDialog1.ShowDialog();

I want to put

openFileDialog1.OpenFile();

Into

this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);

I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.

Thanks,
Ke

Take a look on the links I posted here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

DataAdapter - SELECT Statement - items in last 30 days

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

ThanksTry with the following SQL statement, i belive it should work.

select * from <tablename> where datediff(day, <columnname>, getdate()) < 30

Hope it solves your issue.|||Thanks very much, it worked a treat

Data Using DTS

Hi I have a query and when I run it in SQL query analyzer and select "results to file option I get the nice header of the column in included in the file that it generates. My problem is when I want to do this in a DTS job, the header does not align as nicely. For example see below...
Query run in Query analyzer and select results in file option I get this header...
Employee ID Social Security Number Birth Date
---------------
111111111 123456789 01/01/2000
(not how it is aligned)...
Result from DTS...
"Employee ID","Social Security Number","Birth Date"
"111111111","123456789","01/01/2000"
How can I output as the query analyzer using DTS?
Thanks.

In the Export Data wizard, change the "Delimited" option to "Fixed Width".

Data type Object in Send Mail Task?

I have an Execute SQL Task that runs a simple SELECT query. I have the result set = Full Result Set. The variable is of type Object and the value is System.Oject. After successful completion of the Execute SQL Task, I am doing a Send Mail task. For the Message Source, I want to use this Object. The drop down is only listing variables of type String. Can you not use a variable of type Object in a Send Mail Task? If not, what is the easiest workaround? Thanks!

No, you can't use an object for the message source. You could use a column from the resultset, though. If you give more description on what you are trying to do, we'll be able to suggest some work arounds.

If you are trying to send the contents of the recordset in an email, here are a few posts that might help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1768087&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1502762&SiteID=1

Data type error

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

You should be including single quotes around sAcct1 in the WHERE clause, such as "...WHERE Account < '" + @.User :: sAcct1 + "'"

I believe SQL Server is converting the value of your Account column to a numeric to match the datatype you are sending it. The error is occurring because you have data in that column that fails the conversion.
|||

wpwebster wrote:

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

Is it possible that Account column in tblGL table is numeric? if so, you make sure that you cast accordingly the values of acct1 from Acttbl table. Why are you casting it in the query as varchar and putting it in a string variable? would not be better to to use a data type that is consistent with tblGL.Account?

|||

Thanks for the input. It helped me get to the bottom of it. The Account field is a varchar(13) field although the accounts are in a format of something like 400.00 There was however several records I found where the Accocunt was NA, when I changed those through a derived column data flow control to a "0" it worked fine.

I still find it a bit puzzling that the WHERE clause worked when it was WHERE Account < '400.00' but wouldnt' work when it was WHERE Account < @.UserVariable

Making sure all Accounts looked like numbers did the trick though.

Thanks again for the input.

Regards

Bill

data type conversion in stored procedure

Hi,

I have a stored procedure a portion of which looks like this:

IF (CAST (@.intMyDate AS datetime)) IN (
SELECT DISTINCT SHOW_END_DATE
FROM PayPerView PP
WHERE PP_Pay_Indicator = 'N' )
BEGIN
--ToDo Here
END

where:
@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

however when I run the procedure in sql query analyzer as:

EXEC sp_mystoredproc param1, param2

i get the error:

Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

what is the proper way of doing the conversion from int to datetime in stored procedure?

thank you!

cheers,
g11DBPost some sample data, along with the datetime values you expect them to be converted to.|||Hi blindman,

Below are the sample data:

@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

I just want to convert @.intMyDate of type int which is of the form 19991013 to a type datetime which is of the form 13/10/1999

I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?

Thank you again.

cheers,
g11DB|||I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?
Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH|||Yes, convert it to char(8) and then deal with it as a string.
And when you find some spare time, go and shoot the idiot who decided to store date values that way. You'll be glad you did.|||In case you are interested (and even if you are not :)) casting 19991013 as a date was basically saying to SQL Server:
Please add 19, 991, 013 days to 1st Jan 1900 and give me the date. SQL Server obviously isn't future proofed because it can't handle dates 54 thousand years into the future.

That's why Blindman shoots people like that :)

probably the most exciting thing you'll read about SQL Server dates this week:
http://www.dbforums.com/showthread.php?t=1212546|||Actually, I just enjoy shooting people.|||Hi,
try this
DECLARE @.intMyDate INT
DECLARE @.FFDATE VARCHAR(50)
DECLARE @.TDATE DATETIME
SET @.intMyDate = 19991013

SET @.FFDATE = SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),5,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),7,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),1,4)
SET @.TDATE = CAST(@.FFDATE AS DATETIME)

PRINT @.TDATE|||That's jolly good code however I would still recommend you use the same technique but output the date in ISO (YYYY-MM-DD) format. If you don't believe me then check the link I provided and you'll see Pat making the same point :)|||I Agree, But the poster wants in MM/dd/yyyy format.|||No, he just wants to convert it to a datetime datatype, so the preference for formatting the intermediary string as YYYY-MM-DD is valid. How the resulting datetime value is displayed is a secondary issue. Please revue the section on datetime datatype in Books Online.|||Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH

and you always have the chance of use words for months anyway, so '08/april/2006' never would be 4th of august ;)

declare @.d datetime
set @.d = '08/april/2006'
select @.d

--> 2006-04-08 00:00:00.000

Data Type Confusion...

Which Datatype should i choose for Field "AGE"

Do i select "Int", "Char"?

SQL Server...in Table Desgin, The length for Int only can be max of 4 ?

Is this mean it can only store 4 digit ?

What if i want to store numbers but more then 4 digit ?

What will be the appropriate datatype to store numbers?For age, an appropriate data type would be 'int', you should however contemplate SmallDateTime if you need the full Date of Birth. An 'int' has a storage size of 4 bytes, hence 4, and can be of values from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Numbers can be stored using different data types, depending on what they represent and will be used for. E.g, for money, you simply use the data type 'money'.

Below are all numerics available on MS Sql Server 2000

EXACT NUMERICS
============
Integers
-------
bigint = Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int = Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint = Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint = Integer data from 0 through 255.
-------
bit
-------
bit = Integer data with either a 1 or 0 value.
-------
decimal and numeric
-------
decimal = Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

numeric = Functionally equivalent to decimal.
-------
money and smallmoney
-------
money = Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney = Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
-------
Approximate Numerics
-------
float = Floating precision number data from -1.79E + 308 through 1.79E + 308.

real = Floating precision number data from -3.40E + 38 through 3.40E + 38.|||Hie...

Thanks alot for the info...

I just realize the Length "4" for "int" doesn't meant the total number of 4 digit...?

But how bout the Datatype "Char" or "NvarChar", the length is the number of character is it?|||you can use tinyint for AGE|||:: you can use tinyint for AGE

If you do not expect the person to live longer than a century, yeah :-) .|||::But how bout the Datatype "Char" or "NvarChar", the length is the number of character is
::it?

This is documented in the - documentation, you know.

For char and varchar fields, this is indeed the number of chars you store / can store.