Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts

Friday, March 30, 2012

load from sqlserver to its instance

I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring
up the properties, click on Server Options, then check the Data Access box.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In Enterprise Mgr, right click on the =linked server - CNS-SFO-A46\QA. Bring up the properties, =click on Server Options, then check the Data Access box.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Sandra" wrote in message news:00db01c3d620$c2=708f40$a601280a@.phx.gbl...I need to load data from a table in sqlserver to a table in an =instance of the same server. The query need to be run in the SQL server itself not =in the instance. So...insert into [servername\instancename].[dbname].[dbo].table1 (col1,col2)selectt2.col1,t2.col2from table2 as =t2 I keep getting this message:Server 'CNS-SFO-A46\QA' is =not configured for DATA ACCESS.Thanks in advance for you =help.

--=_NextPart_000_038B_01C3D5F9.27781520--|||I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you find the linked server under Security->Linked Servers?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:058101c3d631$7e232580$a101280a@.phx.gbl...
I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Did you find the linked server under Security->Linked Servers?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Sandra" wrote in message news:058101c3d631$7e=232580$a101280a@.phx.gbl...I'm affraid I don't see a check box for Data Access. Is it possible I'm =looking in the wrong place?>--Original Message-->In =Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring>up the properties, click on Server Options, then check =the Data Access box.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Sand=ra" wrote in message>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...>=I need to load data from a table in sqlserver to a table>in an instance =of the same server. The query need to be>run in the SQL server itself =not in the instance. So...>>insert into [servername\instancename].[dbname].>[dbo].table1 (>col1,>col2)>select>t2.col1,>t2.col2>from table2 as t2>>I keep getting this =message:>>Server 'CNS-SFO-A46\QA' is not configured for DATA =ACCESS.>>Thanks in advance for you help.>

--=_NextPart_000_0038_01C3D60C.8C53A730--|||Yes, I did and I don't find the name of the server listed
but when I try to create New Linked Server it says the
server already exist. The same response I get when I try
to run EXEC sp_addlinkedserver
@.server = 'servername\instancename'
Also, I see the name of the server in the sysservers table.
>--Original Message--
>Did you find the linked server under Security->Linked
Servers?
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:058101c3d631$7e232580$a101280a@.phx.gbl...
>I'm affraid I don't see a check box for Data Access. Is it
>possible I'm looking in the wrong place?
>>--Original Message--
>>In Enterprise Mgr, right click on the linked server -
CNS-
>SFO-A46\QA. Bring
>>up the properties, click on Server Options, then check
>the Data Access box.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>>I need to load data from a table in sqlserver to a table
>>in an instance of the same server. The query need to be
>>run in the SQL server itself not in the instance. So...
>>insert into [servername\instancename].[dbname].
>>[dbo].table1 (
>>col1,
>>col2)
>>select
>>t2.col1,
>>t2.col2
>>from table2 as t2
>>I keep getting this message:
>>Server 'CNS-SFO-A46\QA' is not configured for DATA
ACCESS.
>>Thanks in advance for you help.
>|||The server might already be listed under "remote servers". This would happen
for example if you were doing replication from one instance to another. You
can allow data access by running the procedure
sp_serveroption 'server' ,
'data access',
'true'
Hope this helps,
Joe Lax
<anonymous@.discussions.microsoft.com> wrote in message
news:06be01c3d641$bbad9740$a101280a@.phx.gbl...
> Yes, I did and I don't find the name of the server listed
> but when I try to create New Linked Server it says the
> server already exist. The same response I get when I try
> to run EXEC sp_addlinkedserver
> @.server = 'servername\instancename'
> Also, I see the name of the server in the sysservers table.
>
>
> >--Original Message--
> >Did you find the linked server under Security->Linked
> Servers?
> >
> >--
> > Tom
> >
> >----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >..
> >"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:058101c3d631$7e232580$a101280a@.phx.gbl...
> >I'm affraid I don't see a check box for Data Access. Is it
> >possible I'm looking in the wrong place?
> >
> >>--Original Message--
> >>In Enterprise Mgr, right click on the linked server -
> CNS-
> >SFO-A46\QA. Bring
> >>up the properties, click on Server Options, then check
> >the Data Access box.
> >>
> >>--
> >>Tom
> >>
> >>----
> -
> >--
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com/sql
> >>
> >>
> >>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
> >>I need to load data from a table in sqlserver to a table
> >>in an instance of the same server. The query need to be
> >>run in the SQL server itself not in the instance. So...
> >>
> >>insert into [servername\instancename].[dbname].
> >>[dbo].table1 (
> >>col1,
> >>col2)
> >>select
> >>t2.col1,
> >>t2.col2
> >>from table2 as t2
> >>
> >>I keep getting this message:
> >>
> >>Server 'CNS-SFO-A46\QA' is not configured for DATA
> ACCESS.
> >>
> >>Thanks in advance for you help.
> >>
> >sql

Wednesday, March 21, 2012

Listing Db values

Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.try this one
select sysobjects.name as Table_Name,syscolumns.name as Column_Name,systypes.name as Data_Type from sysobjects
join syscolumns on sysobjects.id=syscolumns.id
join systypes on syscolumns.xtype=systypes.xtype and systypes.status=typestat
where sysobjects.type='u'

Originally posted by kixer
Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.|||Thanx! ;) Now I know the objectnames. All I have to do now is to make a nice treeview with the generated values, so i can log some sort of a dictionary.

Monday, March 19, 2012

List Unused Entries

I am using SQLServer 2000. I have one table (Members) which includes a
MemberID (int) field. I have another table (EventRegs) with a unique
EventID(int) and a foreign key for MemberID. A given MemberID can occur in
more than one row in the EventRegs table. I want to find out which MemberID
values that exist in the Members table that do not occur in any of the rows
in the EventRegs table. Basically, which members are not attending any
events.
I always get tangled up when I try to do this type of query! Can anyone give
me an example to work from?
WayneWayene
SELECT MemberID FROM Members WHERE NOT EXISTS
(SELECT * FROM EventRegs WHERE EventRegs.EventID=Members.MemberID )
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I am using SQLServer 2000. I have one table (Members) which includes a
>MemberID (int) field. I have another table (EventRegs) with a unique
>EventID(int) and a foreign key for MemberID. A given MemberID can occur in
>more than one row in the EventRegs table. I want to find out which MemberID
>values that exist in the Members table that do not occur in any of the rows
>in the EventRegs table. Basically, which members are not attending any
>events.
> I always get tangled up when I try to do this type of query! Can anyone
> give me an example to work from?
> Wayne
>

Monday, March 12, 2012

List registered assemblies

Hi,

It's possible to register an assembly in SqlServer 2005 using the

CREATE ASSEMBLY

syntax.

How can I get a list of assemblies that have been registered?

Thanks

Herbj?rn

Select * from sys.assemblies ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

List of users and user rights

Does anyone have a query that will list all the users and user rights for a sqlserver database.

Thanks so much!sp_helpuser will list all the users.
sp_helplogins gives all the login users.
these procedures might help you

List of tables

Hi,
Is there any command to find all the tables(including system tables) in SQLserver(I do not want to use enterprise manager).
Regards,Originally posted by sursujmad
Hi,

Is there any command to find all the tables(including system tables) in SQLserver(I do not want to use enterprise manager).

Regards,

You can use a sql request like this

select * from sysobjects where type = 's' or type = 'u'|||--Try
exec sp_tables
select user_name(uid)+'.'+name from sysobjects where xtype in ('U','S')
select * from information_schema.tables

Friday, March 9, 2012

List of keywords for sqlserver 2005

Hi,
pls let me know any links that has the list of the sqlserver 2005 Keywords.
-Srik
A full list of SQL Server 2005 keywords is located here:
http://msdn2.microsoft.com/en-us/library/ms245925

SQL Server 2000 keywords are located here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

|||thanx for the info

List of hot fixes after SP3

How can I get a list of all hotfixes that came after SQL
Server 2000 Service Pack 3 ?
I maintain a pretty exhaustive list here, and update it at least once or
twice a week.
http://www.aspfaq.com/2160
http://www.aspfaq.com/
(Reverse address to reply.)
"jean" <jean.bertrand@.sbcglobal.net> wrote in message
news:15ad01c46f38$46c7d6d0$a601280a@.phx.gbl...
> How can I get a list of all hotfixes that came after SQL
> Server 2000 Service Pack 3 ?
>
|||Is there a publicly available hotfix newer that 819?
Thanks
L.Teska
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epOXmjzbEHA.1356@.TK2MSFTNGP09.phx.gbl...
>I maintain a pretty exhaustive list here, and update it at least once or
> twice a week.
> http://www.aspfaq.com/2160
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "jean" <jean.bertrand@.sbcglobal.net> wrote in message
> news:15ad01c46f38$46c7d6d0$a601280a@.phx.gbl...
>
|||Yes, under KB article 810185
2000.80.0878
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lynn Teska" <lteska@.csi.com> wrote in message
news:10g4q8n7col94f1@.corp.supernews.com...
> Is there a publicly available hotfix newer that 819?
> Thanks
> L.Teska
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:epOXmjzbEHA.1356@.TK2MSFTNGP09.phx.gbl...
>

Wednesday, March 7, 2012

List of hot fixes after SP3

How can I get a list of all hotfixes that came after SQL
Server 2000 Service Pack 3 ?I maintain a pretty exhaustive list here, and update it at least once or
twice a week.
http://www.aspfaq.com/2160
http://www.aspfaq.com/
(Reverse address to reply.)
"jean" <jean.bertrand@.sbcglobal.net> wrote in message
news:15ad01c46f38$46c7d6d0$a601280a@.phx.gbl...
> How can I get a list of all hotfixes that came after SQL
> Server 2000 Service Pack 3 ?
>|||Is there a publicly available hotfix newer that 819?
Thanks
L.Teska
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epOXmjzbEHA.1356@.TK2MSFTNGP09.phx.gbl...
>I maintain a pretty exhaustive list here, and update it at least once or
> twice a week.
> http://www.aspfaq.com/2160
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "jean" <jean.bertrand@.sbcglobal.net> wrote in message
> news:15ad01c46f38$46c7d6d0$a601280a@.phx.gbl...
>|||Yes, under KB article 810185
2000.80.0878
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lynn Teska" <lteska@.csi.com> wrote in message
news:10g4q8n7col94f1@.corp.supernews.com...
> Is there a publicly available hotfix newer that 819?
> Thanks
> L.Teska
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:epOXmjzbEHA.1356@.TK2MSFTNGP09.phx.gbl...
>

list of common maintenance tasks?

I'm new to SQL Server after moving an online database from Access to SQL
Server. My database is about 170mb now, and I really don't have any idea
what I need to be doing on a regular basis to keep it 'well-oiled'. What
are the common maintenance tasks in order to keep it running as quickly and
as efficiently as possible?
Any help or pointers to help would be great appreciated.
Thanks,
GlennThe place to start is to open up SQL Enterprise Manager. Select your server
and then choose the Tools | Database Maintenance Planner option. Then
follow the instruction on the Wizard screens.
Mike O.
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...
quote:

> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly

and
quote:

> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>
|||It will take a few hours, but please read the SQL Server 2000 Maintenance
and Operations Guide at
http://www.microsoft.com/technet/tr...ide/default.asp
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...
quote:

> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly

and
quote:

> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>

List of Audit Events for SQLServer

The documentation on SQLServer (both books and online) seems to have treated this as a need-to-know item.

Can someone point me where I should be looking for a complete list of the audit events that SQLServer would spit out.

The list for Sybase is easily found when you do a search for "audit" but nothing comes up for SQLServer.

Thanks in advance!1 If you are looking for additional information on error messages (1 - 21999)?? browse the BOL Contents as follows:

BOL Contents:
Troubleshooting:
Error Message Descriptions:
Resolving System Error Messages:
Errors 1 - 999
...
Errors 21000 - 21999

2 If interested in c2 audit mode there are MS white papers pertaining to the subject.

3 If interested in auditing using profiler or traces there is some information on both in BOL and there are MS resources pertaining to both subjects, (however not all trace flags or results are documented to users).

(Unfortunatly, reference links for 2 & 3 have been moved.)