Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed table
s
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and th
e
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:

> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we ha
ve
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split databas
e
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>

Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed tables
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and the
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:
> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we have
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split database
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>sql

adding a filegroup

when you add a filegroup to a existing server on a half full harddrive
does it take longer to add if the file group size is very large likje
over 100 gigs
*** Sent via Developersdex http://www.codecomments.com ***Is the question does it take longer to load a 100 gig database on a 1/2 full
hard drive then an empty one?
I would think that it would take longer depending on the fragmentation of
your hard drive, your speed of your new drives, your bus speed, your RAID
controller, what type of cabling you are using to connect to your RAID, the
list goes on and on...
Please clarify...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Bill" <nospam@.devdex.com> wrote in message
news:%23THF5$K7GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
> when you add a filegroup to a existing server on a half full harddrive
> does it take longer to add if the file group size is very large likje
> over 100 gigs
> *** Sent via Developersdex http://www.codecomments.com ***|||Mr Bruk:
What I meant was, does it take longer to add a small file group vs a
very large file group, on a drive that already has data on it.
Is adding a file group to a dataset set to a fixed amount of time with
no connection to the size of the filegroup.
In other words does it take longer to add a 100 gig file group vs a 20
gig filegeroup?
Bill
*** Sent via Developersdex http://www.codecomments.com ***|||A filegroup doesn't by itself have any size. It is the database files that b
elongs to a file group
that has size. Yes, adding a larger file will take longer than a small file.
You can test this for
yourself. Create a database with a data file of size 20 MB vs 20GB and you w
ill see this. Note that
in 2005, you have "instant file initialization" but only for database files
(see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for a few comments on
instant file
initialization and what permission is needed). So in 2005, try both with a l
arge data file and a
large log file to see the difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <nospam@.devdex.com> wrote in message news:%23%23wrYsT7GHA.4552@.TK2MSFTNGP05.phx.gbl..
.
> Mr Bruk:
> What I meant was, does it take longer to add a small file group vs a
> very large file group, on a drive that already has data on it.
> Is adding a file group to a dataset set to a fixed amount of time with
> no connection to the size of the filegroup.
> In other words does it take longer to add a 100 gig file group vs a 20
> gig filegeroup?
>
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***|||this question is straightforward the filegroup is being added due to the
other filegroups being full.
My question is does it take four times longer to add (blank as of yet)a
80gig filegroup vs a 20gig filegroup... At this time there is no data in
there, this is being done to add hd space for more room for additional
data..
Bill
*** Sent via Developersdex http://www.codecomments.com ***|||See my reply from earlier today. Again, a filegroup doesn't have a size, the
files does. Depending
on whether you get instant file initialization is a big factor. If you don't
get instant file
initialization, then you look at create time for a file which proportional t
o the file size (windows
has to zero out the newly allocated hard drive space).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <nospam@.devdex.com> wrote in message news:O0zUTjW7GHA.3812@.TK2MSFTNGP02.phx.gbl...[vb
col=seagreen]
>
> this question is straightforward the filegroup is being added due to the
> other filegroups being full.
> My question is does it take four times longer to add (blank as of yet)a
> 80gig filegroup vs a 20gig filegroup... At this time there is no data in
> there, this is being done to add hd space for more room for additional
> data..
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***[/vbcol]

adding a filegroup

when you add a filegroup to a existing server on a half full harddrive
does it take longer to add if the file group size is very large likje
over 100 gigs
*** Sent via Developersdex http://www.codecomments.com ***
Is the question does it take longer to load a 100 gig database on a 1/2 full
hard drive then an empty one?
I would think that it would take longer depending on the fragmentation of
your hard drive, your speed of your new drives, your bus speed, your RAID
controller, what type of cabling you are using to connect to your RAID, the
list goes on and on...
Please clarify...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Bill" <nospam@.devdex.com> wrote in message
news:%23THF5$K7GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
> when you add a filegroup to a existing server on a half full harddrive
> does it take longer to add if the file group size is very large likje
> over 100 gigs
> *** Sent via Developersdex http://www.codecomments.com ***
|||Mr Bruk:
What I meant was, does it take longer to add a small file group vs a
very large file group, on a drive that already has data on it.
Is adding a file group to a dataset set to a fixed amount of time with
no connection to the size of the filegroup.
In other words does it take longer to add a 100 gig file group vs a 20
gig filegeroup?
Bill
*** Sent via Developersdex http://www.codecomments.com ***
|||A filegroup doesn't by itself have any size. It is the database files that belongs to a file group
that has size. Yes, adding a larger file will take longer than a small file. You can test this for
yourself. Create a database with a data file of size 20 MB vs 20GB and you will see this. Note that
in 2005, you have "instant file initialization" but only for database files (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for a few comments on instant file
initialization and what permission is needed). So in 2005, try both with a large data file and a
large log file to see the difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <nospam@.devdex.com> wrote in message news:%23%23wrYsT7GHA.4552@.TK2MSFTNGP05.phx.gbl...
> Mr Bruk:
> What I meant was, does it take longer to add a small file group vs a
> very large file group, on a drive that already has data on it.
> Is adding a file group to a dataset set to a fixed amount of time with
> no connection to the size of the filegroup.
> In other words does it take longer to add a 100 gig file group vs a 20
> gig filegeroup?
>
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***
|||this question is straightforward the filegroup is being added due to the
other filegroups being full.
My question is does it take four times longer to add (blank as of yet)a
80gig filegroup vs a 20gig filegroup... At this time there is no data in
there, this is being done to add hd space for more room for additional
data..
Bill
*** Sent via Developersdex http://www.codecomments.com ***
|||See my reply from earlier today. Again, a filegroup doesn't have a size, the files does. Depending
on whether you get instant file initialization is a big factor. If you don't get instant file
initialization, then you look at create time for a file which proportional to the file size (windows
has to zero out the newly allocated hard drive space).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <nospam@.devdex.com> wrote in message news:O0zUTjW7GHA.3812@.TK2MSFTNGP02.phx.gbl...
>
> this question is straightforward the filegroup is being added due to the
> other filegroups being full.
> My question is does it take four times longer to add (blank as of yet)a
> 80gig filegroup vs a 20gig filegroup... At this time there is no data in
> there, this is being done to add hd space for more room for additional
> data..
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***

adding a filegroup

when you add a filegroup to a existing server on a half full harddrive
does it take longer to add if the file group size is very large likje
over 100 gigs
*** Sent via Developersdex http://www.developersdex.com ***Is the question does it take longer to load a 100 gig database on a 1/2 full
hard drive then an empty one?
I would think that it would take longer depending on the fragmentation of
your hard drive, your speed of your new drives, your bus speed, your RAID
controller, what type of cabling you are using to connect to your RAID, the
list goes on and on...
Please clarify...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Bill" <nospam@.devdex.com> wrote in message
news:%23THF5$K7GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
> when you add a filegroup to a existing server on a half full harddrive
> does it take longer to add if the file group size is very large likje
> over 100 gigs
> *** Sent via Developersdex http://www.developersdex.com ***

Adding a db to my server

I have a ldf and mdf file, i need to add them to my server how do i do this?Attach, never mind !sql

Adding a class to a report project

Hey there. I have been trying to add a class to my report project and I have
not been able to. I have added another project to the sln file to try to get
around this. But I have found no way to add a reference to the new class, so
trying to instaniate the class blows up.
All I want the class for is a central location for all of my embedded code.
I just want to create the custom functions I need one time and include that
file in every report project I may have and be done with it.
Seems reasonable no?
If you could point me in the right direction, that would be great.
TIA
reply here or at rbinns@.<nospam>NationalDiabetic.comTry this article it has a lot of good info on writing custom assemblies and
step by step examples. It is one of the only ones I have found on the
internet that is even remotely helpful
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
"Pinolian" wrote:
> Hey there. I have been trying to add a class to my report project and I have
> not been able to. I have added another project to the sln file to try to get
> around this. But I have found no way to add a reference to the new class, so
> trying to instaniate the class blows up.
> All I want the class for is a central location for all of my embedded code.
> I just want to create the custom functions I need one time and include that
> file in every report project I may have and be done with it.
> Seems reasonable no?
> If you could point me in the right direction, that would be great.
> TIA
> reply here or at rbinns@.<nospam>NationalDiabetic.com
>

Adding a .sql to MSDE Server

Hi All,
I have a .SQL script file that contains database definitions. How can i run
the .sql script file to add this database to my MSDE SQL server?
Thanks for your help
mustafa
"Mustafa Rabie" <mrabie007@.hotmail.com> wrote in message
news:%23vKwbfJBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a .SQL script file that contains database definitions. How can i
run
> the .sql script file to add this database to my MSDE SQL server?
>
Start a command prompt and use OSQL... enter: OSQL -? to get the available
options.
Steve
|||Hi, this is from "Microsoft SQL Server 2000 Sample Database Scripts"
[http://www.microsoft.com/downloads/d...displaylang=en]
To run the scripts using osql:
1.. Open a Command Prompt window.
2.. From the command prompt, use the cd command to navigate to the folder
containing the SQL Server 2000 Sample Database Scripts:
cd c:\program files\SQL Server 2000 Sample Databases
3.. Run osql specifying either Instpubs.sql or Instnwnd.sql as the input
file. These are some examples:
a.. To install the Northwind database on a default instance on the same
computer:
osql -E -i instnwnd.sql
b.. To install the pubs database on a default instance on a different
computer:
osql -E -S ComputerName -i instpubs.sql
c.. To install the Northwind database on a named instance:
osql -E -S ComputerName/InstanceName -i instnwnd.sql
J.Alberto
[ccs-ve]
Nota: no enviar mensajes a mi direccion e-mail,
solo spam. Avisar para asignarle un filtro y no sea
eliminado.

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
Culam
You can use osql to execute your sql statements. sp_addlogin is used to add a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam
|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
CulamYou can use osql to execute your sql statements. sp_addlogin is used to add
a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

Add txt file in report

I want to know with expression that how to add a txt file in report design so
when report is viewed text of txt file can also be viewed in Browser.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200704/1On Apr 12, 12:53 am, "asharma via SQLMonster.com" <u8315@.uwe> wrote:
> I want to know with expression that how to add a txt file in report design so
> when report is viewed text of txt file can also be viewed in Browser.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200704/1
As far as I know, there is not a way to do this directly; however, you
could try converting the text file to an HTML page and using the Jump
to URL property of a control to point to the HTML file on the Web
Server. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

Add to SSRS data extension with config file

Hello,

I try to add an data extension who take the information to connect to the database in a config file. But It's could not retreive the values corresponding to the key.

I'm using the class ConfigurationManager from the namespace System.Configuration.

ConfigurationManager.AppSettings["QueryModel"]

Without the config file, the extension is working well.... I got the impression that reporting service and Visual Studio BI don't find the config file.

If someone can help me or have some advices. I will be pleased

Hi,

Finally, I found how to manage data Extension with config file.

The solution is :

It's not possible to do exactily like a simple application, add a configuration file app.config don't work with extension.

I found a part of the answer on this forum. it's was talking about IExtension.SetConfiguration.

In fact to solve this problem, we need to add a tags in the RSReportDesigner.config file( PrivateAssemblies Directory of Visual Studio 8) or in the RSReportServer.config ( Report Server Directory), just after the definition of the extension

Code Snippet

<Extension Name="My Extension" Type="Microsoft.Samples.ReportingServices.MyExtension.ExtensionClass,Microsoft.Samples.ReportingServices.MyExtension">
<Configuration>
<MyExtensionConfigurationData>
<MyExtensionData1>Value</MyExtensionData1>
<MyExtensionData2>Value</MyExtensionData2>
</MyExtensionConfigurationData>
</Configuration>

</Extension>

You need to implemente the SetConfiuration Method inside your class who implement the IExtension Interface.

Code Snippet

public void SetConfiguration(string configuration)

{

configXML = configuration;

}

the content of the string "configXML" while be the block corresponding to the <MyExtensionConfigurationData> tag

Next, you have to add a fonction to parse the XML block and put it inside a List or a Map...

Bye

add text to a column

Hi,
I am mapping the .csv files to a destination table in sql server.
The names of each column in the .csv file is the same as the ones in the destination table.
In the destination table there is an extra field i.e. FileTypeField

Here is the question;
When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField
I do not see an option to do this. Can you help please?
Thanks

Use a derived column transformation to add a column to the data flow. Then you'll just map that new column to the destination table.|||Thanks

Add sdf file to my project when my sources are on remote server

I have an application that runs on a PDA and manage a database (.sdf) file I use C# with visual studio 5.0.

What I want to do is to add the database file (.sdf) to my project files and open a connection to it.

The problem is that all my source file are stored on remote machine and not locally on my machine and the .sdf file is also stored with the project source files.

When I try to add the file to the project I get the following error fro the data source configuration wizard :

"An error occured while retrieving the information from the database"

DRIVE_REMOTE

Parameter Name : N:\

I get the same error also when I try to open a new connection to the file from the server explorer. When i copy the file to drive "C" I can open a connection to it with no errors.

---

Software engineer

Hello,

Remotely opening an sdf file (SSC database) is not supported. From desktop to device (DB on a device) access is possible, but, I don't think the other way is.

Thanks

Udaya.

Add sdf file to my project when all my sources are on remote machine

I posted this question in the past and did not get any answer.

I put all my sources on a remote server mainly for backup purposes (the remote server is backuped every day) but when I have the .sdf file I can not open it from the visual studio 2005 configuration server here is the description of the problem:

I have an application that runs on a PDA and manage a database (.sdf) file I use C# with visual studio 2005

What I want to do is to add the database file (.sdf) to my project files and open a connection to it.

The problem is that all my source file are stored on remote machine and not locally on my machine and the .sdf file is also stored with the project source files.

When I try to add the file to the project I get the following error fro the data source configuration wizard :

"An error occured while retrieving the information from the database"

DRIVE_REMOTE

Parameter Name : N:\

I get the same error also when I try to open a new connection to the file from the server explorer. When i copy the file to drive "C:" I can open a connection to it with no errors.

I encounter this problem too .
It looks like Sqlce do not allowed connect remotely .
Currently have no answer for this problem.
|||Look at this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2191231&SiteID=1|||

Apparently you can't open SDF database on network shares but in this case you don’t have to.

Few possible solutions to that:

- Add a build step to copy database to a local drive and use that copy in the project.

- Build from local drive and add a script executed at particular time to copy everything to the remote server so it would be backed up.

- Start using version control system (e.g. VSS) instead of rather primitive remote copy.

Last one is the best in my opinion.

Add sdf file to my project when all my sources are on remote machine

I posted this question in the past and did not get any answer.

I put all my sources on a remote server mainly for backup purposes (the remote server is backuped every day) but when I have the .sdf file I can not open it from the visual studio 2005 configuration server here is the description of the problem:

I have an application that runs on a PDA and manage a database (.sdf) file I use C# with visual studio 2005

What I want to do is to add the database file (.sdf) to my project files and open a connection to it.

The problem is that all my source file are stored on remote machine and not locally on my machine and the .sdf file is also stored with the project source files.

When I try to add the file to the project I get the following error fro the data source configuration wizard :

"An error occured while retrieving the information from the database"

DRIVE_REMOTE

Parameter Name : N:\

I get the same error also when I try to open a new connection to the file from the server explorer. When i copy the file to drive "C:" I can open a connection to it with no errors.

I encounter this problem too .
It looks like Sqlce do not allowed connect remotely .
Currently have no answer for this problem.
|||Look at this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2191231&SiteID=1|||

Apparently you can't open SDF database on network shares but in this case you don’t have to.

Few possible solutions to that:

- Add a build step to copy database to a local drive and use that copy in the project.

- Build from local drive and add a script executed at particular time to copy everything to the remote server so it would be backed up.

- Start using version control system (e.g. VSS) instead of rather primitive remote copy.

Last one is the best in my opinion.

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?
Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen
|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>
|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:
> Mikus wrote:
> > How do i add linked server whoose source file is not located on server itself?
> >
> > I want to create linked server which uses excel file as it's source, but
> > this file is located on my pc(named "mix")
> >
> > I want to do something like this...
> >
> > EXEC sp_addlinkedserver 'MikusExcelSource',
> > 'Jet 4.0',
> > 'Microsoft.Jet.OLEDB.4.0',
> > '\\mix\silo\2005 9 Sep_new.xls',
> > NULL,
> > 'Excel 5.0'
> > GO
> >
> > any ideas ?
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>> Mikus wrote:
>> How do i add linked server whoose source file is not located on server itself?
>> I want to create linked server which uses excel file as it's source, but
>> this file is located on my pc(named "mix")
>> I want to do something like this...
>> EXEC sp_addlinkedserver 'MikusExcelSource',
>> 'Jet 4.0',
>> 'Microsoft.Jet.OLEDB.4.0',
>> '\\mix\silo\2005 9 Sep_new.xls',
>> NULL,
>> 'Excel 5.0'
>> GO
>> any ideas ?
>> Have you tried to add the linked server with the syntax you suggest?
>> There're no problems as such to add a linked server using an UNC path.
>> The only limitation that I've seen, is that if the files for the linked
>> server isn't placed on the server it self, you'll have to use this
>> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
>> add a linked server on Server A having the files placed on Server B,
>> I'll have to connect to Server A with Remote Desktop and open up QA and
>> run my query. If I open QA on my own desktop and connect to server A,
>> I'll get an error if I try to use the linked server.
>> There might be ways around this issue, but I've never been able to find
>> a way to get around it.
>> Regards
>> Steen
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen