One thing I've noticed about the SQL Server Management Studio is that while
it allows you to create an alias for the server in the Registered Servers
window. That alias name is not propagated to the Object Browser. It seems
like it should be.
Michael Otey
Please ask this in the private SQL2005 group.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Otey" <mikeo@.teca.com> wrote in message
news:#9xJG2abEHA.3716@.TK2MSFTNGP11.phx.gbl...
> One thing I've noticed about the SQL Server Management Studio is that
while
> it allows you to create an alias for the server in the Registered Servers
> window. That alias name is not propagated to the Object Browser. It seems
> like it should be.
> Michael Otey
>
|||Oops missed. Sorry about that.
"Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
news:u6T5IEebEHA.2340@.TK2MSFTNGP10.phx.gbl...
> Please ask this in the private SQL2005 group.
> --
> -Euan
> Please reply only to the newsgroup so that others can benefit. When
posting,
> please state the version of SQL Server being used and the error
number/exact
> error message text received, if any.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:#9xJG2abEHA.3716@.TK2MSFTNGP11.phx.gbl...
> while
Servers[vbcol=seagreen]
seems
>
Friday, March 30, 2012
IWAM_ and login failure
I've been scratching me head for 3 weeks so far on this one.
I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
friend made a database for me to play around with. He created this on a
Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
This machine is an internal LAN.
Now, when I try to access this page, I repeatedly get a
"System.Data.SqlClient.SqlException: Login failed for user
'MAINSERVER1\IWAM_MAINSERVER'."
I've been googling the hell out of this, and tried various NG's. Nothing has
worked as of yet.
Things I've tried:
I've added the user to the database permissions (as dbo).
I've made sure that the login is listed under 'Security/Logins'.
I added the user to the NTFS permissions for the folder, both where the DB
is stored plus the intranet folder where the pages are housed.
I changed the logon for 'ASP.NET State Service' from the IWAM account, to
Local System Account. I have noticed, that I can not manually start the
service when the logon is set to IWAM (I think this is part of the problem).
I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
I added IUSR_ and NT Authority\System to the 'Security/Logins' and verified
permissions for the DB for the same.
I'm at a loss as to what to try next. Any ideas?
TIA
CharlesCharles
Are you using windows only or mixed security? (If you don't know, open Enter
prise Manage, right click on your server and choose properties. The security
tab will show you)
For windows only
Right click My computer on your desktop and choose manage
Open local users and groups
Add user to appropriate group (usually Administrator or Users depending on w
hat you are going to use account for)
For Windows and mixed
In EM open Security tab and click on Logins
Right click and add new login
Give account details including domain
If you want full access to everything go to Server Roles tab and tick system
s administrator, else tick any other role that is suitable
If you want to contol access at individual level or you have not given sa ac
cess go to database access tab and assign database access
If not sa (systems administrator)
Go to individual databases and choose users
Right click and choose new user
Account should be available from drop down menu
Choose any roles in addition to public that is required
If dbo (db_owner) nothing else required
Else click on properties
Choose role just assigned and click permissions
Assign per,issions at an individual level
All should be done now. Full database access.
Hope this helps
John|||IF the error occurs because the web page is accessing sql for user data ( as
opposed to using SQL for IIS State), then you should ONLY have to
1. Add the IWAM login as a valid login to SQL Server
2. Add the login as a valid user in the database you are using
3. Give the user in the database the appropriate permissions...
A quick simple check would be to simply add the login and make it a system
administrator In SQL Server. then try the app again..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I've been scratching me head for 3 weeks so far on this one.
> I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
> friend made a database for me to play around with. He created this on a
> Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
> This machine is an internal LAN.
> Now, when I try to access this page, I repeatedly get a
> "System.Data.SqlClient.SqlException: Login failed for user
> 'MAINSERVER1\IWAM_MAINSERVER'."
>
> I've been googling the hell out of this, and tried various NG's. Nothing
has
> worked as of yet.
> Things I've tried:
> I've added the user to the database permissions (as dbo).
> I've made sure that the login is listed under 'Security/Logins'.
> I added the user to the NTFS permissions for the folder, both where the DB
> is stored plus the intranet folder where the pages are housed.
> I changed the logon for 'ASP.NET State Service' from the IWAM account, to
> Local System Account. I have noticed, that I can not manually start the
> service when the logon is set to IWAM (I think this is part of the
problem).
> I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
> I added IUSR_ and NT Authority\System to the 'Security/Logins' and
verified
> permissions for the DB for the same.
> I'm at a loss as to what to try next. Any ideas?
> TIA
> Charles
>|||I am using Mixed Security. The login ID is already there, and I included
IUSR_. I went through, and added the server role of SA for both logins. I
performed a net stop/start for mssql, still the same problem.
Again, I rechecked my permission settings in EM, on the DB file itself, on
the 'Data' folder for MSSQL, and the folder/files inside IIS. I have the
permissions set to 'Full Control' on the files/folders.
It's confusing as hell to me what the problem could be.
Charles
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:95E7DA3A-A561-488B-A0F9-A5FBB25DA2AF@.microsoft.com...
> Charles
> Are you using windows only or mixed security? (If you don't know, open
Enterprise Manage, right click on your server and choose properties. The
security tab will show you)
> For windows only
> Right click My computer on your desktop and choose manage
> Open local users and groups
> Add user to appropriate group (usually Administrator or Users depending on
what you are going to use account for)
> For Windows and mixed
> In EM open Security tab and click on Logins
> Right click and add new login
> Give account details including domain
> If you want full access to everything go to Server Roles tab and tick
systems administrator, else tick any other role that is suitable
> If you want to contol access at individual level or you have not given sa
access go to database access tab and assign database access
> If not sa (systems administrator)
> Go to individual databases and choose users
> Right click and choose new user
> Account should be available from drop down menu
> Choose any roles in addition to public that is required
> If dbo (db_owner) nothing else required
> Else click on properties
> Choose role just assigned and click permissions
> Assign per,issions at an individual level
> All should be done now. Full database access.
> Hope this helps
> John
>|||Response inline:
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23Fq5FUDDEHA.3344@.tk2msftngp13.phx.gbl...
> IF the error occurs because the web page is accessing sql for user data
( as
> opposed to using SQL for IIS State), then you should ONLY have to
> 1. Add the IWAM login as a valid login to SQL Server
--Are you referring to the 'Start and Run SQL Server in the following
account'? That uses the Administrator login.
> 2. Add the login as a valid user in the database you are using
--The ID is already added to the DB as a user and SA.
> 3. Give the user in the database the appropriate permissions...
--Already done.
> A quick simple check would be to simply add the login and make it a system
> administrator In SQL Server. then try the app again..
--Still have the same error.
Charles
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
> news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
installed.
> has
DB
to
> problem).
> verified
>
I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
friend made a database for me to play around with. He created this on a
Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
This machine is an internal LAN.
Now, when I try to access this page, I repeatedly get a
"System.Data.SqlClient.SqlException: Login failed for user
'MAINSERVER1\IWAM_MAINSERVER'."
I've been googling the hell out of this, and tried various NG's. Nothing has
worked as of yet.
Things I've tried:
I've added the user to the database permissions (as dbo).
I've made sure that the login is listed under 'Security/Logins'.
I added the user to the NTFS permissions for the folder, both where the DB
is stored plus the intranet folder where the pages are housed.
I changed the logon for 'ASP.NET State Service' from the IWAM account, to
Local System Account. I have noticed, that I can not manually start the
service when the logon is set to IWAM (I think this is part of the problem).
I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
I added IUSR_ and NT Authority\System to the 'Security/Logins' and verified
permissions for the DB for the same.
I'm at a loss as to what to try next. Any ideas?
TIA
CharlesCharles
Are you using windows only or mixed security? (If you don't know, open Enter
prise Manage, right click on your server and choose properties. The security
tab will show you)
For windows only
Right click My computer on your desktop and choose manage
Open local users and groups
Add user to appropriate group (usually Administrator or Users depending on w
hat you are going to use account for)
For Windows and mixed
In EM open Security tab and click on Logins
Right click and add new login
Give account details including domain
If you want full access to everything go to Server Roles tab and tick system
s administrator, else tick any other role that is suitable
If you want to contol access at individual level or you have not given sa ac
cess go to database access tab and assign database access
If not sa (systems administrator)
Go to individual databases and choose users
Right click and choose new user
Account should be available from drop down menu
Choose any roles in addition to public that is required
If dbo (db_owner) nothing else required
Else click on properties
Choose role just assigned and click permissions
Assign per,issions at an individual level
All should be done now. Full database access.
Hope this helps
John|||IF the error occurs because the web page is accessing sql for user data ( as
opposed to using SQL for IIS State), then you should ONLY have to
1. Add the IWAM login as a valid login to SQL Server
2. Add the login as a valid user in the database you are using
3. Give the user in the database the appropriate permissions...
A quick simple check would be to simply add the login and make it a system
administrator In SQL Server. then try the app again..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I've been scratching me head for 3 weeks so far on this one.
> I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
> friend made a database for me to play around with. He created this on a
> Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
> This machine is an internal LAN.
> Now, when I try to access this page, I repeatedly get a
> "System.Data.SqlClient.SqlException: Login failed for user
> 'MAINSERVER1\IWAM_MAINSERVER'."
>
> I've been googling the hell out of this, and tried various NG's. Nothing
has
> worked as of yet.
> Things I've tried:
> I've added the user to the database permissions (as dbo).
> I've made sure that the login is listed under 'Security/Logins'.
> I added the user to the NTFS permissions for the folder, both where the DB
> is stored plus the intranet folder where the pages are housed.
> I changed the logon for 'ASP.NET State Service' from the IWAM account, to
> Local System Account. I have noticed, that I can not manually start the
> service when the logon is set to IWAM (I think this is part of the
problem).
> I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
> I added IUSR_ and NT Authority\System to the 'Security/Logins' and
verified
> permissions for the DB for the same.
> I'm at a loss as to what to try next. Any ideas?
> TIA
> Charles
>|||I am using Mixed Security. The login ID is already there, and I included
IUSR_. I went through, and added the server role of SA for both logins. I
performed a net stop/start for mssql, still the same problem.
Again, I rechecked my permission settings in EM, on the DB file itself, on
the 'Data' folder for MSSQL, and the folder/files inside IIS. I have the
permissions set to 'Full Control' on the files/folders.
It's confusing as hell to me what the problem could be.
Charles
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:95E7DA3A-A561-488B-A0F9-A5FBB25DA2AF@.microsoft.com...
> Charles
> Are you using windows only or mixed security? (If you don't know, open
Enterprise Manage, right click on your server and choose properties. The
security tab will show you)
> For windows only
> Right click My computer on your desktop and choose manage
> Open local users and groups
> Add user to appropriate group (usually Administrator or Users depending on
what you are going to use account for)
> For Windows and mixed
> In EM open Security tab and click on Logins
> Right click and add new login
> Give account details including domain
> If you want full access to everything go to Server Roles tab and tick
systems administrator, else tick any other role that is suitable
> If you want to contol access at individual level or you have not given sa
access go to database access tab and assign database access
> If not sa (systems administrator)
> Go to individual databases and choose users
> Right click and choose new user
> Account should be available from drop down menu
> Choose any roles in addition to public that is required
> If dbo (db_owner) nothing else required
> Else click on properties
> Choose role just assigned and click permissions
> Assign per,issions at an individual level
> All should be done now. Full database access.
> Hope this helps
> John
>|||Response inline:
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23Fq5FUDDEHA.3344@.tk2msftngp13.phx.gbl...
> IF the error occurs because the web page is accessing sql for user data
( as
> opposed to using SQL for IIS State), then you should ONLY have to
> 1. Add the IWAM login as a valid login to SQL Server
--Are you referring to the 'Start and Run SQL Server in the following
account'? That uses the Administrator login.
> 2. Add the login as a valid user in the database you are using
--The ID is already added to the DB as a user and SA.
> 3. Give the user in the database the appropriate permissions...
--Already done.
> A quick simple check would be to simply add the login and make it a system
> administrator In SQL Server. then try the app again..
--Still have the same error.
Charles
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
> news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
installed.
> has
DB
to
> problem).
> verified
>
IVR to Database to Web
Hello all
I am about to begin a project that will be implemented in a local company. It involves using an IVR(interactive voice response) to collect information with regards a customer satisfaction survey. The results are then to be presented on the web. Thus I have an IVR connected to a database which is connected to the web. The results are to be presented in stats and graphs on the web.
I was originally going to use an Oracle database however it has worked out that it is not feesable to use Oracle for such a small application. I am now looking at SQL server. However, I have never dealt with it before. I have extensive knowledge of SQL. What I need to know is SQL server suitable to use when it comes to connecting to a web application, querying the data to show graphs and stats and is it easy to learn for a person who has extensive knowledge of databases? Id appreciate your knowledge and advice on this topicHowdy,
Greetings from sunny Surrey in the UK!!
Well the short answer is - yes - if you are willing to take the time to learn SQLs eccentricities.
Regards size , we have 5 MB databases running ( some with web app links ) so small isnt an issue.
SQL Server is pretty easy to learn, and if you are just quering the database or writing data into it from a web app, it should all be straight forward. Mind you...how long is a bit of string?
Do you have a DBA to set up the server or are you it?
Cheers,
SGsql
I am about to begin a project that will be implemented in a local company. It involves using an IVR(interactive voice response) to collect information with regards a customer satisfaction survey. The results are then to be presented on the web. Thus I have an IVR connected to a database which is connected to the web. The results are to be presented in stats and graphs on the web.
I was originally going to use an Oracle database however it has worked out that it is not feesable to use Oracle for such a small application. I am now looking at SQL server. However, I have never dealt with it before. I have extensive knowledge of SQL. What I need to know is SQL server suitable to use when it comes to connecting to a web application, querying the data to show graphs and stats and is it easy to learn for a person who has extensive knowledge of databases? Id appreciate your knowledge and advice on this topicHowdy,
Greetings from sunny Surrey in the UK!!
Well the short answer is - yes - if you are willing to take the time to learn SQLs eccentricities.
Regards size , we have 5 MB databases running ( some with web app links ) so small isnt an issue.
SQL Server is pretty easy to learn, and if you are just quering the database or writing data into it from a web app, it should all be straight forward. Mind you...how long is a bit of string?
Do you have a DBA to set up the server or are you it?
Cheers,
SGsql
I've problems.
I've 3 problems. First, I've data like this:
Date1 Date2
--
1/1/2005 1/7/2005 {m/d/yyyy}
1/8/2005 1/14/2005
1/15/2005 1/21/2005
If I input date 1/9/2005, so data at record 2 is showed. Because date
1/9/2005 in between 1/8/2005 and 1/14/2005.
If I input date 1/20/2005, so data at record 3 is showed. Because date
1/20/2005 in between 1/15/2005 and 1/21/2005.
and on...
Nah, How syntax SQL to select data above?
Second, I've 2 tables (A & B). I wanna import from table A to B. if Data in
table A exist at table B, so data at table B will be updated. and if Data in
table A not exist at table B, so data at table B will be inserted.
How syntax SQL to do it? Can it only one statement?
Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
its field?
INSERT INTO A (a,b,c,........)
SELECT a,b,c,..........
from B
Or any there other ways to write Syntax SQL (INSERT INTO) so short?> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
DECLARE @.dt SMALLDATETIME
SET @.dt = '20050120'
SELECT Date1, Date2 FROM tablename
WHERE Date1 <= @.dt AND Date2 >= @.dt
Always use YYYYMMDD format when inputting dates. And keep in mind that your
date values are *NOT* stored as the m/d/yyyy format you indicated, unless
they are not DATETIME/SMALLDATETIME.
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
> in
> table A exist at table B, so data at table B will be updated. and if Data
> in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
No, you will need to run a separate INSERT and UPDATE. Or DELETE the rows
from table B that exist in table A, then insert the whole lot.
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
YES. It is easy to get this list, open Query Analyzer, hit F8, find the
table, expand it, and click / drag the Columns folder to the query window.
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
Why do we need a shortcut? If you really like using SELECT *, what do you
do when someone changes the structure of the table?|||Q1:
declare @.d datetime
set @.d = '20050115'
select * from t1 where date1 <= @.d and date2 >= @.d
go
Q2:
You need two statements.
update tb
set tb.c1 = (select c1 from ta where ta.pk = tb.pk), ..., tb.cn = (select cn
from ta where ta.pk = tb.pk)
where exists(select * from ta where ta.pk = tb.pk)
insert into tb
select c1, ..., cn
from ta
where not exists(select * from tb as b where b.pk = ta.pk)
Q3:
Yes
AMB
"Bpk. Adi Wira Kusuma" wrote:
> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
in
> table A exist at table B, so data at table B will be updated. and if Data
in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
> INSERT INTO A (a,b,c,........)
> SELECT a,b,c,..........
> from B
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
>
>|||Thanks for your answerings. But I dont undrstand with your statement. You
said that Always use YYYYMMDD format to filter date. Because I always use
mm/dd/yyyy format to filter date. and it can do properly. Example:
SELECT * FROM TB Where BornDate='1/28/2000'
Why can it do? please explain me so detail.
Second, I ask to you. How to delete data at table A that exist at table B.
Usually I write like this:
DELETE FROM TA where NOID in (SELECT NOID FROM TB).
But it can works, if at table A (TA) has 1 field to be primary key. If table
A (TA) has 4 fields to be primary key. How its syntax so good?|||> Thanks for your answerings. But I dont undrstand with your statement. You
> said that Always use YYYYMMDD format to filter date. Because I always use
> mm/dd/yyyy format to filter date. and it can do properly.
Please read both of these in their entirety:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp
Date1 Date2
--
1/1/2005 1/7/2005 {m/d/yyyy}
1/8/2005 1/14/2005
1/15/2005 1/21/2005
If I input date 1/9/2005, so data at record 2 is showed. Because date
1/9/2005 in between 1/8/2005 and 1/14/2005.
If I input date 1/20/2005, so data at record 3 is showed. Because date
1/20/2005 in between 1/15/2005 and 1/21/2005.
and on...
Nah, How syntax SQL to select data above?
Second, I've 2 tables (A & B). I wanna import from table A to B. if Data in
table A exist at table B, so data at table B will be updated. and if Data in
table A not exist at table B, so data at table B will be inserted.
How syntax SQL to do it? Can it only one statement?
Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
its field?
INSERT INTO A (a,b,c,........)
SELECT a,b,c,..........
from B
Or any there other ways to write Syntax SQL (INSERT INTO) so short?> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
DECLARE @.dt SMALLDATETIME
SET @.dt = '20050120'
SELECT Date1, Date2 FROM tablename
WHERE Date1 <= @.dt AND Date2 >= @.dt
Always use YYYYMMDD format when inputting dates. And keep in mind that your
date values are *NOT* stored as the m/d/yyyy format you indicated, unless
they are not DATETIME/SMALLDATETIME.
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
> in
> table A exist at table B, so data at table B will be updated. and if Data
> in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
No, you will need to run a separate INSERT and UPDATE. Or DELETE the rows
from table B that exist in table A, then insert the whole lot.
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
YES. It is easy to get this list, open Query Analyzer, hit F8, find the
table, expand it, and click / drag the Columns folder to the query window.
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
Why do we need a shortcut? If you really like using SELECT *, what do you
do when someone changes the structure of the table?|||Q1:
declare @.d datetime
set @.d = '20050115'
select * from t1 where date1 <= @.d and date2 >= @.d
go
Q2:
You need two statements.
update tb
set tb.c1 = (select c1 from ta where ta.pk = tb.pk), ..., tb.cn = (select cn
from ta where ta.pk = tb.pk)
where exists(select * from ta where ta.pk = tb.pk)
insert into tb
select c1, ..., cn
from ta
where not exists(select * from tb as b where b.pk = ta.pk)
Q3:
Yes
AMB
"Bpk. Adi Wira Kusuma" wrote:
> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
in
> table A exist at table B, so data at table B will be updated. and if Data
in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
> INSERT INTO A (a,b,c,........)
> SELECT a,b,c,..........
> from B
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
>
>|||Thanks for your answerings. But I dont undrstand with your statement. You
said that Always use YYYYMMDD format to filter date. Because I always use
mm/dd/yyyy format to filter date. and it can do properly. Example:
SELECT * FROM TB Where BornDate='1/28/2000'
Why can it do? please explain me so detail.
Second, I ask to you. How to delete data at table A that exist at table B.
Usually I write like this:
DELETE FROM TA where NOID in (SELECT NOID FROM TB).
But it can works, if at table A (TA) has 1 field to be primary key. If table
A (TA) has 4 fields to be primary key. How its syntax so good?|||> Thanks for your answerings. But I dont undrstand with your statement. You
> said that Always use YYYYMMDD format to filter date. Because I always use
> mm/dd/yyyy format to filter date. and it can do properly.
Please read both of these in their entirety:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp
I've never seen this before
I saw something today that I had never noticed before. A collegue of mine
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:
>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:
>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian
Ive never been good with relationships
I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:
*******************************************************
I've got a structure which looks vaguely like this:
[ANCESTORS]
Grandparent
Parent
Child
If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Grandparent.Name ='Cybill'
This would return all of the children of 'Cybill' and their children. Now, if I use the following query:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Child.Name ='Jean'
This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).
Likewise, if I enter:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Parent.Name ='Ron'
Then I would get Ron's parents and also his children.
*******************************************************
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
Can anybody see my dilemma here?
Mark-up ASP.net posts here
MarkItUp.com... no. I don't. I've done something like this before with someone, and we didn't go about that method. :(
What's the problem with doing inner joins?|||Because, with Inner JOINs I am relying upon the existance of children to be able to return the grandparents.|||I actually have one table "Ancestors" and also a separate linking table which contains the relationships:
[Ancestors] ( id int, Name varchar )
[Relationships] ( id, fkid )
What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is:
Great-Great-Great-Grandparent
Great-Great-Grandparent
Great-Great-Grandparent
Great-Grandparent
Grandparent
Parent
Child
I just have absolutely no idea how to write a single sql query which could filter on one or more levels but ensure referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.|||Can we use a UDF? :-)|||Yes, I'm using SqlServer 2K.
I have finally tuned the stored procedure "program" enough to get my - previously 14 hour (give or take 100 milliseconds) - query down to sub-20 seconds. This is good enough to ship to the client so, I'm going to go with what I have for now.|||20 seconds? I'm still not believing that to be good enough. :) but if it's good enough for the client, it's good enough I guess.
If you're using any temp tables or what not, try indexing them before you apply data to them. I trimmed a procedure that executed in 12 seconds to 2.|||KraGiE, I agree, 20 seconds is a long time to wait, but, it's working so that's a much better position than I had 2 days ago.
I do have # temp tables, about 12 of them actually (more on that later), and my final optimization was to add indexes and optimization hints in the appropriate places, this reduced a 2 minute 20 second query to sub 30 seconds.
As for the 12 temp tables, this, to me was a fair indicator that the schema of the database was wrong to begin with so now, I 'm actually pushing to have the db schema altered too. I'm hoping that this will give me enough of a foothold to be able to achieve the necessary remaining improvements|||Personally, I'm a big fan of int based look up tables. :) Well, in cases like this, I'm fond of them because you can branch them out as far as you want without having just 'add a new field'.
I also think loops are my best friend when used properly.
What I meant by the indexes (if this is what you're doing already, then I'm a redundant moron) is ...
|||Yeh, that's pretty much what I'm doing already re: the indexes ( although who am I to call you a redundant moron :P ) except that I create the index only after population and, therefore also set the fill factor to 100% - but, for the purposes of an illustration, yes, that's pretty much what I do.|||awesome. In some cases, I'll put the index in before I start populating if the data's going to be enormous. It sets a bit faster from my experience.
SET NOCOUNT ONCREATE TABLE #TempTable
(
tableID int IDENTITY,
TableField varchar(50)
SomeOtherVar int
)
GOCREATE INDEX idx_Temp
ON #TempTable ( tableID )
GOINSERT INTO #TempTable
SELECT
TableID,
TableField,
SomeOtherVar
FROM
RealTable
-- WHERE Your CriteriaGO
-- Do Other Calculations
TRUNCATE TABLE #TempTable
DROP INDEX idx_Temp
DROP TABLE #TempTableGO
-- Yes Terri, I space my sql in procedures and QA this way normally, and I'm just lazy when I post here. :)
I've installed SQL express, now what?
This may be a stupid question, but I've installed SQL Express and I don't know what to do next. I went to my program files/Microsft SQL... and the only folder present is a configuration folder.
Next I went to my control panel/admin tools/ODBC and succussfully created a user data source. (With the help of the forum!)
Obviously, I've never used SQL before, but I've read a couple of books and other stuff online, and I want to teach myself. So I decided to install this. Basically, I'm looking for answers on how to access the database. I know it's installed, it's in the Add/Remove programs list. I just don't know how to connect/access it.
Sorry for the dumb question.
Stacey
I think I figured it out. You have to access the DB using another program, and connect using the ODBC that I previously created. Am I correct?
I thought it was an independent DB, meaning I could directly access it. Thanks!
|||Hi
You'll need to download SQL Management Studio Express (here). It will allow you to create databases, run queries etc. That way you won't have to work "in the dark".
HTH
|||
Thank you so much for helping me. Now I just have to figure out how to use it, and what to do!!!
Stacey
|||Update - Thank you so much Andre! I am off and running thanks to you. I figured out how to use it, and now I'm learning SQL thanks to you!!
Subscribe to:
Posts (Atom)