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....
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 RelatedHi 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.
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.
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 RelatedI 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?
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
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
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
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
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
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
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 RelatedI 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.
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/
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?
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!
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
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 RelatedI 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
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
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.
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.
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 RelatedHey,
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!
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
So, I downloaded and installed SQL Server 2005 Express, and I just can't figure out how to get it to do anything.
I'm accustomed to SQL Server 2000, and I don't see anything familiar.
Where's Query Analyzer? Where's Enterprise Manager? All I see is this "Configuration Manager" stuff, which doesn't seem particularly useful.
How am I supposed to use this software? I feel like they could have tried a little harder on this one.
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.
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
Hi!
I have trouble using Visual C # 2008 EE and SQL Server 2005 EE. Together with SQL Server on this computer, I successfully using VC # 2005 Prof and Web Developer 2008 EE. A VC # 2008 EE, I can not add the new connection to SQL Server 2005 through SqlClient in Database Explorer .. simply because the Change Data Source dialog does not give me this opportunity, offering a choice only MS Access, MS SQL Server Compact 3.5 and MS SQL Server Database File. I note once again that, for example, the Web Developer everything works fine.
(Excuse me, I use translate.google.com)
I installed VS2005 on my laptop at the office and of course, got the default instance of SQLEXPRESS. When I take my laptop home and log in (I use my NETWORKlogin login everywhere), I can't authenticate to use the database -- it doesn't like my login unless I'm physically at my office.
So, thinking I'm clever, I changed my SQLEXPRESS instance to use 'SQL Server and Windows Authentication mode' and tried to use the 'sa' login in my connection string. Login failed for 'sa' and since I had never set a password (yes, I tried using a blank) I executed the following query on my db:
exec sp_password @new='WHATEVERPASSWORD', @loginame = 'sa'
The query works (?) but it my connection string doesn't and I'm still unable to use the management tool when not on the original network.
So, I thought I'd add a new, super secret special user, but alas, that has completely escaped me. That frightens me a bit because I'd really like to be able to deploy my database to my web host and trust the my connection strings will work.
So, here are my questions:
1. Can I set up my instance so that I don't have to be on the network on which I installed it in order for it to recognize me (since it's self contained on my machine)? If so, how?
2. How do I create new SQL users?
3. Has the connection string changed significantly, and consequently, am I missing something fundamental in my attempts to connect? If someone could toss out a working SQL Server Express 2005 connection string that doesn't use Windows authentication, that would be really appreciated.
Here's some info from my box, if it helps.
Microsoft SQL Server Management Studio Express 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Thanks for any help out there.
I have a XP Pro machine that acts as our dev SQL machine, and I run my asp.net apps on my local machine (also XP Pro). I can connect (using QA) to the SQL Server just fine (using SQL Server authentication) but for some reason my ASP.net apps won't connect, even though I use the same connection string.
I get 'SQL Server does not exist or access denied.'
Do I need to set up special permissions or something on my ASPNET user to connect to the SQL Server machine? My local machine connects fine to several other SQL Servers including our off-site main serverfarm - so I can't work out what's different with this XP Pro box. I know my connection details are correct because EM and QA connects just fine with the same details.
help.
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO