Sql Enterprise Edition: 2012

Thursday, March 29, 2012

Equivalant Exception for DUP_VAL_ON_INDEX for SQL Server

Hello,
We are in the process of migrating the Oracle database to SQL Server. We need the help on the Exception Handling in SQL Server.
The existing Oracle stored procedure is using the Exception DUP_VAL_ON_INDEX, which raises when the duplicate values gets inserted in the Indexed column. (typcially used with INSERT statement)
We need the equivalant statement for the exception mentioned above.
I request you to elp us out in this case.
Thanking in advance.
Duplicates for a unique index insert return the error number
is 2601. The error message depends on the object name but
it's:
Cannot insert duplicate key row in object '%.*ls' with
unique index '%.*ls'.
-Sue
On Wed, 31 Mar 2004 03:21:06 -0800, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>We are in the process of migrating the Oracle database to SQL Server. We need the help on the Exception Handling in SQL Server.
>The existing Oracle stored procedure is using the Exception DUP_VAL_ON_INDEX, which raises when the duplicate values gets inserted in the Indexed column. (typcially used with INSERT statement)
>We need the equivalant statement for the exception mentioned above.
>I request you to elp us out in this case.
>Thanking in advance.
|||Hello Sue,
Good Morning. Thank you very much for the very useful help. Yesterday late evening i tried the same and was at the conclusion of the error no. 2601 as the equivalant in SQL.
Your ans. supports that and i an confirmed now.
Thank you once again.

Equivalant Exception for DUP_VAL_ON_INDEX for SQL Server

Hello,
We are in the process of migrating the Oracle database to SQL Server. We nee
d the help on the Exception Handling in SQL Server.
The existing Oracle stored procedure is using the Exception DUP_VAL_ON_INDEX
, which raises when the duplicate values gets inserted in the Indexed column
. (typcially used with INSERT statement)
We need the equivalant statement for the exception mentioned above.
I request you to elp us out in this case.
Thanking in advance.Duplicates for a unique index insert return the error number
is 2601. The error message depends on the object name but
it's:
Cannot insert duplicate key row in object '%.*ls' with
unique index '%.*ls'.
-Sue
On Wed, 31 Mar 2004 03:21:06 -0800, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>We are in the process of migrating the Oracle database to SQL Server. We ne
ed the help on the Exception Handling in SQL Server.
>The existing Oracle stored procedure is using the Exception DUP_VAL_ON_INDE
X, which raises when the duplicate values gets inserted in the Indexed colum
n. (typcially used with INSERT statement)
>We need the equivalant statement for the exception mentioned above.
>I request you to elp us out in this case.
>Thanking in advance.|||Hello Sue,
Good Morning. Thank you very much for the very useful help. Yesterday late e
vening i tried the same and was at the conclusion of the error no. 2601 as t
he equivalant in SQL.
Your ans. supports that and i an confirmed now.
Thank you once again.

equallent to decode in oracle?

Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?

update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020

Thanks...I'd use the SQL-92 syntax, the [url=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp]CASE]/url] operator.

-PatP|||Thanks Pat,
I have tried it But, have a question on how to handle the UPDATE. Is there anything wrong in the below statement?
UPDATE Propertytable set visible =
CASE
WHEN propertyid =1 THEN 0
WHEN propertyid=2 THEN 0
WHEN propertyid =3 THEN 1
WHEN propertyid =5 THEN 1
WHEN propertyid =6 THEN 1
WHEN propertyid =7 THEN 0
WHEN propertyid =9 THEN 1
WHEN propertyid =10 THEN 1
WHEN propertyid =11 THEN 0
WHEN propertyid =4 THEN 1
WHEN propertyid =30 THEN 1
WHEN propertyid =38 THEN 1
WHEN propertyid =60 THEN 0
WHEN propertyid =232 THEN 0
WHEN propertyid =233 THEN 0
WHEN propertyid =415 THEN 1
WHEN propertyid =605 THEN 0
END,
where parentid between 2000006001 and 2000006020|||One stinking little comma!UPDATE Propertytable
SET visible =
CASE
WHEN propertyid =1 THEN 0
WHEN propertyid=2 THEN 0
WHEN propertyid =3 THEN 1
WHEN propertyid =5 THEN 1
WHEN propertyid =6 THEN 1
WHEN propertyid =7 THEN 0
WHEN propertyid =9 THEN 1
WHEN propertyid =10 THEN 1
WHEN propertyid =11 THEN 0
WHEN propertyid =4 THEN 1
WHEN propertyid =30 THEN 1
WHEN propertyid =38 THEN 1
WHEN propertyid =60 THEN 0
WHEN propertyid =232 THEN 0
WHEN propertyid =233 THEN 0
WHEN propertyid =415 THEN 1
WHEN propertyid =605 THEN 0
END
WHERE parentid between 2000006001 and 2000006020-PatP|||Thanks a lot Pat, you are awesome...|||Pat?

What the hell is that?

UPDATE Propertytable
SET visible =
CASE
WHEN propertyid IN (1,2,11,60,232,233,605) THEN 0
WHEN propertyid IN (3,5,6,9,10,4,30,38,415) THEN 1
END
WHERE parentid between 2000006001 and 2000006020|||I am migrating the oracle related scripts to SQL Server.:-)|||What the hell is that?For somebody comming from an Oracle background, the right way to do it. Actually, listing the elements is preferred syntactically for a lot of reasons, although your solution is a lot easier to type.

-PatP|||I am migrating the oracle related scripts to SQL Server.:-)

I was refering to Pat not using IN...

You do know that if it's not one of those values, you'll get a Null value

EDIT: Did the version after 8i get CASE?

DECODE is sooooooooooo painful...

Tried to write CASE as a udf in Oracle 8i once...gave up...|||Yes, if you don't include an ELSE clause to cover missing values then you'll get a NULL for values that aren't listed. In most cases, that is exactly what I'd like, although kingno1 might or might not like that behavior.

Once you get used to Decode(), it really isn't bad. Oracle users are so accustomed to it that they consider it natural.

Yes, Oracle 9 got a lot of nifty additions that bring it much closer to the SQL-92 standard. There are still a number of behaviors for otherwise standard SQL constructs that Oracle has long supported/encouraged in PL/SQL that will pretty likely keep PL/SQL from ever reaching standards compliance, but it is doing a lot better than it did in the past.

I would have loved to have had the rights to sell bleacher space for folks to watch you trying to code CASE using Oracle 8i. I could have made a fortune!

-PatP|||How do you do >, <, <>, <=, >=?

I actually figured out how to do it...

Anyone?sql

Equalize mis-synched tables

Occationally, my publisher and push subscribers don't matchup on a few
tables. The subscriber has a few more rowguids than Publisher even after
synching. Synch glip or conflict or such.
What is the best practice to get them matching without cascading more
oddities to other subscribers?
When I try a meaningless update, to kick in the repl trigger, it doesn't
seem to move over. Delete and reinsert? Same Guid or new?
thanks for any suggestions.
Mike
If data falls in the woods and nobody is there to see it ...... ?
This can happen in several circumstances that I know of. Most likely it's
from when you bulk insert the rows and choose the defaults, then
FIRE_TRIGGERS is false and consequently the rows are not added to
MSmerge_contents. Also, if you do a fast-load using the Transform Data task
in DTS: In all cases case, you need to run sp_addtabletocontents to include
the rows then resynchronise. Alternatively you can use sp_mergedummyupdate
for a single row. For the fast load case, in future if you deselect the
check box the triggers will fire.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
The extra rows are on my pull subscriber (server) so I used the
sp_mergedummyupdate to test. It did add a row to the msmerge_contents table
but the generation didn't transfer to the publishers _contents table.
No synch error messages/conflicts. Other data is moving fine.
The extra rows were deleted 1/2/06, reasons unknown (system delete). I
reinserted them with a std insert from a restored db. ** I kept the original
rowguid.
Mistake? I wanted to avoid getting duplicate values in the table.
The pub does have the initial delete in tombstone (orig rowguid). Would
that would filter out the rowguid for future synching?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Paul Ibison" wrote:

> This can happen in several circumstances that I know of. Most likely it's
> from when you bulk insert the rows and choose the defaults, then
> FIRE_TRIGGERS is false and consequently the rows are not added to
> MSmerge_contents. Also, if you do a fast-load using the Transform Data task
> in DTS: In all cases case, you need to run sp_addtabletocontents to include
> the rows then resynchronise. Alternatively you can use sp_mergedummyupdate
> for a single row. For the fast load case, in future if you deselect the
> check box the triggers will fire.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I'm a little unsure of your current situation but if the rows don't exist at
all on the publisher then I'd delete these newly added subscriber rows, then
reinsert them and let new rowguids be created.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Equality comparison of xml fields

I have a stored procedure that has an xml input parameter, and the routine will proceed only when the inputted xml is equal to the stored xml field. Is there some sort of equals comparison for this?

* The xml field is typed DOCUMENT.XML data type instances are incomparable. XML data type preserves the content information of the XML instances and may not be an exact copy of the user-supplied XML data. Comparison depends upons the application semantics.

I can suggest the following for your application:

1) Extract scalar values (using the value() method) from the XML parameter and compare with scalar values in the stored XML field

2) Convert the XML parameter to a string representation and compare with a string representation of the stored XML field

3) Add a column that stores a hashed value of the XML instances. Get the hash value of the XML parameter and make a comparison of the hashed values. For a hash match, use #1 or #2 to ensure that the match is indeed correct. There are well known hashing methods (SHA-1 for example) that can yield good hash matches.

Hope this helps.

Thank you.|||Thanks Shankar for the reply. What I would do is break down this xml chunk into smaller content and perform the above as you suggested.

equal sign

I need to search for the equal sign in field data. Have not been able to
figure it out. Does anyone know how to do this?
fieldname like '%=%'
Thanks for any help you can give!!The following works fine for me. Does it not, for you?
create table #x (blat varchar(50))
insert #x
select 'abc=def'
union all
select 'abc'
union all
select 'def'
union all
select '='
select *
from #x
where blat like '%=%'
--
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rick" <rick@.di-wave.com> wrote in message
news:OWPoYnp2HHA.728@.TK2MSFTNGP05.phx.gbl...
>I need to search for the equal sign in field data. Have not been able to
>figure it out. Does anyone know how to do this?
> fieldname like '%=%'
> Thanks for any help you can give!!
>|||Hmmm No it does not work for me. I will make sure I have not missed typed
something.
Thanks!!
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:eijhwsp2HHA.6072@.TK2MSFTNGP03.phx.gbl...
> The following works fine for me. Does it not, for you?
>
> --
> create table #x (blat varchar(50))
> insert #x
> select 'abc=def'
> union all
> select 'abc'
> union all
> select 'def'
> union all
> select '='
> select *
> from #x
> where blat like '%=%'
> --
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Rick" <rick@.di-wave.com> wrote in message
> news:OWPoYnp2HHA.728@.TK2MSFTNGP05.phx.gbl...
>>I need to search for the equal sign in field data. Have not been able to
>>figure it out. Does anyone know how to do this?
>> fieldname like '%=%'
>> Thanks for any help you can give!!
>|||If you copy and paste the example I posted, and run it, what is the output?
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rick" <rick@.di-wave.com> wrote in message
news:e$wMiyp2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Hmmm No it does not work for me. I will make sure I have not missed typed
> something.
> Thanks!!
>
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
> news:eijhwsp2HHA.6072@.TK2MSFTNGP03.phx.gbl...
>> The following works fine for me. Does it not, for you?
>>
>> --
>> create table #x (blat varchar(50))
>> insert #x
>> select 'abc=def'
>> union all
>> select 'abc'
>> union all
>> select 'def'
>> union all
>> select '='
>> select *
>> from #x
>> where blat like '%=%'
>> --
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Rick" <rick@.di-wave.com> wrote in message
>> news:OWPoYnp2HHA.728@.TK2MSFTNGP05.phx.gbl...
>>I need to search for the equal sign in field data. Have not been able to
>>figure it out. Does anyone know how to do this?
>> fieldname like '%=%'
>> Thanks for any help you can give!!
>>
>sql

equal distribution of start and finish dates

Question. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls helpHi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls help|||Question. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:

> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rate
d
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:

>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:

> mita wrote:
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:

> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rate
d
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
>
>

equal distribution of start and finish dates

Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls helpQuestion. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:
> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rated
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:
> mita wrote:
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||GO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
"Hugo Kornelis" wrote:
> On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
> >Hi all
> >I have got 3 columns in my table- start date,finish date and cost..in the
> >following format...
> >
> >start_date finish_date cost
> >12/12/2000 20/12/2000 $2000
> >01/09/2000 12/10/2000 $400
> >
> >Now if the month and year of the start and finish date is same, the cost
> >remains same...
> >but if the month of the two dates are different, i have to distribute the
> >cost between the two months by calculating the cost for the number of days
> >for both of the months..
> >but i am not able to figure out how?
> >i am using sql 2005 ..
> >my table has got about 1 million rows...
> >pls help
> Hi mita,
> First, you need to create a table that holds all months in your
> reporting period (or more). Something like this:
> CREATE TABLE dbo.Months
> (MonthStart datetime NOT NULL PRIMARY KEY,
> MonthEnd datetime NOT NULL);
> go
> DECLARE @.TheMonth datetime;
> SET @.TheMonth = '200000101'; -- Start at january 2000
> WHILE @.TheMonth <= '20191231' -- End at december 2019
> BEGIN;
> INSERT INTO dbo.Months (MonthStart, MonthEnd)
> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
> END;
> The above is a one time operation, provided you never drop the table.
> Don't forget to add some extra months to the table in a year or ten!
> With this table in place, your query becomes something like this:
> SELECT PeriodStart, PeriodEnd,
> TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
> / DATEDIFF (day, start_date, finish_date) AS cost
> FROM (SELECT CASE WHEN a.start_date > b.MonthStart
> THEN a.start_date
> ELSE b.MonthStart
> END AS PeriodStart,
> CASE WHEN a.finish_date < b.MonthEnd
> THEN a.finish_date
> ELSE b.MonthEnd
> END AS PeriodEnd,
> a.start_date, a.finish_date,
> a.cost AS TotalCost
> FROM dbo.MyTable AS a
> INNER JOIN dbo.Months AS b
> ON b.MonthStart <= a.finish_date
> AND b.MonthStart >= a.start_date) AS d
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 18 Jun 2006 16:32:02 -0700, mita wrote:
>"Hugo Kornelis" wrote:
(snip)
>> First, you need to create a table that holds all months in your
>> reporting period (or more). Something like this:
>> CREATE TABLE dbo.Months
>> (MonthStart datetime NOT NULL PRIMARY KEY,
>> MonthEnd datetime NOT NULL);
>> go
>> DECLARE @.TheMonth datetime;
>> SET @.TheMonth = '200000101'; -- Start at january 2000
>> WHILE @.TheMonth <= '20191231' -- End at december 2019
>> BEGIN;
>> INSERT INTO dbo.Months (MonthStart, MonthEnd)
>> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
>> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
>> END;
>hi hugo
>i just ran this query for creating the months table which u suggested... i
>am getting an error....
>"Conversion failed when converting datetime from character string." what do
>i do?
Hi Mita,
Sorry for the delayed reply.
The error is caused by a stupid typo in my code (quoted above) - I have
one zero to many in the date constant for Jan 1st, 2000. If you change
'200000101' to '20000101', the error should go away.
--
Hugo Kornelis, SQL Server MVP

Equal disk space needed to delete .bak file?

Kind of an obscure question, but here goes.
SQL2000 SP4
I have a E: drive on my SQL server that is ~400gb. On it you'll find
a ~100gb live DB file and last night's 100gb .bak file (maintenance
plan is set for one day retention). The vendor is telling me that I
am going to run out of space doing the SQL backups because 100gb is
used by the live DB, 100gb is used by last nights .bak file, 100gb
will be used to write today's .bak file, and the last 100gb will be
used in the deletion process of removing the old .bak file.
I can't say I know how SQL handles the removal of the old .bak file
but is this a true statement that SQL will need equal free space to
delete the old .bak file?SQL deletes old backups AFTER creating new ones, so you will need space for
at least two backups.
Look at it this way, what happens if your database blows up during a backup.
If you have an older backup, you are fine. If you are overwriting your only
backup, it is time to polish up the resume.
Personally, I would go out and buy a couple of USB drives and back up to
there. Last week my mother purchased a 500GB model for $99. If that is not
an option, you could look into one of the commercially available backup
compression tools. The three major ones are SQLSafe (www.idera.com),
LiteSpeed for SQL (www.quest.com), and SQL Backup (www.red-gate.com).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:04b97215-0ec7-4545-a43b-f072fb3c47a2@.e6g2000prf.googlegroups.com...
> Kind of an obscure question, but here goes.
> SQL2000 SP4
> I have a E: drive on my SQL server that is ~400gb. On it you'll find
> a ~100gb live DB file and last night's 100gb .bak file (maintenance
> plan is set for one day retention). The vendor is telling me that I
> am going to run out of space doing the SQL backups because 100gb is
> used by the live DB, 100gb is used by last nights .bak file, 100gb
> will be used to write today's .bak file, and the last 100gb will be
> used in the deletion process of removing the old .bak file.
> I can't say I know how SQL handles the removal of the old .bak file
> but is this a true statement that SQL will need equal free space to
> delete the old .bak file?|||Right, I aggree. In your scenario I need 300gb, not 400gb as the
vendor claims.
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
--
300gb Total to perform the whole operation
Vendor is saying:
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
100gb - extra needed to delete last nights backup after a successful
backup
--
400gb Total to perform the whole operation
I just don't understand why that extra 100gb is needed.
On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> SQL deletes old backups AFTER creating new ones, so you will need space fo
r
> at least two backups.
> Look at it this way, what happens if your database blows up during a backu
p.
> If you have an older backup, you are fine. If you are overwriting your on
ly
> backup, it is time to polish up the resume.|||Also, a backup on the same disk as the primary is pretty useless when you
think about it.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...[vbcol=seagreen]
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:|||Only if it's not getting written to tape ;)
On Nov 28, 11:28 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Also, a backup on the same disk as the primary is pretty useless when you
> think about it.|||I've never seen Windows needing temp storage in order to delete a file. Perh
aps your vendor somehow
confuses the windows Recycle Bin functionality somehow...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...[vbcol=seagreen]
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:

Equal disk space needed to delete .bak file?

Kind of an obscure question, but here goes.
SQL2000 SP4
I have a E: drive on my SQL server that is ~400gb. On it you'll find
a ~100gb live DB file and last night's 100gb .bak file (maintenance
plan is set for one day retention). The vendor is telling me that I
am going to run out of space doing the SQL backups because 100gb is
used by the live DB, 100gb is used by last nights .bak file, 100gb
will be used to write today's .bak file, and the last 100gb will be
used in the deletion process of removing the old .bak file.
I can't say I know how SQL handles the removal of the old .bak file
but is this a true statement that SQL will need equal free space to
delete the old .bak file?SQL deletes old backups AFTER creating new ones, so you will need space for
at least two backups.
Look at it this way, what happens if your database blows up during a backup.
If you have an older backup, you are fine. If you are overwriting your only
backup, it is time to polish up the resume.
Personally, I would go out and buy a couple of USB drives and back up to
there. Last week my mother purchased a 500GB model for $99. If that is not
an option, you could look into one of the commercially available backup
compression tools. The three major ones are SQLSafe (www.idera.com),
LiteSpeed for SQL (www.quest.com), and SQL Backup (www.red-gate.com).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:04b97215-0ec7-4545-a43b-f072fb3c47a2@.e6g2000prf.googlegroups.com...
> Kind of an obscure question, but here goes.
> SQL2000 SP4
> I have a E: drive on my SQL server that is ~400gb. On it you'll find
> a ~100gb live DB file and last night's 100gb .bak file (maintenance
> plan is set for one day retention). The vendor is telling me that I
> am going to run out of space doing the SQL backups because 100gb is
> used by the live DB, 100gb is used by last nights .bak file, 100gb
> will be used to write today's .bak file, and the last 100gb will be
> used in the deletion process of removing the old .bak file.
> I can't say I know how SQL handles the removal of the old .bak file
> but is this a true statement that SQL will need equal free space to
> delete the old .bak file?|||Right, I aggree. In your scenario I need 300gb, not 400gb as the
vendor claims.
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
--
300gb Total to perform the whole operation
Vendor is saying:
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
100gb - extra needed to delete last nights backup after a successful
backup
--
400gb Total to perform the whole operation
I just don't understand why that extra 100gb is needed.
On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> SQL deletes old backups AFTER creating new ones, so you will need space for
> at least two backups.
> Look at it this way, what happens if your database blows up during a backup.
> If you have an older backup, you are fine. If you are overwriting your only
> backup, it is time to polish up the resume.|||Also, a backup on the same disk as the primary is pretty useless when you
think about it.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> SQL deletes old backups AFTER creating new ones, so you will need space
>> for
>> at least two backups.
>> Look at it this way, what happens if your database blows up during a
>> backup.
>> If you have an older backup, you are fine. If you are overwriting your
>> only
>> backup, it is time to polish up the resume.|||Only if it's not getting written to tape ;)
On Nov 28, 11:28 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Also, a backup on the same disk as the primary is pretty useless when you
> think about it.|||I've never seen Windows needing temp storage in order to delete a file. Perhaps your vendor somehow
confuses the windows Recycle Bin functionality somehow...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> SQL deletes old backups AFTER creating new ones, so you will need space for
>> at least two backups.
>> Look at it this way, what happens if your database blows up during a backup.
>> If you have an older backup, you are fine. If you are overwriting your only
>> backup, it is time to polish up the resume.sql

equal between a String and a Table atribute?

Ihave a if statement and a table with 2 atributes. They are descriptionand date. I will check if there is in the table some date like01-01-2008.

String test "01-01-2008";

if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable))
{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

Does someone know how I can make something like this in C#? Must I use a datasource?

Hi,

Because of the nature of Datetime type i think you should not use string values to compare date values. The seperator character may differ or the time (hour,minute etc. ) part may differ...

Try one of these.

First way;

datetime test=new datetime("2008","1","1");

datetime dbValue=convert.ToDateTime(convert.ToDateTime(..get the value from database..).ToShortDateString()) ;

if (test.Equals(dbValue))

{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

here we get rid of the hour minute part that is different than 0: convert.ToDateTime(convert.ToDateTime(..get the value from database..).ToShortDateString())

Second way;

?n this way we use ToShortDateString() function of datetime type to make the hour minute part as zeros 0.

datetime test=new datetime("2008","1","1");

datetime dbValue=convert.ToDateTime(..get the value from database..).ToShortDateString() ;

if (test.ToShortDateString().Equals(dbValue.ToShortDateString()))

{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

|||

khalidelmeknesi:
Does someone know how I can make something like this in C#? Must I use a datasource?

Well, you will need to make a connection to the data base, to retrieve the results from your SQL statement, and then read the results.

This can be done using any of the datasource objects or manually by

1, creating a sqlconnection, (the connection to your database) sqlcommand (the SQL statement or stored procedure), and sqldataadapter (the mechanism that will save the results to a dataset)

2. fill the sqldatadapter into a dataset (dataset will hold your return results from the database in DataTables and DataRows)

3. check the values returned by iterating through the DataRow in the DataTable of the DataSet

Epoch Date

Help!!! I have an application I support that the vendor is not being
cooperative sin giving me data purge routines. The application uses Epoch
dates (# of ms since 1/1/1970). I found this script on Oracle but need help
converting it to T-SQL where it works without the arithmetic overflow error:
SELECT to_char(
TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
,'dd-mon-yyyy hh24:mi:ss') normal_date
FROM dual;Try,
select dateadd(day, epoch_num / 86400, cast('19700101' as datetime))
AMB
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||Hi
here is the solution:
select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
from <TABLE>
alternatively just go through DATEDIFF and DATEADD functions available in BO
L
thanks and regards
Chandra
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||How do I get around the arithmetic overflow this statement gives me?
"Chandra" wrote:
> Hi
> here is the solution:
> select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
> from <TABLE>
>
> alternatively just go through DATEDIFF and DATEADD functions available in
BOL
> thanks and regards
> Chandra
>
> "Cathy Soloway" wrote:
>|||Hi
Please go through the following post. This might help you
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Are you sure it is milliseconds?
If you see the formaula (epoch_num / 86400), 86400 is the number of seconds
in a day, so the formaula seems to be calculating days. Try second instead
milli:
select DATEADD (second, epoch_num, '01-JAN-1970') normal_date
from <TABLE>
AMB
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Cathy,
Here's one way. If your epoch_num value is a decimal type,
you'll need to convert it to bigint, so you can use the modulo function.
declare @.epoch_num bigint
set @.epoch_num = 1092347839827
select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
Steve Kass
Drew University
Cathy Soloway wrote:
>How do I get around the arithmetic overflow this statement gives me?
>"Chandra" wrote:
>
>|||Thanks Steve. This does it.
"Steve Kass" wrote:

> Cathy,
> Here's one way. If your epoch_num value is a decimal type,
> you'll need to convert it to bigint, so you can use the modulo function.
> declare @.epoch_num bigint
> set @.epoch_num = 1092347839827
> select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
> Steve Kass
> Drew University
> Cathy Soloway wrote:
>
>

EOF with SqlDataSource

I am trying to get record from a table and verify it with a textbox i have a sqldatasource.

i have a text box called txtEmail and this is my Select command. how can i get this working if its possible ?

something like txtEmail.text = SqlDataSource1.Secect then ... my code. (i dont know if this a correct way to do this)

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT Email FROM [t_CustomerAcct]"

thanks,

Hi,

I'm not quite clear what you want. Do you mean querying with some conditions for a return from the data table and verify if it's equal with the value from the text box or just want to make a query according the value from the text box?

If it's the first scenario, see the following sample:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:TestConnectionString2%>" ></asp:SqlDataSource>  SqlDataSource1.SelectCommand ="select CategoryName from Categories where CategoryID='1'";DataView dw = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);string str = dw.Table.Rows[0][0].ToString();if (str ==this.TextBox1.Text) {// equal}else {// not equal}

If it's the second scenario, just make select statement according the value from textbox directly.

this.SqlDataSource1.SelectCommand ="select CategoryName from Categories where CategoryID=@.id";this.SqlDataSource1.SelectParameters.Add("id",this.TextBox1.Text); DataView dw = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);string str = dw.Table.Rows[0][0].ToString();

Thanks.

EOF transactional replication

Hi. I am going out of my mind trying to get transactional
replication working.
Currently the snapshot completes successfully, but I keep
receiving the following error when the initial snapshot
is being applied:
String data, right truncation
(Source: SERVER01 (ODBC); Error number: 22001)
---
String data, right truncation
(Source: ODBC SQL Server Driver(ODBC); Error number:
22001)
---
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number:
S1000)
I have added columns to both the source and destination
to make sure that are sequenced and match perfectly (data
types and null values - everything I can think of)
Using SEM I have been able to use the DTS Export/Import
Wizard to Import into the destination table without any
problems.
Does anybody know what could be causing this error and
how I can fix it.
Also I would really like to see the bcp out snaphot file
to view the contents. How can this been done? Do I need
to add any parameters to the bcp file?
I am at a complete loss... does anyone know how to fix
this, or what could possibly be causing the problem?
Thanks,
Marcy| Hi. I am going out of my mind trying to get transactional
| replication working.
|
| Currently the snapshot completes successfully, but I keep
| receiving the following error when the initial snapshot
| is being applied:
|
| String data, right truncation
| (Source: SERVER01 (ODBC); Error number: 22001)
| ---
| String data, right truncation
| (Source: ODBC SQL Server Driver(ODBC); Error number:
| 22001)
| ---
| Unexpected EOF encountered in BCP data-file
| (Source: ODBC SQL Server Driver (ODBC); Error number:
| S1000)
--
What level of MDAC are you on? Update to the latest MDAC version and see
how you go.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Right now both servers are on:
Windows 2000
SQL Server Standard Edition
SP3 MDAC Version 2.71.9030.9
You sure this could be the problem?
If so, why?
Thanks,
Marcy
>--Original Message--
>| Hi. I am going out of my mind trying to get transactional >| replication working.
>| >| Currently the snapshot completes successfully, but I keep >| receiving the following error when the initial snapshot >| is being applied:
>| >| String data, right truncation
>| (Source: SERVER01 (ODBC); Error number: 22001)
>| ---
>| String data, right truncation
>| (Source: ODBC SQL Server Driver(ODBC); Error number: >| 22001)
>| ---
>| Unexpected EOF encountered in BCP data-file
>| (Source: ODBC SQL Server Driver (ODBC); Error number: >| S1000)
>--
>What level of MDAC are you on? Update to the latest MDAC version and see >how you go.
>Hope this helps,
>--
>Eric C=E1rdenas
>SQL Server support
>.
>|||| Right now both servers are on:
| Windows 2000
| SQL Server Standard Edition
| SP3
| MDAC Version 2.71.9030.9
| You sure this could be the problem?
| If so, why?
| Thanks,
| Marcy
--
Hi Marcy,
As a full time support professional, my job is to make sure that we've
covered all possibilities. I have seen mismatched or outdated MDAC files
causing this error so I pass this info to you.
There is definitely something that's corrupting your data when you do a
synch. Synch is just bcp out and bcp in behind the covers. Do you
experience the same prob when you do the bcp operations manually?
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Might be a stoopid question, but...
Am I suppose to bcp out to a .txt file and then bcp in the .txt file into the table. Or is there a smarter way for me to test the bcp option manually?
Thanks!
Marcy
>--Original Message--
>| Right now both servers are on:
>| Windows 2000
>| SQL Server Standard Edition
>| SP3 >| MDAC Version 2.71.9030.9
>| You sure this could be the problem?
>| If so, why?
>| Thanks,
>| Marcy
>--
>Hi Marcy,
>As a full time support professional, my job is to make sure that we've >covered all possibilities. I have seen mismatched or outdated MDAC files >causing this error so I pass this info to you.
>There is definitely something that's corrupting your data when you do a >synch. Synch is just bcp out and bcp in behind the covers. Do you >experience the same prob when you do the bcp operations manually?
>Hope this helps,
>--
>Eric C=E1rdenas
>SQL Server support
>.
>

EOF Issue

I am trying to write data to a recordset and it doesn't write to the last record. Here is my code:

Set rs = New ADODB.Recordset
rs.Open sql, conn, 1, 2
With rs
j = 0
Do While Not rs.EOF
rs(0).Value = j
j = j + 1
.Update
.MoveNext
Loop
End With
Set rs = Nothing

I think the problem is my eof setting but I don't know how to adjust that in code since I am not using an adodc. If that is my problem then can I get the code snippet, please?

Otherwise, what is my problem?

Thanks!Have you referred to any VB forums, as I can see most of the SQL database related questions. Try it and see.sql

EOF (end of file)

Hi! Is there an equivalent EOF function in Crystal Reports 8.5.?
I want to use a Do While Loop, but dont know the number of records so
I want to use the EOF so it will terminate upon reaching the end of the
file.
Thanks.you can have RECORD COUNT so based on that number you can specify total number of loops you will require in report

Environmental Variable

Does the setup for SQL also set the corresponding environmental variable, or
is this something that has to be done separately? If separately, which
environmental variable and how?
Thanks,
VictorHi ,
It is not at all required to set / create any environment variables before
SQL Server installation. The
installation program will do all the necessary path entries automatically.
Thanks
Hari
MCDBA
"Victor" <vmgoldberg__NO-_*SPAM*_-PLEASE@.earthlink.net> wrote in message
news:ohMvc.36767$zO3.10088@.newsread2.news.atl.earthlink.net...
> Does the setup for SQL also set the corresponding environmental variable,
or
> is this something that has to be done separately? If separately, which
> environmental variable and how?
> Thanks,
> Victor
>

Environmental Variable

Does the setup for SQL also set the corresponding environmental variable, or
is this something that has to be done separately? If separately, which
environmental variable and how?
Thanks,
Victor
Hi ,
It is not at all required to set / create any environment variables before
SQL Server installation. The
installation program will do all the necessary path entries automatically.
Thanks
Hari
MCDBA
"Victor" <vmgoldberg__NO-_*SPAM*_-PLEASE@.earthlink.net> wrote in message
news:ohMvc.36767$zO3.10088@.newsread2.news.atl.eart hlink.net...
> Does the setup for SQL also set the corresponding environmental variable,
or
> is this something that has to be done separately? If separately, which
> environmental variable and how?
> Thanks,
> Victor
>

Environment Variables Within SQL

Is there some way I can access system environment variables within a SQL script? For example, if I have a script "foo.sql" that I'm calling from isql, I want to be able to substitute <hostname> with an env variable I set at the command line:

foo.sql:

update tblFoo set HostName = <hostname>

Thanks.

TerenceQ1 Is there some way I can access system environment variables within a SQL script?

A1 Yes.

Note: The following is in regard to osql (however it should hold true for isql as well)

If issuing queries from osql, (also should work using batch files), one may use environment variables i.e.( %variablename% ) directly. For example:

Define the following two environment variables:
Set TargetDB = Pubs
Set TargetTable = Authors

Then run the following example (replace SqlServer with your SqlServer instance name before running) from the command prompt:

Example:

osql SSqlServer -E -dPubs -q"exit(Select Au_LName from %TargetDB%..%TargetTable% Order By Au_LName Go Select Count(*) As 'AuthorsCount' From %TargetTable%)"|||Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.|||RE:
Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.

A good point. If the simple approach demonstrated is unworkable for the requirements at hand; another approach to consider may be to create one or more stored procedures which may be executed such that the desired results may be achieved indirectly.

For example, several utility procs may be created which shell out to the OS and execute OS commands directly or that call short VB scripts to gather, set, and / or otherwise manipulate the environment variables as required.

environment variables

Well, having only one disk partition, and only one directory, and running only one application probably ROCKS too, at least for that one application :)
But if you're unfortunate enough to be required to use multiple applications, you might be saddened by certain aspects of environment variables, such as them living in one globally competitive namespace, or them being not covered by the NT security model (AFAIK).
But I grant you, that they solve the portability problem with package configurations, so I was happy to use them nonetheless.
I'd use environment variables (or any other hack in all likelihood), if I could find a solution for the lack of reusability in SSIS -- that is driving us to move as much code as possible outside of SSIS for maintainability. :(

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

|||

Cim Ryan wrote:

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

Cim/Perry,
I'm jumping in on your thread here, sorry about that.

Here's a list of things that could, IMO, be made reusable:
-Data-Flows (the most obvious one)
-Tasks (e.g. an Execute SQL task that calls a auditing sproc)
-A sequence container that carries out a single unit of work
-A For/ForEach Loop (including all tasks within it)
-A configured component
-A group of configured components (http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx)
The important point to make is that they shouldn't just be made reusable in the same package, they should be reusable in ANY package. In that sense, each one of them could be a deployable object. Currently the only deployable object is a package - why should that be the case? Why not deploy (for example) a task, or even a component, and then use that in any package?

-Jamie

|||To add to Jamie's excellent list at the lower level,
expressions
I say this because copying and pasting transformations from one Derived Column task to another is tedious, and I tend to believe that copy&paste leads to poor maintainability and scalability.

|||Also, the Aggregate defaults most columns to Group By, it usually misses one out of a long list, and for some reason it always misses the ErrorCode and ErrorNumber, when they are added at the bottom of the output column list from a Lookup Error, so when you finish checking all the columns to group by, you track the Validation error back and find that the Aggregate left those columns unspecified -- which means it is in an invalid state -- so you have to manually set those to Group By.
Actually, sounds more like a bug than an RFE to me, but I don't care what list it goes on if it gets fixed :)
sql

Environment Variables

Hi everyone...

I'm trying to create a database with the parent directory being a environment variable,

something like this:

Code Snippet

CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = '%PARENTDIRECTORY%\mydatabase.mdf',

the problem is I don't know to get an environment variable in transact SQL...

I know that in C# we can get it with %PARENTDIRECTORY%...

Thanx in advance

The following batch may help you...

Code Snippet

Create Table #Result

(

Data varchar(8000)

);

Insert Into #Result

Exec master..xp_cmdshell 'echo %TEMP%';

Declare @.ParentDirectory as nvarchar(256);

Select Top 1 @.ParentDirectory = Data From #result;

Select @.ParentDirectory = @.ParentDirectory + '\mydatabase.mdf'

Exec ('CREATE DATABASE mydatabase

PRIMARY(NAME = myDataBase,

FILENAME = ''' + @.ParentDirectory + ''')')

Drop table #result

|||Thanx Manivannan the code above does the job, but doesn't the xp_cmdshell work only for XP users?

|||

sqlclr's udf can imprement below,

This assembly's permission need to 'EXTERNAL_ACCESS'.

Usage:

Code Snippet

select dbo.GetEnvironmentVariable('temp');

C# Source file:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString GetEnvironmentVariable(string variable)
{
string value = Environment.GetEnvironmentVariable(variable);
return new SqlString(value);
}
};

environment variable

hi,

can you show me how to get the value of an environment variable from a script task?
thanks!

Try Environment.GetEnvironmentVariable method
http://msdn2.microsoft.com/en-us/library/system.environment.getenvironmentvariable.aspx|||

Public Class ScriptMain
Public Sub Main()

Dim home As String = System.Environment.GetEnvironmentVariable("HOMEDRIVE") + System.Environment.GetEnvironmentVariable("HOMEPATH")

Dts.Variables("MyDocuments").Value = Path.Combine(home, "My Documents\")

Dts.TaskResult = Dts.Results.Success
End Sub

There is a sample package that illustrates the method in action here -

Environment Variables- Raw Files
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EnvironmentVariables-RawFiles.html)

Environment users and logins

Hi
We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
Enterprice, and with that doing a revision of our users.
We would like to be able to do a restore a backup of Production to test.
We would like user privileges on the servers to differ so a given user can
execute some stored procedures on one environment but not the other. Say, we
don’t want a service in test with the wrong connection string to be have
access to production.
We have previously done this by using local user groups on the servers, but
we loose the mapping between the Login name and the user name when we do the
restores.
Any ideas or best practices?
Best regards
MikaelMikael
> We would like to be able to do a restore a backup of Production to test.
No problem, just RESTORE command

> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
MS has introduced in SS2005 lots of new features, like SCHEMA for example.
I'd suggets you to spend a hew days to study it

> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:48261D3D-86AC-4B6F-8B0A-380101564866@.microsoft.com...
> Hi
> We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
> Enterprice, and with that doing a revision of our users.
> We would like to be able to do a restore a backup of Production to test.
> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
> Any ideas or best practices?
>
> --
> Best regards
> Mikael

Environment discrepencies concerning stored procedures & data types

Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

The text, ntext, and image data types are invalid in this subquery or aggregate

the error is supposedly the top line here:

SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='',
'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId) ,
c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId
END

GO

wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.Somehow you got single quotes stuck around your column names.

Select 'cms_Document_Contents_ID'=''
...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

SELECT *,
cms_Document_Contents_ID='',
cms_Language_ID='',
Title='',
XML='',
search_text='',
File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId),
c.active,
c.sequence,
c.cms_Document_Section_ID,
c.cms_Section_ID,
c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId

blindman|||Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

sp_attatch_db @.dbname = 'MyDB',
@.filename1 = 'c:\path\to\datafilename.mdf',
@.filename2 = 'c:\path\to\datafilename.mdf'

if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

.^sUbMSg-\/.

see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp for info on this stored procedure.

ps: f*ck that DTS export b*llocks im sticking with the raw data files.

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

Environment and Application Domain between ASP.NET & SQL Server

Hi all,

I have posted the same topic under ASP-NET-Developers but had no reply;
I then posted it under ASP.Net Community and had also no reply.
Hence, i'm posting it here to prevent the impression of spam post ...

I have created a SqlContextTrigger using ADO.NET 2.0 to create a
trigger on
SQL Server 2005.

In the class, I create a file and specify its path to be in the
Application
Domain base directory such as:

String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()

However, the path is always the SQL Server Binn Directory. I'd like to
tell
the program that I want the path to be related not to SQL Server Binn
Directory but to Visual Studio Solution's Project Bin Directory.

How do I do that?

Best regards> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.

I doubt that you will find anything built-in that will do this. The trigger
thread is running in SQL Server and has no knowledge of your client folder
structure nor a means to access it.

One method to accomplish the desired result is to store the desired target
path in a table so that you can retrieve the value from within your trigger
code. If the path is remote, you'll need to use a UNC path and additional
security considerations apply.

I'm not sure what you are trying to do but you might take a look at Service
Broker.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1136546993.487438.187050@.o13g2000cwo.googlegr oups.com...
> Hi all,
> I have posted the same topic under ASP-NET-Developers but had no reply;
> I then posted it under ASP.Net Community and had also no reply.
> Hence, i'm posting it here to prevent the impression of spam post ...
> I have created a SqlContextTrigger using ADO.NET 2.0 to create a
> trigger on
> SQL Server 2005.
> In the class, I create a file and specify its path to be in the
> Application
> Domain base directory such as:
> String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()
> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.
> How do I do that?
> Best regardssql

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

HI,

Call to EnumObjectPermissions method on a SMO ApplicationRole or DatabseRole object returns an empty collection, however, a call to EnumDatabaseRoleMember on a DatabaseRole object using DMO returns the correct list of membership. Please clarify.

Regards,

Joginder

Hmm I had the same problem with SMO, but I did not try DMO.

I found a workaround. Use the similar method at the Database level, and supply the role name as a string.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Database dbHold = smoServer.Databases["YourDB"];

String holdString;

foreach( DatabaseRole dr in dbHold.Roles)

{

ObjectPermissionInfo[] ra = dr.EnumObjectPermissions(); // mysteriously returns null!!

foreach (ObjectPermissionInfo opi in dbHold.EnumObjectPermissions(dr.Name))

{

holdString = String.Format("-OPIRole:{0} has {1}{2}{3}", dr.Name, opi.Grantor, opi.Grantee, opi.ObjectName);

}

Regards,

Chip

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

HI,

Regards,

Joginder

Hmm I had the same problem with SMO, but I did not try DMO.

I found a workaround. Use the similar method at the Database level, and supply the role name as a string.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Database dbHold = smoServer.Databases["YourDB"];

String holdString;

foreach( DatabaseRole dr in dbHold.Roles)

{

ObjectPermissionInfo[] ra = dr.EnumObjectPermissions(); // mysteriously returns null!!

foreach (ObjectPermissionInfo opi in dbHold.EnumObjectPermissions(dr.Name))

{

holdString = String.Format("-OPIRole:{0} has {1}{2}{3}", dr.Name, opi.Grantor, opi.Grantee, opi.ObjectName);

}

Regards,

Chip

EnumErrorlogs using SQLDMO

I am wanting to use the EnumErrorlogs method of the SQLServer object to
identify which of the error logs have been written to in a specified period.
I also need to be able to deploy this to any of our SQL Servers which could
all be running with different locales.
The Date column in the QueryResults output shows this but not in the date
format that I expected. My server is set to English(Australian), my default
language for my login in SQL Server is British English ... i.e. everything is
configured to return dates in the D/M/Y format. However, the date string
returned from EnumErrorlogs is in a M/D/Y format which then messes up the
remainder of my processing.
Is the result ALWAYS returned in M/D/Y ? If so then I could at least code
for that, but if it is possible for it to be in the D/M/Y format then I need
to know where this is set so that I can test for it before continuing to
process the logs.
Can anyone direct me to information about this?
I was hoping it was something to do with the current holder of the ashes (ie
you could have waited about a year for it to fix itself),
but I'm seeing the same here in the UK on totally UK Server. The description
is a bit vague in BOL , going to try a few things.
cheers,
Andy.
"MartinC" <MartinC@.discussions.microsoft.com> wrote in message
news:79DF19A6-89D7-4464-92BF-EC7F6E6B78BB@.microsoft.com...
>I am wanting to use the EnumErrorlogs method of the SQLServer object to
> identify which of the error logs have been written to in a specified
> period.
> I also need to be able to deploy this to any of our SQL Servers which
> could
> all be running with different locales.
> The Date column in the QueryResults output shows this but not in the date
> format that I expected. My server is set to English(Australian), my
> default
> language for my login in SQL Server is British English ... i.e. everything
> is
> configured to return dates in the D/M/Y format. However, the date
> string
> returned from EnumErrorlogs is in a M/D/Y format which then messes up the
> remainder of my processing.
> Is the result ALWAYS returned in M/D/Y ? If so then I could at least
> code
> for that, but if it is possible for it to be in the D/M/Y format then I
> need
> to know where this is set so that I can test for it before continuing to
> process the logs.
> Can anyone direct me to information about this?
|||tried
- Service Account as interactive user with Brit English / UK locale
- changing / reapplying default to UK DD/MM/YY and reboot
giving up, attached is a noddy func to fix
bloody septics :-)
cheers,
Andy.
"Andy Ball" <ng@.spamno77greenfell.com> wrote in message
news:ua3F10v%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was hoping it was something to do with the current holder of the ashes
>(ie
> you could have waited about a year for it to fix itself),
> but I'm seeing the same here in the UK on totally UK Server. The
> description
> is a bit vague in BOL , going to try a few things.
> cheers,
> Andy.
>
> "MartinC" <MartinC@.discussions.microsoft.com> wrote in message
> news:79DF19A6-89D7-4464-92BF-EC7F6E6B78BB@.microsoft.com...
>
begin 666 ConvertErrorlogDate.txt
M( T*)R!A<R!E<G)O<FQO9R!I;B!M+V0O>2!F;W)M870@.:6X@.16YU ;45R<F]R
M3&]G<PT*1G5N8W1I;VX@.0V]N=F5R=$5R<F]R3&]G1&%T92A%<G)O<DQO9T1A
M=&4I#0H)0V]N=F5R=$5R<F]R3&]G1&%T92 ](&-3='(H36ED*$5R<F]R3&]G
M1&%T92PT+#(I("L@.(B\B("L@.;6ED*$5R<F]R3&]G1&%T92P@.,2PR*2 K("(O
J(B K(&UI9"A%<G)O<DQO9T1A=&4L(#<L,C I*0T*16YD($9U;F-T:6]N
`
end

Thursday, March 29, 2012

Equivalant Exception for DUP_VAL_ON_INDEX for SQL Server

Equivalant Exception for DUP_VAL_ON_INDEX for SQL Server

equallent to decode in oracle?

Equalize mis-synched tables

Equality comparison of xml fields

equal sign

equal sign

equal sign

equal distribution of start and finish dates

equal distribution of start and finish dates

Equal disk space needed to delete .bak file?

Equal disk space needed to delete .bak file?

equal between a String and a Table atribute?

Epoch Date

EOF with SqlDataSource

EOF transactional replication

EOF Issue

EOF (end of file)

Environmental Variable

Environmental Variable

Environment Variables Within SQL

environment variables

Environment Variables

environment variable

Environment users and logins

Environment discrepencies concerning stored procedures & data types

Environment and Application Domain between ASP.NET & SQL Server

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

EnumErrorlogs using SQLDMO

Sql Enterprise Edition

Blog Archive

About Me