ASP Classic - SQL Server Linked Query - How to write it for use in asp classic statement?

create a stored procedure
put inside whatever you need
call that stored procedure from page

Have never messed around with stored procedures, so this is a first.
I am trying to find enough information to guide me on the path to doing this.
Any assistance would be welcome.

I am totally lost on this one.
I found out how to create a Stored Procedure through SSMS, and I did that.
But, where to put everything at, and how, and ???
completely lost.

Years ago, here on EE, someone told me that I needed to learn SP since I grasped the Parameters so well, but I never got around to learning it.

what is your sp look like now?

instead of running queries, you will call sp from your code...
thats it...

That is the issue, I have no idea how to start it.
Not with this part.

sp_addlinkedserver 'NAME-THE-CONNECTION', '', 'SQLNCLI', NULL, NULL, 'Server=IP-ADDRESS-OF-SERVER', NULL
exec sp_addlinkedsrvlogin 'NAME-THE-CONNECTION', 'false', NULL, 'USERNAME', 'PASSWORD'

Select allOpen in new window

How would that be written within the SP?
And then have the INSERT added into that.

As I stated, I am lost on this one.
I mean, I can run these scripts from within SSMS.
Which will setup my tables with the content, then I can run my other process script for the mail.
But, would I would like to do, is make this so that I do not have to touch SSMS, at all.

this is procedure code...

create procedure myProc() as
begin
  sp_addlinkedserver ...
  exec sp_addlinkedsrvlogin ...
  insert into ...
end;

Select allOpen in new window

then you will run this from your code, similar to qry...

and check this

How to call SQL Server stored procedures from ASP
https://support.microsoft.com/en-us/help/164485/how-to-call-sql-server-stored-procedures-from-asp

Hi Wayne I can give you a more detailed answer in a couple days. In short you'll have two connection strings on your ASP page, one for local and one for live.

Now you'll have a select statement query using the connection string from your live database as you Loop through that you want to insert to your local database using your local connection string.

If you have just several hundred records this will work fine. However, if you have a a thousand records or more you're better off doing backing up and restoring one database to the other or doing an export in manager where you have both databases.

@Huseyin
I tested the code, and it is throwing an error on myProc() (Expected end of statement)

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/2.asp, line 58
create procedure myProc() as
--------------------------^

I found the link to the Microsoft article yesterday.
It is really kind of worthless since they do not show an actual SP along with the code they supply.

@Scott
At the moment, there are 967 records on the LIVE server.
It will grow as time goes on.
There is also over 200+ non-confirmed members on the site as well, that will hopefully become full members within the next few days or so.
In that case, it will go over 1,200 members +.

I tested the code, and it is throwing an error on myProc() (Expected end of statement)

sp should be created ın SQL usıng SSMS and no "()" needed if you dont have any parameter

create procedure myProc as
begin
  sp_addlinkedserver ...
  exec sp_addlinkedsrvlogin ...
  insert into ...
end;

Select allOpen in new window

OK, I created the stored procedure within SSMS, and it runs.
But, I cannot add the "insert" statement within the stored procedure, as it is calling the server, in this case (of the example I provided): {NAME-THE-CONNECTION}
So, it would need to be called after.
So, how would this be done, and run within the same stored procedure?

you can use dynamic sql execution

declare @sql as nvarchar(max) = 'insert into ... ' + {NAME-THE-CONNECTION} + '.dbo.mytable ...';
EXECUTE sp_executesql @sql;

Select allOpen in new window

see this for more info

sp_executesql (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

I just dont understand this...
are you trying to create db link every time when you run this sp?
why? is someone deleting the link immediately?

create the dblink from ssms, and leave it, and just run the rest of the code in sp's...

you lost me.
What do you mean by:

are you trying to create db link every time when you run this sp?
why? is someone deleting the link immediately?

What I am trying to do is this.

Create the connection, and then execute the insert statement.

How else I am to use this statement. If the connection is not created first? (NAME-THE-CONNECTION)

insert into LocalTable (uid) select uid from [NAME-THE-CONNECTION].[LIVE-DB-NAME].[dbo].[LiveTable]

Select allOpen in new window

I have never used SP, so I am new to it.
Educate me, if there is something that I am missing, please, by all means. Let me know.

I have the SP-DB created in SSMS.
It is there and is ready to use.
Now, I have to do the INSERT statement, and that is what I need to know how to do next. (Which it what I was asking above,  42200951)
The SP DB Link will stay, it will not be messed with again.

Just the INSERT statement now.

I dont see any issue with this sql

insert into LocalTable (uid) select uid from [NAME-THE-CONNECTION].[LIVE-DB-NAME].[dbo].[LiveTable]

Select allOpen in new window

either run this sql from asp
or put it into asp and call the sp

are you asking how to run sql/sp from asp?
I gave a link from ms that shows what to do...

How to call SQL Server stored procedures from ASP
https://support.microsoft.com/en-us/help/164485/how-to-call-sql-server-stored-procedures-from-asp

Right now, I am just trying to test it inside of SSMS.
I have the SP created,

create procedure getList
as
exec sp_addlinkedserver 'NAME-THE-CONNECTION', '', 'SQLNCLI', NULL, NULL, 'Server=IP-ADDRESS-OF-SERVER', NULL
exec sp_addlinkedsrvlogin 'NAME-THE-CONNECTION', 'false', NULL, 'USERNAME', 'PASSWORD'

Select allOpen in new window

Then I have the insert statement, which gives an error about the [NAME-THE-CONNECTION]
Invalid column name '[NAME-THE-CONNECTION]'
(Not even sure if it is written correctly)

declare @sql as nvarchar(max)= 'insert into LocalTable (uid) select uid from [' + NAME-THE-CONNECTION+ '].[LIVE-DB-NAME].[dbo].[LiveTable]'
execute sp_executesql @sql

Select allOpen in new window

it should be

alter procedure getList as
begin
  exec sp_addlinkedserver 'NAME-THE-CONNECTION', '', 'SQLNCLI', NULL, NULL, 'Server=IP-ADDRESS-OF-SERVER', NULL;
  exec sp_addlinkedsrvlogin 'NAME-THE-CONNECTION', 'false', NULL, 'USERNAME', 'PASSWORD';
  declare @sql as nvarchar(max)= 'insert into LocalTable (uid) select uid from [' + NAME-THE-CONNECTION+ '].[LIVE-DB-NAME].[dbo].[LiveTable]'
  execute sp_executesql @sql;
end;

Select allOpen in new window

first you create sp, then alter
or each time you drop and create it...

drop procedure getList;
create procedure getList as
...

Select allOpen in new window

still this does not make sense to me...

why do you need sp for creating dblink?
just create it with ssms and leave it...

then in your sp, put other codes and run it from asp/asp.net

I am NOT recreating it.
Please, read what I wrote.

I have the DB connection done, that SP is already inside of SSMS and ready to use.
I am NOT going to kill it every time.

I am just needing to know how to make the INSERT statement call that SP, and use that connection.
that is the part that I cannot get at this time.

This part, will not run in SSMS.

declare @sql as nvarchar(max)= 'insert into LocalTable (uid) select uid from [' + NAME-THE-CONNECTION+ '].[LIVE-DB-NAME].[dbo].[LiveTable]'
  execute sp_executesql @sql;

Select allOpen in new window

This part, will not run in SSMS.

why? did you try, what happened... looks ok to me...

This is what I get from running that script in SSMS.

Invalid column name 'Connect'.     ----- (Which is,,, NAME-THE-CONNECTION)

Also, the @sql
Is this the name of the SP?

The SP in my case is: getList (create procedure getList)

Either way, if I put the @sql or @getList
I still get the same error, the
Invalid column name 'Connect'

Connect?

maybe you should use [connect]

and dont use any reserved words in object names...

I have used.
connect, Connect, connecting, myConn, and theConn.
I have tried several variations of names, and Nothing worked.

Trust me, when I got that first error the other day, I started trying different names.