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
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Sunday, March 25, 2012
Thursday, March 8, 2012
Database Access Error?
I got this error and ive no idea whey, whenever i click on a database in Visual Studio 2005, to use it (i was on my way to do stored procedures) i get that error.
Also, when the web application is running i get this error:
"Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'DOUGAL\dougal.m*******'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below."
I stared out my 2nd name. I realise this seems to be a failure to login to the database, however, they are file databases and i'm not aware of having ever set a password for either of them!
I also tried deleting the databases to add them again (they have no data at the moment) and i get the same error in Visual Studio and it doesnt let me.
Any suggestions will be greatly appreciatedLog in your system as DOUGAL\dougal.m*******, and go to the directory D:\www\Flougal\App_Data and see if you can first, even get there, and if you can, see if you can even see the database.mdf file, I'm guessing you can't. It's not a SQL Permissions error, it's an OS error (it mentions that in the error). That user can't open that file (in fact, you that user can't even SEE that file).|||i'm afraid i actually can see the file logged in as that user in the error|||Managed to fix the problem.
It turns out a file database cant be attached to a database server in the SQL Server Management Studio aswell as being used in VS2005.
Silly me.![Embarrassed [:$]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tgSp2V8f0OS2khczbeKVupZDesJqB9t_8zvhQcNazG-utunUS7mFtNnc9QEPXfVhEF8jwTtVYyx3z7MjSqAQ6cb9GRqJxuVbBTCKWEebQHZO_K3I1WIA=s0-d)
Wednesday, March 7, 2012
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.
Saturday, February 25, 2012
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
Sunday, February 19, 2012
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
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
Friday, February 17, 2012
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
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 and Length in SQL Server
Hello folks!
I have written a stored proc that selects data from this table:
A1 AA
A2 BB
B1 AAA
B2 BBB
and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB
In short, I wanted to concatenate data in the second column.
My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.
Any ideas how I can get around this problem?
Thanks!
ParulVChances are, it is not limiting the string to 256 characters. This is probably due to the default 256 character result set width in Query Analyzer. Go through Query Analyzer's options and bump it up to a higher value.
I have written a stored proc that selects data from this table:
A1 AA
A2 BB
B1 AAA
B2 BBB
and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB
In short, I wanted to concatenate data in the second column.
My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.
Any ideas how I can get around this problem?
Thanks!
ParulVChances are, it is not limiting the string to 256 characters. This is probably due to the default 256 character result set width in Query Analyzer. Go through Query Analyzer's options and bump it up to a higher value.
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...
>
>
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,
> >>
> >>
> >>
>
>
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,
> >>
> >>
> >>
>
>
Tuesday, February 14, 2012
Data transformation services
Hi
I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.
I have tried to install DTS with no success. I am running the following
Visual web studio express
SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components
Installed SQLEXPR_Toolkit.exe with all its options
Installed SQLServer2005_DTS.MSI
When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.
Can somebody please help me.
DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.
|||Hi
Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.
Regards
Subscribe to:
Posts (Atom)