I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.
I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.
The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.
I need to create either a sql query or a procedure and i am not sure how to go about it.Simply i have two fields in a table. 1 for products values and 1 for how many of that product was purchased.What i am wondering is how to calculate this in a sql query.can someone help pleaseIlan
I am deleting a database but I am not seeing any entry in server logs or in event viewer. and same thing is happens when I am deatching a database.Can any body tell me the way through which I can know when a particualr database was deleted or deatched.
I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment
i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly
Hello All, I have 2 tables Trans & Slmast In Trans Tran_type,Tran_amt,Slcode ---------------------------- VES,10000.00,AB01 DBN,20000.00,AB01 CRN,30000.00,AB01 BRT,40000.00,AB01 DBN,10000.00,AB02 CRN,50000.00,AB02 BRT,20000.00,AB03 CRN,30000.00,AB03
In Slmast Slcode,Sldesc -------------- AB01,ABC Ltd AB02,CAB Ltd AB03,BAC Ltd
All transaction related datas will be in Trans for which Slmast is the master. I want to sum those records based on slcode,tran_type for slcode which does not have tran_type with 'VES'
Select (Loccode+Glcode+Slcode) as LGS,Refno,Refdt Into #VST from Trans_Transnarr Where Trtype='VES' and (Refdt>=Convert(Datetime,@SDt,103) and Refdt<=Convert(Datetime,@EDt,103)) and (Cancflg Is Null or Cancflg='R') and Loccode=@Loccode and Glcode=@Glcode
Select Sl.Sldesc,Tr.Loccode,Tr.Glcode,Tr.Slcode, X.Refno,Convert(Varchar,X.Refdt,103) as Refdt, Sum(Case When Tr.Trtype='VES' Then Tr.Tramt Else 0 End) as VES, Sum(Case When Tr.Trtype='DBN' Then Tr.Tramt Else 0 End) as DBN, Sum(Case When (Tr.Trtype='CPM' or Tr.Trtype='BPM') Then Tr.Tramt Else 0 End) as Refund, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='D') Then Tr.Tramt Else 0 End) as JNLD, Sum(Case When Tr.Trtype='SPS' Then Tr.Tramt Else 0 End) as SPS, Sum(Case When (Tr.Trtype='CRT' or Tr.Trtype='BRT') Then Tr.Tramt Else 0 End) as Receipt, Sum(Case When Tr.Trtype='ADV' Then Tr.Tramt Else 0 End) as ADV, Sum(Case When Tr.Trtype='CRN' Then Tr.Tramt Else 0 End) as CRN, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='C') Then Tr.Tramt Else 0 End) as JNLC, Sum(Case When Tr.Trtype='SRT' Then Tr.Tramt Else 0 End) as SRT Into #VOS from Trans_Journal Tr,Slmast Sl,#VST X Where (Sl.Loccode=@Loccode and Tr.Glcode=Sl.Glcode and Tr.Slcode=Sl.Slcode) and (Tr.Loccode+Tr.Glcode+Tr.Slcode)=X.LGS and (Tr.Cancflg Is Null or Tr.Cancflg='R') Group By Tr.Loccode,Tr.Glcode,Tr.Slcode,Sl.Sldesc,X.Refno,X.Refdt
ALTER TABLE #VOS ADD OPDR Float,OPCR Float
UPDATE #VOS SET #VOS.OPDR = S.DRAMT,#VOS.OPCR = S.CRAMT FROM #VOS, (SELECT GLCODE,SLCODE, (CASE WHEN DRCRFLAG = 'D' THEN OPBAL ELSE 0 END) AS DRAMT,(CASE WHEN DRCRFLAG = 'C' THEN OPBAL ELSE 0 END) AS CRAMT FROM OBDATA WHERE LOCCODE=@Loccode and GLCODE =@Glcode) S WHERE #VOS.GLCODE = S.GLCODE AND #VOS.SLCODE = S.SLCODE
Always wondered, now I have the time to ask - we currently backup ALLdatabases and these seem to all grow in size, the MSDB BAK files are now35mb each and growing!!! Can we remove these from our Enterprise Manager?- Master- Model- MSDBCheers, @sh
I am writting a very simple ASP.NET code, I am simlply trying to establish a connection with the database in SQL Server 2000 and than closing the connection but still I am getting an exception,the name of the database is mydatabase, SQL Server 2000 is running in the windows authenticationfiltered=SSPI. The name of SQL Server 2000 running onfiltered=xyz, I checked the name from SQL Server Service Manager.Also I would like to state here that I also tryed to establish a connection using C# and the connection was successfully establish which means that there is no problem with SQL Server also I sucessfully established the connection with MS Access and my ASP.NET application which proves that there is no problem with IIS also.Also please check that the connection string which I am providing is correct or not.The exception which I am getting is as follows: /////////////////////////////////////////////////////////////////////////////// An exception occured while establishing connection Login failed for user 'xyzASPNET'. //////////////////////////////////////////////////////////////////////////// Please also note that I have a user with the name xyz/Administrator in the logins of Sql Server 2000. Also note one more thing that the name of my PC is xyz, I think that there is some problem with the connection string which i am providing so please especially check the connection string that either I have missed some thing or some thing has gone wrong,also please tell that are there any security restructions for ASP.NET to access SQL Server 2000 or some thing like that which I have to remove.The code is as follows: ////////////////////////////////////////////////////////////////////////////////////// <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %>
The same above code is also present in the attachement file data.aspx , please guide me where I am making mistake and please tell me that how can I correct this mistake so that the code executes correctly.Also please tell that what things should be included in the connection string and also please check the connection string of this code.
Need your help !! I have a database say for e.g Test_db . I have data in the form of Image and text in the database . The image data or symbols corresponds to the right text data . This database is being replicated to other servers . The servers are SQL Server 7.0 with SP 3 .
But when this database is replicated , the subscriber does not get the data in the right form . The image and text fields do not correspond properly as it is on the publisher . But sometimes it works .
This is a strange problem and i could not make out what could be the exact problem . Does any body have insight of this problem pl . Any kind of help will be appreciated .
How much RAM is necessary for concurrent SQL connections? My server has 2 Xeon processors, 1GB RAM, a 10GB database size on a TCP/IP box. CPU utilization can run 80-90% and pages/sec can sometimes jump up to 40, especially is the users are running reports from the SQL database. I've been told by some people that I have enough RAM to support the 130 SQL user connections. Others I've been told that I need at least 40MB RAM per user (which means that I'm about 4GB short of RAM). Based on previous entries, I obviously need more, but how much more is the question.
Hi, I have a doubt..... I have a text field that is 255 bytes in lenght and I want the contents of this full field to be split.....on various gouds which may be ',' or and or 'And' etc
This field stores all the skills...
So i have created a search module ....but this search is taking awfully long as u know text field searches are tedious...
Can u please suggest a way I could parse/split the text field to enable faster search....
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person. I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID and e.EMPLOYEE_ONCLOCK <> 'False' and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
Hi folks; I have two SQL machines; SERVER1 (Publisher) and SERVER2(Pull Subscriber). Every thing seems to be working but: I've just imported 17,000 records from another SQL database into a published table of the production server SERVER1 using IMPORT-EXPORT wizard. Now the subscriber doesn't get updated with these records. When i run the merge-agent; the agent just stops with success without pulling the data.
When i set the agent-profile to ROWCOUNT-VALIDATION; it fails at this table.
Publisher db at SERVER1 has recovery-model =FULL Subscriber db at SERVER2 has recvery-model=BULK-LOGGED
I have two related tables in my SQL database that I wish to join as follows:------------------------------------tblCustomersID (pk)Nameetc.tblCustomerManagersID (pk)CustomerID (fk)Manager (this *is* an fk but for the purposes of demonstration isnot)StartDate (indicates the date upon which the manager took / is takingcontrol of the company)------------------------------------Example entries are:tblCustomers1 Microsoft2 Symantec3 BorlandtblCustomerManagers1 1 Barry 01/01/032 1 Peter 01/07/033 2 Norman 01/02/034 3 Terry 01/01/035 3 Peter 01/07/05------------------------------------What I want to do is extract, in one query, a list of all customers andtheir *current* associated manager, so the result set today would be:Microsoft Peter 01/07/03Symantec Norman 01/02/03Borland Terry 01/01/03Currently I have:SELECT [Name], [Manager], [StartDate]FROM tblCustomersINNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =tblCustomers.[ID]WHERE [StartDate] <= GETDATE()ORDER BY [Name], [StartDate] DESCbut this obviously returns multiple entries for customers having managersprior to today eg:....Microsoft Peter 01/07/03Microsoft Barry 01/01/03....I know this is a simple question but I cannot think of a way of doing itwithout making the query extremely complicated.Any help is appreciated,Thanks,df
Need some suggestions for senior management for DR Purposes:
Background:
WSS/MOSS2007 is being used as a Document Management solution.
17 Servers geographically dispersed around the UK. Each server runs WSS 3, SQL Server 2005 and IIS. Each server is linked into a PiP cloud via 2MB MPLS.
At each location; We are looking at 20 core databases; each pre-sized to 10GB. If I take one site as an example, the previous nights backup totalled 135GB.
The company has taken a centralised view on backup's, so SQL Server Data and Log files are replicated using Double-Take to a central location where by the files are taken onto tape daily (Full backup of all files).
As a precaution, I take a Full SQL Server backup daily and also Tran Logs every 4 hours locally and keep it there for 2 days; however if the site goes boom I loose those, so for this purpose; please forget they exist.
As I expect; when I restore the mdf and ldf files from tape, I will get errors when I attach those files into SQL Server for transactional inconsistencies which I'm well aware of.
Other options I've considered are:
1) DB Mirroring. Not a bad option, but still have to get the DB to the Mirror Server in the first place. Also DB Mirroring is not recommended for more than 10 mirrored databases.
2) Log Shipping. Same issue as above; Have to get the data here in the first place. Then once Log Shipping is setup; if I have a failure; I need to start the whole lot off again.
3) Transactional Replication. Issue is with the initial replication getting the data from A to B, then if I need to use it in a DR situation; I will get issues saying this table is being used for replication. This can be worked around, but it's a not a quick process...
4) 3rd Party Backup Compression. E.G. Litespeed; Redgate SQL Backup, etc. Good; Tests have shown a 42% compression for us, however if I refer to the earlier example of 135GB, this compresses to 81GB. Throw in the theoretical max for a 2MB link of 19GB / 24 Hours, this would take 4 Days to copy.
Other thoughts I've come up with are:
A) Split the tables into different file groups; not sure how easy this would be as the DB's and Tables already exist.
B) Full/Diff/Tran. Still have the issue of scheduling the full backup over the weekend and taking 4 days to get here.
C) Local Tape Backups. Issue is relying on someone to change the tape on a daily basis. It's not centrally managed and how do we restore in a DR situation ?
Have setup a sql 2005 x64 sp2 a/p cluster and installed a cert from our own cert. authority. Enabled "Force encryption" on the server config.
Can connect connect with all my clients exept one.
If I try to connect with this specific client the errorlog on server shows:
Error: 17835, Severity: 20, State: 1. 2007-05-09 18:33:41.99 Logon Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 138.xxx.xxx.xx7]
I'm trying to install SQL Express Adv and it warns me I need IIS to support certain features for Reports. Despite hours of searching through the MS website, I can not figure out what features those are to determine if I want put in IIS. Furthermore, I can not even figure out how to get IIS on my system. I need to do this for my laptop, which is XP Pro 2002 SP2. I also need to do this for my desktop, which is XP Home 2002 SP2. Neither of them have IIS installed that I can tell (and according to the initial install check done by SQL Express Adv it is not installed).
I required a suggestion from your side Actually I am working in MS Access from last 6 months. Now I want to convert my database into SQL Server I have a knowledge of SQL Server and I don't have any problem with this.
But what I want to know how to maintain a report in SQL Server I don't have any knowledge for creating a report. My freinds suggest me a Visual Studio 2005 for creating a Crystal Report.
So Please suggest me it is helpful or not and onething more is that I have to work on SQL Server 2000 or SQL Server 2005 which is more beneficial.
Please reply me for this Waiting for your any kind of reply Please Thanks Ashish
I am trying to do a select with the closest match.
Code Snippet
CREATE TABLE [#Person]
(
[PersonId] tinyint IDENTITY(1,1) NOT NULL
,[Height] char(2) NOT NULL
,[Weight] char(3) NOT NULL
,[Age] varchar(3) NOT NULL
,[HairColor] varchar(7) NOT NULL
,[EyeColor] varchar(7) NOT NULL
);
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('71', '150', '23', 'Brown', 'Blue');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('70', '190', '22', 'Blonde', 'Brown');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('69', '140', '20', 'Black', 'Brown');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('65', '150', '21', 'Brown', 'Green');
SELECT * FROM [#Person]
WHERE
([Height] > '66' AND [Height] < '72')
AND ([Weight] > '150' OR [Weight] < '180')
AND ([Age] > '20' OR [Age] < '25')
AND ([HairColor] IN ('Brown', 'Blonde', 'Black'))
AND ([EyeColor] IN ('Blue', 'Brown', 'Green'));
DROP TABLE [#Person];
This simple example works great. But what if I wanted everybody who met 4 of the 5 conditions? I tried to think of a counting solutions, and then order by the count but I could quite get there. Any help would be greatly appreciated. My live data is actual sales records with 20 some odd record types. I am hoping any solutions I find will scale well.
I have application running on Windows XP which uses the date format of en-US(MMDDYYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)
