How to Setup Linked Server within Same SQL Server

Been wandering around the searches and cannot find a specific answer so hopefully someone can help me. Basic situation is I'm setting up an automatic transfering of data to a historical database that resides on the same SQL Server.

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname 'linked1', <Databasename>
exec sp_addlinkedsrvlogin 'linked1', 'false', null, <user>, <password>

SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'select * from dbo.table')

I run this and it says it cannot find the instance...You cannot specify local server as a linked server.|||If you check the documentation for sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp), it looks like example 2 is almost exactly what you need. Just provide the machine name instead of machine\instance and you'll be in business!

-PatP|||Originally posted by rdjabarov
You cannot specify local server as a linked server. That's strange, it let me do it on my test machine.

-PatP|||What's the SP level on your test? I even decided to try it on one of my Prod servers (SP3a) and it failed just like on my Personal Edition (SP3a).|||My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||Thank you for all of the replies. I got everything worked out without having to use the linkserver but the help will definitely help me at a later time. Thanks again

Originally posted by Pat Phelan
My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||I was actually about to suggest, that if it's the same server then you don't need OPENQUERY, or a linked server for that matter. Just a cross-database query would do.

How to setup front-end IIS and back-end SQL server?

Hi,

I need some help in setting up a front end IIS box and back end SQL 2005 box. I will be using merge replication.

Front end server will be a 32 bit.

How exactly do you do this? Anyone have any links to articles that walks through the setup?

Thanks,

p

Hi Pintu, see this link:

http://www.databasejournal.com/features/mssql/article.php/1438231

how to setting report viewer to view exactly as VS preview

hi all..I'm new here and got a problem, and i hope you guys can help me.
My problems:
I want to create one card , size : height =2.25, width =4.5 and it should be
on 2 page because I want to make it front and back.
i 'm using rectangle and textbox, and i set the page break after first
rectangle . When i preview the card, it shows 2 pages, one front and one back,
as it should be.
when i preview using print layout at VS, it became 5 pages. I already setting
using
[(Report.PageWidth - Report.LeftMargin - Report.RightMargin)>=Body.Width] but
it won't do.
And one more thing, when preview it on browser (report viewer), it shows one
full page, not the size of the card. So how can I make the preview setting
only preview the card exactly like the layout of the card, not full screen?
any help will be appreciated. thank you.the HTML version is optimized for a web rendering and the size of the pages
will be different.
but if the user try to print or export in pdf, then the layout will follows
your needs.
maybe you can try to change the interactive size of the report (not the page
size, the interactive size) to see if you can force the HTML page size.
"gmi" <u37770@.uwe> wrote in message news:78d60e78bb21e@.uwe...
> hi all..I'm new here and got a problem, and i hope you guys can help me.
> My problems:
> I want to create one card , size : height =2.25, width =4.5 and it should
> be
> on 2 page because I want to make it front and back.
> i 'm using rectangle and textbox, and i set the page break after first
> rectangle . When i preview the card, it shows 2 pages, one front and one
> back,
> as it should be.
> when i preview using print layout at VS, it became 5 pages. I already
> setting
> using
> [(Report.PageWidth - Report.LeftMargin - Report.RightMargin)>=Body.Width]
> but
> it won't do.
> And one more thing, when preview it on browser (report viewer), it shows
> one
> full page, not the size of the card. So how can I make the preview setting
> only preview the card exactly like the layout of the card, not full
> screen?
> any help will be appreciated. thank you.
>

How to set up permissions for a user, who need to work with SQL Server Agent

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Owner can execute job as well.|||

Thanks for the hint, but I do not want to have this user as an owner. The owner should be another account. This user (developer) should only have the capability to execute jobs we (DBA) provided to him. He should not see ALL jobs nor should he execute ALL jobs. I would like to set permissions in a way that he can start/stop/enable/disable ONE specific job.

Is there an easy way to realize it? Or maybe a more complicated way?

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set the width on the multi-select drop down (not the simple select)?

Hi,

How can I set the width on the multi-select parameter box? I checked the post about setting the SELECT, but that affects the single parameter box, not the multi-select.

I know there is an HtmlViewer.css file and have changed the rsReportServer.config file to use it, but I don't know what the tag is to set the width on the drop down parameter list box in the htmlViewer.css file. Or is it in another file?

Any ideas?

Thanks.

pl

The width of the MVP parameter box is not configurable sorry to say.

|||

Thank you for your response.

Is there any chance that the ability to set the width for the multi-select parameter box might be included in RS 2005 SP1?

Also, I read somewhere that the solution Report Manager is a project that is available as a download for Visual Studio 2005. If I were to download the project, could I set this feature up myself?

Thanks.

|||Has this bug been addressed in SP1. Users are gobsmacked when they hear that the size of the multi box cannot be set to the width of the widest entry.|||

This behavior will not changed in SP1. From our customers we have seen this as a good recommendation for the next release and it is on the list (with many other suggestions) as a possible feature improvement.

How to set the Timeout and Timeoutspecified property?

Hi all
We are getting a 'Operation Timedout' error when trying to render a report
as pdf. Will setting the Timeout and Timeoutspecified property will help
resolve this issue. Based on some criteria this report can take long time.
Also I cant find how to set the timeout and timeoutspecified property?
Please help
Thanks
RahulI think for a report you can set the timeout in the report
manager.
>--Original Message--
>Hi all
>We are getting a 'Operation Timedout' error when trying
to render a report
>as pdf. Will setting the Timeout and Timeoutspecified
property will help
>resolve this issue. Based on some criteria this report
can take long time.
>Also I cant find how to set the timeout and
timeoutspecified property?
>Please help
>Thanks
>Rahul
>
>.
>

How to set sql server setting let to set null value when a empty string (zero length)

How to set sql server setting let to set null value when a empty string
(zero length) set to column?
I want it automatic to set null value when maintain data is a zero-length
string press to column of table. for example,
when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null three values to table1.There is no such setting in SQL Server. You could create a trigger which per
forms this, but I would
look for some alternate options instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ABC" <abc@.abc.com> wrote in message news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...darkred">
> How to set sql server setting let to set null value when a empty string (z
ero length) set to
> column?
> I want it automatic to set null value when maintain data is a zero-length
string press to column
> of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null
> three values to table1.
>|||Would probaby have to create a trigger of the form (completely untested!)
CREATE TRIGGER NullifySomeEmptyStrings
ON table1
FOR AFTER INSERT, UPDATE
AS
--NULL out column2 if it now contains an empty string ''
IF UPDATE(column2)
BEGIN
UPDATE table1
SET column2 = NULL
WHERE table1.ThePrimaryKeyColumn IN
(SELECT ThePrimaryKeyColumn
FROM INSERTED
WHERE INSERTED.column2 = '')
END
"ABC" <abc@.abc.com> wrote in message
news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...
> How to set sql server setting let to set null value when a empty string
> (zero length) set to column?
> I want it automatic to set null value when maintain data is a zero-length
> string press to column of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
> save abc, null and null three values to table1.
>|||Why do you want it to be Null?
If you use Front End application, validate this and send valid data to
server
Madhivanan

how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

How To Set Multiple ReadOnlyVariables in Script Component in Integration Services 2005

Hello!

I'ave got a problem of setting more than one Variable in ReadOnlyVariables Property of ScriptComponent...I provide comma separated list of names ( As described in the help ) byt VS Studio Editor can not be opoened claiming that there is no a variablle with such a name...Looks like it doesn't treat the list as a collection of names...

Please help.

Vladimir

Make sure there are no spaces in the list.

Var1,Var2,Var3

This will not work:

Var1, Var2, Var3

Also note that variable names are case sensitive.|||Triple-check your spelling and the scope your variables are defined in. I got the error just this morning and it was a spelling problem.
|||

Thanks for your response...

I verified the spelling got rid of spaces...but result is the same

It is interesting thing.. I have only two variables: One is set on a package level and another is on the Data Flow Task level...

When I set one of them in ReadonlyVariables and another in ReadandWriteVariables it allows me to open VS for Applications. If I move both to the same location ( ReadonLy or ReadAnd Write with comma separation and no spaces ) it issues the message I described...

I tried specifying the namespaces for the variables, but with no Luck...

Not usre what to do...

Any ideas will be greately appreciated...

Thanks,

Vladimir

|||What version of SSIS are you using?

RTM? SP1? SP2?

how to set default data & log directory

I tried to add volume to default data & log directory of "new data default location" of database setting at SQL server properties, but it never be saved, when I get in properties again, the setting is disappear.
Does somebody could advise me how to save it?
Thank you
TingNever mind, I found out why the directory couldn't be able saved is because I went through it via terminal sevices. :p

How to set an action?

Hi,
I want to link to a website (like www.google.com) when user click any cell in cube browser.

Then I set an action, the setting detail as following:
Target Type: Cells
Target Object: All Cells
Action Content Type: URL
Action expression: www.google.com

But it's not work fine. When I click any cell in cube browse there is an error message.

Is any problem in my setting detail? especially Action expression?

thanks,You should put double quotes around literal srings in the action expression, so:

"www.google.com"|||Thanks. I add double quotes to it. There is no error message.

But I click the cell, there is no response.
I thought the action would be open a browser and link to google.
Why the action is no any response?|||Try the full URL: "http://www.google.com", because certain safety options can block browsing if the URL doesn't start with "http://" or "https://"

How to set a column to be an identity column in TSQL.

Can someone provide the syntax for setting a column to be an identity column
in a stored proceedure. When I export my database, my identity columns are
no longer identity columns.I don't beleive you can add an Identity property to an existing column.
You may need to create a temp table with the identity column in it, copy the
data to the temp table, drop the existing table and then rename your temp
one.
"Jim Mitchell" <jim_mitchell@.mindspring.com> wrote in message
news:uSn1WWFfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> Can someone provide the syntax for setting a column to be an identity
column
> in a stored proceedure. When I export my database, my identity columns
are
> no longer identity columns.
>

How to send notification to multiple email accounts?

I am in process of setting up job failure notification and one of the requirements that I have is to send notification to multiple users. I would appreciate any suggestion. One of the possible ways would be to create some sort of group account on exchange server with all users added as members. Is there anyway though where I could use users' individual accounts and based on that forward notification to them?

Thanks

Which version of sql server is this?

In SQL Server 2005 you can use Database Mail - http://www.sqlserverclub.com/articles/article05.aspx

In SQL Server 2000 try SQL Mail - http://support.microsoft.com/kb/263556

and also you can send mail using SP_OACreate SP

Refer : http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

Madhu

|||

Madhu,

I am sorry for not mentioning in advance that I am using Database Mail in SQL Server 2005 and that is why I am wondering how I could send notification to multiple users from job failure. As I understand using Profile would send email to only one of the accounts listed in that profile. I am quite familiar with Database Mail and I have been using it successfully for almost a year now.

|||

Without creating distribution list or group recipient from Exchange Server, I couldn't find any other way to send job failure notification to multiple people. My solution was to create a procedure that collects history of failed job and add it as a last step to my jobs so that whenever job failed it would execute that last step. Within that procedure I used sp_send_dbmail with a list of users email addresses. Solution seem to work brilliant.