It's possible to call a view from a different SQL Server?

dear friends,

It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...

Thanks

Create a linked server then use a fully qualified object name: SELECT * FROM Server2.database.dbo.view|||

I did it and the point 1. works, but in point 2. with Inner join, doesnt work:

1. (OK)

SELECT * FROM GCXNCLISM1S301.SMS_B02.dbo._CPY_OfferManager_1005

2. (NOT OK)

SELECT dbo.v_R_System.Netbios_Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,

dbo.v_GS_PC_BIOS.SerialNumber0, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, dbo.v_R_System.User_Name0

FROM GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

Error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.Netbios_Name0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Model0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.SerialNumber0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.User_Name0" could not be bound.

THE SINTAX IS DIFFERENT?

THANKS!!

|||

hi,

did you try this :

SELECT T2.Netbios_Name0, T1.Manufacturer0, T1.Model0,

T4.SerialNumber0, T3.TotalPhysicalMemory0, T2.User_Name0

FROM [GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_COMPUTER_SYSTEM] T1 INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_R_System] T2 ON T1.ResourceID = T2.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_X86_PC_MEMORY] T3 ON T2.ResourceID = T3.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_PC_BIOS] T4 ON T2.ResourceID = T4.ResourceID

...

|||

Dear Stephane,

Returns the folow error:

Msg 208, Level 16, State 1, Procedure TESTE2, Line 3

Invalid object name 'GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM'.

(1 row(s) affected)

Thanks

|||

OK,

I called the view directly to the remote server and it works. And I have other problem when I do a Inner Join with one table in my seever:

ALTER PROCEDURE [dbo].[TESTE4]

AS

SELECT Netbios_Name0 as CN,TotalPhysicalMemory0 as RAM, Model0=

CASE

WHEN TotalPhysicalMemory0 <600000 THEN Model0 + ' 512MB'

WHEN TotalPhysicalMemory0 >600000 THEN Model0 + ' 1GB'

END, SerialNumber0 as NrSerie,

TotalPhysicalMemory0 as RAM, User_Name0 as UserID, MA_MODELO_ID, MA_Nome

FROM GCXNCLISM1S301.SMS_B02.dbo.v_gestao_desktop_balcoes, ModeloPC_ALIAS

WHERE Model0 NOT LIKE '%ProLiant%'

AND Model0 =MA_Nome

Returns the error:

Msg 468, Level 16, State 9, Procedure TESTE4, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

IF I substitute the last line of code for this:

AND Model0 COLLATE Latin1_General_CI_AS =MA_Nome COLLATE Latin1_General_CI_AS

It works, but the query dont return rows... And I know that there is values that join the both sources... :-(

Can anyone help me with COLLATE?

THANKS!!

it is save to reindex table system ?

Hi friends,
Is it save to reindex (dbcc reindex) table system in db
master, msdb, model ?
Can we truncate datafile & log in tempdb ?
How to determine size of db in tempdb ?
Please advice.
Thanks.> Is it save to reindex (dbcc reindex) table system in db
> master, msdb, model ?
I wouldn't recommend this unless you have a very good backup in place. Why
do you think you need to reindex these tables?
> Can we truncate datafile & log in tempdb ?
Sure, see http://www.aspfaq.com/2446
> How to determine size of db in tempdb ?
Using Query Analyzer:
USE tempdb
GO
EXEC sp_spaceused|||Not only that but DBCC DBREINDEX does not allow system tables to be
reindexed (in SQL Server 2000)
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > Is it save to reindex (dbcc reindex) table system in db
> > master, msdb, model ?
> I wouldn't recommend this unless you have a very good backup in place.
Why
> do you think you need to reindex these tables?
> > Can we truncate datafile & log in tempdb ?
> Sure, see http://www.aspfaq.com/2446
> > How to determine size of db in tempdb ?
> Using Query Analyzer:
> USE tempdb
> GO
> EXEC sp_spaceused
>|||But I found , they are fragmented, this is one of them in
master db:
DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00%
[1:5]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 4760.0
- Avg. Page Density (full)................: 41.19%
If we cannot use reindex (dbcc reindex), can we defrag it
with dbcc defrag ? or just leave it as it is.
Thanks.
>--Original Message--
>Not only that but DBCC DBREINDEX does not allow system
tables to be
>reindexed (in SQL Server 2000)
>--
>Paul Randal
>DBCC Technical Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in
message
>news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
>> > Is it save to reindex (dbcc reindex) table system in
db
>> > master, msdb, model ?
>> I wouldn't recommend this unless you have a very good
backup in place.
>Why
>> do you think you need to reindex these tables?
>> > Can we truncate datafile & log in tempdb ?
>> Sure, see http://www.aspfaq.com/2446
>> > How to determine size of db in tempdb ?
>> Using Query Analyzer:
>> USE tempdb
>> GO
>> EXEC sp_spaceused
>>
>
>.
>|||There are only 8 pages in this table. You shouldn't worry about
fragmentation until you get several dozen pages at least.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kresna rudy kurniawan" <anonymous@.discussions.microsoft.com> wrote in
message news:096c01c3a28a$d412bc10$a401280a@.phx.gbl...
> But I found , they are fragmented, this is one of them in
> master db:
> DBCC SHOWCONTIG scanning 'sysaltfiles' table...
> Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
> TABLE level scan performed.
> - Pages Scanned........................: 8
> - Extents Scanned.......................: 5
> - Extent Switches.......................: 4
> - Avg. Pages per Extent..................: 1.6
> - Scan Density [Best Count:Actual Count]......: 20.00%
> [1:5]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 80.00%
> - Avg. Bytes Free per Page................: 4760.0
> - Avg. Page Density (full)................: 41.19%
> If we cannot use reindex (dbcc reindex), can we defrag it
> with dbcc defrag ? or just leave it as it is.
> Thanks.
>
> >--Original Message--
> >Not only that but DBCC DBREINDEX does not allow system
> tables to be
> >reindexed (in SQL Server 2000)
> >
> >--
> >Paul Randal
> >DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in
> message
> >news:e1bDwGooDHA.1096@.TK2MSFTNGP11.phx.gbl...
> >> > Is it save to reindex (dbcc reindex) table system in
> db
> >> > master, msdb, model ?
> >>
> >> I wouldn't recommend this unless you have a very good
> backup in place.
> >Why
> >> do you think you need to reindex these tables?
> >>
> >> > Can we truncate datafile & log in tempdb ?
> >>
> >> Sure, see http://www.aspfaq.com/2446
> >>
> >> > How to determine size of db in tempdb ?
> >>
> >> Using Query Analyzer:
> >>
> >> USE tempdb
> >> GO
> >> EXEC sp_spaceused
> >>
> >>
> >
> >
> >.
> >sql

Issues passing Table Name as a parameter to a Stored Procedure

Hi Friends,

I use the stored procedure pr_Set_Min_Max_Ind to set some indicators in certain type of tables in my databases.

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind] ( @.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName )
................
................
...
...
...
................
End

The procedure pr_Set_Min_Max_Ind takes five parameters ( @.t, @.k1, @.k2, @.r1, @.r2 ) of which
@.t is the table name in which I want to set some indicators and
the rest of the parameters (@.k1, @.k2, @.r1, @.r2) are column names in the table @.t

Say, I have this procedure stored in database A. Then to execute the procedure on table TestTable_A in database A , I write the statement as:

exec pr_Set_Min_Max_Ind TestTable_A, Column1, Column2, Column3, Column4 (statement-1)

Now, if I want to execute this procedure on a table TestTable_B in a different database B, I am NOT able to issue a statement like:

exec pr_Set_Min_Max_Ind B.dbo.TestTable_B, Column1, Column2, Column3, Column4 (statement-2)

The only way I now know to accomplish this is to save a copy of this procedure in database B and write a statement similar to statement-1. But, for some reasons I DO NOT want to do that. I just want to have the procedure stored in only one database and be able to pass the table names from other databases in the format database_name.dbo.table_name ;So, guys please let me know how I can pass the table name in the format shown as in statement-2 above.

Thanks in advance for taking time to give me a solution.

With Best Regards,

-Ram.

Depends on how you create your SQL statements based on the table names and column names that you pass to your stored procedure but you should be able to do a SELECT statement from another database in the same SQL Server. Try posting your SP so that we will be able to see where the problem could be.

SQL Server Helper
http://www.sql-server-helper.com

|||

Hi,

The following is the SP that I was referring to.

Thank you.

--

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind](@.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName)

As

Declare @.SQL Varchar(8000),

@.x SysName

Set @.x = '##_' + @.t + '_' + Replace(Convert(Char,GetDate(),114),':','')

Begin

-- Tables which have a Min_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Min_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 + ' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ',' + @.r2 + ') = 1 Then ''Y'' Else ''N'' End New_Min_Ind, IsNull(Min_Ind,''-'') Old_Min_Ind From ' + @.t +') t Where Old_Min_Ind <> New_Min_Ind And New_Min_Ind = ''Y'' '

Print @.Sql

Exec(@.SQL)

-- Update any record based on key fields and set the Min_Ind to N if it is not N

Set @.SQL = 'Update a Set Min_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Min_Ind,''-'') <> ''N'' '

Print @.Sql

Exec(@.SQL)

-- Update table and set the Min_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Min_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 +' And b.k2 = a.' + @.k2 + ' And b.r1 = a.' + @.r1 + ' And b.r2 = a.' + @.r2

Print @.Sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

-- Tables which have a Max_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Max_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 +' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ' Desc,' + @.r2 + ' Desc) = 1 Then ''Y'' Else ''N'' End New_Max_Ind, IsNull(Max_Ind,''-'') Old_Max_Ind From ' + @.t +') t Where Old_Max_Ind <> New_Max_Ind And New_Max_Ind = ''Y'' '

Print @.sql

Exec(@.SQL)

-- Update any record based on key fields and set the Max_Ind to N if it is not N

Set @.SQL = 'Update a Set Max_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Max_Ind,''-'') <> ''N'''

Print @.sql

Exec(@.SQL)

-- Update table and set the Max_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Max_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = ' + @.k1 +' And b.k2 = ' + @.k2 + ' And b.r1 = ' + @.r1 + ' And b.r2 = ' + @.r2

Print @.sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

Return @.@.Error

End

|||

You will need to prefix your If ... from Information_Schema.Columns with the name of the database passed from your @.t variable...

Something like this

declare @.DBName varchar(50)
declare @.TblName varhcar(50)

Set @.DBName = parsename(@.t, 3)
Set @.TblName = parsename(@.t, 1)

-- 1=object name, 2=schema name, 3=database name, 4= server name

Then you'll need to change your If count(*)... from Information_Schema.Columns statement into dynamic SQL (so that the database name gets interpretted)...

I would use sp_executesql to return your count (from the If statement against information_schema). See BOL on how to return a value using sp_executesql.

Note, just a partial example...

SET @.SQLString = N'Select @.retCountOut = Count(*) From ' + @.dbname + '.Information_Schema.Columns Where Table_Name = ''' + @.tblname + ''' Column_Name = 'Min_Ind''

SET @.ParamDef = N'@.retCountOut int Output, @.dbname varchar(50), @.tblname varchar(50)';

EXECUTE sp_executesql @.SQLString, @.ParamDef, @.dbname = @.dbname, @.tblname = @.tblname, @.retCountout=@.retCount OUTPUT;

If @.retCount<> 0
Begin.....

|||I would strongly suggest against this sort of thing. This code is really hard to follow and will be problematic. I would consider using this code to generate non-dynamic SQL that you run directly. Then the code generator might be dynamic, but each module won't be, and will perform better, and be easier to manage.

IsSorted Property problem

Dear friends,

I have a ETL to import some data. When I try to open de merge join transformation, the BIDS give me an error. The error is the IsSorted must me set to true in both datasources, but I dont want to insert the sort transformation, because I know that both datasources are sorted... How can I update the IsSorted property for the both datasources? I didn't find the place to do it... it's not possible?

Thanks!

in the SOurce Components; you can right click, advanced properties; Input and output properties and set the IsSorted property of the 'OLE DB Source Output' equal to true. Additionally; you have to expand the 'output columns' node and set SortKeyPosition property for the proper columns. 0 means it is not sorted; 1 for the first column in the sort; 2 for the second one; 3 for the 3rd one....

Notice that by doing this you are just changing the metadata; the data will no be sorted. If data is not properly sorted; merge join will yield unexpected results. Also, make sure there are not tranformations in the dataflow, prior to the merge join; that alter the original sort of the data.

|||

Dear Rafael Salas

Thanks for your answer. I have some problem about "IsSorted Property". And I solve it by your guide. It is useful.

Again, Thanks for your support!

Tam Nguyen!