Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!
When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>
|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TE
ST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:
> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> > Hi guys,
> >
> > My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> > not working. My settings are as follows:
> >
> > Checked other data source
> > Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> >
> > Under Security tab:
> > Be made using the logins current security context is checked.
> >
> >
> > Please help!
> >
>
>

Adding column to Merge Pub

I have a merge publication (SQL 2k) and needed to add a column to a
replicated table. I used the stored proc and settings below and it seemed
to work fine. When I synched with a subscriber, it seemed to have applied
the table modification first, then downloaded the updates that were on the
publication. I just want to make sure I am doing this correctly before I run
on the production database. Thanks.
David
exec sp_repladdcolumn @.source_object = 'ConfigFA'
, @.column = 'NextCaseNumber'
, @.typetext = 'int NOT NULL DEFAULT 1'
, @.publication_to_add = 'MCFIDataPub'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 1
That is exactly right, it has to do the table modification first to pull the
data back to the publisher and then merge it there. The data on the
publisher will already have the changed format.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u163fB4HGHA.2680@.TK2MSFTNGP09.phx.gbl...
>I have a merge publication (SQL 2k) and needed to add a column to a
>replicated table. I used the stored proc and settings below and it seemed
>to work fine. When I synched with a subscriber, it seemed to have applied
>the table modification first, then downloaded the updates that were on the
>publication. I just want to make sure I am doing this correctly before I
>run on the production database. Thanks.
> David
> exec sp_repladdcolumn @.source_object = 'ConfigFA'
> , @.column = 'NextCaseNumber'
> , @.typetext = 'int NOT NULL DEFAULT 1'
> , @.publication_to_add = 'MCFIDataPub'
> , @.force_invalidate_snapshot = 0
> , @.force_reinit_subscription = 1
>

Adding an sql table through VS2005

Hi,

Currently I am able to check for and add a column to an SQL database using this:

Dim connectionstring As String = My.Settings.ConnectionString

Dim eSQL As String = ""

Using connection As New System.Data.SqlClient.SqlConnection(connectionstring)

Dim cmd As New System.Data.SqlClient.SqlCommand(eSQL, connection)

connection.Open()

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND COLUMN_NAME = 'Company' ) " _

& "BEGIN " _

& "ALTER TABLE [Client] " _

& "ADD Company int " _

& "END "

cmd.CommandText = eSQL

cmd.ExecuteNonQuery()

End Using

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

hi,

GraemeP wrote:

Hi,

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND TABLE_TYPE = 'BASE TABLE' ) " _

& "BEGIN " _

& "CREATE TABLE [Client] (" _

& "Company int NOT NULL PRIMARY KEY," _

& "Name varchar(100) NOT NULL DEFAULT 'new company');" _

& "END;"

regards

|||Perfect - thanks!|||

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

|||

hi,

GraemeP wrote:

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

the very same pattern is not possible for views, as the CREATE VIE statement must be the first in it's own batch... typically you'll overcome this problem dropping the view, if existing like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE VIEW dbo.v1 AS

SELECT 1 AS [ColX];

GO

IF OBJECT_ID('dbo.v1') IS NOT NULL BEGIN

PRINT 'drop';

DROP VIEW dbo.v1;

END;

GO

CREATE VIEW dbo.v1 AS

SELECT 1 AS [ColX];

GO

DROP VIEW dbo.v1;

but this method has gotchas.. all permissions eventually set are gone when the objec is dropped..

regards

Adding an sql table through VS2005

Hi,

Currently I am able to check for and add a column to an SQL database using this:

Dim connectionstring As String = My.Settings.ConnectionString

Dim eSQL As String = ""

Using connection As New System.Data.SqlClient.SqlConnection(connectionstring)

Dim cmd As New System.Data.SqlClient.SqlCommand(eSQL, connection)

connection.Open()

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND COLUMN_NAME = 'Company' ) " _

& "BEGIN " _

& "ALTER TABLE [Client] " _

& "ADD Company int " _

& "END "

cmd.CommandText = eSQL

cmd.ExecuteNonQuery()

End Using

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

hi,

GraemeP wrote:

Hi,

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND TABLE_TYPE = 'BASE TABLE' ) " _

& "BEGIN " _

& "CREATE TABLE [Client] (" _

& "Company int NOT NULL PRIMARY KEY," _

& "Name varchar(100) NOT NULL DEFAULT 'new company');" _

& "END;"

regards

|||Perfect - thanks!|||

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

|||

hi,

GraemeP wrote:

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

the very same pattern is not possible for views, as the CREATE VIE statement must be the first in it's own batch... typically you'll overcome this problem dropping the view, if existing like

SET NOCOUNT ON; USE tempdb; GO CREATE VIEW dbo.v1 AS SELECT 1 AS [ColX]; GO IF OBJECT_ID('dbo.v1') IS NOT NULL BEGIN PRINT 'drop'; DROP VIEW dbo.v1; END; GO CREATE VIEW dbo.v1 AS SELECT 1 AS [ColX]; GO DROP VIEW dbo.v1;

but this method has gotchas.. all permissions eventually set are gone when the objec is dropped..

regards