Friday, March 30, 2012
How to Attach a database to SQLServer7
How do I "attach" a database to SQLServer 7 ?
I created a database and developed a VB program to use it. I wanted to
demo it to a co-worker of mine. So, I COPIED the database (the .MDF
file and it associated .LDF file) (that's: copied the files with
Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
and sent it to him (he's 2000 miles away). He copied it on to his
server (running SQLServer 7) but we can't get SQLServer to see it.
I've read a couple of things about "attaching" a copied database
(mainly Q224071) but I can't figure out how to do that. It tells me to
run a "stored procedure". How? Where is this sp_attach_db stored
procedure? And how do I use it? Can someone give me a clue as to how
to do this?
BTW, this is NOT in a production environment. No one but me uses this
database or even the server itself. So, I'm pretty much free to do
whatever needs to be done.
Thanks for any guidance.INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
> run a "stored procedure". How? Where is this sp_attach_db stored
> procedure? And how do I use it? Can someone give me a clue as to how
> to do this?
Well, sp_attach_db is present in the master database and this is similar to
using any conventional stored procedure. The article contains good
information on how to move a database to different locations.
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"Martin" <martinvalley@.comcast.net> wrote in message
news:2f7okvsjllmh0fp4nsrkp4paem59vavms4@.4ax.com...
> OK, I'm an idiot - hopefully, someone will help me out anyway...
> How do I "attach" a database to SQLServer 7 ?
> I created a database and developed a VB program to use it. I wanted to
> demo it to a co-worker of mine. So, I COPIED the database (the .MDF
> file and it associated .LDF file) (that's: copied the files with
> Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
> and sent it to him (he's 2000 miles away). He copied it on to his
> server (running SQLServer 7) but we can't get SQLServer to see it.
> I've read a couple of things about "attaching" a copied database
> (mainly Q224071) but I can't figure out how to do that. It tells me to
> run a "stored procedure". How? Where is this sp_attach_db stored
> procedure? And how do I use it? Can someone give me a clue as to how
> to do this?
> BTW, this is NOT in a production environment. No one but me uses this
> database or even the server itself. So, I'm pretty much free to do
> whatever needs to be done.
> Thanks for any guidance.
>|||Thanks, I got it working.
What I didn't understand was HOW to execute the stored procedure. If
some other clueless newbie reads this, run "Query Analyzer" and
execute the query from there.
On Tue, 26 Aug 2003 19:59:25 -0700, Martin <martinvalley@.comcast.net>
wrote:
>OK, I'm an idiot - hopefully, someone will help me out anyway...
>How do I "attach" a database to SQLServer 7 ?
>I created a database and developed a VB program to use it. I wanted to
>demo it to a co-worker of mine. So, I COPIED the database (the .MDF
>file and it associated .LDF file) (that's: copied the files with
>Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
>and sent it to him (he's 2000 miles away). He copied it on to his
>server (running SQLServer 7) but we can't get SQLServer to see it.
>I've read a couple of things about "attaching" a copied database
>(mainly Q224071) but I can't figure out how to do that. It tells me to
>run a "stored procedure". How? Where is this sp_attach_db stored
>procedure? And how do I use it? Can someone give me a clue as to how
>to do this?
>BTW, this is NOT in a production environment. No one but me uses this
>database or even the server itself. So, I'm pretty much free to do
>whatever needs to be done.
>Thanks for any guidance.
>|||Martin,
FYI, on SQL Server 2000 from the Enterprise Manager. Connect to the
server. Right click on Databases. Left click on All Tasks. Left click
on Attach Database... Fill in the blanks. This will not work on SQL
7.0, use the Query Analyzer. Also you do not need the ldf for an
attach, it will create a new one, although it will not hurt.
Norman
Martin <martinvalley@.comcast.net> wrote in message news:<kvfpkv4tovnk1id8td6s5i9krtlidtlscf@.4ax.com>...
> Thanks, I got it working.
> What I didn't understand was HOW to execute the stored procedure. If
> some other clueless newbie reads this, run "Query Analyzer" and
> execute the query from there.
>
> On Tue, 26 Aug 2003 19:59:25 -0700, Martin <martinvalley@.comcast.net>
> wrote:
> >OK, I'm an idiot - hopefully, someone will help me out anyway...
> >
> >How do I "attach" a database to SQLServer 7 ?
> >
> >I created a database and developed a VB program to use it. I wanted to
> >demo it to a co-worker of mine. So, I COPIED the database (the .MDF
> >file and it associated .LDF file) (that's: copied the files with
> >Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
> >and sent it to him (he's 2000 miles away). He copied it on to his
> >server (running SQLServer 7) but we can't get SQLServer to see it.
> >
> >I've read a couple of things about "attaching" a copied database
> >(mainly Q224071) but I can't figure out how to do that. It tells me to
> >run a "stored procedure". How? Where is this sp_attach_db stored
> >procedure? And how do I use it? Can someone give me a clue as to how
> >to do this?
> >
> >BTW, this is NOT in a production environment. No one but me uses this
> >database or even the server itself. So, I'm pretty much free to do
> >whatever needs to be done.
> >
> >Thanks for any guidance.
> >
how to attach a .mdf file?
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>> You also need the ldf file. Are you clicking the Add button in the att
>> dialog box to browse for the file you want to attach?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>>
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>sql
Wednesday, March 28, 2012
how to attach a .mdf file?
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/d...>
userinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>
Monday, March 26, 2012
How to apply sp4 to multiple instances that have different sp leve
g
on the same server. The server is running Windows 2000.
SERVER Name: ABC01DB
SqlServer Instances follow:
Default 8.00.534 SP2 Developer Edition
DEV 8.00.760 SP3 Developer Edition
ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
ABC_MGR 8.00.760 SP3 Developer Edition
Questions:
1) Do I need to apply any service packs other than sp4 to any of them prior
to applying sp4.
2) Is there any way to apply sp4 to multiple instances simultaneously or
must I apply sp4 separately for each instance.
3) Any suggestions or comments regarding the steps to simplify applying sp4
to all of the above instances.I proceeded with the upgrade and simply applied the sp4 to each instance,
starting with those which had no service packs first, (RTM). Then, I applie
d
it to the Default which was at sp2, and finally to those at sp3.
It all works.
"theWizard1" wrote:
> I need to apply sp4 for SqlServer 2000 to the following instances all runn
ing
> on the same server. The server is running Windows 2000.
> SERVER Name: ABC01DB
> SqlServer Instances follow:
> Default 8.00.534 SP2 Developer Edition
> DEV 8.00.760 SP3 Developer Edition
> ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
> ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
> ABC_MGR 8.00.760 SP3 Developer Edition
> Questions:
> 1) Do I need to apply any service packs other than sp4 to any of them pri
or
> to applying sp4.
> 2) Is there any way to apply sp4 to multiple instances simultaneously or
> must I apply sp4 separately for each instance.
> 3) Any suggestions or comments regarding the steps to simplify applying sp
4
> to all of the above instances.
How to apply sp4 to multiple instances that have different sp leve
on the same server. The server is running Windows 2000.
SERVER Name: ABC01DB
SqlServer Instances follow:
Default 8.00.534 SP2 Developer Edition
DEV 8.00.760 SP3 Developer Edition
ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
ABC_MGR 8.00.760 SP3 Developer Edition
Questions:
1) Do I need to apply any service packs other than sp4 to any of them prior
to applying sp4.
2) Is there any way to apply sp4 to multiple instances simultaneously or
must I apply sp4 separately for each instance.
3) Any suggestions or comments regarding the steps to simplify applying sp4
to all of the above instances.I proceeded with the upgrade and simply applied the sp4 to each instance,
starting with those which had no service packs first, (RTM). Then, I applied
it to the Default which was at sp2, and finally to those at sp3.
It all works.
"theWizard1" wrote:
> I need to apply sp4 for SqlServer 2000 to the following instances all running
> on the same server. The server is running Windows 2000.
> SERVER Name: ABC01DB
> SqlServer Instances follow:
> Default 8.00.534 SP2 Developer Edition
> DEV 8.00.760 SP3 Developer Edition
> ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
> ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
> ABC_MGR 8.00.760 SP3 Developer Edition
> Questions:
> 1) Do I need to apply any service packs other than sp4 to any of them prior
> to applying sp4.
> 2) Is there any way to apply sp4 to multiple instances simultaneously or
> must I apply sp4 separately for each instance.
> 3) Any suggestions or comments regarding the steps to simplify applying sp4
> to all of the above instances.sql
Friday, March 23, 2012
How to alter server name?
How to alter the server name after I install a SQL Server 2000. The SQL
Server name would be the computer name in default, however, I want to alter
the Server name different from computer name which the SQL Server reside in.Look up sp_dropserver and sp_addserver in BOL
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Yelson Hwo" <hy5005@.hotmail.com> wrote in message
news:e0vxSWs6DHA.2524@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi,
> How to alter the server name after I install a SQL Server 2000. The SQL
> Server name would be the computer name in default, however, I want to
alter
quote:
> the Server name different from computer name which the SQL Server reside
in.
quote:|||I have try this way, it does not work.
>
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> д?
news:OJYSmbs6DHA.2732@.TK2MSFTNGP09.phx.gbl...
quote:|||What did you try? Can you provide the code?
> Look up sp_dropserver and sp_addserver in BOL
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Yelson Hwo" <hy5005@.hotmail.com> wrote in message
> news:e0vxSWs6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> alter
> in.
>
Ray Higdon MCSE, MCDBA, CCNA
--
"Yelson Hwo" <hy5005@.hotmail.com> wrote in message
news:e41mZgs6DHA.3896@.TK2MSFTNGP11.phx.gbl...
quote:|||Hi:
> I have try this way, it does not work.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> д?
> news:OJYSmbs6DHA.2732@.TK2MSFTNGP09.phx.gbl...
SQL[QUOTE]
reside[QUOTE]
>
Please read this KB article and it may be useful:
http://support.microsoft.com/defaul...kb;en-us;303774
Best Wishes
Wei Ci Zhou|||I did as the below:
--
sp_dropserver 'old_name'
go
sp_addserver 'new_name', 'local'
go
--
now I restart the SQL Services, the @.@.servername change as the "new_name",
but acctually the SQL Server Name in work still is "old_name", if you try
use the "new_name" as the Server name, you can't establish a connection with
it, you can only establish a connection with the "old_name".
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com>
news:uBVtRks6DHA.2576@.TK2MSFTNGP11.phx.gbl...
quote:|||I didn't catch that was what you were trying to do, See if this link helps -
> What did you try? Can you provide the code?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Yelson Hwo" <hy5005@.hotmail.com> wrote in message
> news:e41mZgs6DHA.3896@.TK2MSFTNGP11.phx.gbl...
> SQL
to[QUOTE]
> reside
>
http://support.microsoft.com/defaul...kb;en-us;289573
Ray Higdon MCSE, MCDBA, CCNA
--
"Yelson Hwo" <hy5005@.hotmail.com> wrote in message
news:uRvPr0s6DHA.2168@.TK2MSFTNGP12.phx.gbl...
quote:
> I did as the below:
> --
> sp_dropserver 'old_name'
> go
> sp_addserver 'new_name', 'local'
> go
> --
> now I restart the SQL Services, the @.@.servername change as the "new_name",
> but acctually the SQL Server Name in work still is "old_name", if you try
> use the "new_name" as the Server name, you can't establish a connection
with
quote:|||Seems like you didn't rename the machine in Windows?
> it, you can only establish a connection with the "old_name".
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com>
> news:uBVtRks6DHA.2576@.TK2MSFTNGP11.phx.gbl...
The[QUOTE]
> to
>
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Yelson Hwo" <hy5005@.hotmail.com> wrote in message news:uRvPr0s6DHA.2168@.TK2MSFTNGP12.phx.gbl...[Q
UOTE]
> I did as the below:
> --
> sp_dropserver 'old_name'
> go
> sp_addserver 'new_name', 'local'
> go
> --
> now I restart the SQL Services, the @.@.servername change as the "new_name",
> but acctually the SQL Server Name in work still is "old_name", if you try
> use the "new_name" as the Server name, you can't establish a connection wi
th
> it, you can only establish a connection with the "old_name".
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com>
> news:uBVtRks6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> to
>[/QUOTE]
Wednesday, March 21, 2012
How to align the text in Justify format with SQL Reports?
When i was working with SQL reporting services of MS SQLSERVER 2005, I am not able to align the text in Justify format, since there is no such option in it. But i need to align the text in justify format. How do i align it?
You may want to try this post in the Reporting Services area - this forum has to do with documentation.
Monday, March 19, 2012
How to add SQL Server to SourceSafe?
Can anyone tell me the best place to find documentation on how to add SQL
Server databases to SourceSafe?
Thanks
Hi
SQL Server databases can not be in VSS, but the DDL can be stored there.
If you use Visual Studio, you can store your SPs and Triggers in VSS.
Currently, it is mostly a manual procedure until SQL Server 2005 arrives.
Regards
Mike
"kppk" wrote:
> HI,
> Can anyone tell me the best place to find documentation on how to add SQL
> Server databases to SourceSafe?
> Thanks
|||Hello kppk.
We do a FREE scripting tool that can add all your schema statements to
Visual Source Safe as well as those tables where you want to script out the
data (for look up tables, static data, system data etc). It comes as part of
the DB Ghost package and is completely FREE. It also comes with a COM object
and documentation so you can use it in your programming if you desire.
http://www.innovartis.co.uk/Evaluation.aspx
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
"kppk" wrote:
> HI,
> Can anyone tell me the best place to find documentation on how to add SQL
> Server databases to SourceSafe?
> Thanks
how to add rows with Script Component?
Why am I getting this design-time error from my script:
'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'
Here's my script:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim output_row As Buffer = Row
Dim seq As Integer = 0
For Each code As String In Row.IC.ToString().Split("*"c)
If seq > 0 Then
output_row = Row.AddRow()
End If
output_row.seq = seq
output_row.value = code
seq += 1
Next
End Sub
End Class
And here's the error it generates:
Error 3 Validation error. Convert Bib (ISC) to Prep Item_Detail: Split ISC codes [52]: Error 30311: Value of type 'ScriptComponent_d021a3fd485946868f5f0daadaf0e57c.Input0Buffer' cannot be converted to 'System.Buffer'. Line 15 Column 36 through 38 Error 30390: 'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'. Line 19 Column 30 through 39 Error 30456: 'seq' is not a member of 'System.Buffer'. Line 21 Column 13 through 26 Error 30456: 'value' is not a member of 'System.Buffer'. Line 22 Column 13 through 28 ConvertDIN.dtsx 0 0
You are getting the error because your trying to get async script behavior from a synchronous script. This is clear from the for loop per input row, which could produce multiple output rows per input row. This is practically the definition of an asynchronous component.So, make your component asynchronous.
1. On your output, set the SynchronousInputID to 0. Now you have an async component.
2. Add columns to your output, like seq and value.
3. Add rows to the output, as in the following.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim seq As Integer = 0
For Each code As String In Row.IC.ToString().Split("*"c)
If seq > 0 Then
Output0Buffer.AddRow()
Output0Buffer.seq = seq
Output0Buffer.value = code
End If
seq += 1
Next
End Sub|||
Thanks, now it compiles! But how does the object returned by Output0Buffer.AddRow() get its seq and code properties set? When I ran the above code, I got 0 output rows.
Also, how do I output a row when seq = 0, that is, the output row that corresponds to the first code in the input Row.IC string?
|||I took out the test for seq > 0, and replaced the Recordset destination I was using for debugging with an OLE destination, and now my data view shows 1 row being output for each input row. Thanks!
Monday, March 12, 2012
How to add languages to the table sys.fulltext_languages?
HELLO ALL!
select * from sys.fulltext_languages
this query results nothing, so I need to add languages to sqlserver to be used with FTS.
Is there any way to do that?
THANK YOU!!!
It is best to not post multiple versions of your question like this. Post and wait for a response. That shows respect for the folks here since they don't have to spend time answering a question that was answered in another posting.
|||
See the follwing links for clarification:
default full-text language Option
"The default value of this option is the language of the server."
http://msdn2.microsoft.com/en-us/library/ms180854.aspx
Setting Server Configuration Options
http://msdn2.microsoft.com/en-us/library/ms189631.aspx
sys.fulltext_languages (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176076.aspx
Jens K. Suessmeyer.
http://www.sqlserver2005.de
how to add job to sqlserver
i want to add job that runs on a specific time of the day in sqlserver 2005 how?
thanks in advance.
In SQL Server Express you cannot because the Agent that runs Jobs the automation subsystem is not included with Express. If you look you will find tools developed by other developer to do it, some for free others sell it, another option you can buy 2000 and use the Agent to run it. Hope this helps.
|||Yes, SQL Server Agent not included in SQL Server Express, look at these options for planing and starting jobs:http://www.sqldbatips.com/showarticle.asp?ID=29
http://www.sqldbatips.com/showarticle.asp?ID=27
http://www.codeproject.com/useritems/SQLAgent.asp
http://www.lazycoding.com/products.aspx
You might see that these alternatives are driven by private projects and are not supported by Microsoft but do their job.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, March 9, 2012
how to add autonumber (increment) unique ID to sqlserver
I am a newbie learning sql server. I used to use Access table where I set the primary ID (int) as autonumber. Every insert will generate a new ID, which is incremented by 1.
How do I add an INSERT statement to add the incrementing primary ID in sql server. I noticed that you can't set the ID to autonumber as you do in Access. I read that you can set the ID as identity, which can auto increment.
How do I handle this in the INSERT cmd. The only thought is to call a sql cmd with a max on the current id and increment by 1 in vbscript. Afterwards, add the increment ID into the INSERT call.
Is there an easier way to do this?
Thanks,
JohnHi John,
Correct. You can't write to an identity field (and I didn't know you could in Access). You don't write to it, so don't include that field in your insert statement.
If you need the new ID number, return the value from the SQL Server SCOPE_IDENTITY function after you insert the row, either by returning it from your stored procedure or immediately after doing the insert. And you can batch them together so that you have only one round trip to the SQL Server server.
And there are lots of ways to do this, depending on the needs of your application.
Is that enough information? If not, just ask. It's pretty straightforward once you get to know SQL Server a bit.
Don
Sunday, February 19, 2012
How To access Sqlserver Reporting Two Different Server
Hi Friends ,
I have a problem to access the sqlserver Reporting on different server. Actually I created Sql Server Reporting in one server with in that server i able to access the report. but out of that server that means another server webapplication i unable to access it. how can i rectify this problem.
Redirecting to the RS forum.
Mike
|||Let's begin by naming your servers Server 1 (Server with Reporting Services) and Server 2 (Web Server with Web Application). I am not quite able to understand the exact problem you are facing. Either you are ok running reports on Server 1 but when you log in to Server 2, you are unable to run the reports (Scenario A). Or you are trying to have a Web Application on server 2 access the reports and display them (Scenario B).
If Scenario A is your problem, then you probably have some type of authentication issue. Is SQL running on Server 1 or is it on a different server? If it is a different server, are you on a domain or are you running under a workgroup? Here is a webcast that speaks to "double-hop" authentication which you will need if you have three different servers involved: http://support.microsoft.com/kb/887682
If Scenario B, take a look at this article by Russell Christopher on impersonation and think about using it on your Web Application: http://blogs.msdn.com/bimusings/archive/2005/11/18/494436.aspx
If none of the above helps, please reply with a precise outline of your problem and setup and I will try again.
Good luck!
Larry Smithmier
How To access Sqlserver Reporting Two Different Server
Hi Friends ,
I have a problem to access the sqlserver Reporting on different server. Actually I created Sql Server Reporting in one server with in that server i able to access the report. but out of that server that means another server webapplication i unable to access it. how can i rectify this problem.
Redirecting to the RS forum.
Mike
|||Let's begin by naming your servers Server 1 (Server with Reporting Services) and Server 2 (Web Server with Web Application). I am not quite able to understand the exact problem you are facing. Either you are ok running reports on Server 1 but when you log in to Server 2, you are unable to run the reports (Scenario A). Or you are trying to have a Web Application on server 2 access the reports and display them (Scenario B).
If Scenario A is your problem, then you probably have some type of authentication issue. Is SQL running on Server 1 or is it on a different server? If it is a different server, are you on a domain or are you running under a workgroup? Here is a webcast that speaks to "double-hop" authentication which you will need if you have three different servers involved: http://support.microsoft.com/kb/887682
If Scenario B, take a look at this article by Russell Christopher on impersonation and think about using it on your Web Application: http://blogs.msdn.com/bimusings/archive/2005/11/18/494436.aspx
If none of the above helps, please reply with a precise outline of your problem and setup and I will try again.
Good luck!
Larry Smithmier
how to access sql server express from other computer on the netwrok
Hi experts
I use a sqlserver on a local computer. i want to access the sqlserver from other computers on the network using the VWD. When trying to connect to the database i get the error:
"An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I enabled the TCP/IP and the Named Pipes protocols on the server and still the same error.
Thanks
check windows firewall enbale or not..
if yes, then let sql 2005 port pass through
|||I just found the sollution.
1. when accessing from other computers, the servers 'Guest' account is used, so this accoun should have the correct permissions (not recommended) or access using another privileged acount
2. enable remote connection in the "SQL Serve surface area Configuration" tool
Thanks for the attention anyway