ZOS DB/2 Linked Server Without Additional Software
Apr 23, 2008
I vaguely recall that there is a way to install a linked server or similar datasource on SQL 2005 without the need for ANY additional software... No drivers, no third party packages, just a moderately complex configuration issue.
Once the beastie was configured, you could assign linked server logins, build queries using four part names, etc with no significant restrictions.
Unfortunately, I don't remember the details of this feat of majik. Can anybody refresh my feeble memory?
If i initially installed SQL Server 2005 Developer Edition using the "default instance", how do i create an additional (new) SQL Server 2005 (90) "named instance" without reinstalling SQL Server 2005?
We have a SQL 2008 Instance existing on active/passive cluster with 2 nodes running on Windows server 2008 R2 Ent. Edn.
Now we need to install another SQL instance on this cluster. So what are the prerequisites apart from new IP address? Do we need shared disks or can the existing disks can be utilized?
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch. 4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
Currently both of our servers have 1 gig of RAM. We are adding and additional 1gig of RAM to our local server but none to the remote server. Transactional replication is performed between both servers in both directions. SQL Server is configured to dynamically configure memory on both servers.
Are there any issues or concerns that I should be aware of? Thanks
I have code on a website that does a search to an Access database based on what the person enters as the search criteria. My SQL code uses a like % statement for all the fields in the search criteria. The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for..... I did read up and found that the data must be character for the like statement. Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE: <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5"> <tr> <td width="400" bgcolor="#3366cc" colspan="2"> <form method="post" name="DaForm" action="Makeit.asp" align="center"> <div align="center"><center><table border="1" width="350" cellpadding="3" bgcolor="#3366cc"> <tr> <td align="left" width="189"><select name="TypeSearch" size="1"> <option selected value="PartNumber">Part Number</option> <option value="Description">Description </option> <option value="Mfg">Manufacturer</option> </select></td> <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td> </tr> <tr> <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit" name="B1" value="Search!"><input type="reset" name="B2" value="Clear"> </td> </tr> </table> </center></div> </form> </td> </tr></table>Makeit ASP COde: <%@ LANGUAGE="VBSCRIPT" %> <html> <head> <title>Your Search Results!</title> <meta name="Microsoft Border" content="lb, default"> </head> <body> <% Dim MySql
Set cn = Server.CreateObject("ADODB.Connection") cn.Open("parts") MySql = "SELECT * FROM parts" If Request.Form("TypeSearch") = "Part Number" Then MySql = MySql & " WHERE PartNumber LIKE '%" &_ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Description" Then MySql = MySql & " WHERE Description LIKE '%" & _ Request.Form("DaInBox") & "%'" End If Dim rs Set rs = new Server.CreateObject("ADODB.Recordset") rs.Open MySql, cn, 0,1 %> <% If rs.BOF and rs.EOF Then%> <h2 align="center">We did not find a match!</h2> <%Else%>
<%If Not rs.BOF Then%> <h2>Here are the results of your search:</h2> <table BORDER="0" width="100%" cellpadding="3"> <tr> <th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th> </tr> <% Do While Not rs.EOF %> <tr> <td><%=rs("PartNumber")%> <%=rs("Description")%>
I have two tables that I am pulling data from to insert into a third table.
Table 1: Organization Address Date Name 404 St. 12/31/1999
Table 2: Organization Software Type Quantity Name SW1 5 Name SW2 6 Name SW3 7 Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity) SELECT Table 1.Name, Table 1.Address, Table 1.Date, (Select Table 2.Quantity Where Table 2.Software Type = 'SW1'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW2'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW3') FROM Table 1, Table 2 WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
SELECT ARDoc."Cpnyid", ARDoc."Custid", ARDoc."CuryOrigDocAmt", ARDoc."DocBal", ARDoc."DocDate", ARDoc."Doctype", ARDoc."slsperid", ARDoc."Territory", ARDoc."RecordType", ARDoc."user7", ARTran."CmmnPct", ARTran."CuryTranAmt", ARTran."DrCr", ARTran."ExtCost", ARTran."InvtId", ARTran."JrnlType", ARTran."Qty", ARTran."RefNbr", ARTran."Rlsed", ARTran."S4Future04", ARTran."S4Future05", ARTran."TranAmt", ARTran."TranClass", ARTran."TranDate", ARTran."TranType", ARTran."UnitDesc", ARTran."UnitPrice", RptCompany."CpnyName", RptCompany."RI_ID", Customer."Name", Salesperson."CmmnPct", Salesperson."Name", Salesperson."SlsperId" FROM { oj ((("SOLUSBS02APP"."dbo"."zARDoc_Comm" ARDoc INNER JOIN "SOLUSBS02APP"."dbo"."RptCompany" RptCompany ON ARDoc."Cpnyid" = RptCompany."CpnyID") INNER JOIN "SOLUSBS02APP"."dbo"."Customer" Customer ON ARDoc."Custid" = Customer."CustId") LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."Salesperson" Salesperson ON ARDoc."slsperid" = Salesperson."SlsperId") LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."ARTran" ARTran ON ARDoc."Custid" = ARTran."CustId" AND ARDoc."Refnbr" = ARTran."RefNbr" AND ARDoc."Doctype" = ARTran."TranType"}
Currently, if a new rep takes over for an old ones invoices and accounts...he will also get credit on the report which this query is for. Instead I need to use a table SOShipHeader to be 'date sensitive'. SOShipHeader will have the correct 'SlsperID', but will still need to pull the name from Salesperson."Name"
My guess, would be that I need to wedge the SOShipHeader table between the ARDoc and Salesperson tables?
We are looking for a solution for a client that we will make software for. We are a ISV. After completion of the software, our client then wants to sell this software to their partners, people in that business, etc. We do not want to add any additional cost, by means of them(client(s)) purchasing a server, server software or licensing. Is there any solution that will make this work? A runtime or embedded database solution? If there is a license we need to purchase, what would that be? SQL Express Edition work?
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far. Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end). I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?
Hi, I use the FOR XML AUTO to retrive native XML from a database with: SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')" However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements: XML is Stored: <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> Returns: <databasename> <tablename> <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> </tablename> </databasename>
I have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2. Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName FROM Table1 T1 LEFT JOIN Table2 T2 on T1.CallerNumber = T2.CallerNumber WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName FROM Table1 T1 LEFT JOIN Table2 T2 on T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name' WHERE T1.CallerNumber = 'some number'
1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join). Can I get the goodness of both in one or do I have to create two separate Stored Procedures? Thanks all,Bullpit
I added one crummy column to my table. I updated the stored procedure and added the thing to the aspx page which is my form for adding an article. I have a strong feeling that something is foul over here...Why is it that Visual Studio will not allow me to capitalize the word get and when I delete the () after ShortDesc they immediatlye reappear and the get statement is set to get. Every other one of them, and there are 9 others use GET and there is no () after the public property variable name. Does anyone know the reason for this? Public Property Author As System.String GETReturn _Author End Get Set(ByVal Value As System.String) _Author= ValueEnd Set End PropertyPublic Property ShortDesc() As System.String GetReturn _ShortDesc End GetSet(ByVal value As System.String) End Set End Property
I have a stored procedure that uses a dynamic order by statement. This statement works okay until I try to select ticket's by userEmail which is passed in to my stored procedure as a parameter. Here is the code that works for my dynamic sort order: CREATE PROCEDURE [dbo].[SelectAllTickets] @SortOrder varchar(250) AS SET NOCOUNT ON Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID, b.typeID, b.typeName, c.userID, c.UserFirstName, f.statusID, f.statusName FROM Tickets a LEFT OUTER JOIN Type b ON b.typeID = a.typeID LEFT OUTER JOIN Users c ON c.userID = a.userID LEFT OUTER JOIN Status f ON f.statusID = a.statusID ORDER BY ' + @SortOrder) I modied this procedure to create one in which I select tickets based on the userEmail as a criteria as well.. this one fails due to Incorrect syntax near the keyword 'BY' CREATE PROCEDURE [dbo].[SelectTicketByUser]@SortOrder varchar(250),@userEmail varchar(50) AS SET NOCOUNT ON Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID, b.typeID, b.typeName, c.userID, c.UserFirstName, c.userEmail, f.statusID, f.statusName FROM Tickets a LEFT OUTER JOIN Type b ON b.typeID = a.typeID LEFT OUTER JOIN Users c ON c.userID = a.userID LEFT OUTER JOIN Status f ON f.statusID = a.statusID WHERE a.statusID <> 40 AND c.userEmail = ' + @userEmail + 'ORDER BY ' + @SortOrder) Any ideas on what syntax I should be using? Thanks!
We have a quad sql server that runs OLTP transactions at the rate of 100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix? -- will
I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?
I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example
EdgeID Desc TermType ReltTotID
1Global Edge Model w/ Fwd Earn II T 1 2Short Term Global Edge Model w Fwd Earn IIS 1 3Long Term Global Edge Model w Fwd Earn IIL 1 4Emerging Market Edge Model w Fwd Earn T 4 5Short Term EM Edge Model w Fwd Earn S 4 6Long Term EM Edge Model w Fwd Earn L 4 19SmallCap Edge Model w/ Fwd Earn T 19 20SmallCap Short Term Edge Model w/ Fwd EarnS 19 21SmallCap Long Term Edge Model w/ Fwd EarnL 19 35Global+EM Edge Model w Fwd Earn T 35
The final query result is :
EdgeID Description Short Desc PerID UnivID DefID
1Global Edge Global Developed 500622355938 4Emerging Market Emerging Markets 500632356039 19SmallCap Edge Small Cap Edge 500642364244
I would like it to be :
1Global Edge Global Developed 500622355938 2Short Term Global Developed NULL2355938 3Long TermGlobal Developed NULL2355938 4Emerging Market Emerging Markets 500632356039 5Short Term Emerging Markets NULL2356039 6Long Term Emerging Markets NULL2356039 19SmallCap Edge Small Cap Edge 500642364244 19Short Term Small Cap Edge NULL2364244 19Long Term Small Cap Edge NULL2364244
JOIN OptMod..GO_Models m ON em.EdgeModelID = m.ModelID AND m.ModelType = 'E' AND Status = 1
JOIN OptMod..GO_EdgeModelDisplayParameters emdp ON emdp.EdgeModelID = em.EdgeModelID AND emdp.ParameterName = 'NewEdge32 Screening'
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn ON emdn.EdgeModelID = em.EdgeModelID AND emdn.ParameterName = 'NewEdge32 Display Name'
LEFT JOIN OptMod..GO_ModelUniverses mu ON em.EdgeModelID = mu.ModelID
LEFT JOIN OptMod..vUniverses univ ON mu.UniverseID = univ.UniverseID
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm ON emdp_perm.EdgeModelID = em.EdgeModelID AND emdp_perm.ParameterName = 'NewEdge32 Permissions'
I am looking for a best practice or any suugestion on the best way to install an additional Sql 2005 instance in a cluster environment. We have a production environment that is active/active/passive and need to install an additional instance with a little impact to the current production instances as possible. Ant suggestions or articles I can review?
I have a case. I have 3 numeric fields and 1 category field to be displayed on bar chart reports. the problem is 2 of the 3 numeric fields have significant different scale value. so I need to add additonal scale at the right side of bar chart to represent 1 numeric fields + the 2 other numeric fields at the left side scale (Y). Please advice. Thanks.
