remove columns created by replication process

One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :

select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'

Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :

...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?

Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

-PatP

Remote queries using sp_executesql run inconsistently

This one has stumped me!
I recently implemented a process to monitor database usage and growth
on our production servers. I use on server as the "master" that
collects data from all the other servers into one database. The
problem I'm having is that only every other day the process completes
after having successfully collected data from all servers. On the
alternate days, only data from the "master" server is collected. I've
used some debugging code to determine that my process is successfully
communicating with each server each day, but I can reliably plan on the
every-other-day behavior.
I apologize if this explanantion is too vague. Here is some code...
This is the SP on the "master" server that contacts all the other
servers and collects the data (this is within a cursor that loops
through the list of server names as stored in a local table):
-- Creates the generic linked server
select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
exec sp_serveroption 'MSSQL', 'rpc', 'true'
exec sp_serveroption 'MSSQL', 'rpc out', 'true'
set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
@.svr_nm + ''' where ServerName = ''new'''
execute sp_executesql @.sqlstr
set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
execute MSSQL.master.dbo.sp_executesql @.sqlstr
-- clean-up
exec sp_droplinkedsrvlogin 'MSSQL',null
exec sp_dropserver 'MSSQL'Still reviewing your code, but just wondering why are you adding and
dropping links to the remote servers each time you run the job? Why not
just permanently link the servers?
Will get back to you on the rest when I can review your code in more detail.
Thx
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
> This one has stumped me!
>
> I recently implemented a process to monitor database usage and growth
> on our production servers. I use on server as the "master" that
> collects data from all the other servers into one database. The
> problem I'm having is that only every other day the process completes
> after having successfully collected data from all servers. On the
> alternate days, only data from the "master" server is collected. I've
> used some debugging code to determine that my process is successfully
> communicating with each server each day, but I can reliably plan on the
> every-other-day behavior.
>
> I apologize if this explanantion is too vague. Here is some code...
>
> This is the SP on the "master" server that contacts all the other
> servers and collects the data (this is within a cursor that loops
> through the list of server names as stored in a local table):
>
> -- Creates the generic linked server
> select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
>
> exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
> exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
> exec sp_serveroption 'MSSQL', 'rpc', 'true'
> exec sp_serveroption 'MSSQL', 'rpc out', 'true'
>
> set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
> MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
> @.svr_nm + ''' where ServerName = ''new'''
>
> execute sp_executesql @.sqlstr
>
> set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
> execute MSSQL.master.dbo.sp_executesql @.sqlstr
>
> -- clean-up
> exec sp_droplinkedsrvlogin 'MSSQL',null
> exec sp_dropserver 'MSSQL'
>|||We would prefer not to leave permanent linked servers out our servers if not
for a specific database or purpose; we've found that developers can sometime
s
abuse them. Also, the dynamic nature of the script allows us to add and
remove servers from the process more easily.
Thanks for reviewing my code. I look forward to your feedback!
"Michael C#" wrote:

> Still reviewing your code, but just wondering why are you adding and
> dropping links to the remote servers each time you run the job? Why not
> just permanently link the servers?
> Will get back to you on the rest when I can review your code in more detai
l.
> Thx
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
>
>|||Nothing's jumping out at me, other than you're not fully-qualifying all of
the tables (i.e., temp_drives). Are you seeing anything in your Event Logs
on either the local computer or remote linked servers? My best guess would
be a security/login failure on the remote machine, but you'd have to check
the logs for that. Could be that the commands are timing out, for instance
if you're running intensive operations every other day like backups and
index rebuilds, etc. Look for any other activities that are occurring on
your server on days of failure. It might end up just being a case of
scheduling the job to run earlier or later in the day.
There might be additional info in the SQL Server Logs (under "Management" in
EM).
Let me know if you see anything in your logs.
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
> We would prefer not to leave permanent linked servers out our servers if
> not
> for a specific database or purpose; we've found that developers can
> sometimes
> abuse them. Also, the dynamic nature of the script allows us to add and
> remove servers from the process more easily.
> Thanks for reviewing my code. I look forward to your feedback!
>
> "Michael C#" wrote:
>|||No, nothing in the event logs. The security is set up correctly. It could
be a timeout issue, but I would assume that I'd receive an error message in
that case.
Also, there are no other long-running jobs during this time, and no
processes that run only every other day.
I'm going to be adding a bit more code to the process this w, so
hopefully my additional testing will help to reveal the answer.
Thanks for your help
"Michael C#" wrote:

> Nothing's jumping out at me, other than you're not fully-qualifying all of
> the tables (i.e., temp_drives). Are you seeing anything in your Event Log
s
> on either the local computer or remote linked servers? My best guess woul
d
> be a security/login failure on the remote machine, but you'd have to check
> the logs for that. Could be that the commands are timing out, for instanc
e
> if you're running intensive operations every other day like backups and
> index rebuilds, etc. Look for any other activities that are occurring on
> your server on days of failure. It might end up just being a case of
> scheduling the job to run earlier or later in the day.
> There might be additional info in the SQL Server Logs (under "Management"
in
> EM).
> Let me know if you see anything in your logs.
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
>
>|||Did you find a resolution on this?
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
> No, nothing in the event logs. The security is set up correctly. It
> could
> be a timeout issue, but I would assume that I'd receive an error message
> in
> that case.
> Also, there are no other long-running jobs during this time, and no
> processes that run only every other day.
> I'm going to be adding a bit more code to the process this w, so
> hopefully my additional testing will help to reveal the answer.
> Thanks for your help
> "Michael C#" wrote:
>|||No, not yet. This is only something I can work on when I have all my other
"regular" work done. :-(
"Michael C#" wrote:

> Did you find a resolution on this?
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
>
>

Remote Process of Report

Hi,

We have a reports running in a system in our internal network. Now our requirement is we have to publish the same report into web for the public. How can I do that..?

Note :

I can not install reporting service in my Web server system.

There should be a way to put the report from the reporting server to the web server and display in the webpage to the public.

This is really an urgent requirement.

Are you using RS2005? If so, you can embed the report viewer control into your ASP.Net application.|||

I'm using 2003, Is there any way to do this. My problem is I have to show a report to a website which is running in diffrent system in the same network.

the user will be provided by a hyperlink in the asp.net webpage by clicking the link the user should see the report.

Thanks

Mohan

|||

It is tricky in 2003 without the viewer control.

Your user's won't be authenticated against your RS backend, so you will have to get the contents of the report from within your ASP.Net application. You will want to use the HTMLFragment deviceinfo setting. The trickier part is resolving the secondary streams (like images and charts). You will have to use the StreamRoot deviceinfo to redirect image requests back to your ASP.Net application, so you can make them on behalf of your users.

Remote cube processing

Hi,

I'm using Informatica 8 for ETL procedures and I would like my SASS 2005 to process a cube as the ETL ends.
Does anyone know if there's a component for Informatica that supports XMLA (or any other way to process the cube remotly) ?

Thanks in advance,

Ariel.

Hi,

you can use a command line tool that is avaliable from microsoft,

it is called as ASCMD.

Check this link for download and other information on ASCMD. : http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Hope this helps

Regards

|||

Hi Vijay,

I'll try it on my system and get back here (it's not connected to the internet).

Thanks a lot.

|||Hi all,

Well, I checked my sytem and my ETL server is linux so I can't use ascmd because it works only on windows servers.
Any workaround or suggestions?

Thanks in advance,
Ariel.|||

Hi,

In which database is the data stored after the ETL?

Regards

|||Hi,

The DB is Oracle.
Infronatica is installed on Linux server.|||

Hi,

This is what you can do:

Set up Http access to your Analysis Server 2005.

Link to setup http access: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx

From your Linux system, which contains Informatica:

After your ETL completes,

pass a pre-constructed xmla command (Http call) ,

to the Http access point (msmdpump.dll) of the Analysis Server 2005.

This xmla command would contain a "Process" batch command,

which instructs the Analysis Server to process a specific object (Cube/MeasureGroup/Partition/dimension).

Infact you can even create new partitions and then process them dynamically using XMLA.

xmla overview :

http://msdn2.microsoft.com/en-us/library/ms187178.aspx

xmla command for processing analysis server 2005 objects:

http://msdn2.microsoft.com/en-us/library/ms187199.aspx

In Analysis Server 2005 you would have the data source configured to get data from Oracle or whichever data stores you have.

Hope this helps.

Regards

Remote Connection Problems

I am not able to register a remote server and connect to
it currently without going through this process:
1. Open "My Network" and click on "Entire Network"
2. Search for the SQL Server by host and domain name (i.e.
host.domain.com)
3. Sign in with a valid username and password
4. Connect to the SQL Server through MMC
Does anyone know why I need to establish the Windows
Authentication first? Shouldn't I be able to connect with
just SQL Authentication?Hi Shannon,
For Windows Authentication, SQL Server takes your current Windows user
details. That's useful if you are in the same domain. Sounds like you aren't
in the same domain and Windows is establishing a new set of credentials when
you navigate to the system. Those credentials are then being used by SQL
Server.
SQL Server authentication requires separate users to be set up inside SQL
Server (ie not Windows users).
HTH,
--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shannon Burns" <sburns875@.hotmail.com> wrote in message
news:068101c364c2$b38e3420$a301280a@.phx.gbl...
> I am not able to register a remote server and connect to
> it currently without going through this process:
> 1. Open "My Network" and click on "Entire Network"
> 2. Search for the SQL Server by host and domain name (i.e.
> host.domain.com)
> 3. Sign in with a valid username and password
> 4. Connect to the SQL Server through MMC
> Does anyone know why I need to establish the Windows
> Authentication first? Shouldn't I be able to connect with
> just SQL Authentication?