Multi table designations in sql string

I'm putting together a resultset in an sql string
(it will be used in asp.net) where I'm drawing from a few different
tables, and I do have a relation with the tblDeal and tblSalesrep ID :
''
strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.salesrep_id WHERE d.Orderdate = '" &
request.querystring("boarddate") & "';"
'''
I am not getting an error, but no response when running this sql string
(i did response.write the "boarddate" variable successfully, so thats
not the problem)
'
.NetSports> strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
> s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
> d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
> FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.salesrep_id WHERE d.Orderdate = '" &
> request.querystring("boarddate") & "';"
If I read your post correctly, you are asking SQL Server to figure out your
boarddate as a parameter to your query.
If I am correct in your interpretation, perhaps your SQL query [VSS script]
(which should be a stored procedure) should look like this:
<sql>
IF EXISTS (
SELECT *
FROM SysObjects
WHERE Type = 'P'
AND Name = 'WhateverYouCallIt '
)
DROP PROCEDURE dbo.WhateverYouCallIt
GO
CREATE PROCEDURE dbo.WhateverYouCallIt (
@.boardDate INT
) AS
SELECT d.salesrep_id,s.fname,s.lname, s.boardtotal_note,
s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross, d.SplitRep_ID,
d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,
b.Amount_Attempted
FROM tblBankTrans b,
tblDeal d
INNER JOIN tblSalesrep s
ON s.ID = d.salesrep_id
WHERE d.Orderdate = @.boardDate
GO
GRANT EXEC ON dbo.WhateverYouCallIt TO PUBLIC
GO
</sql>
Then you call it like this:
<vb.NET>
' Assumes that your connection string is in your Web.config or
App.config
' and that you have an Imports System.Configuration item in your code
class
Dim cnstr As String = ConnectionSettings.AppSetting("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim da As New SqlDataAdapter("dbo.WhateverYouCallIt", cn)
Dim dt As New DataTable()
With da.SelectCommand
.CommandType = CommandType.StoredProcedure
' Assumes that you have a VB variable called boardDate that is the
' same as the stored procedure's (above) @.boardDate parameter:
.Parameters.Add("@.boardDate", boardDate)
End With
da.Fill(dt)
' etc...
</vb.NET>
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||actually, I would probably like to see if the multi-table inner joins
are the correct way to make this sql string work, seeming that I am
also using the tblBankTrans table, but with no joins or relationships.

Multi Server/Database Nightmare

We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.

First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.

Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.

Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.

I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.

Any other reasonable options out there. I just can't imagine my setup is all that unique.

Have you explored the possibility of using Synonyms?

(Even dyanmically creating them when necessary.)

|||Whats a Synonym?

Multi row key updates?

I have been using MS Viso (the one that integrates with Visual Studio .NET)
to generate ddl script for my database. It uses the following update trigger
code to enforce the referential integrity, but will only execute the code fo
r
one row updates, it will throw an error for multi-row updates. I am wonderin
g
why it only allows the one row, as it appears (to me) that the code will wor
k
fine for multi-row updates as well.
UPDATE "ReferedTable"
SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
FROM inserted, deleted, "ReferedTable"
WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
I understand that it is updating the refered key column in the related
tables whenever the primary key column of the parent table is changed, but I
do not understand why it only allows one row at a time to be updated.
Can someone please explain this for me? And can it actually be used for
multi-row updates? If not then what would be a good way of doing it? Thanks--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
DELETE CASCADE methods of a Foreign Key? E.g.:
create table t (
a char(1) primary key,
c char(2) not null
)
create table s (
a char(1) not null references t (a)
on update cascade on delete cascade,
d datetime not null
)
Whenever t.a is changed s.a will reflect the changes and all rows in s.a
will be updated.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQj4zo4echKqOuFEgEQKRgACg3GMmYjF9+Igx
UGwWwRMF3YJL5msAn0Le
4XoP+70vYIkNaJh/nfxGf6Nj
=Wx8M
--END PGP SIGNATURE--
Gary K wrote:
> I have been using MS Viso (the one that integrates with Visual Studio .NET
)
> to generate ddl script for my database. It uses the following update trigg
er
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am wonder
ing
> why it only allows the one row, as it appears (to me) that the code will w
ork
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it? Thanks[/color
]|||"MGFoster" wrote:

> Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
> DELETE CASCADE methods of a Foreign Key? E.g.:
>
Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
NULL/SET DEFAULT options. Also our database requirements specify before/afte
r
auditing which can only be done in INSTEAD OF triggers (due to the nature of
the tables used, which can't be changed, or at least not by me), and using
INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
restrictions.
We have borrowed from the programming structure that Viso produces, in that
while we still create foreign key references they are disabled so we can
implement our own version of referential integrity + auditing.
Personally I would prefer to use another DB package, but unfortunately to
keep things cheap and easily integratable with our MS Office products we are
stuck with SQL Server.
Thanks for the reply MG, but sorry, it's not something we can use.|||Gary K wrote:
> "MGFoster" wrote:
>
> Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
> NULL/SET DEFAULT options. Also our database requirements specify before/af
ter
> auditing which can only be done in INSTEAD OF triggers (due to the nature
of
> the tables used, which can't be changed, or at least not by me), and using
> INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
> restrictions.
> We have borrowed from the programming structure that Viso produces, in tha
t
> while we still create foreign key references they are disabled so we can
> implement our own version of referential integrity + auditing.
> Personally I would prefer to use another DB package, but unfortunately to
> keep things cheap and easily integratable with our MS Office products we a
re
> stuck with SQL Server.
> Thanks for the reply MG, but sorry, it's not something we can use.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
trigger):
"Usually, when an UPDATE statement that references a table attempts to
set the value of a computed, *identity*, or timestamp column, an error
is generated because the values for these columns must be determined by
Microsoft? SQL Server?. These columns must be included in the UPDATE
statement to meet the NOT NULL requirement of the column. However, if
the UPDATE statement references a view with an INSTEAD OF UPDATE
trigger, the logic defined in the trigger can bypass these columns and
avoid the error."
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQj5HZIechKqOuFEgEQJWDACePu5W/oh+PLuf3ysomu6DVtaT6IQAoLBB
y1D1g1dzEYdEoG6vh1+WNsx1
=IYDc
--END PGP SIGNATURE--|||"MGFoster" wrote:

> Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
> trigger):
> "Usually, when an UPDATE statement that references a table attempts to
> set the value of a computed, *identity*, or timestamp column, an error
> is generated because the values for these columns must be determined by
> Microsoft? SQL Server?. These columns must be included in the UPDATE
> statement to meet the NOT NULL requirement of the column. However, if
> the UPDATE statement references a view with an INSTEAD OF UPDATE
> trigger, the logic defined in the trigger can bypass these columns and
> avoid the error."
>
Nope, I try to avoid those like the plague now. We use uniqueidentifiers as
ROWGUIDCOL columns, not only for 'bookmark' uses of such a column, but also
to make replication a lot easier.
The main point of my question was to confirm that the given code would
handle multi-row referential updates on a parent table primary key column
(which i have now done in a practical experiment), and to find out why the M
S
Viso designers would only let one row be updated at a time with the code.
I have confirmed that the code will perform multi-row updates and it appears
to work correctly, but as the famous quotes says, "Just because we can do
something, does it mean we SHOULD?" I am basically looking for any problems
that might arise from the use of the code.|||I should have included this in the last message, but here is the code I used
to test the multi-row update code.
use tempdb
go
-- these table testers are only so I can reuse the code if it needed any
changes (it did)
if objectproperty(object_id('tblb'), 'IsTable')=1
drop table tblb
go
if objectproperty(object_id('tbla'), 'IsTable')=1
drop table tbla
go
create table tbla ( -- our parent table in the relationship
-- (i couldn't be bothered typing in GUIDs so we use a tinyint pk)
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL
)
go
create table tblb ( -- our child table in the relationship
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL,
ia tinyint NOT NULL,
-- and this is our relationship contraint
CONSTRAINT FK_b FOREIGN KEY (ia) REFERENCES tbla (ii)
)
go
-- we are going to look after the integrity, so we disable the constraint
alter table tblb nocheck constraint FK_b
go
create trigger tbla_upd on tbla for update as
begin
-- our test trigger is only for the update condition, and since we control
what is
-- going to happen we can skip all the extra code we will be using.
-- this code will only be executed (for every child table) when the primary
key is updated
update tblb
set tblb.ia = inserted.ii
from inserted, deleted, tblb
where tblb.ia = deleted.ii
end
go
-- insert parent table values
insert tbla values (1, 'first')
insert tbla values (2, 'second')
insert tbla values (3, 'third')
-- insert child table values
insert tblb values (1, 'first/first', 1)
insert tblb values (2, 'first/second', 2)
insert tblb values (3, 'first/third', 3)
insert tblb values (4, 'second/third', 3)
insert tblb values (5, 'second/second', 2)
insert tblb values (6, 'third/second', 2)
insert tblb values (7, 'second/first', 1)
insert tblb values (8, 'third/third', 3)
go
-- what it looks like before we change things
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'second' series to a new unique value
update tbla set ii=4 where ii=2
go
-- and we see what we get (works ok!)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'third' series to a value that is already in
use
-- (error testing, and yes it does throw an error as it is supposed to)
update tbla set ii=4 where ii=3
go
-- and then we see what we get after the change (which doesn't happen)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go|||Gary,
The code looks wrong to me, but even so, it can't be
fixed unless there is another candidate key on the table.
First off, there is no join condition between inserted and
either of the other two tables. If 10 rows are updated, which
of the 10 inserted.PrimaryKey values will be assigned to
ReferedTable.ReferedKey? The way this proprietary
SQL Server syntax works, an arbitrary one of the 10
possibilities will be used.
But the problem is worse than that. Because the primary
key is being updated, there is no way to identify the correct
correspondence between an old row and a new row.
Suppose the update was this:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 123
when 2 then 456
end
where PrimaryKey in (1,2)
Within the trigger, there is no way to distinguish
this update from a different one:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 456
when 2 then 123
end
where PrimaryKey in (1,2)
At least not without it being possible to identify which
row is which on the basis of some column or columns
other than the PrimaryKey column.
If only one row is updated, the is no ambiguity.
Steve Kass
Drew University
Gary K wrote:

>I have been using MS Viso (the one that integrates with Visual Studio .NET)
>to generate ddl script for my database. It uses the following update trigge
r
>code to enforce the referential integrity, but will only execute the code f
or
>one row updates, it will throw an error for multi-row updates. I am wonderi
ng
>why it only allows the one row, as it appears (to me) that the code will wo
rk
>fine for multi-row updates as well.
>UPDATE "ReferedTable"
>SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
>FROM inserted, deleted, "ReferedTable"
>WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
>I understand that it is updating the refered key column in the related
>tables whenever the primary key column of the parent table is changed, but
I
>do not understand why it only allows one row at a time to be updated.
>Can someone please explain this for me? And can it actually be used for
>multi-row updates? If not then what would be a good way of doing it? Thanks
>|||Gary,
Check Itzik Ben-Gan's presentation & scripts on RI in SQL 7.0 and 2000 at
http://www.sql.co.il/ug/13/Thirteenth.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gary K" <GaryK@.discussions.microsoft.com> wrote in message
news:D3D0B4F4-EC82-41E6-A527-E6202A5394F7@.microsoft.com...
> I have been using MS Viso (the one that integrates with Visual Studio
.NET)
> to generate ddl script for my database. It uses the following update
trigger
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am
wondering
> why it only allows the one row, as it appears (to me) that the code will
work
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it?
Thanks

MSSQLSERVER Service ... just gone

I just attempted to log into an application that uses
SQL. I received the error "Net Service
Configuration" ... "The specified service does not exist
as an installed service."
I'm shocked. Why would this service just disappear?It wouldn't. To ensure it hasn't logon locally to the SQL Server, control
panel, services, MSSQLServer.
I would imagine the error message relates to a different service.
--
HTH
Ryan Waight, MCDBA, MCSE
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4701c3b2ff$a8c87560$a101280a@.phx.gbl...
> I just attempted to log into an application that uses
> SQL. I received the error "Net Service
> Configuration" ... "The specified service does not exist
> as an installed service."
> I'm shocked. Why would this service just disappear?|||It wouldn't. Check the <SQL> dir for stuff in the LOG and INSTALL directories
for evidence on what might have happened.
> I just attempted to log into an application that uses
> SQL. I received the error "Net Service
> Configuration" ... "The specified service does not exist
> as an installed service."
> I'm shocked. Why would this service just disappear?
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

MSSQLSERVER and SQLExpress coexist?

i had sqlserver2000 (including the enterprise manager and desktop engine) and .net 2003 installed in my development machine, I developed a vb.net application that communicates with a locally hosted mssql db.

recently i had .net 2005 and MS Server 2005 installed, then I converted my application in .net 2005, after, I started having problem deploying the application and db together to client machine and even on my own development machine, it's all database communication sort of problems. (now i have SQL Server Management Studio Express CTP installed)

Did i make any mistake? should i uninstall sqlserver2000, .net 2003 before installing server2005 and .net 2005?

btw, in my explorer, C:\Program Files\Microsoft SQL Server, i have 5 subfolders: "80", "90", "MSSQL", "MSSQL.1" and "MSSQL$SETUPTESTAPPINS". i know that 80 and MSSQL are the folders for server2000, 90 and MSSQL.1 are probably server2005 folders. but what is "MSSQL$SETUPTESTAPPINS"? from the readme.txt i found it's related to server2000, but what is it about?

also, in my registry, HKEY_Local_Machine/Software/Microsoft/Microsoft SQL Server/, the value of "InstalledInstances" is MSSQL SETUP TESTAPPINS SQLEXPRESS, after i removed SQLServer2000 stuff, the key value became "SETUP TESTAPPINS SQLEXPRESS", Now i dont have server2000 in my system anymore, how come SETUP TESTAPPIN is still there?

sorry for my bad explanation, plz help

This question might be better answered in the Setup forum, but from what I can gather from your statements, I don't think you did anything wrong. SQL Server 2000 and SQL Server 2005 will coexist on a single machine, as will the .NET versions. That may be where your issue is.

If you can post the specific errors you're seeing, that might a better place to start.

Buck Woody

|||

Hi,

Yes SQL 2000 and SQL Express / 2005 can coexists, i have MSDE and SQL Express installed on my system, it works without any problem.

Can you post error message from Windows Event Viewer and SQL Server Error Log

Hemantgiri S. Goswami

MSSQLSERVER and SQLExpress coexist?

i had sqlserver2000 (including the enterprise manager and desktop engine) and .net 2003 installed in my development machine, I developed a vb.net application that communicates with a locally hosted mssql db.

recently i had .net 2005 and MS Server 2005 installed, then I converted my application in .net 2005, after, I started having problem deploying the application and db together to client machine and even on my own development machine, it's all database communication sort of problems. (now i have SQL Server Management Studio Express CTP installed)

Did i make any mistake? should i uninstall sqlserver2000, .net 2003 before installing server2005 and .net 2005?

btw, in my explorer, C:\Program Files\Microsoft SQL Server, i have 5 subfolders: "80", "90", "MSSQL", "MSSQL.1" and "MSSQL$SETUPTESTAPPINS". i know that 80 and MSSQL are the folders for server2000, 90 and MSSQL.1 are probably server2005 folders. but what is "MSSQL$SETUPTESTAPPINS"? from the readme.txt i found it's related to server2000, but what is it about?

also, in my registry, HKEY_Local_Machine/Software/Microsoft/Microsoft SQL Server/, the value of "InstalledInstances" is MSSQL SETUP TESTAPPINS SQLEXPRESS, after i removed SQLServer2000 stuff, the key value became "SETUP TESTAPPINS SQLEXPRESS", Now i dont have server2000 in my system anymore, how come SETUP TESTAPPIN is still there?

sorry for my bad explanation, plz help

This question might be better answered in the Setup forum, but from what I can gather from your statements, I don't think you did anything wrong. SQL Server 2000 and SQL Server 2005 will coexist on a single machine, as will the .NET versions. That may be where your issue is.

If you can post the specific errors you're seeing, that might a better place to start.

Buck Woody

|||

Hi,

Yes SQL 2000 and SQL Express / 2005 can coexists, i have MSDE and SQL Express installed on my system, it works without any problem.

Can you post error message from Windows Event Viewer and SQL Server Error Log

Hemantgiri S. Goswami

MSSQL v7 sp4 Timesync

can anyone tell me if I can sync my server to a time server on the net while running this version of MSSQL? I am also running InSQL v 7.1 SP8 on the same machine. Win2K Server SP4.

Thanks

Gary

forgot to mention that the time zone has to be set to casablanca monrovia, is there any time servers out there with that zone?