SQL Statement Troubles Finding Maximums.
Aug 16, 2006
Hello everyone!
Here's the deal. I have a table that contains data for contracts each time they are brought to our attention for a review. The first column in the table contains the contract number, the second column contains which review number the data is associated with (the first time it was reviewed, the second time, third, etc.). I need to make a subtable that contains the records from the last review of each contract. The problem is that, of course, each contract can and will have different numbers of reviews than most other contracts. I know how to use the max funtion to return the largest review number of the table, but this doesn't help because not all contracts have that many reviews. Is there any way I can accomplish this using a single SQL statement?
Thanks for the help!
Ryan
View 5 Replies
ADVERTISEMENT
Feb 28, 2008
I have just about figured everything out except for 2 things.
First sometimes there will not be a ‘177’ record. How can I structure my IF statement so I can bypass the code. Other wise it creates a hdr and a blank detail record? Or is there another conditional verb I can use that might be better?
Second I can’t get my cnt to show up in my hdr records. The print statement is not even showing them. What am I doing wrong?
Any ideas?
Thanx in advance,
Here's my code:
DECLARE @Cnt int
DECLARE @Sys varchar (4);
DECLARE @vwSys varchar (4), @vwPrin varchar (4)
/* Define Cursor */
/*Works like an ARRAY*//*holds current record */
Declare vwCursor CURSOR FOR
SELECT Sys, Prin FROM dbo.vwPrin
ORder by Sys, Prin
open vwCursor
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print '1st FETCH '
Print @vwSys
Print @vwPrin
SET @Sys = @vwSys
WHILE @@Fetch_Status = 0--34
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP(1) '9', @Cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '020' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
Print @cnt
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, Clerk_Code, Memo_Text)--45
SELECT Account_Number, Transaction_Code, Clerk_Code, Memo_Text
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '115' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
[b]IF (SELECT Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin FROM dbo.tblTrans)[/b]
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, SubTrans_2, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_Id, Op_Code
FROM dbo.tblTrans--55
WHERE dbo.tblTrans.Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
/*Continue FETCH Command until EOF */
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print ' 2nd FETCH '
Print @cnt
END
--Print @vwSys
--Print @vwPrin
END
CLOSE vwCursor
DEALLOCATE vwCursor
END
View 8 Replies
View Related
Jan 12, 2007
Im having a slight problem with my Like statement, I'm just building a simple search/match routine. I have the following SQL statement SELECT TOP(4) tags FROM post WHERE atags LIKE '%test%'that statement is ok, it will return many results with test in it but if I add more then one search word like LIKE '%+test test2+%' or LIKE '%+test%test2+%' All I seem to get is results that only have the exact two tags not any others that may have one of them.How can I get it to display all records that have an instance of any of those words. Any help would be much appreciated.
View 14 Replies
View Related
Mar 28, 2006
Hi, I just want to know the following.
Is it posible to write a SQL statement that searches all values in a table where the
e.g:
"Jobid = 1" and sort the result set according to the "CategoryID"?
then if you want to display the values, you can populate them according to the "CategoryID"?
and then also write the result set to a "text file", and catagorize everything according to the "categoryID" in the following format???
Category Number: 8 Description: Site
Date Labour Hours Rate Subtotal
10/05/2005 Artisan 20 95.00 1,900.00
10/06/2005 Labourer 7 45 315.00
Category Total 2,215.00
And then after that the next "categoryid"
Thank You, I really hope you understand what I mean.
your help will be greatly appreaciated.
View 4 Replies
View Related
Apr 11, 2000
Hey Everybody,
Can anyone help me out in finding the above stuffs in
SQL Server?
1.Maximum Transaction volume on SQL Server 7.0
2.Maximum Transaction capacity on SQL Server 7.0
3.Transaction and user limits on SQL Server 7.0
Our Server is
Compaq / NTServer / 2 processor @500 + MHZ /
0.5 - 1 GB memory /
SCSI 2 Ultra wide (not fiber)/
Raid 5
I really appreciate your help.
Thank You.
Marx
View 1 Replies
View Related
Jun 2, 2006
Hi,I'm running SQL Profiler on an SQL Server 2000 database. I see that onestored procedure gets repeatedly executed having a handle of '1'. Thisquery takes a long time to complete.How do I find what the text of the stored procedure is? I cant see anyhandle being created (using sp_prepare) with an id of '1' in theprofiler. Is there any way to force the server to re-prepare allstatements so that I can see the statement text and its preparation inSQL Profiler?Cheers,Birju
View 3 Replies
View Related
Mar 12, 1999
Hey Swynkers,
I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:
WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0
Any suggestions?
Thanks,
Fenderson
View 3 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
View Related
Feb 18, 2014
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
View 3 Replies
View Related
Aug 9, 2005
We are trying to move data from a product called TABLEBase that runs onthe mainframe and we are experiencing problems trying to BCP the datainto SQL Server. The SQL server we are tying to BCP into is SQL 2000.We are using BCP from a command prompt.Here is what happens.the first time it doesn't load anything and we get no messages.The onlyway I got it to work was to run BCP and have it create a ForMaT file.I compared the ForMat file I created by hand,to the ForMat file Icreated from BCP they are identical. (I used a product called beyondcompare to check it). After I created the ForMaT file and loaded thedata, I then deleted the data and ran it with the original ForMaT file(no changes) and the data loaded fine this time.Has anyone else run into this before? It is driving us nuts!
View 6 Replies
View Related
May 16, 2007
I finally got an SSL cert to show up!!!
But when I try to connect, I get this pesky error message:
provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.
Does anyone know how I turn on ' trusting myself ' or get this to go away?
View 3 Replies
View Related
Jun 11, 2007
Hi I'm currently having some troubles formulating an SQL query. The idea is this (in psuedo code): Select * From datatableIf (@input1 = "Hello") Then ( WHERE @input1 = column1 AND column4 = 1 )ELSE ( WHERE @input1 = column1 AND @input2 = column2 AND @input3 = column3 ) Basicly I need it to perform the first filter if input1 is very specific or otherwise perform the the second filter Thanks
View 5 Replies
View Related
Apr 17, 2004
Hi,
I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.
CREATE PROCEDURE spScanUpdate
@id varchar (100),
@name varchar (75)
AS
DECLARE @GUID_ID as uniqueidentifier
SELECT@GUID_ID = CAST ( @id as uniqueidentifier )
UPDATE tScan
SET name = @name
WHEREid = @GUID_ID
GO
View 5 Replies
View Related
Jun 9, 2004
Hi there,
I am trying to code a button that queries a database, and as a result of the query will re-direct a user to a different page. I have put together the following code, but know it is so wrong. Any suggestions on how to develop this would be greatly appreciated! I need it to be done through a button rather than a session etc.
Sub button_Click (ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim myData AS SqlConnection
Dim cmdSelect as SqlCommand
myData = New SqlConnection(ConnectionString)
myData.open()
cmdSelect = new SqlCommand("SELECT payDate FROM tblPayments WHERE userName = Matt", myData)
IF cmdSelect >= Date.Now() Then
Response.Redirect("youraccount.aspx")
Else
Response.Redirect("payment.aspx")
End If
myData.close()
End Sub
Thanks in advance for your help,
TCM
View 6 Replies
View Related
Dec 22, 2004
Hi guys I am having troubles writting a SQL query. Here is the situation I have two phrases and I have to do a search on first three letters of each word in the first phrase and see if it matches any word in the second phrase.
For example
Phrase 1: "The Company Corporation"
Phrase 2: "Correy and sons"
This should be a match since "Cor" from Corporation matches with "Cor" from Correy.
Any help....
View 5 Replies
View Related
Mar 1, 2004
I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.
What can I do?
I hope your answer
Thanks
Cristopher Serrato
View 5 Replies
View Related
Jun 17, 2008
I'm having a problem creating a isnull statement.
I want to do two things I want to list all of the accounts that have a null value in a numeric field. And I want to update those accounts to 0.00.
I tried:
select Account_Num, isnull(TotalDDMFEE, 0)
FROM Addr_20080402
But it returned all records
For the update I tried:
update Addr_20080402
CASE
when TotalDDMFEE = ' ' then TotalDDMFEE = 0.00
AND
update Addr_20080402
CASE
when TotalDDMFEE = isnull(TotalDDMFEE, 0) then TotalDDMFEE = 0.00
Any ideas how I should have written these two queries?
Thanx,
Trudye
View 3 Replies
View Related
Apr 18, 2007
Hi all,
the system I am working on was upgraded recently from SQL2000 to SQL2005, and it's generating strange issues after then.
The maintenace plan stopped working (the job was going to run forever if I didn't stop them), and I was able after some tests to understand the cause: It's impossible to backup two of the databases I have on the server.
If I try to di it manually I got this:
ERROR 3026
"Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized (...)"
You could think it's because someone is using the DBs at the same time, but I tried to Execute the Plan at night and the morning before it was still there running since 12 hours. If I cut from the maintenance plan these two databases the job runs just fine.
A strange thing, maybe a relevant clue, is that when I go to the tasks-->Backup and the SQL Server suggests a name for the new bak file instead of having a .BAK file (as for the others DBs I have there - something like 10) It tries to put the backup in a .TRN file (the sequence number seems to be correct - I have myDB_backup_200704181200.TRN instead of myDB_backup_200704181200.BAK); what makes me think this is a clue is that all this happens only for these two databases I cannot backup.
Ring any bells?
Thanks in advance,
Cheers,
Giovanni
View 7 Replies
View Related
Sep 12, 2006
We have an application that connects to a remote site's SQL server using aVPN client. It also uses a local MS SQL database located on one of ourservers. It seems once the VPN connection is established the client machineis always looking to the remote VPN connection/server for it's data. Itested this out by going into the ODBC applet and doing a "TEST CONNECTION"with the VPN client closed. The response was immediate and the connectionworked. Once I established the Internet VPN connection the connectionrepeatedly failed after a several seconds while it appeared to be looking onthe Internet for a local server.What can we do to fix this problem.Thanks,Charles MacleanMTS, Inc.
View 1 Replies
View Related
Oct 13, 2006
Hi guys,
I have an sql query I am getting stuck on for a university assignment. Some background:
The tables of the Database in question are listed below, primary keys are underlined:
Sports (SportCode, SportName)
Events (EventID, SportCode, EventDescription)
Heats (HeatEventID, EventID, HeatCategory)
Fixtures (FixtureID, HeatEventID, FixtureTime, FixtureDate, Location, Field)
Universities (UniID, UniversityName, StateCode)
RegisteredMembers (RegMemberID, MembershipType, FirstName, LastName, Address, Postcode, Gender, UniID)
Teams (TeamID, Division, UniID, TeamName)
TeamMembers (TeamID, RegMemberID)
Results (FixtureID, TeamID, Score, Versus)
Part of this assignment is to use a function to check, when adding a new fixture, that the teams havent: played each other before, or planned to play against each other in the future.
This is done via the Results table, I can pass the two TeamID's from the drop down boxes in the MS Access form into the function, I am wondering what is the best way to find out if the two teams have played each other before? If they have played, or will play each other, there will be two records, one for each team connected to the one FixtureID.
View 1 Replies
View Related
Oct 23, 2007
I am trying to export a table from SQL 2005 to excel and I recieve this error:
Error 0xc002f210: Prepearation SQL Task:Executing the query "CREATE TABLE...
Here is the error report:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "CREATE TABLE `customer` (
`company` LongText,
`custid` LongText,
`custnum` Long,
`name` LongText,
`address1` LongText,
`address2` LongText,
`address3` LongText,
`city` LongText,
`state` LongText,
`zip` LongText,
`country` LongText,
`resaleid` LongText,
`salesrepcode` LongText,
`territoryid` LongText,
`shiptonum` LongText,
`termscode` LongText,
`shipviacode` LongText,
`printstatements` Byte,
`printlabels` Byte,
`printack` Byte,
`fincharges` Byte,
`credithold` Byte,
`groupcode` LongText,
`discountpercent` Decimal(6,2),
`primpcon` Long,
`primbcon` Long,
`primscon` Long,
`comment_` LongText,
`estdate` DateTime,
`faxnum` LongText,
`phonenum` LongText,
`taxexempt` LongText,
`aracctid` LongText,
`markupid` LongText,
`billday` Long,
`oneinvperps` Byte,
`defaultfob` LongText,
`creditincludeorders` Byte,
`creditreviewdate` DateTime,
`creditholddate` DateTime,
`creditholdsource` LongText,
`creditclearuserid` LongText,
`creditcleardate` DateTime,
`creditcleartime` LongText,
`edicode` LongText,
`obsolete803_editest` Byte,
`obsolete803_editranslator` LongText,
`character01` LongText,
`character02` LongText,
`character03` LongText,
`character04` LongText,
`character05` LongText,
`character06` LongText,
`character07` LongText,
`character08` LongText,
`character09` LongText,
`character10` LongText,
`number01` Decimal(20,9),
`number02` Decimal(20,9),
`number03` Decimal(20,9),
`number04` Decimal(20,9),
`number05` Decimal(20,9),
`number06` Decimal(20,9),
`number07` Decimal(20,9),
`number08` Decimal(20,9),
`number09` Decimal(20,9),
`number10` Decimal(20,9),
`date01` DateTime,
`date02` DateTime,
`date03` DateTime,
`date04` DateTime,
`date05` DateTime,
`checkbox01` Byte,
`checkbox02` Byte,
`checkbox03` Byte,
`checkbox04` Byte,
`checkbox05` Byte,
`currencycode` LongText,
`countrynum` Long,
`langnameid` LongText,
`bordercrossing` LongText,
`formatstr` LongText,
`btname` LongText,
`btaddress1` LongText,
`btaddress2` LongText,
`btaddress3` LongText,
`btcity` LongText,
`btstate` LongText,
`btzip` LongText,
`btcountrynum` Long,
`btcountry` LongText,
`btphonenum` LongText,
`btfaxnum` LongText,
`btformatstr` LongText,
`parentcustnum` Long,
`taxregioncode` LongText,
`iccust` Byte,
`contbillday` Long,
`emailaddress` LongText,
`shippingqualifier` LongText,
`allocprioritycode` LongText,
`linkportnum` Long,
`webcustomer` Byte,
`customertype` LongText,
`nocontact` Byte,
`territorylock` Byte,
`custurl` LongText,
`pendingterritoryid` LongText,
`extid` LongText,
`consolidateso` Byte,
`bill_frequency` LongText,
`creditincludepi` Byte,
`globalcust` Byte,
`ictrader` Byte,
`taxauthoritycode` LongText,
`externaldeliverynote` Byte,
`globalcredincord` Byte,
`globalcredincpi` Byte,
`globalcurrencycode` LongText,
`externalid` LongText,
`globalcredithold` LongText,
`globallock` Byte,
`checkduplicatepo` Byte,
`creditlimit` Decimal(15,0),
`custpilimit` Decimal(15,0),
`globalcreditlimit` Decimal(15,0),
`globalpilimit` Decimal(15,0),
`docglobalcreditlimit` Decimal(15,0),
`docglobalpilimit` Decimal(15,0),
`rfqattachallow` Byte,
`discountqualifier` LongText,
`number11` Decimal(20,9),
`number12` Decimal(20,9),
`number13` Decimal(20,9),
`number14` Decimal(20,9),
`number15` Decimal(20,9),
`number16` Decimal(20,9),
`number17` Decimal(20,9),
`number18` Decimal(20,9),
`number19` Decimal(20,9),
`number20` Decimal(20,9),
`date06` DateTime,
`date07` DateTime,
`date08` DateTime,
`date09` DateTime,
`date10` DateTime,
`date11` DateTime,
`date12` DateTime,
`date13` DateTime,
`date14` DateTime,
`date15` DateTime,
`date16` DateTime,
`date17` DateTime,
`date18` DateTime,
`date19` DateTime,
`date20` DateTime,
`checkbox06` Byte,
`checkbox07` Byte,
`checkbox08` Byte,
`checkbox09` Byte,
`checkbox10` Byte,
`checkbox11` Byte,
`checkbox12` Byte,
`checkbox13` Byte,
`checkbox14` Byte,
`checkbox15` Byte,
`checkbox16` Byte,
`checkbox17` Byte,
`checkbox18` Byte,
`checkbox19` Byte,
`checkbox20` Byte,
`shortchar01` LongText,
`shortchar02` LongText,
`shortchar03` LongText,
`shortchar04` LongText,
`shortchar05` LongText,
`shortchar06` LongText,
`shortchar07` LongText,
`shortchar08` LongText,
`shortchar09` LongText,
`shortchar10` LongText,
`allowaltbillto` Byte,
`demanddeliverydays` Long,
`demanddatetype` LongText,
`demandaddleadtime` Long,
`demandaddaction` LongText,
`demandchangeleadtime` Long,
`demandchangeaction` LongText,
`demandcancelleadtime` Long,
`demandcancelaction` LongText,
`demandnewlineleadtime` Long,
`demandnewlineaction` LongText,
`demandqtychangeleadtime` Long,
`demandqtychangeaction` LongText,
`demandchangedateleadtime` Long,
`demandchangedateaction` LongText,
`tradingpartnername` LongText,
`resdelivery` Byte,
`satdelivery` Byte,
`satpickup` Byte,
`hazmat` Byte,
`doconly` Byte,
`refnotes` LongText,
`applychrg` Byte,
`chrgamount` Decimal(16,2),
`cod` Byte,
`codfreight` Byte,
`codcheck` Byte,
`codamount` Decimal(16,2),
`groundtype` LongText,
`notifyflag` Byte,
`notifyemail` LongText,
`declaredins` Byte,
`declaredamt` Decimal(16,2),
`periodicitycode` Long,
`servsignature` Byte,
`servalert` Byte,
`servhomedel` Byte,
`deliverytype` LongText,
`servdeliverydate` DateTime,
`servphone` LongText,
`servinstruct` LongText,
`servrelease` Byte,
`servauthnum` LongText,
`servref1` LongText,
`servref2` LongText,
`servref3` LongText,
`servref4` LongText,
`servref5` LongText,
`earlybuffer` Long,
`latebuffer` Long,
`demandunitpricediff` Byte,
`demandunitpricediffaction` LongText,
`excfromval` Byte,
`addressval` Byte,
`rebatevendornum` Long,
`rebateform` LongText,
`creditcardorder` Byte,
`demandcheckforpart` Byte,
`demandcheckforpartaction` LongText,
`changedby` LongText,
`changedate` DateTime,
`changetime` Long,
`chargecode` LongText,
`individualpackids` Byte,
`intrntlship` Byte,
`certoforigin` Byte,
`commercialinvoice` Byte,
`shipexprtdeclartn` Byte,
`letterofinstr` Byte,
`ffid` LongText,
`ffcompname` LongText,
`ffaddress1` LongText,
`ffaddress2` LongText,
`ffaddress3` LongText,
`ffcity` LongText,
`ffstate` LongText,
`ffzip` LongText,
`ffcountry` LongText,
`ffcountrynum` Long,
`ffphonenum` LongText,
`nonstdpkg` Byte,
`deliveryconf` Long,
`addlhdlgflag` Byte,
`upsquantumview` Byte,
`upsqvshipfromname` LongText,
`upsqvmemo` LongText,
`upsqvemailtype` LongText,
`ffcontact` LongText,
`etcaddrchg` Byte,
`PROGRESS_RECID` Decimal(19,0),
`PROGRESS_RECID_IDENT_` Decimal(19,0)
)
" failed with the following error: "Too many fields defined.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)
- Copying to `customer` (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
I am new to SQL so any help would be greatly appreciated.
View 1 Replies
View Related
May 30, 2007
I am getting problem when i try to browse the reports, it displays the following error messages:
-
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
Refresh button, or try again later.
A name was started with an invalid character. Error processing resource 'http://localhost/Reports/'. Line 1, Position 2 <%@ Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.HomePag...The installation process seems to work without any issues, however we cannot see http://localhost/reportserver/
View 7 Replies
View Related
Mar 18, 2007
I've installed SQL server express on a remote server. Using remote desktop, I can log in using Windows Authetication.
But I also want to be able to login remotely. Using Server Management Express I've added a new login and specified the password for SQL server authentication. My problem is that the password keeps getting changed to a mystery 15 character value.
What's going on?
View 1 Replies
View Related
Jan 16, 2008
Hello everybody!
I'm fighting with report's HTML view for a week, and i cannot overcome it. The problem is wrong pager spliting. Having changed a lot of controls properties i acheived correct pagination while exporting to PDF format (it gives me 4 pages in A4 format).
But HTML pagination haven't changed at all. It gives me 2 large pages. And whatever i change it gives me the same 2 pages.
Has somebody deal with that?
Thanks for any help!
View 3 Replies
View Related
Nov 11, 2006
I'm confused. I am trying to return the results of a stored proc to a web service I have created using Visual Web Developer Express. The stored proc works correctly and can execute it from SQL Server. The last line of the stored proc is a simple select. If I comment out everything in the stored proc except the final SELECT statement, I can successfully return the data.using the "INVOKE" during my debug from my service page. I can enter the parameters and it works fine. BUT when I try to call the full stored proc from my service, I don't get anything. I am confused - is there something different you have to do when using stored procs? It appears to execute from my code, as I can see the data in the table change when I call the proc. But I can't seem to return the results unless I comment out everything in the stored proc except the SELECT....anyone else run across this? What am I missing? Code snippet is below...thanks in advance. headjoog <WebMethod()> _
Public Function ReturnData(ByVal SomeKey As Int16, ByVal StartDate As Date, ByVal EndDate As Date, _
ByVal aName As String, ByVal SomeNum As Int16, ByVal AnotherNum As Int16) As MyData.MyData_DataTable
Dim theSet As New MyData()
Dim theAdapter As New MyData_TableAdapters.MyDataTableAdapter()
Return theAdapter.GetDayPatterns(SomeKey, StartDate, EndDate, aName, SomeNum, AnotherNum)
End Function
View 1 Replies
View Related
Dec 9, 2004
I have a sproc that does an insert/update into tables HS_Entity and HS_Content. When I run it from Sql Query Analyzer, it executes no problemo. When I call it from my asp.net app, using the exact same (hard-coded) parameters, I get this error:
"Object must implement IConvertible."
I understand this is normally a conversion issue, but I've checked each value a million times!
========================================================
-- ENTITY TABLE
@name nvarchar (50),
@keywords nvarchar(500),
@permissions nvarchar (12),
@isActive bit,
@createdBy nvarchar(50),
@updatedBy nvarchar(50),
-- CONTENT TABLE
@contentPk int,
@categoryPk int,
@contentTitle nvarchar(500),
@content nvarchar(2500)
as
declare @entityPk int
declare @version int
declare @updated smalldatetime
declare @created smalldatetime
set @updated = getDate()
set @created = getDate()
if @contentPk > 0
-- UPDATE ENTITY AND CONTENT: INSERTS NEW ENTITY AND CONTENT AND INCREMENTS VERSION
begin
set @entityPk = (select entityPk from HS_Content where contentPk=@contentPk)
set @version = (select version from HS_Entity where entityPk=@entityPk)-- + 1
exec HS_Entity_IU
10, --1
@version,
@name,
@keywords,
@permissions,
@isActive,
@created,
@updated
update HS_Content set
entityPk=@entityPk,
categoryPk=@categoryPk,
contentTitle=@contentTitle,
content=@content
where contentPk=@contentPk
end
else
-- INSERT ENTITY AND CONTENT: INSERTS NEW ENTITY AND CONTENT
begin
set @version = 1
exec HS_Entity_IU
10,
@version,
@name,
@keywords,
@permissions,
@isActive,
@created,
@updated
set @entityPk = (select top 1 entityPk from HS_Entity order by entityPk asc)
insert into HS_Content values
(@entityPk, @categoryPk, @contentTitle, @content)
end
==============================================
Here is a portion of the c# code:
==============================================
paramCol[i] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
paramCol[i].Direction = ParameterDirection.Input;
paramCol[i++].Value = name;
paramCol[i] = new SqlParameter("@keywords", SqlDbType.NVarChar, 500);
paramCol[i].Direction = ParameterDirection.Input;
paramCol[i++].Value = keywords;
paramCol[i] = new SqlParameter("@permissions", SqlDbType.NVarChar, 12);
paramCol[i].Direction = ParameterDirection.Input;
paramCol[i++].Value = permissions.ToString();
paramCol[i] = new SqlParameter("@isActive", SqlDbType.Bit, 1);
... etc
try {
SqlHelper.ExecuteNonQuery(Global.ConnectionString, "HS_Content_IU", paramCol);
} catch (Exception ex) {
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
==============================================
thanks for your help!
Brent
View 2 Replies
View Related
Jan 23, 2005
I've got a problem here logging on mssql server
It seems to me that the problem is in ConnectionString
When I add new connection to the project via Microsoft OLEDB provider for SQL Server
I use login 'sa' and password 'pwd'. Then if I check 'Allow saving password' (or something like that - i use other language) everything works fine, but if i don't at last I get an error message
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'sa'.
Source Error:
...
Line 47: Me.SqlCommand1.Connection.Open()
...
Can I use connection string with no explicit inclusion of password?
TIA
View 2 Replies
View Related
Jan 26, 2006
Hi All,
Can anybody please tell me if a query such as this (Valid in MS Access)
can work in SQL Server:
SELECT Description, Sum(Total) FROM (
SELECT Description, Total FROM Table_A
UNION ALL
SELECT Description, Total FROM Table_B
UNION ALL
SELECT Description, Total FROM Table_C
)
GROUP BY Description
The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.
Any insight would be greatly appreciated. Thank you.
View 2 Replies
View Related
Sep 13, 2007
This what happened in Vista. I just set up my SqlServer Express (SSMS), attached my databases: all of them except one.
The reason why it happened is that when I copied my folder with DBs from XP to Vista I found out that one mdf file was missing.There was the log file for that DB intact. It is a somewhat secondar database (at leas temporary until I will have resolved some tricky issues in C# app, then it will get full load). When I bagan chenging my conn strings in C# to accomodate new Vista's tastes I realized that that DB was not attached to VS 2005 either. I began to investigate.
I went to XP and checked thoroughly. First I thought this DB (mdf file for this DB) was stored in another partition. It does not seem to be the case. The entire disk was searched: nothing found.
The weird thing is: I can see this DB in SSMS in XP with ALL tables intact. I opened the design and also opened the tables. The values are all there. This is my first question: how can it be explained? Could it be that all the tables are in the log file for this DB?
So at this point I have no way to copy the DB from XP to Vista the way I did before with other DBs.
The next problem is this:
I went to Vista and tried to restore this DB from a backup. The backup may not be too fresh but I do not care. All I need is the structures and stored procedures. Vista gives me hard time.
This is my code:
RESTORE DATABASE [intraDay] FROM DISK = 'J:SqlServerBackUpsIntraDayintraDayFullRM.BAK' WITH RECOVERY,
TO 'C:intraDay.mdf'; -- this is just to try the RESTORE in principle. I will restore the DB to a different folder.
GO
I get an error that
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'TO'.
I understand that the MOVE clause may be missing. I tried that but got in trouble with it as well.
Will appreciate any help.
I've never done RESTORE before.
Thanks.
View 3 Replies
View Related
Sep 20, 2007
I used MS SQL Server Management Studio Express to back up my SQL Server 2005 database called "PMDB" on a server in my office to a jump drive. Then, I removed the jump drive from the server and plugged it into my laptop. I then tried using MS SQL Server Management Studio Express to restore "PMDB" to my laptop SQL Server 2005 Express Edition (instance "Primavera") to a database called "pmdb$primavera". I've had several issues:
1. pmdb$primavera is now "locked up" "in the middle of a restore". I can't seem to unlock it even by rebooting my laptop.
2. when I execute the restore (I've used several command sequences...) I get a message that there are additional "families" restore is waiting for. I don't understand that.
3. I tried restoring to a completely new database (in the same instance...), but got the "family" problem. The query just hangs forever. When I cancel it, it hangs the database (see #1).
4. I now have two databases in the "Primavera" instance on my laptop in the "restoring" state. I can't get to either one of them.
I'm desperate. I have a customer presentation on Thursday where I must have the database working. Help!
Here's one of the queries I executed.... though I tweaked items here and there for 3 and 4 above.
restore database pmdb
from disk='f:databasebackupPMDB-BAK.BAK'
with recovery,
move 'pmdb_Dat' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_DAT.MDF',
move 'pmdb_log' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_LOG.LDF'
go
The issue seems to be #2 above each time.
View 4 Replies
View Related
Mar 29, 2006
Hi to all.I'm trying to transact from an ASP.NET application in a machine with an SQL Server 2005 placed in a different machine. I can connect without problems from my application to the database, but the problems are when I need to transact with it. The error message I receive is "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool." (at code I'm using the TransacionScope object).On the first machine, where we have the Visual Studio 2005 and the application web environtment is placed, is an standard Windows XP Professional. On the MSDTC configuration I have checked outgoing and the firewall is down. On the second machine, where is placed my SQL Server 2005, I have all the checkboxs checked at the MSDTC Configuration, and I have the Windows Firewall enabled with the SQL Server and the MSDTC as exceptions, with the ports 1433 and 135 as exceptions too. At the SQL Server Configuration Manager I have as enabled the TCP/IP, the Named Pipes and Shared Memory at Protocols for MSSQLSERVER.When I'm using an SQL Server 2000 database the code works fine and I don't have problems.Any one can help me?Thanks in advance.Jesús
View 12 Replies
View Related
Nov 28, 2007
Hey,
I am running into trouble performing a second join on a column while using aggregate functions over that table.
What I think is happening is that the aggregate values (sum, avg, count) are being multiplied because I am joining on the same table a second time, but I don't know how to fix or avoid this problem.
Here's the query:
---
SELECT
CAST(ro.xing_tc_no AS INT) AS loc_id,
SUM(ro.total_fatal) AS tot_fatal,
SUM(ro.total_serious) AS tot_serious,
SUM(ro.total_minor) AS tot_minor,
COUNT(ro.report_no) AS tot_accidents,
AVG(ro.NO_OCCUPANTS) AS num_occupants
FROM
UserHotspots uh
LEFT JOIN rods_k_crossing AS rkc
ON rkc.location_id = uh.CrossingID
LEFT JOIN rods_occurrence AS ro
ON rkc.tc_location_nbr = ro.xing_tc_no
GROUP BY
ro.xing_tc_no
---
The second join is also performed on rods_k_crossing and that is when the sums, avgs, and counts get multiplied. If I take that second join out the numbers come out fine.
Thanks for any help!
View 2 Replies
View Related
Apr 17, 2008
Hi!
Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields.
May be some one could help me with this?
CREATE FUNCTION [dbo].[GetPagedStatistics]
(
@start_index int,
@count int,
@condition nvarchar(255),
@order_field nvarchar(255),
@date_from datetime,
@date_to datetime )
RETURNS @total_stat TABLE (
username nvarchar(255),
first_name nvarchar(255),
last_name nvarchar(255),
properties_count int,
enquiries_count int,
visits_count int,
id_user int)
BEGIN
INSERT @total_stat
SELECT
top (@count)
dbo.users.username,
dbo.users.first_name,
dbo.users.last_name,
ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count,
ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count,
ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count,
dbo.users.id_user
FROM
dbo.property_statistics RIGHT OUTER JOIN
dbo.advertisement RIGHT OUTER JOIN
dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN
dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON
dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisement
WHERE
1=@condition and
(dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and
(
(dbo.enquiry_emails.creation_date <= @date_to
and dbo.enquiry_emails.creation_date >= @date_from
and dbo.property_statistics.view_date <= @date_to
and dbo.property_statistics.view_date >= @date_from ) or
(dbo.property_statistics.view_date is null) or
(dbo.enquiry_emails.creation_date is null)
) and
(ISNULL(dbo.advertisement.id_parent, 0) = 0)
GROUP BY
dbo.users.username,
dbo.users.first_name,
dbo.users.last_name,
dbo.users.id_user
order by
case when @order_field='username' then dbo.users.username end,
case when @order_field='first_name' then dbo.users.first_name end,
case when @order_field='last_name' then dbo.users.last_name end,
case when @order_field='properties_count' then 1 end,
case when @order_field='enquiries_count' then 1 end,
case when @order_field='visits_count' then 1 end
RETURN
END
View 1 Replies
View Related
