I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
View 1 RepliesThis is probably a very simple question but i am having problems with stored procedure syntax.
I want to insert fields with preset values (DateCreated/OrderVerified)
and two values (Name/ Address1) from a databletable called "Customer" and insert both of these into a datatable called "Orders"
I have tried the following syntax but it is not working. Can anyone tell me where i am going wrong
CREATE PROCEDURE SP_CreateOrder
(@CustomerID Varchar(50))
ASINSERT INTO Orders (DateCreated, Verified)VALUES ( GETDATE(), 0,)AND INSERT Into Orders (Name, Address)SELECT (Customer.Name, Customer.Address) FROM Customer
WHERE CustomerID = @CustomerID
Many thanks martin
need help how to
change the shift order in my stord prosege backward
on the field "shifttype"
not like this
shifttype
---------------------------------------------------------
111111 2008-02-24 Sunday 1
111111 2008-02-23 Saturday 2
111111 2008-02-22 Friday 3
111111 2008-02-21 Thursday 4
111111 2008-02-20 Wednesday 5
111111 2008-02-19 Tuesday 6
111111 2008-02-18 Monday 7
111111 2008-02-17 Sunday 8
111111 2008-02-16 Saturday 1
111111 2008-02-15 Friday 2
111111 2008-02-14 Thursday 3
111111 2008-02-13 Wednesday 4
111111 2008-02-12 Tuesday 5
111111 2008-02-11 Monday 6
111111 2008-02-10 Sunday 7
---------------------------------------------------------------------------------------
i need it like this
shifttype
------------------------------------------------------
111111 2008-02-24 Sunday 8
111111 2008-02-23 Saturday 7
111111 2008-02-22 Friday 6
111111 2008-02-21 Thursday 5
111111 2008-02-20 Wednesday 4
111111 2008-02-19 Tuesday 3
111111 2008-02-18 Monday 2
111111 2008-02-17 Sunday 1
111111 2008-02-16 Saturday 8
111111 2008-02-15 Friday 7
111111 2008-02-14 Thursday 6
111111 2008-02-13 Wednesday 5
111111 2008-02-12 Tuesday 4
111111 2008-02-11 Monday 3
111111 2008-02-10 Sunday 2
Code Snippet
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int NOT NULL,
[ShiftType] int NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL
)
INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103) UNION ALL
SELECT 222222,2,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 333333,3,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,-1*spt.number,Enddate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)
--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id= 8 and DATENAME (dw,ShiftDate )='Saturday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts
--select datediff(ms,@g,getdate())
Hey guys and gals,
I got asked a question yesterday about queries that JOIN between two databases on a single server... Simple enough
SELECT e1.employee_number
FROM Database1.dbo.employees As e1
INNER
JOIN Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But what about joining tables from two separate servers?
I figured that this would use full 4 part naming
SELECT e1.employee_number
FROM Server1.Database1.dbo.employees As e1
INNER
JOIN Server2.Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But I got the following error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
So off I went to trusty BoL and Google to see what sp_addlinkedserver was all about and came up with the following
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
Is this syntax correct?
I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
Is this process reversible?
I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
Does the link affect all databases on a single server, or just the one I'm playing with?
How do I connect to a specific database on a specific server?
Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
EDIT: highlighted in pink
Our Production server is Windows 2003 and SQL 2000 Enterprise. The server name is VB-GP.
I dumped 2 of the databases from the production server and restored them to server name SQL2K5-Pub (a TEST server) which was a fresh install of Windows 2003 and SQL 2000 Enterprise. I then upgraded this "TEST" server to SQL 2005 Enterprise. All is fine so far.
Now I'm setting up Transactional Replication in my test environment. I configured SQL2K5-Pub as a Distributor which went well. I created a a Publication of 1 database which went well. I attempted to create a Publication of the second database and received the following error.
Error Message
*********************************************************************
Could not find server 'VB-GP' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Could not use view or function '[dbo].ContractBillingStatus_
*********************************************************************
Remember - server name VB-GP is the production server but I have restored to a test server with a different server name. I do not want the production server to have any part of my test environment.
Any ideas?
Thanks,
Terry
I have used this command
sp_addlinkedserver 'linkedserver', 'Access', 'Microsoft.Jet.OLEDB.4.0','C:CATS_MDBintrax_1_db.mdb'
to link an .mdb databse. the trouble is, I do not know how to query tbl_user which is inside. When I try
Code Snippetselect * from [linkedserver].catalogs.dbo.tbl_user
it throws this error:
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linkedserver". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
Thanks
VENU
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
HOW DO WE MAP THE LOGINS USING EM AFTER ADDING THE SERVER USING SP_ADDLINKEDSERVER
Thanks
VENU
Hi
Can anyone explain me as to how to map my local_userid to the linked server.
Under the security tab of the linked server properties what options are to be used..any help in this regard is greatly appreciated
Thanks
VENU
HI!
i m using sp_ADDLINKEDSERVER for getting data from the other server name 'db est'.
And when i write
'
EXEC SP_ADDLINKEDSERVER 'db est', 'SQL SERVER'
GO
EXEC sp_addlinkedsrvlogin 'db est', FALSE ,NULL ,'sa','sa'
GO
SELECT * FROM db est.Practice.DBO.Employees
'
And then run it in query analyzer. it gives following error due to '' in the server name.
Incorrect syntax near ''.
how to resolve this issue?
Regards,
Shabber Abbas Rizvi.
Hi,
Iam trying to use database in different server. i have searched for that i got to know there is something called
"sp_addlinkedserver" & "sp_addlinkedsrvlogin " stored procs exist. i have tried using this code block to register server to sys servers.
Code Block
exec sp_addlinkedserver @server='ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname='ServerName', @useself=false, @rmtuser='testUser', @rmtpassword='pwd'
when i login using Domain user it is throwing an error as "Login Failed for the User(Domain user)", but when i use "sa" user it works fine.
Is there any settings which i have to take care while using domain user.
can anyone help on this.
Thanks,
-Badri
I'm trying to link access table and execute in Query analyzer or query window.
but I'm pretty sure that I connect properly....
1)
sp_addlinkedserver 'linkedserver', 'Access 97', 'Microsoft.Jet.OLEDB.4.0','C:Mag.mdb'
after i ran this it displays....
(1 row(s) affected)
(1 row(s) affected)
Server added.
2)
sp_addlinkedsrvlogin 'linkedserver', true, 'sa', 'sqluser','sqlpassword'
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
3)
after that when i try to run the sql statement like
Select * from linkedserver..tablename
I GOT THIS ERROR MESSAGE:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'linkedserver..tablename'.
I appreciate if anybody have idea about that.
Thankx a lot
QUESTION 1
Is it possible to run a JOIN on tables that are on seperate servers and youre not able to link the servers using "sp_addlinkedserver?"
QUESTION 2
I try to run
"exec sp_addlinkedserver 'sqlserver1'"
and I get the following:
"Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36
User does not have permission to perform this action."
I try to then run
"SELECT *
FROM server1.database1.schema1.table1 AS a
JOIN server2.database2.schema2.table2 AS b
ON a.[column1] = b.[column2]"
and I get the following:
"OLE DB provider "SQLNCLI" for linked server "ITCMSARPT" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
It would appear the the server is now linked, yet it does't accept my credentials. I do have the ability to log into the server on a seperate query.
Can anyone provide a solution to my issue?
I wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database.
--Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins
if exists (select * from dbo.sysobjects where id = object_id(N'sp_compare_table_record_counts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_compare_table_record_counts
go
create proc sp_compare_table_record_counts as
----Creates the connection to the Remote Database. We need to make sure that the
----ODBC connections on each of the Production and Development Servers
----are named the same, so that we can use the same script and it will refer to the correct server
EXEC sp_addlinkedserver
@server = 'SERVER_BK',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
set quoted_identifier off
select substring(remote_table.tablename,1,40) as Tablename, local_table.tablerowcount as L_SERVER,
remote_table.tablerowcount as R_SERVER,
case when remote_table.tablerowcount = local_table.tablerowcount then 'Counts Match'
else 'Counts Do Not Match'
end as Result_of_Compare
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table, tablecounts local_table
where remote_table.tablename = local_table.tablename
order by Result_of_Compare, Tablename
-- table name on R_SERVER but not on L_SERVER
select substring(local_table.tablename,1,40) as 'Tables not on R_SERVER'
from tablecounts local_table where local_table.tablename not in (select * from OpenQuery([SERVER_BK], 'select tablename from tablecounts'))
-- table name on L_SERVER but not on R_SERVER
select substring(remote_table.tablename,1,40) as 'Tables not on L_SERVER'
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table where remote_table.tablename not in (select tablename from tablecounts)
----Deletes the connection to the Remote Database to free up resources. This is just cleanup.
EXEC sp_dropserver 'SERVER_BK', 'droplogins'
I tested, and the stored procedure works OK when it is run as a New Query (i.e. I copy the guts and run it). When I execute the following, it fails:
exec msdb..sp_send_dbmail
@profile_name = 'DB Mail Profile',
@recipients = 'Recipient List',
@query_no_truncate = 25,
@query_result_separator = ' ',
@subject = 'Record Counts',
@query = 'exec db..sp_compare_table_record_counts', <--db is the appropriate db where the sp is
@body_format = 'text'
Here is the error I get...any ideas would be greatly appreciated. I'm kind of new at this:
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 7399, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
The OLE DB provider "MSDASQL" for linked server "SERVER_BK" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER_BK".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'CONVERTSDB', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'F:Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedHi,I have a sql query like this select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08' group by [mycolumn]order by [mycolumn] descIf all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.(0+1+2)/3 = 1. But sql is returning a value as if my 0's were nulls and not factored in: (1+2)/2 = 1.5Does anyone know why this is happening and how to fix it?
View 5 Replies View RelatedHi,
I have a sql query like this
select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08'
group by [mycolumn]
order by [mycolumn] desc
If all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.
(0+1+2)/3 = 1.
But sql is returning a value as if my 0's were nulls and not factored in:
(1+2)/2 = 1.5
Does anyone know why this is happening and how to fix it?
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View Relateda different scenario leads to the same problem, the error message:
Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"
I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server.
this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away.
I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution?
Hi,I'm trying to include a button control inside an iff where I am showing the results of a datalist. My aim is for each user listed, to show whether or not he is already a friend of the logged in user. To do this, I want to create an iff where if the user is already a friend, I will show the text "already a friend". If the user is not yet a friend, there will be a button saying "add as friend" which when clicked will execute an insert query to the database to add that user as a friend. I tried the code below, but it doesn't seem to recognize the button inside the iff. I really need a button to work if the result of the iff condition is DBNull.Value... Can anyone please help? <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<asp:LinkButton id='addbuddy' runat='server' > Add as buddy</asp:LinkButton>", "Already buddy")%> I also tried the code below instead, but it doesn't work either, and I do not know what to include instead of true and false, since the button would only be enabled if the case was true. <asp:LinkButton id='addbuddy' runat='server' Enabled="<%#IIf(DataBinder.Eval(Container.DataItem, “userNameIfFriend�) is DBNull.Value, “True�, “False�)%>">Add as friend</asp:LinkButton> Thanks a lot.
View 6 Replies View RelatedHi everybody
I have this sql which calculates the total
MY TABLE IS:
A B C
100 5 3
100 0 5
20 0 0
sql is
A - B + C as total
the result is it gives me only the total of the first row since it has values on the on the B AND C
the result is
A B C total
100 5 3 98
100 0 5
20 0 0
with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...
thanks for any help
Collin writes "i have tried to do a query between 2 different tables that have different columns. within both these tables there is a column that has a column name different from the column name of the other table but the data within both columns is the same. i wanted a query that compares the data between the 2 tables.
Example is:
Table 1
Columns : firstname , age , id , grade.
Table 2
Columns : name, sex , school.
lets say that the data of column "firstname" of table 1 contains the same data as column "name" of table 2 and i would want to do a query that retrieves records for where data in the column "firstname" in table 1 is found in column "name" of table 2.
sql server 2000 with service pack 3 running on windows 2003 server service pack 1
Rgds,
Mr. C. "
I have a three server peer-to-peer replication setup that includes articles for tables and views. As I understand the BOL, scheme changes -- which I take to mean changes, amont other things, changes in the design of a table or view -- should automatically replicate to the other servers in the topology. Here are my quesitons:
When including a view as part of the publication, what is it, exactly, that is getting replicated? If all the tables supporting a given view are being replicated, and the view exists on all three boxes, whatelse, besides the view schema (and changes thereto) is being replicated?
Secondly, if in fact schema changes are replicated, why can't I modify a view that is part of a publication? When I try to make a change to such a view, I get a server timeout message, every single time. When I remove the view from the publication, I can make the modification with no trouble. What does replicating schema changes mean if I can't make changes to the schema?
Thanks for any enlightenment on this.
Randy
Hi all,
I am processing multiple flatfiles in the same folder using a "for each loop". Inorder to keep track of the file where the record comes from, I would like to add the full path and filename to the record that gets written to SQL.
Can anyone help me with this.
Thanks,
Dan
I'm using reporting services actions to execute some javascript. My javascript is quite complicated. Thus I don't want to duplicated it on each item. I've created javascript function and wan't to execute it from actions. But I can't find the way how to include my javascript code into my reports. If there's way to include it for all reports generated on reporting services server - it works fine for me.
Thanks in advance!
Hi,
I'm trying to write a SQL SELECT statement where the phone numer ("telnr") is divided on three rows. How do I write a newline? I've tried , NEWLINE, and a few others.
Thanks in advance!
Pettrer, Sweden (VB, Sql Server, VWD Express, Asp.Net 2.0)
Code:
SelectCommand="SELECT [gID], [enamn], [fnamn], telnr1 + ' ' + telnr2 + ' ' + telnr3 As telnr, [epost] FROM...
The corresponding gridview's cell's value is
08-43 244 234 08-432
23 08-424333
and should be
08-43 244 234
08-432 23
08-424333
Hi All,
Here's my situation: I need to move several application db's to a new server. The current server is SQL 7.0 SP2, the new server will be SQL 7.0 SP3.
In order to avoid having the problems of mismatched logins and db user definitions, I'd like to restore the current master over the master that's created at install on the new server.
I was doing some testing of this by trying to restore the 7.0 SP2 master onto a SQL 7.0 DE edition and had some problems. (Obviously, this is not the exact scenario that I need to take place -- I want to restore standard edition to standard edition.)
Is restoring the master db from different service packs under 7.0 going to pose a problem? Is restoring the master db the best way to get around the logins issue when moving db's?
Thanks in advance for all replies.
Hi All
I have a table of students, and a table of lecturers, and I wish to use one select statement to return a list of users.
What I really need to do is something like this:
SELECT Students.UserID AS UserID, Lecturers.UserID AS UserID, Students.FullName AS FullName, Lecturers.FullName AS FullName FROM Students, Lecturers
which would (in my dream world!) produce something like:
UserID FullName
s0002131 Darren Student
s0054242 Richard Student
e13412 Michael Lecturer
x92342 Linda Lecturer
Does anyone know how I should do this? Should I be doing it in an stored proceedure and returning the record set from that? Currently I'm only using ASP and one line SQL statements, and would like to continue doing so.
I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that?
Here is my query:
select Z, count(*)as num from Table
group by Z
having count(Z) > 1 ----- this returns 213 rows
Select Z, A,B,C,D From Table
Group By Z, A,B,C,D
Having Count(Z)>1 ----This gives me nothing
Hope I am explaining this correctly as to what I want.
Thanks,
Saru Brochu
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
Hi,I have 2 tables, Mail_subject and Mail_Usage.Mail_Subject contains the subject, body and some other bits of info.CREATE TABLE [Waterford_MailSubject] ([ID] [int] NOT NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailCategory] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[MailCreateDate] [smalldatetime] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOMail_Usage records the amount of times a certain mail was sent.CREATE TABLE [Waterford_MailUsage] ([ID] [int] NOT NULL ,[RepScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MemberScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[TimeDate] [smalldatetime] NULL ,) ON [PRIMARY]GOThey are joined by Subject (not my idea, its a DB ive inherited).What i need is to get the Mail Subject and the number of times thatMail was sent. Ive Joined them using an INNER JOIN which gave me acount of the number of times each one occoured except for Mails thathave not been used. I need to get zero as the count of Mails notsent. Ive tried a LEFT OUTER JOIN but it didnt work either.Can someone point out what i need to do ?
View 1 Replies View RelatedI've seen plenty of posts regarding the estimation of table size,usually in the processing of planning for server storage needs.Well, I've got a different problem. I need to know how much data eachof our Customers are using in a Database. (1 SQL DB stores multiplecustomers).Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:655 MB.So, how can I ask SQL Server how much data each Row in each table istaking up? I want to be able to calculate nightly the total size, so Iwould take each row in each table that belong to the customer, and addall the sizes together. I want to take into account blobs that arestoring images and PDF files also.Thanks in advance,Jesse Wolgamott
View 1 Replies View Related