How to remove user authentication for my report server.

Hi Experts,
I had tried to remove report authentication (on a report-by-report
basis), select
a report in the Report Manager, then select the Properties tab and
select the very bottom radio button that says 'Credentials are not
required' and then 'Apply.'
I have tried above things but after doing like that report is not
rendring it is showng error that
credential is not set.
So please tell what to do... This i done for a single report only
suppoese if i want to remove authentication from my report server it
self then what to do, how i can implement that.
Or can any body tell me how to remove authentication from my report
server so that any body can run the report and it should not ask any
user name or pass word.
Regards
DineshI presume that you are using Shared Data source,then open your project and go
to shared data source. click properties and there you select "Credentials not
required", and before doing that go to the "Edit" data source and click on
Advance and click on security "UserID" give the admin user id or an Id
equivalent to admin.
Amarnath, MCTS.
"Dinesh" wrote:
> Hi Experts,
> I had tried to remove report authentication (on a report-by-report
> basis), select
> a report in the Report Manager, then select the Properties tab and
> select the very bottom radio button that says 'Credentials are not
> required' and then 'Apply.'
> I have tried above things but after doing like that report is not
> rendring it is showng error that
> credential is not set.
> So please tell what to do... This i done for a single report only
> suppoese if i want to remove authentication from my report server it
> self then what to do, how i can implement that.
> Or can any body tell me how to remove authentication from my report
> server so that any body can run the report and it should not ask any
> user name or pass word.
> Regards
> Dinesh
>

How to remove the records

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
Order by Name

The above will select all the records from InPatient and will retrieve either the status code ('A') or blank ('') from PTList, depending on whether or not the john has marked the record as "his".

What I want to do is to return the same dataset, but WITHOUT any records belonging to john.

I can't use <>'John' because 14 others might have the silly thing as active at the same time. What I'd really like to do is

Select * from
(the above)
Where Status=Null

I know it can be done, but I don't know how. Any ideas?I am guessing you want one of the following.

Tim S

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
WHERE PTList.Status IS NULL
Order by Name

Second Guess

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
WHERE NOT EXISTS
( SELECT * FROM PTList WHERE
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
)
Order by Name|||yup, it was the where not exists.

I'd already tried a variant on the other one, it returned nothing, but I probably set it up incorrectly.

Just tried the other way, also. Worked like a charm. I'm not positive, butg from somewhere I got the idea that the Exists works something like an IN statement? That would imply that the first way would be faster ... at least in machine terms.

Thank you

How to remove 'Select a Value' from the drop down list.

I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in all
the drop down list.
I don't want 'Select a value' in the drop down list
Please advise how to remove it from the drop down listYour report has parameters and unless a default is provided you will get the
'Select a value message. If you want it to be blank, provide a blank as the
default value.|||you have to provide a default value for your lists.
without a default value, the Select a value text is displayed
"amitmittal77" <u17066@.uwe> wrote in message news:598ef6aff1ffa@.uwe...
>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>all
> the drop down list.
> I don't want 'Select a value' in the drop down list
> Please advise how to remove it from the drop down list|||I am using dataset for the dropdown which have default default values
the default value setting in the report parameter Screen is
i have selected From query option in default value section
under From query folllowing values have been selected
Dataset - my dataset
Value field - Parameter value
Still i am getting 'Select a value'
Jéjé wrote:
>you have to provide a default value for your lists.
>without a default value, the Select a value text is displayed
>>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>>all
>> the drop down list.
>> I don't want 'Select a value' in the drop down list
>> Please advise how to remove it from the drop down list
--
Message posted via http://www.sqlmonster.comsql

how to remove cursor ?`

declare @.oldchgcode nchar(20),@.chgcode nchar(20),@.tmpchgcode_branchid
nvarchar(50)
declare tmp cursor for select oldchgcode ,chgcode from
DTS_MASTER.dbo.chginfo
where oldchgcode is not null and oldchgcode <> chgcode order by oldchgcode
I can declare a cursor very well and run in the first time.
However, when I run the sec.times, it said, I had declare already.
How can I delete the cursor ?Hi, Agnes
You should close the cursor, then deallocate it. You can also use local
cursors, which deallocate themselves (when the scope in which they are
defined ends).
However, using cursors is generally a bad idea. Most tasks can be
solved more efficiently using set-based code (processing all the rows
in a single statement, instead of processing one row at a time).
Razvan|||DEALLOCATE CURSOR <cusrsorname>
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uV15Po%23SGHA.5828@.TK2MSFTNGP14.phx.gbl...
> declare @.oldchgcode nchar(20),@.chgcode nchar(20),@.tmpchgcode_branchid
> nvarchar(50)
> declare tmp cursor for select oldchgcode ,chgcode from
> DTS_MASTER.dbo.chginfo
> where oldchgcode is not null and oldchgcode <> chgcode order by oldchgcode
> I can declare a cursor very well and run in the first time.
> However, when I run the sec.times, it said, I had declare already.
> How can I delete the cursor ?
>
>sql

How to refer to subselect columns in select statement

Hi everybody,

I have been looking around for an answer to this one, but haven't had any luck so far. Hope some of you clever minds out there can help me out on this :-)

I have an sql statement that does updates into another table like this:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2'

select 0 as 'zero'

from my_table where ("some clause")

Now, I want to replace the 'zero' column with something useful like the sum of subselect 1 and 2. How can I do that? The only solution I can make work is something like:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2',

(

(select number1 from some_other_table where (... linked)) +

(select number2 from some_third_table where (... again linked))

) as 'sub1_plus_sub2'

from my_table where ("some clause")

It works, but in my current project I would end up with tons of duplicate lines. This way I would end up having to maintain select code several places.

Is there a way to refer to a column in the same select statement that you're executing? In the ORDER BY clause you can use things like ORDER BY 1,2 to refer to columns without having to use the entire statement again, but I haven't been able to find anything that seems useful on this subject. I guess what I miss is a way like

field(2).value + field(3).value

Best regards, Jan

In T-SQL you can't do as you guess..

But it is possible to reuse the values. For achive your requirement you have to use the derived table..

Code Snippet

insert into result_table

Select

[Value 1]

,[My Sub1]

,[My Sub2]

,[My Sub1] + [My Sub1] as [Sub1_Pluse_Sub2]

From

(

select

value1 as [Value 1]

,(select number1 from some_other_table where (... linked)) as [My Sub1]

,(select number2 from some_third_table where (... again linked)) as [My Sub2]

from

my_table

where

("some clause")

) as Data

|||

try this instead

INSERT
INTO result_table
SELECT value1 as 'value 1'
, some_other_table.number1 AS 'my sub1'
, some_third_table.number2 AS 'my sub2',
, some_other_table.number1 + some_third_table.number2 as 'sub1_plus_sub2'
FROM my_table INNER JOIN
some_other_table ON (.. linked) INNER JOIN
some_third_table ON (... again linked)
WHERE ("some clause")

How to refer to calculated field ?

When I do the following sql statement, I got the error "Invalid column name
'buy'."
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [buy]=0 then 1 else 2 end --> error here
FROM tbl1
How can I refer to Buy in the same sql statement ?
Thanks.That is not a calculated field, it is a column alias. You cannot refer it in
other clauses in the same select statement unless it is nested in a derived
table. Your options are:
SELECT col,
< expression using col >
FROM ( SELECT CASE WHEN col = 'buy'
THEN 1 ELSE 2
END
FROM tbl ) t ( col ) ;
You can also reuse the same expression to avoid the derived table construct
as well.
Anith|||You can't. You'll have to use a derived table. Something like:
select DerivedTable.Buy
,Ddif = case when DerivedTable.Buy=0 then 1 else 2 end
from (
SELECT Buy = case when [col1]='BUY' then 1 else 2 end
FROM tbl1
) DerivedTable
Of course this is untested.
ML
http://milambda.blogspot.com/|||Your first 'BUY' statement is pretty simple - just 1 or 2 based on col1 =
BUY, so how about :
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [col1]<>'BUY' then 1 else 2 end
FROM tbl1
Or something like that. In other words, build the logic for Ddif back to
the source column unaltered by the first case statement.
However, there may be a problem in your logic in terms of what happens first
and second, etc. See what you think:
If you are saying:
For COL1 if 'BUY' then 1 else 2
then after that is done there will be 0 rows where BUY = 0, the only options
are 1 or 2.
So the dDif column will always be 2 no matter what.
"fniles" wrote:
> When I do the following sql statement, I got the error "Invalid column nam
e
> 'buy'."
> SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
> Ddif = case when [buy]=0 then 1 else 2 end --> error here
> FROM tbl1
> How can I refer to Buy in the same sql statement ?
> Thanks.
>
>|||Thank you everybody for your help.

> You can also reuse the same expression to avoid the derived table
> construct
How can I reuse the same expression ? In my case, I will need to reuse the
column alias in more than 1 places in the same sql statement.
For ex:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> That is not a calculated field, it is a column alias. You cannot refer it
> in other clauses in the same select statement unless it is nested in a
> derived table. Your options are:
> SELECT col,
> < expression using col >
> FROM ( SELECT CASE WHEN col = 'buy'
> THEN 1 ELSE 2
> END
> FROM tbl ) t ( col ) ;
> You can also reuse the same expression to avoid the derived table
> construct as well.
> --
> Anith
>|||I am wondering since I will do this in a stored procedure and I need to use
the derived column more than once in the same sql statement, would it be
possible to use a temporary table for the derived column, and if it is how
can I do that ? Thanks.
The sql statement:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
"fniles" <fniles@.pfmail.com> wrote in message
news:%23SD3Bsn%23FHA.360@.TK2MSFTNGP09.phx.gbl...
> Thank you everybody for your help.
>
> How can I reuse the same expression ? In my case, I will need to reuse the
> column alias in more than 1 places in the same sql statement.
> For ex:
> SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
> ABC = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>|||Please post a simplified version of your table schema with a few sample data
& expected results. Include a brief explanation of what you are trying to
accomplish here.
Based on the code snippet you posted, it looks mostly like some formatting
issue that, in general, can be handled differently, perhaps even outside the
database server.
Anith|||I am actually trying to convert Access db to SQL Server, and the query I
posted was a part of a longer query that I need to convert.
CREATE TABLE tblA (
Price float NULL,
CC varchar(50) NULL,
Account varchar(50) NULL,
Quantity smallint NULL,
:
)
CREATE TABLE tblB (
CC varchar(50) NULL,
:
)
CREATE TABLE tblC (
Account varchar(50) NULL,
:
)
SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
HClose = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
Example:
tblA
Price CC Account
6 1212.25 ES 12345
6 0 ES 12345
1 1585.5 NQ 12345
1 51 NQ 12345
tblB
Symbol
ES
NQ
tblC
Account
12345
Result:
Buy HClose Account
ES 1212.2500 77618 12345
ES 0.0000 12345
NQ 1585.5000 101540 12345
NQ 51.0000 3352 12345
I am also having a problem converting a floating number to a 4 decimal
floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
In my Stored Procedure, can I just derived the column Buy in a temp table,
and refer it in the next sql statement ?
Thanks.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>|||Just to add, the derived column HClose is also going to be used again in the
same query.
This is from the Access query:
IIf([buy]=0,[Hprice]-[HCLOSE],[HCLOSE]-[Hprice]) AS Hdif
"fniles" <fniles@.pfmail.com> wrote in message
news:etFhZ7o%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am actually trying to convert Access db to SQL Server, and the query I
>posted was a part of a longer query that I need to convert.
> CREATE TABLE tblA (
> Price float NULL,
> CC varchar(50) NULL,
> Account varchar(50) NULL,
> Quantity smallint NULL,
> :
> )
> CREATE TABLE tblB (
> CC varchar(50) NULL,
> :
> )
> CREATE TABLE tblC (
> Account varchar(50) NULL,
> :
> )
> SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
> HClose = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
> from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
> tblA.Account = tblC.Account
> WHERE (((tblA.Quantity)<>'0'))
> Example:
> tblA
> Price CC Account
> 6 1212.25 ES 12345
> 6 0 ES 12345
> 1 1585.5 NQ 12345
> 1 51 NQ 12345
> tblB
> Symbol
> ES
> NQ
>
> tblC
> Account
> 12345
>
> Result:
> Buy HClose Account
> ES 1212.2500 77618 12345
> ES 0.0000 12345
> NQ 1585.5000 101540 12345
> NQ 51.0000 3352 12345
>
> I am also having a problem converting a floating number to a 4 decimal
> floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
> In my Stored Procedure, can I just derived the column Buy in a temp table,
> and refer it in the next sql statement ?
> Thanks.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>|||This is a part of the original Access query that I need to convert to SQL
Server:
SELECT Format([tblA].[PRICE],"0.0000") AS BUY,
IIf(Val([BUY])>0,(Left$([BUY],InStr([BUY],".")-1)*32+Mid$([BUY],InStr([BUY],".")+1,2))*2+(IIf(Mid$([BUY],InStr(
[BUY],".")+3,1)=5,1,0)),Null)
AS HClose,
IIf([buy]=0,1-[HCLOSE],[HCLOSE]-1) AS Hdif,...
FROM (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>"0"));
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>sql

How to refer from a stored procedure to a table in another databas

Hi,
I need to select data from a table in my stored procedure. The problem is
that the table I am interested in resides in another database. Hardcoding
fully qualified name is not an option. Database name where the table is -
must be a parameter of SP. Is there any other way than using dynamically
generated query string inside stored procedure? Generated query string is
difficult to maintain.
Any ideas?
Thanks in advance.Alexander Korol wrote:
> Hi,
> I need to select data from a table in my stored procedure. The
> problem is that the table I am interested in resides in another
> database. Hardcoding fully qualified name is not an option. Database
> name where the table is - must be a parameter of SP. Is there any
> other way than using dynamically generated query string inside stored
> procedure? Generated query string is difficult to maintain.
>
Two options that I can think of:
1.A series of IF statements, which I'm pretty sure would be the solution of
choice, given that you don't have more than 10 or so databases ...
2. Another option is to create a view that unions the tables including a
column for source database name (I think this is how partitioning is
implemented )
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

How to recall column name defined in CASE function

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @.Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@.PolicyType AS VARCHAR(10),
@.ReportFrom AS DATETIME,
@.ReportTo AS DATETIME,
@.Business AS VARCHAR(1),
@.Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType

WHERE
SLA = @.Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDateuse an outer query
SELECT ColumnA
, ColumnB
, SLA
FROM (
SELECT ColumnA
, ColumnB
, SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1
THEN 'L'
else 'S'
END
FROM DNIssue D
LEFT OUTER
JOIN KILRIShare S
ON S.PolicyNo = D.PolicyNo
AND S.PolicyType = D.PolicyType
AND S.Origin = D.Origin
AND S.EndrNo = D.EndorsementNo
AND S.RINo = D.PartyNo
LEFT OUTER
JOIN KILPolicy P
ON P.PolicyNo = D.PolicyNo
AND P.PolicyType = D.PolicyType
AND P.Origin = D.Origin
AND P.EndrNo = D.EndorsementNo
LEFT OUTER
JOIN v_report_KILDNFund F
ON F.DebitNote = D.DebitNote
LEFT OUTER
JOIN PolicyProfile R
ON R.Origin = D.Origin
AND R.PolicyType = D.PolicyType
ORDER
BY D.PolicyType
, D.DebitNote
, D.Origin
, D.PolicyNo
, D.EndorsementNo
, D.EntryDate
) as t
WHERE SLA = @.Term or bite the bullet and code the datediff into the WHERE condition|||you'll have to repeat the case statement in your where clause|||It works! Thank you so much!!!

How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to read the xml returned by select query with "For XML Auto,Elements"

Hi all,

I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.

How can i do that.

I am using asp.net vb.net.

Check if this post is helpful.

http://forums.asp.net/t/1182514.aspx

How to read in multiple SELECT statements of different return types in T-SQL

HI,
I understand how to use NextResultset() in ADO.NET to return multiple
readers from a stored proc. What if a SELECT returns a scalar and
another returns rows.
Thank you!> What if a SELECT returns a scalar and
> another returns rows.
Let me rephrase above:
"What is a SELECT returns a scalar and another returns a table"
Actually all SELECT statements returns tables (with some obscure exceptions like COMPUTE). So, a
scalar is just a table that happens to have only one column and one row. I.e., you treat is as a
table. The ExecuteScalar method is just something that ADO.NET designers cooked up to make it easier
for us to get the value from a select statement that returns a table with one column and one row.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<webdevaccount@.gmail.com> wrote in message
news:1182811103.414233.319970@.m36g2000hse.googlegroups.com...
> HI,
> I understand how to use NextResultset() in ADO.NET to return multiple
> readers from a stored proc. What if a SELECT returns a scalar and
> another returns rows.
> Thank you!
>

How to read from Sql express

Hi,

i have a program written in VB6 and writes without any problem in sql

express 2005 but when i try to Select information from SQL

to VB6 i always have a error " Object Required"

The link is ODBC Source with SQL NATIVE CLIENT

and the code is something like this

dbname = txtDb

Set Db = Ws.OpenDatabase(txtDb, False, False, "ODBC")

txtDb = Db.Name

logMessage = "Ligar a " & txtDb

UpdateLog (logMessage)

lblDbStatus = "Ligar"

picDbStatus.Visible = True

lblFileStatus = "Parado"

picFileStatusOff.Visible = True

tmr.Enabled = True

SQLStateQuery = "SELECT STATE FROM DYELOTS where dyelot = '" & Header(0) & "'"

Set rsState = Db.OpenRecordset(SQLStateQuery)

any one have a idea where is the problem

ThanksOk - ignore the text boxes and all that other stuff: can you just issue a SQL statement using your VB6 code without getting an error?

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

How to read Exchange server mail attachments

Hi,
I am building an application which reads attatchments from the same
Exchange server where this application runs , and select the data from that
attachment and purge it to database. Can any \one help me with sample code
regards Ar
As far as I ran out of credits with my galss spehere, there is no way for us
to suggest what kind of connection method you are using (MAPI,etc.) or what
coding language you use ?
You should feed us with further information...
Jens Smeyer.
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:ePScgh%23PFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am building an application which reads attatchments from the same
> Exchange server where this application runs , and select the data from
> that
> attachment and purge it to database. Can any \one help me with sample code
> regards Ar
>
>
|||Hi,
Thaxs fro your reply. I haven't started coding yet.planning to do it in
C#. So can u send me some code samples
*** Sent via Developersdex http://www.codecomments.com ***

How to read Exchange server mail attachments

Hi,
I am building an application which reads attatchments from the same
Exchange server where this application runs , and select the data from that
attachment and purge it to database. Can any \one help me with sample code
regards ArAs far as I ran out of credits with my galss spehere, there is no way for us
to suggest what kind of connection method you are using (MAPI,etc.) or what
coding language you use ?
You should feed us with further information... :)
Jens Süßmeyer.
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:ePScgh%23PFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am building an application which reads attatchments from the same
> Exchange server where this application runs , and select the data from
> that
> attachment and purge it to database. Can any \one help me with sample code
> regards Ar
>
>

How to read Exchange server mail attachments

Hi,
I am building an application which reads attatchments from the same
Exchange server where this application runs , and select the data from that
attachment and purge it to database. Can any \one help me with sample code
regards ArAs far as I ran out of credits with my galss spehere, there is no way for us
to suggest what kind of connection method you are using (MAPI,etc.) or what
coding language you use ?
You should feed us with further information...
Jens Smeyer.
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:ePScgh%23PFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am building an application which reads attatchments from the same
> Exchange server where this application runs , and select the data from
> that
> attachment and purge it to database. Can any \one help me with sample code
> regards Ar
>
>|||Hi,
Thaxs fro your reply. I haven't started coding yet.planning to do it in
C#. So can u send me some code samples
*** Sent via Developersdex http://www.codecomments.com ***

How to read data from remote server inside a transaction?

Hello, everyone:

I have a local transaction,

BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT

View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

It looks like remote server is not available inside the local transaction. How to handle that?

Thanks

ZYT1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
Eg:

SET XACT_ABORT ON
begin distributed tran

insert into sometable select * from remoteserver.database.dbo..foo

commit tran
SET XACT_ABORT OFF


or check this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2|||Hello, Mailler:

Thanks. MSDTC is running. The query you posted doesn't work.

ZYT|||what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?|||Hi, Mailler:

Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

ZYT|||check the link i given in first reply

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
Michael
On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
MichaelOn Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
MichaelOn Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to question

hey all,
Name, Inv(y/n), Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, Y, Desc1
Cust2, null, Desc1
Cust2, Y, Desc2
How would i select distinct customer names and get all the fields in my
result at the same time?
i know about select distinct name from table
but that only gives my 1 column and i need all the fields to show up.
thanks,
ariAri
The DISTINCT clause applies to ALL columns in the column list of the SELECT
statment. Based on the data you supplied, what are you expecting for the
resultset?
HTH
Jerry
"ari" <ari@.discussions.microsoft.com> wrote in message
news:0FEB617D-F1D3-4B11-A099-B375DB03D4A0@.microsoft.com...
> hey all,
> Name, Inv(y/n), Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, Y, Desc1
> Cust2, null, Desc1
> Cust2, Y, Desc2
> How would i select distinct customer names and get all the fields in my
> result at the same time?
> i know about select distinct name from table
> but that only gives my 1 column and i need all the fields to show up.
> thanks,
> ari|||ok i understand now. Thank you.
"Jerry Spivey" wrote:

> Ari
> The DISTINCT clause applies to ALL columns in the column list of the SELEC
T
> statment. Based on the data you supplied, what are you expecting for the
> resultset?
> HTH
> Jerry
> "ari" <ari@.discussions.microsoft.com> wrote in message
> news:0FEB617D-F1D3-4B11-A099-B375DB03D4A0@.microsoft.com...
>
>