Database backup!....................

Hi All,
Does anyone know how to write an xp_ Stored Procedure or
dll to make to normal sql backup run faster.
Thanks and please point me to the right direction or
source for this.
tony
trying to issue a SQL Server backup thru a self created DLL will most likely
take longer and have more overhead. I would suggest you look at SQL
LiteSpeed from http://www.imceda.com/ to speed up your backups. They will
also be much smaller as well.
Andrew J. Kelly SQL MVP
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony
|||You can also use more than one backup device - this may help your
situation. You can find some other suggestions and things to monitor
for bottlenecks in the books online topic:
Optimizing Backup and Restore Performance
-Sue
On Mon, 13 Sep 2004 10:53:00 -0700, "tony"
<anonymous@.discussions.microsoft.com> wrote:

>Hi All,
>Does anyone know how to write an xp_ Stored Procedure or
>dll to make to normal sql backup run faster.
>Thanks and please point me to the right direction or
>source for this.
>tony
|||Have a look at our article at http://www.yohz.com/articles_01.html. Amongst
other things, it describes how Microsoft provides a standard interface for
third party software vendors to implement their own backup/restore solution.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony

database backup

In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
sql

database backup

In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.Mohit
SET @.CommandString = 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.name +
'" MD "' + @.LocalBackupPath + '\' + @.name + '"'
EXEC xp_cmdshell @.CommandString
SET @.SQL = 'BACKUP DATABASE [' + @.name + '] TO DISK = N''' +
@.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),
112) + '.bak'' WITH INIT, NOUNLOAD, NAME = N''' + @.name + ' backup'', SKIP,
STATS = 10, NOFORMAT'
EXEC(@.SQL)
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
>
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.|||On Jan 10, 4:51=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Mohit
> =A0 SET @.CommandString =3D 'IF NOT EXIST "' + @.LocalBackupPath + '\' + @.na=me +
> '" MD "' + @.LocalBackupPath + '\' + @.name + '"'
> =A0 EXEC xp_cmdshell @.CommandString
> =A0 SET @.SQL =3D 'BACKUP DATABASE [' + @.name + '] TO DISK =3D N''' +
> @.LocalBackupPath + '\' + @.name + '\' + @.name + CONVERT(VARCHAR, GETDATE(),=
> 112) + '.bak'' WITH INIT, NOUNLOAD, NAME =3D N''' + @.name + ' backup'', SK=IP,
> STATS =3D 10, NOFORMAT'
> =A0 EXEC(@.SQL)
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:69386449-4e88-44f4-9081-455ab5e32941@.k2g2000hse.googlegroups.com...
>
> > In procedure create folder for each database and backup of the
> > database should get saved in that folder.
> > For e.g =A0: If your database name is 'Test' then in procedure create
> > folder 'test' and store backup of test database in test folder. Then
> > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > and store backup in that folder.- Hide quoted text -
> - Show quoted text -
hi,
i dont have admin permission to execute xp_cmdshell. Is there any
other option to solve this question.

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.

Data type of parameter passing to a stored procedure

Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.

The exact error message that occurs is:

ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@.EMedical", advarwchar, adParamInput)

I need to know what to write instead of advarwchar?
Thanks in advance1) memo and varchar isn't the same!
2) you should specify a size for varchar type!
.parameters.Append cmd.CreateParameter ("@.EMedical", advarwchar, adParamInput, 1000)
or other size instead of 1000

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 (STRING)

HI EVERY ONE,
I want to use long datatype as a string, which has variable length and may
exceed (8000) character. in a stored procedure or function as a variable.
when i tried to use the datatype ( Text, or nText ) i was not able to create
a stored procedure which manipulate where i have the following error message:
"The text, ntext, and image data types are invalid for local variables."
Any suggestions please.
Thanks
Rami,
Hi
Unfortunately for a local variable you are limited. You may want to use
multiple variables or a table as an alternative.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> HI EVERY ONE,
> I want to use long datatype as a string, which has variable length and may
> exceed (8000) character. in a stored procedure or function as a variable.
> when i tried to use the datatype ( Text, or nText ) i was not able to
> create
> a stored procedure which manipulate where i have the following error
> message:
> "The text, ntext, and image data types are invalid for local variables."
> Any suggestions please.
> Thanks
> Rami,
|||I Tried to use a table fields variable but u can not manipulate the field the
same way u manipulate usual string,
for example:
Create Table T1
(
value TEXT
)
the following statment will generate an error message:
Update T1 set value = value + 'any additional string'
could u provide me with better techniques.
"John Bell" wrote:

> Hi
> Unfortunately for a local variable you are limited. You may want to use
> multiple variables or a table as an alternative.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
>
>
|||Hi
Unfortunately to read/write/update text you require specific functions For
UPDATETEXT see
http://msdn.microsoft.com/library/de...reate_4hk5.asp
Large string handing has improved in SQL Server 2005.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...[vbcol=seagreen]
>I Tried to use a table fields variable but u can not manipulate the field
>the
> same way u manipulate usual string,
> for example:
> Create Table T1
> (
> value TEXT
> )
> the following statment will generate an error message:
> Update T1 set value = value + 'any additional string'
> could u provide me with better techniques.
> "John Bell" wrote:
|||Thanks John,
Rami
"John Bell" wrote:

> Hi
> Unfortunately to read/write/update text you require specific functions For
> UPDATETEXT see
> http://msdn.microsoft.com/library/de...reate_4hk5.asp
> Large string handing has improved in SQL Server 2005.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
>
>

DATA TYPE (STRING)

HI EVERY ONE,
I want to use long datatype as a string, which has variable length and may
exceed (8000) character. in a stored procedure or function as a variable.
when i tried to use the datatype ( Text, or nText ) i was not able to create
a stored procedure which manipulate where i have the following error message:
"The text, ntext, and image data types are invalid for local variables."
Any suggestions please.
Thanks
Rami,Hi
Unfortunately for a local variable you are limited. You may want to use
multiple variables or a table as an alternative.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> HI EVERY ONE,
> I want to use long datatype as a string, which has variable length and may
> exceed (8000) character. in a stored procedure or function as a variable.
> when i tried to use the datatype ( Text, or nText ) i was not able to
> create
> a stored procedure which manipulate where i have the following error
> message:
> "The text, ntext, and image data types are invalid for local variables."
> Any suggestions please.
> Thanks
> Rami,|||I Tried to use a table fields variable but u can not manipulate the field the
same way u manipulate usual string,
for example:
Create Table T1
(
value TEXT
)
the following statment will generate an error message:
Update T1 set value = value + 'any additional string'
could u provide me with better techniques.
"John Bell" wrote:
> Hi
> Unfortunately for a local variable you are limited. You may want to use
> multiple variables or a table as an alternative.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> > HI EVERY ONE,
> >
> > I want to use long datatype as a string, which has variable length and may
> > exceed (8000) character. in a stored procedure or function as a variable.
> >
> > when i tried to use the datatype ( Text, or nText ) i was not able to
> > create
> > a stored procedure which manipulate where i have the following error
> > message:
> >
> > "The text, ntext, and image data types are invalid for local variables."
> >
> > Any suggestions please.
> >
> > Thanks
> > Rami,
>
>|||Hi
Unfortunately to read/write/update text you require specific functions For
UPDATETEXT see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
Large string handing has improved in SQL Server 2005.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
>I Tried to use a table fields variable but u can not manipulate the field
>the
> same way u manipulate usual string,
> for example:
> Create Table T1
> (
> value TEXT
> )
> the following statment will generate an error message:
> Update T1 set value = value + 'any additional string'
> could u provide me with better techniques.
> "John Bell" wrote:
>> Hi
>> Unfortunately for a local variable you are limited. You may want to use
>> multiple variables or a table as an alternative.
>> John
>> "Rami" <Rami@.discussions.microsoft.com> wrote in message
>> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
>> > HI EVERY ONE,
>> >
>> > I want to use long datatype as a string, which has variable length and
>> > may
>> > exceed (8000) character. in a stored procedure or function as a
>> > variable.
>> >
>> > when i tried to use the datatype ( Text, or nText ) i was not able to
>> > create
>> > a stored procedure which manipulate where i have the following error
>> > message:
>> >
>> > "The text, ntext, and image data types are invalid for local
>> > variables."
>> >
>> > Any suggestions please.
>> >
>> > Thanks
>> > Rami,
>>|||Thanks John,
Rami
"John Bell" wrote:
> Hi
> Unfortunately to read/write/update text you require specific functions For
> UPDATETEXT see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
> Large string handing has improved in SQL Server 2005.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
> >I Tried to use a table fields variable but u can not manipulate the field
> >the
> > same way u manipulate usual string,
> >
> > for example:
> >
> > Create Table T1
> > (
> > value TEXT
> > )
> >
> > the following statment will generate an error message:
> >
> > Update T1 set value = value + 'any additional string'
> >
> > could u provide me with better techniques.
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Unfortunately for a local variable you are limited. You may want to use
> >> multiple variables or a table as an alternative.
> >>
> >> John
> >>
> >> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> >> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> >> > HI EVERY ONE,
> >> >
> >> > I want to use long datatype as a string, which has variable length and
> >> > may
> >> > exceed (8000) character. in a stored procedure or function as a
> >> > variable.
> >> >
> >> > when i tried to use the datatype ( Text, or nText ) i was not able to
> >> > create
> >> > a stored procedure which manipulate where i have the following error
> >> > message:
> >> >
> >> > "The text, ntext, and image data types are invalid for local
> >> > variables."
> >> >
> >> > Any suggestions please.
> >> >
> >> > Thanks
> >> > Rami,
> >>
> >>
> >>
>
>

Data Truncation in sp parameter

It seems that there is no error message if the length of the parameter
value exceeds parameter datalength
Create Procedure #test (@.data varchar(10))
as
Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
The result is
This is te
Why does SQL Server not raise any error?
MadhivananThat is what is called implicit conversion. Read BOL on this
Regards
R.D
--Knowledge gets doubled when shared
"Madhivanan" wrote:

> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||Hi
You just "selected" the data.SQL Server cuts off the result. Try doing
insertion and you will get the error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(2))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129800867.933647.36380@.g49g2000cwa.googlegroups.com...
> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||But when you give width 10, there is no error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(10))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
Madhivanan|||well,because @.data is already varchar(10) (sql server cuts off) and your
column had declared as varchar(10) , what's problem?
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129802989.571260.209840@.f14g2000cwb.googlegroups.com...
> But when you give width 10, there is no error
> Create Procedure #test (@.data varchar(10))
> as
> create table #t (col varchar(10))
> insert into #t Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
>
> Madhivanan
>|||> But when you give width 10, there is no error
That's right, because the parameter already made your data 10 characters
long, which fits just nicely in a VARCHAR(10) column.
You realize that not all data validation *has* to happen within the
database, right?|||Well
My question is why does SQL Server doesnt raise an error when the
length of value is nore than the parameter length?
I expect the same error that happend in this case
Declare @.t table(data varchar(10))
insert into @.t values('This is testing')
select data from @.t
Server: Msg 8152, Level 16, State 9, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
Madhivanan