How to get two different SQL servers to talk to one another

Hi all I need to create a view for another sql server on the network.

I can create a view for two to databases on the same sql server to talk but how do you do it for a differend sql server??

CREATE VIEW dbo.Revocations_View
AS
SELECT TM#, LastName, FirstName, MI, SSN, [I/R #], Date, ReasonofRevocation, Notes, Termination, Conditional, WasEmployeeFined, LicenseSuspension,
Status
FROM LicensingActions.dbo.Revocations_TblUse sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) to put the servers on "speaking terms" with each other. In a secured network, you may have to deal with Security Account Delegation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp). After you've resolved that, you need to use four part names (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_5vvp.asp) and you're in business.

-PatP|||thanks pat thats exaclty what I need
appreciate it :)|||a fine meal and big bottle of wine outta do it. could'nt resist.

How to get thru security of Sql server http endpoint?

I have write the coding to create http endpoint in the sql server 2005, but if i type my pc ownself ip address (ex, 192.168.0.110) to connect to the sql server http endpoint, it prompt me for Username and Password. I didn't set any username and password before, what should i type ? Or what should i do to pass thru this authentication?

Thanks in advance.

Cheers,

Winson

Hi Winson,

For security reasons, the SQL Server 2005 http endpoints for Native XML Web Services require the client application to send user credentials. By default, the user account who created the HTTP endpoint will have access to connect to the endpoint. Users of the sysadmin role will also have access to the endpoint. All other SQL login accounts, must be granted explicit connect permissions. Please refer to Books Online's (BOL) GRANT Endpoint permission topic available at http://msdn2.microsoft.com/en-us/library/ms187811.aspx for additional information. For information regarding how to specify user credentials in the client application in .Net Frameworks, please refer to the following BOL topic http://msdn2.microsoft.com/en-us/library/ms175929.aspx.

Jimmy

|||

Hello Jimmy,

Thanks for the help, actually the situation is like this. I using nusoap (php) to connect to the Sql server http endpoint. but i fail to pass thru the security of sql sever http endpoint over the Internet. What do you think? Do you have any idea regarding this this of connection? nusoap (php) <> sql server http endpoint?

Thanks in advanced.

Cheers,

Winson

How to get this output

hi their
i create a temp table called tempEmpTran and it content a fileds like
Refrlno Description opt-refno

1474 New 5
1474 followup 3
1474 followup 3
1470 new 7

No my problem is i need to eleminate the refrlno which Description <> 'followup',but again the problem is if i see any referrals which repeating more than once then i need to check if any of that Rrefrlno got any Description which equal to the followup, if yes i need to eliminate the whole Referealno from the list,

Refrlno Description opt-refno
1474 New 5 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1470 new 7 -should include,cos its got description 'new'

so once all these condition satisfied then the output should looks like this

Refrlno Description opt-refno
1470 new 7

Any Idea ?
regards
Nirangatry this:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Formatting seems to be screwed on that post, heres the query again:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Hi sam,
Many thanks to u r fast respone,but in that query there is not line which eleminate the Description='Followup' ?
any idea ?
regards
Niranga|||Have you ran it?

It only returns records who have a single refrlno... so there shouldn't be any need to filter based on your sample data.

Have a play and try to understand what it does. Then you can expand on it |||

Hey, you could do this 2 ways:

Select *

from tempEmpTran

Where Refrlno not in (Select Refrlno from tempEmpTran where Description = 'Followup')

Select *

from tempEmpTran a

Where not exists (Select Refrlno from tempEmpTran b where Description = 'Followup' and a.Refrlno = b.Refrlno)

BobP

|||Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga|||

Niranga wrote:

Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga

Sorry about that, didn't bother creating the tables and testing. You just need to add an alias to the column name
count(*) becomes
count(*) as [Cnt]|||hi,

here's another alternative.
SELECT *
INTO #tempEmpTran
FROM (
SELECT 1474 AS Refrlno
, 'New' AS Description
, 5 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1470 AS Refrlno
, 'New' AS Description
, 7 AS [opt-refno]
) a

SELECT a.*
FROM #tempEmpTran a INNER JOIN
(
SELECT Refrlno
, IsNew = SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END))
FROM #tempEmpTran
GROUP BY
Refrlno
HAVING SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END)) = 0
) b ON a.RefrlNo = b.RefrlNo

DROP TABLE #tempEmpTran|||

here you go..

Code Snippet

Create Table #data (

[Refrlno] int ,

[Description] Varchar(100) ,

[opt-refno] int

);

Insert Into #data Values('1474','New','5');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1470','new','7');

Select

*

from

#Data Main

Where

Not Exists

(

Select

Sub.[Refrlno]

From

#Data Sub

Where

Main.[Refrlno]=Sub.[Refrlno]

And Sub.[Description] = 'followup'

)

how to get the real boundary of "time" dimension of a subcube dynamically?

how to get the real boundary of "time" dimension of a subcube dynamically?

e.g.

CREATE SUBCUBE [SMS2005] AS
(
SELECT
(
[time].[date].[date].&[2007-01-01T00:00:00] : [time].[date].[date].&[2007-01-10T00:00:00]
)
ON COLUMNS
FROM [SMS2005]
)
go

I want to create a global measure that show the stocks on the boundaries of the subcube'range
(in this case , they are 2007-1-1 and 2007-1-10).

Whenever a different new subcube (focused on time dimension) is built,
this measure can behave differently based on the subcube.

To this problem ,
I think the key is that
how to get the real boundary of "time" dimension of the subcube dynamically
in the definition of the globally wanted measure.

Does anyone has a inspiration?

3x.

So eagerly for the answer.|||

I wrote the following measure,but it does not work.

create calculated member
sms2005.measures.w as
([time].[date].[date].members.item(0) , [Measures].[stocks])
go

|||UP ! Is there anyone who knows how to get it?

How to get the Out put from the Stored Procedure

Hi Every body,

i am trying to execute a stored procedure and want the out put to be passed to another database table.

I tried to create a OLEDB Source and gave the Exec Procedure Statement. I tried to see the preview and able to see the out put results. But when i am clicking on the columns to map to the destination i am not able to see the metadata.

Can you guys pls let me know how to do this.

thanx in advance..

Regards,

Dev

If your stored procedure is complex, you may need to insert a SELECT that returns the expected columns as the first statement in your stored proc. You can add a WHERE clause like WHERE 0 = 1 to ensure that no rows are returned.

When you are using a multiple operation stored procedures, a lot of tools (SSIS included) use the first resultset to determine the metadata for the stored proc. You can add a "dummy" resultset to ensure that it gets the right metadata.

|||

Hi,

I really appreciate your early reply. i will give a try and let you know the out come.

Thanx & Regards,

Dev

|||

hi,

can you pls let me know how to write a dummy SQL Statement for the out put, as i am not able to get the table dosent existsin the Database

Dev

|||

Hi,

I tried a sample like this

1. Created a SP

ALTERprocedure [dbo].[usp_test]

as

begin

declare @.error_number int,

@.row_count int

CREATETABLE #temp (

test1 varchar(50),

test2 varchar(50)

)

SELECT*from #temp where 0=1;

end

2. Added OLEDB SOURCE -- > added as a SQL Command EXEC usp_test.

3. now when i click on the metadata it's not showing up the coloumns

Please suggest what i am doing wrong

Regds,

Dev

|||

SELECT '' AS mystringcolumn, 1 as myintcolumn WHERE 1 = 0

The columns here need to match your expected resultset, both in name and type.

|||You need to put the select before the create table. See my post above.|||

Add this t-sql at the end of the storedproc

declare @.sql varchar (50 )

selelct @.sql='select * from #temp where 0=1'

Exec (@.sql)

give it a try

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.

How to get rid of duplicates in the table

I have a table which has quite a few duplicates and I cannot create unique
index on it.
First I want to list those duplicate so I can do something about it and
secondly, I want to delete them so I can create index.
Thanks
Mac,
Please post the DDL including sample data.
Thanks
Jerry
"Mac" <msamani@.comcast.net> wrote in message
news:e9Auq1l1FHA.3524@.tk2msftngp13.phx.gbl...
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
|||Mac,
Build an index and do a select out all rows and add GROUP BY <COL>
HAVING COUNT(*) > 1. You will then get the rows which are duplicate
based on the <COL>. You can certainly add <COL1>, <COL2>. Then
remove/modify the rows and you should be able to build a unique index.
Be aware you need to do this when the system is quiet otherwise you can
have extra duplicates occur. Also your applicatio may need to be
updated to deal with a UNIQUE index collission.
Shahryar
Mac wrote:

>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
>First I want to list those duplicate so I can do something about it and
>secondly, I want to delete them so I can create index.
>Thanks
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Put an identity on the table
group by your fields and Max identity value, insert that into another table
delete everything from orig table where identity is not in the other table
drop identity
create index
http://sqlservercode.blogspot.com/
"Mac" wrote:

> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>
|||Hi Mac.
This is a 4 step solution. First you have to move all dups into a new table,
lets call TEMP_TAB. Second delete all dups from your master table, lets call
TAB_WITH_DUPS. Third move all records from TEMP_TAB to TAB_WITH_DUPS. Forth
dropTEMP_TAB.
For your understanding check following out.
-- STEP 0 - Preparing for the test
CREATE TABLE TAB_WITH_DUPS (COL_1 VARCHAR(10), COL_2 VARCHAR(10), COL_3
VARCHAR(10), )
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('BB', 'BB', 'AA')
INSERT INTO TAB_WITH_DUPS VALUES ('CC', 'BB', 'CC')
SELECT * FROM TAB_WITH_DUPS
-- STEP 1 - Move dups to a new table
SELECT
DISTINCT
COL_1,
COL_2,
COL_3
INTO
TEMP_TAB
FROM
TAB_WITH_DUPS
GROUP BY
COL_1,
COL_2,
COL_3
HAVING
COUNT(*) > 1
SELECT * FROM TEMP_TAB
-- STEP 2 - Delete dups from the master table
DELETE a
FROM TAB_WITH_DUPS a
INNER JOIN TEMP_TAB b
ON a.COL_1 = b.COL_1
AND a.COL_2 = b.COL_2
AND a.COL_3 = b.COL_3
SELECT * FROM TAB_WITH_DUPS
-- STEP 3 - Bring data back to the master table.
INSERT INTO TAB_WITH_DUPS SELECT * FROM TEMP_TAB
SELECT * FROM TAB_WITH_DUPS
-- STEP 4 - Get rid of the newly created table
DROP TABLE TEMP_TAB
"Mac" wrote:

> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>
|||I believe this is a simpler method:
1. SELECT * INTO #Temp1 FROM [TableName]
2. TRUNCATE TABLE [TableName]
3. CREATE UNIQUE INDEX [IndexName] ON [TableName] (ColumnNames] WITH
IGNORE_DUP_KEY
4. INSERT INTO [TableName] (ColumnNames)
SELECT (ColumnNames)
FROM #Temp1
Mike

How to get rid of duplicates in the table

I have a table which has quite a few duplicates and I cannot create unique
index on it.
First I want to list those duplicate so I can do something about it and
secondly, I want to delete them so I can create index.
ThanksMac,
Please post the DDL including sample data.
Thanks
Jerry
"Mac" <msamani@.comcast.net> wrote in message
news:e9Auq1l1FHA.3524@.tk2msftngp13.phx.gbl...
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>|||Mac,
Build an index and do a select out all rows and add GROUP BY <COL>
HAVING COUNT(*) > 1. You will then get the rows which are duplicate
based on the <COL>. You can certainly add <COL1>, <COL2>. Then
remove/modify the rows and you should be able to build a unique index.
Be aware you need to do this when the system is quiet otherwise you can
have extra duplicates occur. Also your applicatio may need to be
updated to deal with a UNIQUE index collission.
Shahryar
Mac wrote:

>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
>First I want to list those duplicate so I can do something about it and
>secondly, I want to delete them so I can create index.
>Thanks
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Put an identity on the table
group by your fields and Max identity value, insert that into another table
delete everything from orig table where identity is not in the other table
drop identity
create index
http://sqlservercode.blogspot.com/
"Mac" wrote:

> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||Hi Mac.
This is a 4 step solution. First you have to move all dups into a new table,
lets call TEMP_TAB. Second delete all dups from your master table, lets call
TAB_WITH_DUPS. Third move all records from TEMP_TAB to TAB_WITH_DUPS. Forth
dropTEMP_TAB.
For your understanding check following out.
---
-- STEP 0 - Preparing for the test
---
CREATE TABLE TAB_WITH_DUPS (COL_1 VARCHAR(10), COL_2 VARCHAR(10), COL_3
VARCHAR(10), )
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('BB', 'BB', 'AA')
INSERT INTO TAB_WITH_DUPS VALUES ('CC', 'BB', 'CC')
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 1 - Move dups to a new table
---
SELECT
DISTINCT
COL_1,
COL_2,
COL_3
INTO
TEMP_TAB
FROM
TAB_WITH_DUPS
GROUP BY
COL_1,
COL_2,
COL_3
HAVING
COUNT(*) > 1
SELECT * FROM TEMP_TAB
---
-- STEP 2 - Delete dups from the master table
---
DELETE a
FROM TAB_WITH_DUPS a
INNER JOIN TEMP_TAB b
ON a.COL_1 = b.COL_1
AND a.COL_2 = b.COL_2
AND a.COL_3 = b.COL_3
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 3 - Bring data back to the master table.
---
INSERT INTO TAB_WITH_DUPS SELECT * FROM TEMP_TAB
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 4 - Get rid of the newly created table
---
DROP TABLE TEMP_TAB
"Mac" wrote:

> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||I believe this is a simpler method:
1. SELECT * INTO #Temp1 FROM [TableName]
2. TRUNCATE TABLE [TableName]
3. CREATE UNIQUE INDEX [IndexName] ON [TableName] (ColumnNames] WITH
IGNORE_DUP_KEY
4. INSERT INTO [TableName] (ColumnNames)
SELECT (ColumnNames)
FROM #Temp1
Mike

How to get rid of duplicates in the table

I have a table which has quite a few duplicates and I cannot create unique
index on it.
First I want to list those duplicate so I can do something about it and
secondly, I want to delete them so I can create index.
ThanksMac,
Please post the DDL including sample data.
Thanks
Jerry
"Mac" <msamani@.comcast.net> wrote in message
news:e9Auq1l1FHA.3524@.tk2msftngp13.phx.gbl...
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>|||Mac,
Build an index and do a select out all rows and add GROUP BY <COL>
HAVING COUNT(*) > 1. You will then get the rows which are duplicate
based on the <COL>. You can certainly add <COL1>, <COL2>. Then
remove/modify the rows and you should be able to build a unique index.
Be aware you need to do this when the system is quiet otherwise you can
have extra duplicates occur. Also your applicatio may need to be
updated to deal with a UNIQUE index collission.
Shahryar
Mac wrote:
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
>First I want to list those duplicate so I can do something about it and
>secondly, I want to delete them so I can create index.
>Thanks
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Put an identity on the table
group by your fields and Max identity value, insert that into another table
delete everything from orig table where identity is not in the other table
drop identity
create index
http://sqlservercode.blogspot.com/
"Mac" wrote:
> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||Hi Mac.
This is a 4 step solution. First you have to move all dups into a new table,
lets call TEMP_TAB. Second delete all dups from your master table, lets call
TAB_WITH_DUPS. Third move all records from TEMP_TAB to TAB_WITH_DUPS. Forth
dropTEMP_TAB.
For your understanding check following out.
---
-- STEP 0 - Preparing for the test
---
CREATE TABLE TAB_WITH_DUPS (COL_1 VARCHAR(10), COL_2 VARCHAR(10), COL_3
VARCHAR(10), )
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('BB', 'BB', 'AA')
INSERT INTO TAB_WITH_DUPS VALUES ('CC', 'BB', 'CC')
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 1 - Move dups to a new table
---
SELECT
DISTINCT
COL_1,
COL_2,
COL_3
INTO
TEMP_TAB
FROM
TAB_WITH_DUPS
GROUP BY
COL_1,
COL_2,
COL_3
HAVING
COUNT(*) > 1
SELECT * FROM TEMP_TAB
---
-- STEP 2 - Delete dups from the master table
---
DELETE a
FROM TAB_WITH_DUPS a
INNER JOIN TEMP_TAB b
ON a.COL_1 = b.COL_1
AND a.COL_2 = b.COL_2
AND a.COL_3 = b.COL_3
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 3 - Bring data back to the master table.
---
INSERT INTO TAB_WITH_DUPS SELECT * FROM TEMP_TAB
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 4 - Get rid of the newly created table
---
DROP TABLE TEMP_TAB
"Mac" wrote:
> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||I believe this is a simpler method:
1. SELECT * INTO #Temp1 FROM [TableName]
2. TRUNCATE TABLE [TableName]
3. CREATE UNIQUE INDEX [IndexName] ON [TableName] (ColumnNames] WITH
IGNORE_DUP_KEY
4. INSERT INTO [TableName] (ColumnNames)
SELECT (ColumnNames)
FROM #Temp1
Mike

How to get reports on internet?

We were using reports on our intranet environment for a while and tried to
create couple of reports on internet. We did this -
Installed a Windows 2003 Server/SQL Server 2000/Report Services, then
uploaded reports. Can we get reports on our web pages without giving an
external IP for Reporting services server?
With our current installation, we are getting a 'Page Not Found' error.
Any idea?I am pretty sure you will need an external IP in order for the "world" to see
the Reporting Server.
That being said, I am sure you have run into the login window. All external
users will need to have windows accounts because by default the
authentication method used is windows. You will need to add a custom
security extension to change this to forms authentication.
"Binoy" wrote:
> We were using reports on our intranet environment for a while and tried to
> create couple of reports on internet. We did this -
> Installed a Windows 2003 Server/SQL Server 2000/Report Services, then
> uploaded reports. Can we get reports on our web pages without giving an
> external IP for Reporting services server?
> With our current installation, we are getting a 'Page Not Found' error.
> Any idea?

How to get relation between two feilds of same table

Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.

My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.

there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.

In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.

I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)

How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.

Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.

Priyadarshini

You might want to ask this in a C# forum.

How to get rank?

I would like to write a query that gives me the values of a set of
observations, and their rank.

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

I would like to select this:

1 10 -- rank 1, value 10
2 5
3 1

I can put them into a temp table with an identity column, ordered by
the column I'm interested in, and then retrieve in order by the
identity column. I'm wondering if there's a way to do that with a
subquery.

Thanks,
JimRunning count is one way

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

select (select count(*) from #Values v where val <= v2.Val) as Rank,*
from #Values v2
order by 2

Denis the SQL Menace
http://sqlservercode.blogspot.com/

jim_geiss...@.countrywide.com wrote:
> I would like to write a query that gives me the values of a set of
> observations, and their rank.
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
> I would like to select this:
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
> Thanks,
> Jim|||Jim,

if you are using SS 2005 , use row_number() or rank() OLAP function|||Where do you want to show data?
If you use front end application, do Ranking there

Madhivanan


jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

> I would like to write a query that gives me the values of a set of
> observations, and their rank.
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
> I would like to select this:
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
> Thanks,
> Jim

How to get mobile subscription to sync with dynamic snapshot

Hello Everyone,

I have a publication on a Sql 2000 (SP4) server. This publication has dynamic filtering enabled. What I want to do is create an interface which will generate a new dynamic snapshot based on filtering input from users. So far I can create the dynamic snapshot easy enough and I can see the filtered results on a Sql 2000 subscriber. However, there seems to be no way to configure a mobile database to point to the dynamic snapshot. This is easy to configure in a Sql 2000 subscription - the options are right there are on the properties page. Yet, it seems like the mobile database only points to the required unfiltered snapshot through the IIS proxy.

Is there any way to force the mobile database to use the dynamic snapshot instead?

Thanks,

Mike

You can set the HOST_NAME() function in code to anything you want on the mobile app (using the SqlCeReplication object's HostName property), and use this is your publication filter.

Hope this assists.

|||

Thanks, Eric.

I thought of that as an option. However, I would rather use a Forms or a Web app to set HOSTNAME. If I set it in the Mobile App itself, then the users are going to be forced to try to enter the filtering criteria while the device is cradled, which would be clumsy at best.

Right now, I can set the HOSTNAME using the ActiveX replciation objects in a VB app, but it only seems to work on a dynamic snapshot.

|||

Hi Mike,

I am not sure I understand - the users should be able to enter the criteria any time on the device (and save it in registry or a file). I assume that it is the users that select the hostname, not you. In the Device app you can add code to validate the entered hostname....

|||

Thanks, Erik.

That's actually a good idea. The app I am writing replaces a commercial app in which the users enter their filtering criteria in the Windows component of the system. The resulting data is then downloaded to the mobile device. I was stuck in that mindset. Yes, I could have them enter the information anytime with the understanding that the filter will not apply until the next time they synchronize with the host.

Thanks for the help!

Mike

|||If that answers your post, please remember to mark as answered, thanks.

How to get list of item in Report Server

hi,
I'm new to reporting services, and i'm using custom authorization.
I need to create a windows application which will connect to the report
server and gets list of all files and folders from the root directory and set
policies.
I want to know how to retrieve all the folders and files from root directory.
I included the reporting service component and created an object for that,
after that how i should retrieve the list of folders from the report server.
I would like to know how to resolve this, send me the way how to retrieve
either in VB or in C#.There are 2 techniques available:
1) There is a List method (List...). Call it from the root path "/" and
then recurse each to build the tree. Each item has an item type (Folder,
DataSource, Report, etc)
a. I'm going from memory, but I think it's either ListChildren or
ListItems
2) FindItems from the root path "/" and assemble from there.
a. Again, I'm going from memory.
Look to MSDN or SQL Server BOL for the methods.
BTW, you wouldn't be the same Uday that worked for a company called
PowerCerv years ago would you? I know the name is common, but I'm curious.
-Tim
"uday" <uday@.discussions.microsoft.com> wrote in message
news:D5BE2C9D-7617-42B2-B915-467F8B706948@.microsoft.com...
> hi,
> I'm new to reporting services, and i'm using custom authorization.
> I need to create a windows application which will connect to the report
> server and gets list of all files and folders from the root directory and
> set
> policies.
> I want to know how to retrieve all the folders and files from root
> directory.
> I included the reporting service component and created an object for that,
> after that how i should retrieve the list of folders from the report
> server.
> I would like to know how to resolve this, send me the way how to retrieve
> either in VB or in C#.
>
>

How to get last records in grouped query.

Hi,
I want to create query where I could group records by quarters, and get
the last record in each group.

e.g
Create Table MyTable
(
Value [float],
date[datetime]
)
Insert into MyTable (value, [date]) values (100, '1-1-2000')
Insert into MyTable (value, [date]) values (110, '1-2-2000')
Insert into MyTable (value, [date]) values (120, '1-3-2000')
Insert into MyTable (value, [date]) values (130, '1-4-2000')
Insert into MyTable (value, [date]) values (140, '1-5-2000')
Insert into MyTable (value, [date]) values (150, '1-6-2000')
Insert into MyTable (value, [date]) values (160, '1-7-2000')

Now I would like to get this data grouped by quarter, and get the last
value from each quarter. So here I would like to get result like this
(120, q1 -2000)
(150, q2 -2000)
(160, q3 -2000)
I know how to create aggregate functions but I have problem with getting
that last record from each group.

*** Sent via Developersdex http://www.developersdex.com ***I have solution unless somebody has better one:
Create temp table with dates only, grouped by quarter and do right join
query on MyTable.

Create Table #temp1
(
IdTemp1 int not null Primary key identity ,
[date] datetime
)
Insert into #temp1
(
[date]
)

Select
Max(date)
from MyTable
group by Year([date]), DatePart (quarter,[date])
order by Year([date]), DatePart (quarter,[date])

select * from MyTable right join #temp1 on mytable.date = #temp1.date

*** Sent via Developersdex http://www.developersdex.com ***|||SELECT A.Value, B.Yr, B.Qtr
FROM MyTable as A
JOIN (SELECT Yr = datepart(year, date),
Qtr = datepart(quarter, date),
max(date) as LastDate
FROM MyTable
GROUP BY datepart(year, date),
datepart(quarter, date)) as B
ON A.date = B.LastDate

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopawrote:

Quote:

Originally Posted by

>Hi,
>I want to create query where I could group records by quarters, and get
>the last record in each group.
>
>e.g
>Create Table MyTable
>(
>Value [float],
>date[datetime]
>)
>Insert into MyTable (value, [date]) values (100, '1-1-2000')
>Insert into MyTable (value, [date]) values (110, '1-2-2000')
>Insert into MyTable (value, [date]) values (120, '1-3-2000')
>Insert into MyTable (value, [date]) values (130, '1-4-2000')
>Insert into MyTable (value, [date]) values (140, '1-5-2000')
>Insert into MyTable (value, [date]) values (150, '1-6-2000')
>Insert into MyTable (value, [date]) values (160, '1-7-2000')
>
>Now I would like to get this data grouped by quarter, and get the last
>value from each quarter. So here I would like to get result like this
>(120, q1 -2000)
>(150, q2 -2000)
>(160, q3 -2000)
>I know how to create aggregate functions but I have problem with getting
>that last record from each group.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***