|
I beleive the with structure you are seeing is not available in 2000 (sorry to state the obvious). What it is doing is allowing you to use a sub select. So the select in () is named Subsidaries and is then used in the join in the second select statement.
Work around would be a view ot temp table to isolate the first select so you can use it in the second select (or completely redesign the query)
OR
You could upgrade SQL Server 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The WITH indicates a CTE (Common Table Expression) which was only introduced with SQL Server 2005. Probablt a temp table to replace your Subsidaries select is the best bet.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello Friends,
I am using SSIS and I have SQL Server Source and the same Destination. I have a column datetime created in my source and it contains data for about 15 years. I just want to retrieve the data for the current month. i.e, from September 1, 2008 to September 17, 2008. Can anyone plz help me, how can I achieve this?
Using Conditional Split Transformation or SQL Query for this? I want to do this task using SSIS.
Thank you.
notes4we
|
|
|
|
|
 see if this is what you want
declare @mydate datetime
set @mydate = '1/4/2006'
select year(@mydate)
select day(@mydate)
select mouth(@mydate)
not sure about the exact funtion name. if not search in sql help, easy to find.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Try the following
select *
from [Table]
where datepart(m,[Date field]) = 9
and datepart(d,[Date field]) between 1 and datepart(d,getdate())
Ryan
|
|
|
|
|
Thank you so much for all your replies.
The business logic was that there are two units associated with a single machine. So, the number fields were units that I wanted together in one.
If there are two columns UnitID1 and UnitID2, both having integers in it and if we wish to get the UnitID1 and UnitID2 as “UnitID1, UnitID2” in our destination Stations, then we use the following derived column expression. UnitID1 has no null values, but UnitID2 might have null values also.
[Copy of UnitID1] + (ISNULL([Copy of UnitID2]) ? " " : " , " + [Copy of UnitID2])
'Copy' as I have used Data Conversion transformation to change the datatypes before using derived column. Thank you once again all of you.
|
|
|
|
|
hi,
I have a table with persons having a integer as a unique key.
now i've made a second table groups defined by the persons in it.
the combination of the uniek keys of the persons should be my unique key in the groups table.
for example
1. person a
2. person b
3. person c
first group is
1,2
second group is
1,3
third group is
2,3
fourth group is
1,2,3
Does anyone has any idea how i can store this data easely as unique key and still find the related data in an easy way ?
Data delivered to search a group are the combinations of the persons.
greetz and thx
Kurt
|
|
|
|
|
I'm not sure I follow you, but shouldn't you have a table of Groups
1. Group1
2. Group2
3. Group3
Then a table to represent the many-to-many relationships
Person Group
1 1
1 2
1 4
2 1
2 3
2 4
3 2
3 3
3 4
|
|
|
|
|
thx for the response,
but i'm not quite sure.
if i deliver you person 2 and 3 can you give me the group the present ?
i gues i need a select where group is the same for person 2 and 3 and where group is not used by other persons or so..
hmm this might work...
Problem is that i get a bunch of persons and update common data as group,
i'll need to check if the group exists
if not i'll add the common data
else i'll update the common data of those persons.
so i didn't think about a cross table..
good thinking...
thx
just thinking now that i need to insert multiple keys in de many-to-many table when adding one record for a group, but that should not be a problem.
|
|
|
|
|
listen,
i don´t understand what you want to do. what about the groups stuff? name then to understand, maybe anyone could help you.
do you have 5 tables? persons,group1?,group2?,group3?,group4? 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi!
I am a newbee to SQL and I am trying to write a trigger. My aim is to realize a triger when a new record has been made in TABLE (A), and identical record will be written in TABLE (B) please see the example below. What intriques my mind is, would it be the last record in TABLE(A) which will be written in TABLE(B) with the definition below.
If you give me a correct example, I would really appreciate it.
Thanks.
<br />
CREATE TRIGGER Trigger2<br />
ON dbo.Customer<br />
FOR <br />
INSERT INTO Administration (AccountStatus, UserType, UserLoginId,UserPassword,PasswordReminder,PasswordReminderAnswer,IsUserLoggedIn)<br />
VALUES (False, <br />
Customer.Usertype,<br />
Customer.UserLoginId,<br />
Customer.UserPassword,<br />
Customer.PasswordReminder,<br />
Customer.PasswordReminderAnswer,<br />
False)<br />
AS<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
|
In SQL, you shouldn't think of it as a last row. What you really want is to identify the row by either a sequence number or a timestamp. Try to break yourself from visualizing the data like a file.
Good luck.
|
|
|
|
|
try this;
declare @id_last int
set @id_last = (select max(id) from mytable)
if that is a report? ok, if not (meaning you edit,delete,add) better place some kind of "lock"(search this keyword) because people can trigger that procedure at the same time!!! 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi All, could you please give some tips and suggestions on how to improve performance of databases in SQL and optimize in better ways.
Be a good professional who shares programming secrets with others.
|
|
|
|
|
This is far too large a topic to answer in a forum. You are better off looking at a specialised website such as sqlservercentral.com
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you are speaking of MS-SQL sever, you should investigate using SQL profiler. Typically, you would look for long running SQL statements or high CPU, high disk IO, etc. There are many resources out there on SQL Profiler. I've used it with tremendous success. Good luck. David
|
|
|
|
|
Thanks Mr.David for your quick response. Thanks again for reminding me to use sql profiler. I will try today itself.
Be a good professional who shares programming secrets with others.
|
|
|
|
|
hi,
I am using sql server 2005 and i have to deploy my database into client side.
my database table having some very important data and
I want to restrict server administrator not to open my database or he can not make any direct changes in databse table.
Please let me know , is there any way to stop server adminstrator to make direct changes in database.
Is there any sql scripts to stop this.
thanks
Hari
|
|
|
|
|
As far as I know you cannot stop the SA account from accessing any database, thats the whole point of the sa.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you need to restrict windows administrators, you can disable BUILTIN/Administrators from logins. If it is something else you need, could you specify a bit more.
Mika
|
|
|
|
|
Hello people,
Is there any good link to study about LDAP server and its main usage and where it will be deployed ?
Many Thanks,
Sakthi
Nice things do nice works
|
|
|
|
|
hi all ,
i'm trying to import db from one db to other , here i'm getting only tables and views only .
in that db i'm having stored procedures also but im not getting stored procedures .
can any body explain me how to get the whole db .
Thanks ,
csn
|
|
|
|
|
Redgate[^] has excellent tools for working with databases.
|
|
|
|
|
For Procs you will have to generate script in old DB and then execute it in new db after imporying tables and views.
By the way whats exact nature of requirement that is forcing you to import DB?
Razov
If you think this answer helps you please rate it, it helps me. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
