|
Big Joke
♫ 99 little bugs in the code,
99 bugs in the code
We fix a bug, compile it again
101 little bugs in the code ♫
|
|
|
|
|
Hi all,
I want to execute a union query where data comes from two different servers.What i did is first i linked the server to my current server as
Exec sp_addlinkedServer 'myserver'
Exec sp_addlinkedsrvlogin 'myserver','false',NULL,sa,''
and
then i executed the foll query
select user_name+'t1' from myserver.mydatabase.dbo.User
but i get the foll error
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
any help would be appreciated...
When you fail to plan, you are planning to fail.
|
|
|
|
|
I'm guessing that the remote server does not have Mixed Mode Authentication enabled.
SG
Aham Brahmasmi!
|
|
|
|
|
hi,
go through this
http:
and then also if your problem occurs then
"This is caused by trying to connect to SQL Server when SQL Server is not configured for Mixed Mode authentication."
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi thanks for your reply...
The link u provided really helped..
Thanks again.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Is there possibility to define unsigned type or convert already exist type, int for example, to unsigned int in MSSQL?
|
|
|
|
|
Can you be more clear in your question?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Sure.
I have some unsigned short variable in C process and I need to load its values into database.
The values of this variable may exceed 32K and I also don't want to use 4 bytes data valuse. Just like in C process - 2 bytes long.
|
|
|
|
|
There is no unsigned integer types in SQL Server, except for tinyint (0 - 255).
You can use an user defined data type, but that will not make the size of the data smaller (which is what I'm assuming you want to do). You can also add a constraint to a column so the value is >= 0.
As for saving space, no way to unsign an int.
|
|
|
|
|
There are no unsigned integers in SQL Server, except for tinyint.
So for bigger values define a INT or BIGINT data type ( as per your database demand ) and add a check contraint to accept only positive values.
Like the below example.
create table x_table(i int, j_Pos_value int constraint chk_j _cons Check
(j_Pos_value>=0))
"We can't solve problems by using the same kind of thinking we used when we created them"
|
|
|
|
|
Hi,
I want to know that in sql server 2000 when user wants to reterive particular record from table,Is this true that whole table rows are processed or executed .
becuase i want to know how i improve the performance of my database.
Any Suggestion .
Regards
Rameez
|
|
|
|
|
This depends on many things. Generally to get 1 record you use a where clause to filter the table. If the field you are filtering on does NOT have an index then a table scan is required until it finds the record. This is called art of database tuning. There a many books written on this subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
You should definitely look into this tool. I have used it to find out what is going on while my application is running. Also, when writing your SQL statements, you should avoid statements like "select * from myTable" you should only list the columns that are really required otherwise you will be sending more data than is necessary over the network.
My 2 cents.
David
|
|
|
|
|
Yes, all records of database are traversed.
"We can't solve problems by using the same kind of thinking we used when we created them"
|
|
|
|
|
SQL 2005 Standard SP1
I have these two queries I am trying to join into one result set; I have been struggling with this for days and get close but can not seem to get it correct.
Query 1:
select count(*) as 'Total Tickets Assigned' , location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
WHERE Report_Date >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112)))
group by l.location_name
Query 2:
select count(*) as 'Total Closed for Range'
, location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) ) and
status_type_id ='3'
group by l.location_name
basically all I need to get is the "count(*) as 'Total Closed for Range'" into query 2, however; I keep getting the total in each row rather than the total for each client.
Also I have been trying to send the results of the query out via email.....I know this should be simple but I keep getting errors on that as well.
Any help would be greatly appreciated.
Thanks,
Dennis
Regards,
Hulicat
modified on Wednesday, August 20, 2008 1:53 PM
|
|
|
|
|
If you want to have single query which output result same with your two querys then post your data of tables and result which you want to have from only single query.
Regards.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I am posting here because I am unable to make the single query output correctly
I would like one result set looking like this:
'Total Tickets Assigned','Total Closed for Range', 'Client'
Regards,
Hulicat
|
|
|
|
|
Select
total.Cient
,TotalTicketsAssigned
,TotalClosedForRange
From
(
select count(*) as 'TotalTicketsAssigned' , location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
WHERE Report_Date >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112)))
group by l.location_name
) total
Left Join
(
select count(*) as 'TotalClosedforRange'
, location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) ) and
status_type_id ='3'
group by l.location_name
) Closed on
total.Cient = closed.Cient
|
|
|
|
|
Thank for that; it gived me two errors one on line 11 and one on 23 the last.
1st error on 11 is a syntax error near ")"
2nd error is on line 23 the last is error near "closed"
Heere it is revised a bit...this one is tuff for me thanks for the help!!!
Select total.Client,TotalTicketsAssigned,
TotalClosedForRangeFrom select count(*) as
'TotalTicketsAssigned' , location_name
'Client'from job_ticket jinner join priority_type p
on p.priority_type_id = j.priority_type_idinner join
tech t on t.client_id = j.assigned_tech_idinner join
location l on l.location_id = j.location_id WHERE Report_Date
>= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
AND Report_Date
< DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
group by l.location_name)
total.client
Left Join (select count(*)
as 'TotalClosedforRange', location_name 'Client'from
job_ticket jinner join priority_type p on p.priority_type_id =
j.priority_type_idinner join tech t on t.client_id =
j.assigned_tech_idinner join location l on l.location_id =
j.location_id where (last_status_update_time >=
DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) ) and status_type_id ='3'
group by l.location_name)
Closed on total.Client = closed.Client
Regards,
Hulicat
|
|
|
|
|
I copied and pasted the two queries you supplied into the join sub-query syntax, so the line 11 one is something from the query you supplied (and I'm guessing the second one is resulting from the first error).
To try to clear things up for you, here's a simple structure of a sub-query join:
Select
<select list>
From
(
<query 1>
) q1
Join
(
<query 2>
) q2 on
<join list>
<other clauses (probably will not have any here)>
The select must use the names assigned to the columns, so if you have Select Name, Date as OpenDate; then you would need to use Name for name (because the name was not changed) and OpenDate for the original column Date.
If you think of the two queries as tables, it might make the concept easier to grasp. If the above query was written from tables, it would be Select * From Table1 q1 Join Table2 q2 on ...
Hope that helps.
|
|
|
|
|
Thanks for that!!!
You made it crystal clear and I have it working now!!
Omce again thanks for taking the time to teach and give an example of perspective...
That will really help me moving forward.
Thanks and Regards,
Dennis
Regards,
Hulicat
|
|
|
|
|
need help on something i have this
Dim SQLIinsert As String = "UPDATE httpuser SET credits= " + -1 + "' WHERE username='" + My.Settings.Username + "'"
what i need is to decrease the column credits by 1 but it this i get -1 in the database.
can someone explain whats wrong? ive searched but im almost ready to quit.
thanks in advance
ive posted the same on vb.net because i guess this question envolves both parts
|
|
|
|
|
"UPDATE httpuser SET credits=credits -1 WHERE username='" + My.Settings.Username + "'"
But be aware from SQL Injections, take a read about it.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
