Link to Different SQL Database

I had connect another SQL server VIA VPN , the server name is IP e,g
123.123.123.123
I can read the data sucessfully,
However, I want to insert some data from my local server.
sp_addlinked server '123.123.123.13'
sp_linkedserver (I can see it)
then select * from [123.123.123.123].database.dbo.myTable
I got an error said 'SQL Server does not exist or access denied'
Does any one can help , ThanksHi
You example shows two different IP addresses!!
John
"Agnes" wrote:

> I had connect another SQL server VIA VPN , the server name is IP e,g
> 123.123.123.123
> I can read the data sucessfully,
> However, I want to insert some data from my local server.
> sp_addlinked server '123.123.123.13'
> sp_linkedserver (I can see it)
> then select * from [123.123.123.123].database.dbo.myTable
> I got an error said 'SQL Server does not exist or access denied'
> Does any one can help , Thanks
>
>|||Sorry, the IP in my example should be the same
"John Bell" <jbellnewsposts@.h0tmail.com> glsD:7C2DF328-79A7-4836-912F-3A2CB6D803C
3@.microsoft.com...
> Hi
> You example shows two different IP addresses!!
> John
> "Agnes" wrote:
>|||Hi!
After adding the linked server you should tell SQL server which user on
local server may access remote server with system procedure
sp_addlinkedsrvlogin (see on BOL for details).
One useful idea is to add a name to the linked server in spite of the IP
address.
Hope that helps.
Joo Arajo
"Agnes" <agnes@.dynamictech.com.hk> escreveu na mensagem
news:OU2n3EvVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>I had connect another SQL server VIA VPN , the server name is IP e,g
> 123.123.123.123
> I can read the data sucessfully,
> However, I want to insert some data from my local server.
> sp_addlinked server '123.123.123.13'
> sp_linkedserver (I can see it)
> then select * from [123.123.123.123].database.dbo.myTable
> I got an error said 'SQL Server does not exist or access denied'
> Does any one can help , Thanks
>|||I had process sp_addlinkedserverlogin , but still got error.
BTw, how can i alias the linked server name ?
THanks
"Joo Arajo" <joaoribeiroaraujo_DOMINIOIOL_@.nospam.PT> glsD:OF7DQ8zVFHA.3704@.TK2
MSFTNGP14.phx.gbl...
> Hi!
> After adding the linked server you should tell SQL server which user on
> local server may access remote server with system procedure
> sp_addlinkedsrvlogin (see on BOL for details).
> One useful idea is to add a name to the linked server in spite of the IP
> address.
> Hope that helps.
> Joo Arajo
> "Agnes" <agnes@.dynamictech.com.hk> escreveu na mensagem
> news:OU2n3EvVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Try something like:
EXEC sp_addlinkedserver @.server=3D'Remote_Svr', @.srvproduct=3D'',
@.provider=3D'SQLOLEDB',
@.datasrc=3D'123.123.123.123'
You can then use Remove_Svr as the name in your query. It sounds like
the login you are using does not have the correct permissions. You may
want to make sure that DTC is running on the server (although I would
expect a different message). You could use profiler on the remote
server to see what is happening.
John
Agnes wrote:
> I had process sp_addlinkedserverlogin , but still got error.
> BTw, how can i alias the linked server name ?
> THanks
> "Jo=E3o Ara=FAjo" <joaoribeiroaraujo_DOMINIOIOL_@.nospam.PT>
=BC=B6=BCg=A9=F3=B6l=A5=F3=B7s=BBD:OF7DQ
8zVFHA.3704@.TK2MSFTNGP14.phx.gbl...
user on
the IP
e,g|||Hi
Why should u put the IP Address in SELECT Statement , u can use the link
server Name '
U can also use the OPENQUERY for this as
SELECT * FROM OPENQUERY('SQL STATAEMENT')
Renjith
"Agnes" wrote:

> I had connect another SQL server VIA VPN , the server name is IP e,g
> 123.123.123.123
> I can read the data sucessfully,
> However, I want to insert some data from my local server.
> sp_addlinked server '123.123.123.13'
> sp_linkedserver (I can see it)
> then select * from [123.123.123.123].database.dbo.myTable
> I got an error said 'SQL Server does not exist or access denied'
> Does any one can help , Thanks
>
>sql

Line break after group

I need to insert a line break in a detail row after certains groups of data.
example:
Week Day Detail
1 Mon 1
1 Tue 1
1 Wed 2
1 Thu 2
1 Fri 1
2 Mon 1
2 Tue 1
2 Wed 2
2 Thu 2
2 Fri 1
I need it to have a line break after week 1, 2, etc. Also, the detail column
has aggregates, runningvalues etc. There can't be a runningvalue on the line
break. I want the data to look like this:
Week Day Detail
1 Mon 1
1 Tue 1
1 Wed 2
1 Thu 2
1 Fri 1
2 Mon 1
2 Tue 1
2 Wed 2
2 Thu 2
2 Fri 1I would create a group on Week with no group header but a single blank row
group footer. This should be your innermost group. Outer groups will
calculate the aggregates properly. You'll need to scope any running values
to outer groups.
"Dutch" wrote:
> I need to insert a line break in a detail row after certains groups of data.
> example:
> Week Day Detail
> 1 Mon 1
> 1 Tue 1
> 1 Wed 2
> 1 Thu 2
> 1 Fri 1
> 2 Mon 1
> 2 Tue 1
> 2 Wed 2
> 2 Thu 2
> 2 Fri 1
> I need it to have a line break after week 1, 2, etc. Also, the detail column
> has aggregates, runningvalues etc. There can't be a runningvalue on the line
> break. I want the data to look like this:
> Week Day Detail
> 1 Mon 1
> 1 Tue 1
> 1 Wed 2
> 1 Thu 2
> 1 Fri 1
> 2 Mon 1
> 2 Tue 1
> 2 Wed 2
> 2 Thu 2
> 2 Fri 1

Line 1: Incorrect syntax near 're'.

Hi Guys,
This is my Sql Query to insert values. All the fields are nvarchar
except the postcode which is Int. I have a condition if the postcode
field is numeric then insert values otherwise error message. Only one
of the customer gets the message on his browser
"Line 1: Incorrect syntax near 're'.
/westernsydney/register.asp, line 198 "
But other registrations are still going through with this code. Can
someone tell me why this is happening
strSql5 = "INSERT INTO tblattendeem
(attendee_firstname,attendee_surname,att
endee_location_street1,attendee_loca
tion_suburb,attendee_location_postcode,a
ttendee_company_name,attendee_phone,
attendee_email,event_id)"_
& "VALUES
('"&Request.QueryString("firstname")&"','"&Request.QueryString("surname")&"'
,"_
&"
'"&Request.QueryString("address")&"','"&Request.QueryString("suburb")&"',"_
&" '"&Request.QueryString("postcode")&"',
'"&Request.QueryString("company")&"',"_
&" '"&Request.QueryString("phone_number")&"',
'"&Request.QueryString("email")&"',"_
&" '"&Request.Cookies("event_id")&"' )"
Many ThanksHey Nachi, without looking at your sample data, it's difficult to tell
what's going on. My guess is that you have a customer with either a
quote (") or an apostrophe (') in the column.
There are other issues that you be aware of, however; building a string
like you are doing increases the chances that someone will be able to
inject another SQL statement into your code, thus doing damange to your
application. See http://aspalliance.com/385 for an example.
Also, do any of your postal codes start with a 0? If so, you may want
reconsider using an integer to store the data.
HTH,
Stu|||Hi Stu,
Thank you so much for your time & Quick reply. This Groups is really
useful. Im waiting on the Data from this specific customer. thank you
for your suggestion as well on Sql Injection. I will impl this in
future.
K Regards,
Nachi
Stu wrote:
> Hey Nachi, without looking at your sample data, it's difficult to tell
> what's going on. My guess is that you have a customer with either a
> quote (") or an apostrophe (') in the column.
> There are other issues that you be aware of, however; building a string
> like you are doing increases the chances that someone will be able to
> inject another SQL statement into your code, thus doing damange to your
> application. See http://aspalliance.com/385 for an example.
> Also, do any of your postal codes start with a 0? If so, you may want
> reconsider using an integer to store the data.
> HTH,
> Stu|||To emphasize Stu's point, there are hackers who do nothing all day but
search for web sites to exploit SQL injection vulnerabilities. If your site
is exposed to the public internet, be sure to heed Stu's advice use only
parameterized SQL queries. I hope that 'in the future' means now.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nachi" <Rengenath@.gmail.com> wrote in message
news:1148612435.121757.148630@.j33g2000cwa.googlegroups.com...
> Hi Stu,
> Thank you so much for your time & Quick reply. This Groups is really
> useful. Im waiting on the Data from this specific customer. thank you
> for your suggestion as well on Sql Injection. I will impl this in
> future.
> K Regards,
> Nachi
> Stu wrote:
>

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
TascienYour specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegroups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
--
David Gugick
Quest Software
www.quest.com|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
--
David Gugick
Quest Software
www.quest.com

Limiting the amount of data used between two tables

Two tables: People and TestValues
People
--
Name varchar(50)
TestValues
--
TestString varchar(10)
Insert into People (Name) values ('William')
Insert into People (Name) values ('Blake')
Insert into People (Name) values ('Kevin')
Insert into People (Name) values ('Merry')
Insert into People (Name) values ('Gary')
Insert into People (Name) values ('Tamara')
Insert into People (Name) values ('Rich')
insert into TestValues (testString) values ('A')
insert into TestValues (testString) values ('M')
insert into TestValues (testString) values ('K')
insert into TestValues (testString) values ('X')
insert into TestValues (testString) values ('Z')
The goal is to have each table filter the other, so that we only return
those items that they have in common. So:
'William' has an "A", so WIlliam and A both are included (there is also
an M, but A is a match so M is not necessary)
"Blake" has an "A", so both are included
"Kevin" has a "K"
"Merry" matches "M"
"Gary" matches "A"
"Rich" doesn't match anything, and isn't included
"X" and "Z" don't match, and aren't included
I would rather not create two cursors and two temp tables if there is a
set based way to do this. Thanks!
- Wm in Kansas Cityselect p.name, min(t.teststring) as teststring
from people p
join testvalues t on p.name like '%'+t.teststring+'%'
group by p.name
this probably won't perform very well, especially if the amount of data
gets large.
seamlyne@.hotmail.com wrote:
> Two tables: People and TestValues
> People
> --
> Name varchar(50)
>
> TestValues
> --
> TestString varchar(10)
>
> Insert into People (Name) values ('William')
> Insert into People (Name) values ('Blake')
> Insert into People (Name) values ('Kevin')
> Insert into People (Name) values ('Merry')
> Insert into People (Name) values ('Gary')
> Insert into People (Name) values ('Tamara')
> Insert into People (Name) values ('Rich')
>
> insert into TestValues (testString) values ('A')
> insert into TestValues (testString) values ('M')
> insert into TestValues (testString) values ('K')
> insert into TestValues (testString) values ('X')
> insert into TestValues (testString) values ('Z')
> The goal is to have each table filter the other, so that we only return
> those items that they have in common. So:
> 'William' has an "A", so WIlliam and A both are included (there is also
> an M, but A is a match so M is not necessary)
> "Blake" has an "A", so both are included
> "Kevin" has a "K"
> "Merry" matches "M"
> "Gary" matches "A"
> "Rich" doesn't match anything, and isn't included
> "X" and "Z" don't match, and aren't included
> I would rather not create two cursors and two temp tables if there is a
> set based way to do this. Thanks!
> - Wm in Kansas City
>|||select * from #people join #TestValues on charindex(testString, Name)>0

Limitations of SQL Server Accelerator for BI

We are using BI Accelerator and we faced the problem that it is not possible
to insert new rows. We have some shared dimensions and when we define cubes
we also have to add list of dimensions. Very quickly we fill in the whole
space of the worksheet. The worksheet is protected and basically we do not
have any ability to have the whole Warehouse definition on one workbook. How
could we overcome this problem ?
There is not an easy solution to this.
In the latest version, there is a provision of controlling and invoking the
system via an XML file.
However, you will have to maintain the XML file yourself with your own XML
editor. Look in the 1.2 release notes and additional documentation.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"ksp" <ksp@.discussions.microsoft.com> wrote in message
news:FBB639F4-F70C-4756-AE17-2060DB3FC1C1@.microsoft.com...
> We are using BI Accelerator and we faced the problem that it is not
possible
> to insert new rows. We have some shared dimensions and when we define
cubes
> we also have to add list of dimensions. Very quickly we fill in the whole
> space of the worksheet. The worksheet is protected and basically we do not
> have any ability to have the whole Warehouse definition on one workbook.
How
> could we overcome this problem ?

Limitation of 1023 Characters

I am using ASP to query a SQL Server 2000 database.
Whenever I try to insert large pieces of data (column is
declared as varchar), the data allowed is limited to 1023
characters. I have installed SP3, and still the same
error. It is not down to any syntax in the cell as trying
to insert 1024 instances of "b" (bbbbb etc. etc.) will
error on the insertion of the 1024th. I am sure this must
be a configuration conflict somewhere.
Anybody out there able to help me rectify this?
Many thanks
Martin Burford> Whenever I try to insert large pieces of data (column is
> declared as varchar),
VARCHAR(what?)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Sorry. Varchar(4000) in this case.
Martin
>--Original Message--
>> Whenever I try to insert large pieces of data (column
is
>> declared as varchar),
>VARCHAR(what?)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||All right, so can you show the statement that ASP is trying to run (e.g. if
you are using conn.execute sqlStatement, use response.write sqlStatement
instead, and show us the result).
Also, the exact error message might be helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> Sorry. Varchar(4000) in this case.
> Martin
> >--Original Message--
> >> Whenever I try to insert large pieces of data (column
> is
> >> declared as varchar),
> >
> >VARCHAR(what?)
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >.
> >|||I think I have actually determined the error. I was
entering my data through Enterprise Manager, which I'm
guessing can't accept more than a byte of info (1,024
chars a bit of a coincedence). Entering the data through
INSERT statements in Query Analyzer works upto 4000 chars
which is fine. This however causes a slight problem...is
there an easier way rather than creating custom insert
statements for each record? Otherwise, I'll just create a
basic admin area interface, and insert the code through
submitted form values (textareas).
Many thanks
Martin Burford
>--Original Message--
>All right, so can you show the statement that ASP is
trying to run (e.g. if
>you are using conn.execute sqlStatement, use
response.write sqlStatement
>instead, and show us the result).
>Also, the exact error message might be helpful.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
>> Sorry. Varchar(4000) in this case.
>> Martin
>> >--Original Message--
>> >> Whenever I try to insert large pieces of data
(column
>> is
>> >> declared as varchar),
>> >
>> >VARCHAR(what?)
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >.
>> >
>
>.
>|||> I think I have actually determined the error. I was
> entering my data through Enterprise Manager,
Don't do that! Enterprise Manager should be used for management, not data
entry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||http://www.aspfaq.com/search.asp?q=enterprise+manager+1023+characters
:-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Martin Burford" <martin_burford@.hotmail.com> wrote in message
news:065101c3c314$97ee3980$a401280a@.phx.gbl...
> I think I have actually determined the error. I was
> entering my data through Enterprise Manager, which I'm
> guessing can't accept more than a byte of info (1,024
> chars a bit of a coincedence). Entering the data through
> INSERT statements in Query Analyzer works upto 4000 chars
> which is fine. This however causes a slight problem...is
> there an easier way rather than creating custom insert
> statements for each record? Otherwise, I'll just create a
> basic admin area interface, and insert the code through
> submitted form values (textareas).
> Many thanks
> Martin Burford
> >--Original Message--
> >All right, so can you show the statement that ASP is
> trying to run (e.g. if
> >you are using conn.execute sqlStatement, use
> response.write sqlStatement
> >instead, and show us the result).
> >
> >Also, the exact error message might be helpful.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> >> Sorry. Varchar(4000) in this case.
> >>
> >> Martin
> >>
> >> >--Original Message--
> >> >> Whenever I try to insert large pieces of data
> (column
> >> is
> >> >> declared as varchar),
> >> >
> >> >VARCHAR(what?)
> >> >
> >> >--
> >> >Aaron Bertrand
> >> >SQL Server MVP
> >> >http://www.aspfaq.com/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >