help- how to retrive image from sql server 2000 database

hi,
i m use asp.net 1.1.

i want to retrive image or picture from sql server 2000 database.

what should i do?

plz give "sample code" and solution.

it's urgent.

thanks in advanceone more thing,

i want to put that retrived image into asp.net's "image web control".

what should i do?
give solution.

thanks one's again.

help for T-SQL code generator for DataMart

I replicated a table from DW to DM

but i filtered the table by month.

now i have several tables with the same schema on the datamart

the table has five keys. what i want to do is to write a

sqlwizard code that will automatically write an update statement from the replicated

table. What the wizard will do is read the fields of the DM table then identify the keys and

generate the source code for update.

lets name the proc sqlwiz

exec sqlwiz (DMtable1,dwtable1)

the sp should return the desired update statement like this

update dmtable1 set DM.nonkeyfield1= dw.nonkeyfield1,

DM.nonkeyfield2= dw.nonkeyfield2,

DM.nonkeyfield3= dw.nonkeyfield3

from dwtable1 dw where

dm.keyfield1=dw.keyfield1 and

dm.keyfield2=dw.keyfield2

pls use any of the northwind table with composite pk.

my DM is sql2k5.

the sp can also be used for generating update codes for vb.net

thanks,

joey

1. You will need to use Dynamic SQL
2. Make use of INFORMATION_SCHEMA.COLUMNS

Help finding the top 3 zipcodes within the top 5 counties

I need to show data for the top 3 zipcodes for EACH of the top 5
counties. I feel totally blocked on how to make this work properly.
Here is my code - anything you can suggest will be greatly appreciated:

SET ROWCOUNT 5
DECLARE @.tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
INSERT INTO @.tblTopMarkets
select
S.StateCD,
S.CountyCD
FROM DAPSummary_By_County S
WHERE S.SaleMnYear > '01/01/2004'
GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC
-- the above works fine but next select produces only 3 rows;
-- I need 3 times 5 rows (how to effect a "loop")
SELECT TOP 3-- zips in a county
D.StateCD,
D.CountyCD,
D.Zip,
"Nbr_Mtg"= Sum(Nbr_MTG)
FROM @.tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD

WHERE D.SaleMnYear > '01/01/2004'
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESCJJA (johna@.cbmiweb.com) writes:
> I need to show data for the top 3 zipcodes for EACH of the top 5
> counties. I feel totally blocked on how to make this work properly.
> Here is my code - anything you can suggest will be greatly appreciated:
> SET ROWCOUNT 5
> DECLARE @.tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
> INSERT INTO @.tblTopMarkets
> select
> S.StateCD,
> S.CountyCD
> FROM DAPSummary_By_County S
> WHERE S.SaleMnYear > '01/01/2004'
> GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC
> -- the above works fine but next select produces only 3 rows;
> -- I need 3 times 5 rows (how to effect a "loop")
> SELECT TOP 3 -- zips in a county
> D.StateCD,
> D.CountyCD,
> D.Zip,
> "Nbr_Mtg" = Sum(Nbr_MTG)
> FROM @.tblTopMarkets T
> LEFT JOIN GovtFHADetails D
> ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD
> WHERE D.SaleMnYear > '01/01/2004'
> GROUP BY D.StateCD, D.CountyCD, D.Zip
> Order By Sum(Nbr_MTG) DESC

This is a whole nicer to do in SQL 2005, where you have ranking functions,
so you can rank the rows in the query.

But now we are on SQL 2000. Being a bit tired tonight, I didn't come up
with anything better than:

SET ROWCOUNT 0 -- don't forget to reset!

CREATE TABLE #temp (ident int IDENTITY,
stateCD ...
countyCD ...
zip ...
Nbr_mtg ...)
INSERT #temp (stateCD, coutnyCD, zip, nbr_mtg)
SELECT D.StateCD, D.CountyCD, D.Zip, Sum(Nbr_MTG)
FROM @.tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD

WHERE D.SaleMnYear > '01/01/2004'
GROUP BY D.StateCD, D.CountyCD, D.Zip
GROUP BY D.StateCD, D.CountyCD, D.Zip, Sum(Nbr_MTG) DESC

SELECT a.stateCD, a.countyCD, a.zip, a.nbr_mtg
FROM #temp a
JOIN (SELECT stateCD, countyCD, zip, ident = min(ident)
FROM #temp
GROUP BY stateCD, countyCD, zip= AS b
ON a.stateCD = b.stateCD
AND a.countyCD = b.countyCD
AND a.zip = b.zip
AND a.ident < b.ident +3
ORDER BY a.stateCD, a.countyCD, a.zip, a.nbr_mtg

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much for your suggestion. I couldn't get it to work as it
was but I tried to take the idea and apply it. Came up with a new
version (same objective) but I am still stuck. I cannot get it to peel
off the top 3 rows in each county. I get 393 rows in the final
resultset where I really want only 15 rows (5 counties times top 3
zipcodes in each county). I am beginning to think I need a cursor.
Here's my SQL:

SET ROWCOUNT 5
DECLARE @.tblTemp TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
ZipCHAR(5),
Nbr_MtgINT)
DECLARE @.tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
INSERT INTO @.tblTopMarkets
SELECT S.StateCD,
S.CountyCD
FROM DAPSummary_By_County S
WHERE S.SaleMnYear > '01/01/2004'
GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC

SET ROWCOUNT 0
INSERT INTO @.tblTemp (StateCD, CountyCD, Zip, Nbr_Mtg)
SELECT D.StateCD,
D.CountyCD,
D.Zip,
"Nbr_Mtg"= Sum(Nbr_MTG)
FROM @.tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD
WHERE D.SaleMnYear > '01/01/2004' AND D.NonPro IS NOT NULL
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESC
DECLARE @.tblByCounty TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
ZipCHAR(5),
Nbr_MtgINT,
NationalRank INT)
INSERT INTO @.tblByCounty (StateCD, CountyCD, Zip, Nbr_Mtg,
NationalRank)
SELECT A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.ident AS NationalRank
FROM @.tblTemp A -- this set ranks by biggest zipcodes WITHIN
each county
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip
SELECT A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.ident, A.NationalRank
FROM @.tblByCounty A -- this set ranks by biggest zipcodes WITHIN
each county
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip

SELECT A.ident, B.ident, A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg,
A.NationalRank
FROM @.tblByCounty A
JOIN
(SELECT Min(X.ident) AS ident, X.StateCD, X.CountyCD, X.Zip,
X.Nbr_Mtg, X.NationalRank
FROM @.tblByCounty X
GROUP BY X.StateCD, X.CountyCD, X.Zip, X.Nbr_Mtg, X.NationalRank
) AS B
ON A.StateCD = B.StateCD
AND A.CountyCD = B.CountyCD
AND A.Zip = B.Zip
AND A.ident = B.ident
WHERE A.ident < B.ident + 3
ORDER BY A.StateCD, A.CountyCD, A.Nbr_Mtg DESC|||it would be a snap in 2005, yet it's quite doable in 2000

create table #zips(id int, region char(2), zip int, sum_sales int)
insert into #zips values(1, 'IL', 60563, 12)
insert into #zips values(2, 'IL', 60564, 13)
-- a tie deliberately
insert into #zips values(3, 'IL', 60565, 14)
insert into #zips values(4, 'IL', 60566, 14)
insert into #zips values(5, 'IL', 60567, 14)
insert into #zips values(6, 'IL', 60569, 14)

insert into #zips values(7, 'WI', 53718, 12)
insert into #zips values(8, 'WI', 53711, 1)
insert into #zips values(9, 'WI', 53712, 4)
insert into #zips values(10, 'WI', 53715, 7)
insert into #zips values(11, 'WI', 53714, 5)
insert into #zips values(12, 'WI', 53712, 3)

select * from #zips z
where (select count(*) from #zips z1 where z.region=z1.region
and ((z.sum_sales<z1.sum_sales)or(z.sum_sales=z1.sum_sales and
z.id<=z1.id))) <= 3

id region zip sum_sales
---- -- ---- ----
4 IL 60566 14
5 IL 60567 14
6 IL 60569 14
7 WI 53718 12
10 WI 53715 7
11 WI 53714 5

(6 row(s) affected)

drop table #zips|||You are brilliant! Thank you so much for your help! I adapted your
approach and example to my data and it works beautifully. Here is my
final SQL:

SET ROWCOUNT 5
DECLARE @.tblTemp TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
ZipCHAR(5),
Nbr_MtgINT)
DECLARE @.tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
INSERT INTO @.tblTopMarkets
SELECT S.StateCD,
S.CountyCD
FROM DAPSummary_By_County S
WHERE S.SaleMnYear > '01/01/2004'
GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC

SET ROWCOUNT 0
INSERT INTO @.tblTemp (StateCD, CountyCD, Zip, Nbr_Mtg)
SELECT D.StateCD,
D.CountyCD,
D.Zip,
"Nbr_Mtg"= Sum(Nbr_MTG)
FROM @.tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD
WHERE D.SaleMnYear > '01/01/2004' AND D.NonPro IS NOT NULL
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESC
DECLARE @.tblByCounty TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
ZipCHAR(5),
Nbr_MtgINT,
NationalRank INT)
INSERT INTO @.tblByCounty (StateCD, CountyCD, Zip, Nbr_Mtg,
NationalRank)
SELECT A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.ident AS NationalRank
FROM @.tblTemp A
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip

SELECT A.ident, A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.NationalRank
FROM @.tblByCounty A
WHERE
(SELECT COUNT(*)
FROM @.tblByCounty X
WHERE X.StateCD = A.StateCD AND X.CountyCD = A.CountyCD
AND
(
(A.Nbr_Mtg < X.Nbr_Mtg)
OR
( A.Nbr_Mtg = X.Nbr_Mtg AND A.ident <= X.ident)
)
) <= 3
ORDER BY A.StateCD, A.CountyCD, A.Nbr_Mtg DESC

Help finding the Max Total

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2

It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
lauraThe quick and dirty version...

select top 1 a.WBS2, a.BillExt
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
order by BillExt desc|||Nothing dirty about it...|||That would be nice if there was only 1 record that needed to be returned.
select a.WBS2, BillExt=max(a.BillExt)
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
group by a.WBS2
order by BillExt desc|||Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

For instance :

Wbs2 BillExt MaxValue
0141 0 0
0143 0 0
1217 20580 1

Thanks,
Laura|||That would be nice if there was only 1 record that needed to be returned.

Which is wat she asked for...I left my mind reading hat at home...

Laura....INSERT What to Where?

Insert into that record?

I don't understand.|||I'm sorry it's hard to describe what I am asking.

If I have the following information in a table:

WBS1 Amount MAX
0141 0
1217 2
1222 200

I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

Ending result:

WBS1 Amount MAX
0141 0
1217 2
1222 200 1

Is this possible?|||Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

Why do you want to mark this record and what are you going to do with it?|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
Laura|||You can't use subqueries? What kind of lunacy is that?

You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

http://www.hov-hov.dk/you.htm|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
LauraCan you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

-PatP

Side note to Blindman, I'm going to have to bookmark that site!

Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

sql

help coverting a varchar to a float

Hi,

I'm using the following code to convert a varchar to a float in a trigger.

declare @.acre varchar (6)

set @.acre_size = 0.0

select @.acre = plotsizeacre
from inserted

declare @.num int
select @.num = isnumeric (@.acre)

if @.num = 1
set @.acre_size = @.acre

This normally works fine, but I'm getting errors if the plotsizeacre field is 1,75

Casting to a float or converting to a float also gives errors.

Any ideas how to solve this problem? (The field would normally be filled in properly, eg 1.75).

Thanks in advance,

Ian

You cannot use ISNUMERIC to do strick checking. This function will return 1 for value that can be converted to any of the integer, numeric, float and money data types. The value '1,75' can be converted to money but not float. Your best option is to chnage the schema and modify the column to float. This will require modifications from the client side also to make sure that the value that user enters is typed accordingly. If you have to keep the varchar data type then you will have to perform the cleaning of the value yourself - meaning you have to check for bad formats and convert appropriately or error out gracefully.

Help converting procedural VB code to SQL

I am at the last hurdle on converting a very large chunk of VB code that
massages a recordset to produce a report.
This question relates to my previous question from 3/2 and the ddl that I
posted for that question.
I need to replace the following vb code with SQL and I think I can do it
with a Case statement but would really appreciate some input on this. The V
B
code follows the url for the original message.
http://msdn.microsoft.com/newsgroup...r />
4F24A2-F7
1F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
'***********Code Start************
Dim TempValue As Single
If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
If iGreenRpt Then ' A green report has been requested
If iRunInData Then 'Data or spec is not green so make adjustment
TempValue = iValue * iFactor
Else ' Take data as is "Green"
TempValue = iValue
End If
statAdjustData = TempValue
Else 'Non-green or Runin/"Market Rating" report has been requested
If Not iRunInData Then 'Data or spec is green so make adjustment
TempValue = iValue / iFactor
Else 'Take data as is "Runin"
TempValue = iValue
End If
statAdjustData = TempValue
End If ' Green or Runin/Market data report requested
Else ' iFactor = 1 or 0 therefore no need to adjust
statAdjustData = iValue
End If ' iFactor = To Or <> 1 or 0
'********Code End*****************--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps:
DECLARE @.True TINYINT, @.False TINYINT
SET @.True = 1
Set @.False = 0
SELECT ... ,
CASE WHEN iFactor Not In (0,1)
THEN CASE WHEN iGreenReport = @.True
THEN CASE WHEN iRunInData = @.True
THEN iValue * iFactor
ELSE iValue
END
WHEN iGreenReport = @.False
THEN CASE WHEN iRunInData = @.False
THEN iValue / iFactor
ELSE iValue
END
END
ELSE iValue
END As statAdjustData
FROM ...
WHERE ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
Yk4XGtIPaWOgPNdJC8g+Zz1r
=ZTUB
--END PGP SIGNATURE--
StvJston wrote:
> I am at the last hurdle on converting a very large chunk of VB code that
> massages a recordset to produce a report.
> This question relates to my previous question from 3/2 and the ddl that I
> posted for that question.
> I need to replace the following vb code with SQL and I think I can do it
> with a Case statement but would really appreciate some input on this. The
VB
> code follows the url for the original message.
> http://msdn.microsoft.com/newsgroup...76C%2C774F24A2-
F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
> '***********Code Start************
> Dim TempValue As Single
> If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
> If iGreenRpt Then ' A green report has been requested
> If iRunInData Then 'Data or spec is not green so make adjustme
nt
> TempValue = iValue * iFactor
> Else ' Take data as is "Green"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> Else 'Non-green or Runin/"Market Rating" report has been requested
> If Not iRunInData Then 'Data or spec is green so make adjustme
nt
> TempValue = iValue / iFactor
> Else 'Take data as is "Runin"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> End If ' Green or Runin/Market data report requested
> Else ' iFactor = 1 or 0 therefore no need to adjust
> statAdjustData = iValue
> End If ' iFactor = To Or <> 1 or 0
> '********Code End*****************|||Thanks for the reply.
I ended up doing this as a function and it seems to work very well and is
fast.
Stvjston
CREATE FUNCTION dbo.StatAdjustData ( @.IVal as FLOAT, @.iFactor as FLOAT,
@.iGreen as BIT, @.iRunnin as bit)
RETURNS FLOAT
BEGIN
DECLARE @.RetVal as FLOAT
IF @.iFactor <> 0 and @.iFactor <> 1
BEGIN
IF @.iGreen = -1
if @.iRunnin = -1
BEGIN
SET @.RetVAL = @.iVal * @.iFactor
END
ELSE
BEGIN
SET @.RetVAl = @.iVal
END
ELSE
IF @.iRunnin <> -1
BEGIN
SET @.RetVal = @.iVal / @.iFactor
END
ELSE
BEGIN
SET @.RetVal = @.iVal
END
END
ELSE
BEGIN
SET @.RETVAL = @.IvAL
END
RETURN (@.RetVal)
END
"MGFoster" wrote:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Perhaps:
> DECLARE @.True TINYINT, @.False TINYINT
> SET @.True = 1
> Set @.False = 0
> SELECT ... ,
> CASE WHEN iFactor Not In (0,1)
> THEN CASE WHEN iGreenReport = @.True
> THEN CASE WHEN iRunInData = @.True
> THEN iValue * iFactor
> ELSE iValue
> END
> WHEN iGreenReport = @.False
> THEN CASE WHEN iRunInData = @.False
> THEN iValue / iFactor
> ELSE iValue
> END
> END
> ELSE iValue
> END As statAdjustData
> FROM ...
> WHERE ...
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
> Yk4XGtIPaWOgPNdJC8g+Zz1r
> =ZTUB
> --END PGP SIGNATURE--
>
> StvJston wrote:
2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
>

Help connecting to SQLEXPRESS using asp.net

WE have just upgraded our database from sql 2000 to sql express.
Nowthe asp.net code doesn't work. I tried many different things, no
luck. Please help! thanks very much.
Here is the connection code:
conn = New SqlConnection( "Server=xxx.xxx.net,1433; UID=User;
PWD=lxxx2007;Database=mssql3" )
strSelect = " Select * from dbo.New_Broker_Table INNER JOIN
dbo.UserList ON dbo.New_Broker_Table.Broker_ID = dbo.UserList.Broker_ID WHERE dbo.UserList.User_ID =@.userID "
cmdSelect = New SqlCommand( strSelect, conn )
cmdSelect.Parameters.Add( "@.userID", session("userid") )
conn.Open()
dtrEmployee = cmdSelect.ExecuteReader( CommandBehavior.SingleRow )
i keep getting the SQL Server does not exist or access denied error.If you are trying to connect remotely, then the problem could be that SQL
Server Express installs by default with disabled remote connections. Here is
an article that outlines the steps to allow remote connections, as well as
setting up exception in the firewall and starting the SQL Server Browser
service, which could be the problem too:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Next is to check the correct server instance. By default SQL Server Express
installs under a named instance SQLEXPRESS, so you may need to add that to
the Server parameter in your connection string. You can also remove the hard
coded port number in the connection string.
If all that fails then you can see some good connectivity troubleshooting
steps here:
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Addition to Plamen's msg.
You receive a "SQL Server does not exist or access denied" error message
when you try to connect to a SQL Server named instance in a cluster by using
TCP/IP sockets:
http://support.microsoft.com/kb/888228/en-us
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
<lytung@.gmail.com> wrote in message
news:1193969000.898641.140040@.e9g2000prf.googlegroups.com...
> WE have just upgraded our database from sql 2000 to sql express.
> Nowthe asp.net code doesn't work. I tried many different things, no
> luck. Please help! thanks very much.
> Here is the connection code:
> conn = New SqlConnection( "Server=xxx.xxx.net,1433; UID=User;
> PWD=lxxx2007;Database=mssql3" )
> strSelect = " Select * from dbo.New_Broker_Table INNER JOIN
> dbo.UserList ON dbo.New_Broker_Table.Broker_ID => dbo.UserList.Broker_ID WHERE dbo.UserList.User_ID =@.userID "
> cmdSelect = New SqlCommand( strSelect, conn )
> cmdSelect.Parameters.Add( "@.userID", session("userid") )
> conn.Open()
> dtrEmployee = cmdSelect.ExecuteReader( CommandBehavior.SingleRow )
> i keep getting the SQL Server does not exist or access denied error.
>

Help (require code)

right im programming a database program and i need some code. (the database is in sql/msde)
This is what i am attempting to do:
I have a page with textboxes on it with buttons at the bottom
i need to program the database with the add function

this is the code that i have already
Imports system.data.sqlclient

'You need two objects

Dim conn as new sqlconnection
dim comm as new sqlcommand

conn.connectionstring = "Your UDL String here"
comm.text = "insert into books (isbn, title, author, publisher, description, price,)
'books is the name of the databse
values ( '1234' , 'Title', 'Author', 'Publisher' )
conn.open
comm.connection = conn
comm.executenonquery
comm.close
conn.close

now i think i need to insert this at the top of the page but im not 100% sure (btw i have the udl string)

so what do i need to add to this code to make the page submit data that has been enterd into textboxes into a database table(sql/msde) on the click of the button?

thanks for any help providedI think you need to read some very simple getting started tutorials. What you've asked is very, very easy and to be honest shows that you've not really got any kinda' grip on this stuff. The totorials on this forum should sort you out - it'll take you 30mins to learn this, honest. Sorry if this sounds a bit cruel by you'll get no where fast by cribbing blocks of code without understand it.

HELP ! How to Print Through Network Printer

Hello All,
I have Sample printer deliver extension provided with Reporting
Services. This code works well when i use Local printer, but i want to
use network printer, for that i changed config file as -
<Printer>\\199.63.103.253\NB1OACHP5100</Printer>
but Reporting Services shows error - The printer
\\199.63.103.253\NB1OACHP5100 is not currently installed on the server.
Can anybody help me how to solve this? Also can i print the report on
an trigger like in my application i use a button to print, and on click
this report get printed.
Please help me if you solved such problem or you know how to solve
this.
Thank You
Regards
RajAll printers you use from the server Must be defined to the server... This is
simply the control panel add printer stuff.
This is doc'd in books on line...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Raj" wrote:
> Hello All,
> I have Sample printer deliver extension provided with Reporting
> Services. This code works well when i use Local printer, but i want to
> use network printer, for that i changed config file as -
> <Printer>\\199.63.103.253\NB1OACHP5100</Printer>
> but Reporting Services shows error - The printer
> \\199.63.103.253\NB1OACHP5100 is not currently installed on the server.
>
> Can anybody help me how to solve this? Also can i print the report on
> an trigger like in my application i use a button to print, and on click
> this report get printed.
> Please help me if you solved such problem or you know how to solve
> this.
> Thank You
> Regards
> Raj
>|||Thanks for reply Wayne, but i already defined the printer to the
server. It is installed on my machine where server is installed. And
also do anybody know how to print report without preview on click
event?
Thanks & Regards
Raj|||We've been trying to auto-print without the preview or dialog box that pops
up, the only thing we have found is by doing a subscription...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Raj" wrote:
> Thanks for reply Wayne, but i already defined the printer to the
> server. It is installed on my machine where server is installed. And
> also do anybody know how to print report without preview on click
> event?
> Thanks & Regards
> Raj
>|||The printer delivery sample indicates that the server name/address must be
included in the configuration for a Networked printer. This does not appear
to be true. All that is necessary here is that the name be exactly the
same (exact case also) as what is defined in the Control Panel for the
computer
on which the Report Server is running.
"Raj" wrote:
> Hello All,
> I have Sample printer deliver extension provided with Reporting
> Services. This code works well when i use Local printer, but i want to
> use network printer, for that i changed config file as -
> <Printer>\\199.63.103.253\NB1OACHP5100</Printer>
> but Reporting Services shows error - The printer
> \\199.63.103.253\NB1OACHP5100 is not currently installed on the server.
>
> Can anybody help me how to solve this? Also can i print the report on
> an trigger like in my application i use a button to print, and on click
> this report get printed.
> Please help me if you solved such problem or you know how to solve
> this.
> Thank You
> Regards
> Raj
>

Help !

Im going mad trying and failing to figure this out . running SQl server 2000
(sp3). I have had code snippets and examples thrown at me, yet im missing
something fundermental .. if i use the following code in TSQL i get a
sucess on the executon of the DTS
Declare @.Packagename varchar(255) -- Gets most recent Version
Declare @.Userpwd Varchar(255) -- Login Password
Declare @.Intsecurity bit
Declare @.pkgpwd varchar(255)
Declare @.hr int
Declare @.Object int
Set @.Intsecurity = 0
Set @.Userpwd = 'MyPassword'
set @.pkgpwd = NULL
Set @.Packagename = 'TESTDTS'
-- Create the Package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.Object OUTPUT
If @.hr <> 0
Begin
Print 'Error Creating Package'
End
Else
Begin
Print 'Package Created'
End
-- Load the package
Declare @.svr varchar(15)
Declare @.login varchar(100)
Select @.login = 'MyUserName'
Select @.svr = @.@.serverName
Declare @.flag int
Select @.flag = 0
if @.intsecurity = 0
if @.userpwd = Null
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd
else
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd, @.ServerPassword = @.userpwd
else
begin
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword
=
@.pkgPwd
end
If @.hr <> 0
Begin
Print 'Error Loading Package'
End
Else
Begin
Print 'Package loaded'
End
EXEC @.hr = sp_OAMethod @.object, 'Execute'
If @.hr <> 0
Begin
Print 'Error Executing Package'
End
Else
Begin
Print 'Package Executed'
End
-- unitialize the package
EXEC @.hr = sp_OAMethod @.object, 'UnInitialize'
If @.hr <> 0
Begin
Print 'Error UnInitializing Package'
End
Else
Begin
Print 'Package UnInitialized'
End
-- release the package object
EXEC @.hr = sp_OADestroy @.object
If @.hr <> 0
Begin
Print 'Error Releasing Package'
End
Else
Begin
Print 'Package Released'
End
That use's SQL Authentication, however i need to use Wondows Authenticaton.
i can log on query analiser using windows Authentication, yet no matter what
i seem to do to the variables to try and get it to use Windows
Authentication it always fails. The SQL server is currently set for SQl and
Windows Authentication. Can anybody shed any light on what im overlooking ?> if @.userpwd = Null
What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
ML|||ML, this code was taken from a help page.. I have amended as you suggested
but the results are still the same . I Log onto the network using my logon
user name and password. I can open a session of T-SQL and log on usoing
windows authentication without a problem.. so i dont understand why the
loadfromsqlserver wont allow me to use windows Authentication
"ML" wrote:

> What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
>
> ML|||What about the @.Intsecurity variable? Have you tried setting it to 1 ?
ML|||ML
I have set @.Intsecurity = 1 , @.Userpwd = Null, @.login = SUSER_SNAME() and
@.Flag = 256 . still get an error -2147217843 when loading the package
"ML" wrote:

> What about the @.Intsecurity variable? Have you tried setting it to 1 ?
>
> ML|||Please post the entire error message.
Have you tried contacting the author of the script?
Is there a special reason behind executing the DTS package from T-SQL?
ML|||ML,
Im going to give up on this idea, Ive spent days at this problem with no
solution. I dont seem to be able to get any sort of error message back, onl
y
-2147217843 when i interrigate the value of @.hr. I can not find that error
number in the sql server books online. I have looked in the Server Logs ..
but nothing in there. Im a a total loss as to why this wont work with Window
s
Authentication and more frustrated at the fact that i carnt find out what is
causing the problem. I had origionaly tried to hav this run from a VB6
application that gets fired as an event from a FTP server, but then i was
still having issues on accesss rights across the network when trying to open
a txt file. I will fire a question of to the author of the code... see if he
can help at all .... if not its back to the drawing board
thanks for taking the time out to try and help
"ML" wrote:

> Please post the entire error message.
> Have you tried contacting the author of the script?
> Is there a special reason behind executing the DTS package from T-SQL?
>
> ML

Help - syncronizing forms in Access 2000

regular access db - usually use sub-forms or tabs - this time I want seperate forms - have linked fields - but can't final code line in "open form" button command. Any suggestions welcome. Denny :(but can't final code line in "open form" button command

Can you explain this a little better. You might want to bullet point exactly what you want. If you want to just populate two forms on the open, you need to just scroll down on properties of main form until you get to the open propety. You'll want to set the properties of the parent form first, then the second. I have no idea if this is what you are trying to do.

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
ThanksI have used this update successfully:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive.../03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
Thanks
I have used this update successfully:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
ThanksI have used this update successfully:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server :)
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server :)
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

HELP - Need a very quick code sample for calling report

Hi - any help would be tremendously appreciated.

I have been asked to create a quick project that calls a report from a hyperlink and need just a very basic way to do so.

The report is published to a SRS Server so all I need to be able to do is have a hypelink that will show the report on screen

Thanks in advance for any help

Rendering a Report using URL Access

If the info above is not sufficient you can google for the above keywords and am sure you will find what you need.

|||

If you need more detail, the full syntax is available athttp://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_urlaccess_7kbq.asp

The basic syntax is

http://server/virtualroot?[/pathinfo]&prefix:param=value[&prefix:param=value]...n]

sohttp://reportbox/reportserver?/CorpReports/SalesSummary&rs:Command=Render&rs:format=HTML4.0

wouldrender theSalesSummary report in theCorpReports folder on thereportserver vroot on thereportbox server inHTML 4.0 format

Help - Error when modifying dataset

I keep getting this error when I go to Preview the report. I have changed
the code for the report. then I pasted my new code in and adjusted all the
fields in the layout to respond to the new fields. But I can't get it to
Preview - I keep getting this error message.
The SortExpresssion expression for the table â'table1â' refers to the field
â'projectâ'. Report item expressions can only refer to fields within the
current data set scope or, if inside an aggregate, the specified data set
scope
Can anyone tell me how to correct this information to use my new code?
Thank you
Normad"NormaD" wrote:
> I keep getting this error when I go to Preview the report. I have changed
> the code for the report. then I pasted my new code in and adjusted all the
> fields in the layout to respond to the new fields. But I can't get it to
> Preview - I keep getting this error message.
> The SortExpresssion expression for the table â'table1â' refers to the field
> â'projectâ'. Report item expressions can only refer to fields within the
> current data set scope or, if inside an aggregate, the specified data set
> scope
> Can anyone tell me how to correct this information to use my new code?
> Thank you
> Normad
Thanks to any of you who were reading this to respond. I went into the XML
and did the changes.
normad

help

Can anybody please help me with this problem
1. I have a table with the following column :
code Date Time vol exptime elapse
4 20010424 104232 945 40165
My problem is none of these column can be my primary key so my question is
How can I assign another column say 'transact_id' which will have an incremental and unique number? In other word my objective are:
a. I would like to have the data sorted according to code, date, time
b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Yes I think I can add another column which is the combination of date and time so each column will look like this yyyymmddhhmmss--> e.g. 20010101102010 which is 1 Jan 2001 10:20:10|||It would really help if you could post the DDL (probably the CREATE TABLE statement) for your table. Otherwise we need to guess at too much. Yes, it is quite possible to create a query like what you want.

-PatP|||I have attached the sample file
for you convenience I have selected random sample of less than 100 observation
thanks in advance for your help|||a. I would like to have the data sorted according to code, date, time you don't need a primary key to do that

b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on you could add an IDENTITY column to the table, but you don't need to

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?yes, i can

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

clarifying exactly what you want is important to the eventual sql

by the way, if you can, you should replace the date and time columns with one datetime column|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachment

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

I'm not really sure whether i understand your question however, here's my explanation: The code is a stock code so I arrange the data to be sorted according to code, date and time and I would like to calculate the elapse
as the difference between exptime for the current row and the previous row using macro in excel the langguange will look like this
R1C1 = "=RC[-1]-R[-1]C[-1]"

please tell me if you think you need more explanation|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachmentif you meant to attach a new description, you forgot ;)

i will use your first description:select t1.code
, t1.[Date]
, t1.[Time]
, t1.vol
, t1.exptime
, t1.exptime
-t2.exptime as diff
from currprev as t1
left outer
join currprev as t2
on t1.code = t2.code
and cast(t2.[Date] as char(8))
+cast(t2.[Time] as char(6))
= (
select max(
cast([Date] as char(8))
+cast([Time] as char(6))
)
from currprev
where (
[Date] < t1.[Date]
or [Date] = t1.[Date]
and [Time] < t1.[Time]
)
)
order by 1,2,3
the results of this are: code Date Time exptime elapse
5 20010102 100921 36561
5 20010102 104046 38446 1885
5 20010102 132221 48141 9695
5 20010102 132518 48318 177
5 20010103 102123 37283 -11035
5 20010103 120312 43392 6109
5 20010103 122434 44674 1282
5 20010103 150953 54593 9919
5 20010103 150953 54593 9919
5 20010103 151918 55158 565
5 20010103 151918 55158 565
5 20010104 101123 36683 -18475
5 20010104 121213 43933 7250
5 20010104 144338 53018 9085
5 20010104 145634 53794 776
5 20010104 153809 56289 2495
5 20010105 123717 45437 -10852
5 20010105 132814 48494 3057
5 20010125 112752 41272 -7222
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 114443 42283 470
5 20010125 114550 42350 67
5 20010125 114756 42476 126
5 20010125 114756 42476 126
5 20010125 114905 42545 69
5 20010125 114905 42545 69
5 20010125 115235 42755 210
5 20010125 121430 44070 1315
5 20010125 123000 45000 930
329 20010424 104232 38552
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 110925 40165 1185
329 20010424 110925 40165 1185
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120627 43587 4
329 20010424 121024 43824 237
329 20010521 104239 38559 -5265
et cetera
having an IDENTITY primary key would have helped immensely

your data has dupes in it, and consequently the output reflects this, because the dupes are cross-joined within groups of the same code/[Date]/[Time]

i strongly urge you to clean up the data before proceeding|||Thanks very much, (sorry about the attachment)
However I can not delete the duplicates since it is a vaild observations
Do you have any suggestion about creating an IDENTITY primary key ?