hi there , i'm using sql server 2005 express and i have some problems with the two of my tables :), iwant to query both of my tables but the table entries are not in an equal count of rows for example:
incoming(table) outgoing(table)
money date corp money date corp
15 1,1,2006 ar 17 1,1,2006 ar
25 1,2,2007 ar 21 2,2,2007 es
35 2,2,2007 es
6 3,3,2007 ar
in this example the first table has more rows but the opposite is possible in my tables because this is an (account extract) query and here's my problem: first i used the "select incoming.money as m1,incoming date as d1,incoming.corp as c1,outgoing.money as m2,outgoing.date as d2,outgoing.corp as c2 from incoming left outer join outgoing on incoming.corp=outgoing.corp where incoming.corp='ar'(or don't use the where eliminating)" any way i couldn't get the correct results, then i used the (union all) select , the result was ok but the results form is not what i want here's the form i want and is it possible to query to tables to get this?
in this query i just wanted the elements with the corporation named 'ar' for example:
m1 m2 d1 d2 c1 c2
15 17 1,1,2006 1,1,2006 ar ar
25 null(or (0)) 1,2,2007
6 null(or (0)) 3,3,2007
is it possible to achieve this form(and may be any of the tables may have more rows than the other, it's not exact) , i am in a very difficult position :) and now i need your help , thanks anybody to think of helping me
I have a query as follow: SELECT @resRate = (SELECT resRate FROM ProjectAssign WHERE proNo = (SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee) ) ) the definition of table Timer_cust contains refNum(int), projNo(varchar) and projName(varchar).Timer_cust table doesn't contain column username. So this query shoud generate a runtime error. however it works fine without error.But if I run SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee)A runtime error message appears.Why?
select * from jobmaster where status in ('Active') and materialtypecode in (1,2) and unit='IMPERIAL' and superusercode='S051000014' order by controllercode,jobname
through ADO I got the error message:
Invalid column name controllercode
When I execute the same above query in MS SQL Query Analyzer it is working.
I am having in writing a simple select command........here's My ProblemI am using the ASP SQLDataSources in VS2005.... and my need is that i need to show Products details in a Gridview...... Actually through QueryString a StoreID is being fetched..... and the Products under those StoreID are shown..... if there is nothing in query string then it should show all the results... ........ It means that my querystring should be something like thisselect * from tblProducts where StoreID = <xyz>and <xyz> should be some thing that could show all the rows in that table ........ i mean that it should show all the possible results that can be shown through...select * from tblProducts
I have two pages that Im using the same query out of... one page returns results, the other page returns an error... SOMEBODY HELP!!! I just need a hint if nothing more!!!
The Error: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'WHERE'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Global.Default_Search(Object Sender, EventArgs e) in C:InetpubwwwrootChampionRealtyINFOprojecttrackingglobal.vb:line 206
The SQL Query:
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, Offices.Name, Employees.First + ' ' + Employees.Last AS EmpName FROM PT_Job INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID WHERE PT_Job.JobID=1
The SQL Query Output By ASP.NET: (When an error is produced on the trouble page)
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName FROM PT_Job INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID WHERE JobID=1
The Trouble Page:
Public Sub Default_Search(ByVal Sender As Object, ByVal e As EventArgs) dim NeedAnd as boolean = False dim strSQL as string = "SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, " & _ "Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName " & _ "FROM PT_Job " & _ "INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID " & _ "INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID " & _ "INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID " & _ "INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID " & _ "INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID " & _ "INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID " & _ "WHERE " dim C as New SQLCommand(strSQL, Conn) dim DR as SQLDataReader If txtJobNum.Text <> Nothing Then strSQL += "JobID=" & CInt(txtJobNum.Text) & " " NeedAnd = True End If If ddlJobType.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "CatID=" & ddlJobType.SelectedItem.Value & " " NeedAnd = True End If If txtCreated.Text <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "PT_Job.Created LIKE '" & txtCreated.Text & "' " NeedAnd = True End If If ddlStatus.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "PT_Job.Status=" & ddlStatus.SelectedItem.Value & " " NeedAnd = True End If If ddlAssign.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "Employees.ID=" & ddlAssign.SelectedItem.Value & " " NeedAnd = True End If If ddlOffice.SelectedItem.Value <> Nothing Then If NeedAnd Then strSQL += "AND " strSQL += "Offices.ID=" & ddlOffice.SelectedItem.Value & " " NeedAnd = True End If
response.write(strSQL)
Conn.Open Try DR = C.ExecuteReader If DR.Read Then rptResults.Datasource = DR rptResults.Databind End If DR.Close Catch Exc as Exception 'ErrorAlert(Exc, strSQL) response.write("<p>" & Exc.ToString.Replace(Environment.NewLine(), "<br />") & "</p>") End Try Conn.Close
End Sub
The Page That Works:
Sub LoadResults(ByVal Sender As Object, ByVal e As EventArgs)
' Clean up some things... txtError.Visible = False txtError.Text = Nothing
' Create database interaction objects... dim Conn as Object dim C as Object dim DR
' Set database interaction objects... Select Case CInt(DBType.SelectedItem.Value) Case 0 Conn = New SQLConnection(txtConnString.Text) C = New SQLCommand(txtSQL.Text, Conn) Case 1 Conn = New OleDbConnection(txtConnString.Text) C = New OleDbCommand(txtSQL.Text, Conn) Case Else txtError.Visible = True txtError.Text = "Whoa... wierd error d00d... o.0" End Select
' Open the database for reading... Conn.Open
Try
' Load the datagrid with any information retrieved... Select Case CInt(CommType.SelectedItem.Value) Case 0 DR = C.ExecuteReader() dgResults.Datasource = DR dgResults.Databind DR.Close Case 1 C.ExecuteNonQuery() Case Else txtError.Visible = True txtError.Text = "That's some funky ****..." End Select
Catch Exc As Exception
' Make the error viewable so we know what went on... txtError.Visible = True txtError.Text = Exc.ToString
End Try
' Close the connection to the database... Conn.Close
First off, let me get my disclaimer out of the way - I'm a relative newbie at this SQL lark, so please ignore the kludgy-ness of this code etc. Thanks ;-)
DECLARE issue Cursor scroll FOR -- Create the cursor select articleIssue, articleDate from cr_newsletterArticles where newsletter = @newsletter -- Which Newsletter order by articleIssue desc ;
open issue -- Open the cursor SET @previssue = 0 -- Set our comparison variable to 0
FETCH next FROM issue -- Grab the first recordset from the cursor into @issuecounter, @date -- Stick it into our variable WHILE @@fetch_status = 0 -- While we're not at EOF begin -- begin if @issuecounter != @previssue BEGIN -- begin insert into @issues(issues,dates) values(@issuecounter, @date) SET @previssue = @issuecounter -- set comparison variable to issuecounter END fetch next from issue -- Grab the next recordset into @issuecounter, @date END -- End CLOSE issue -- close cursor deallocate issue select * from @issues GO
When I run this query in Query Analyser, it seems to work fine. IE. I get a table returned with the correct values. However, when I try and get this data into ASP via the old objRS.open "EXECUTE stored procedure <value>" command, no data is returned...
I'm not sure if this is an ASP problem, or a SQL problem... So, I altered the query slightly, and instead of a temporary table, I used a static one in the database, and it still didn't work - even though when I open the table in Enterprise manager the content is there...
Also, if I run the query via an ASP objCommand.execute "procedure <value> " and then use objRS.open "SELECT * from table" then it pulls the data in properly then...
If I was dealing with millions of recordsets in my original table, then I can understand that maybe the ASP isn't waiting long enough for SQL to return its final table. But I'm currently only dealing with about 50... I'd rather use the temporary table route rather than static incase of multiple hits at the procedure at the same time...
Any ideas? Please go easy - I refer you back to my disclaimer ;-)
I have a query like below .. if i add where Served = 1 , the query takes foreever... if i remove it, it takes only 6 sec...
I am not sure why this is hapening?
select distinct a.Episode_Key, case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI' when ag.Category = 'SEDC' then 'SED' when ag.Category = 'ACCA' then 'SA' when ag.Category like 'CGA%' then 'Gam' end as [Category], ag.Agreement_Type_Name as [Agreement], p.ServiceProvider, s2.Served from dbo.Assessment a INNER JOIN ( select distinct Episode_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence] from dbo.Assessment a INNER JOIN dbo.CD_Provider_Xref p ON a.Provider_CD = p.Provider_CD where Creation_DT >= '07/01/2007' and Reason_CD = 1 group by Episode_Key, p.ServiceProvider ) as s1 ON a.CSDS_Object_Key = s1.Sequence INNER JOIN dbo.CD_Provider_XREF p ON a.Provider_CD = p.Provider_CD INNER JOIN dbo.CD_Agreement_Type ag ON ag.Agreement_Type_CD = a.Agreement_Type_CD LEFT OUTER JOIN ( select distinct Episode_Key, p.ServiceProvider, 1 as [Served] from dbo.Encounters e INNER JOIN dbo.CD_Provider_Xref p ON e.Provider_CD = p.Provider_CD where Encounter_Begin_DT between '01/01/2008' and '01/31/2008' and Procedure_CD is not null and Encounter_Units > 0 ) as s2 ON a.Episode_Key = s2.Episode_Key and p.ServiceProvider = s2.ServiceProvider ????---where Served = 1 group by a.Episode_Key, ag.Agreement_Type_Name, p.ServiceProvider, Served, case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI' when ag.Category = 'SEDC' then 'SED' when ag.Category = 'ACCA' then 'SA' when ag.Category like 'CGA%' then 'Gam' End
Hi all,Using SQL Server 2000, SP4.I have a table of street names (Rua) whose ids (cod_rua) are foreignkeys into a consumer table (Consumidor). It turns out that the "Rua"table has many unused records which I'd like to wipe out. For instance,there are some 2800 unused records in the "Rua" table, and only some200 records actually being used by the "Consumidor" table (which,itself, has some 5000 records).Attempting to find the unused records, I issued the following query:a)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDOR)To my surprise, the query came out empty. But the following queryshowed the 200 or so records which *are* being used:b)SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR)I've found two solutions for the query to list the records *notexisting* in the Consumidor table:c)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDORWHERE COD_RUA IS NOT NULL)d)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR))I know that there are many other possible solutions to the query(including left joins), but what I don't understand is why the query a)failed.Can some of you, oh mighty gurus, enlighten me?For the record, here's how both tables are (partially) declared:CREATE TABLE Rua (Cod_Rua int NOT NULL ,Rua varchar (35) NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodRuaPRIMARY KEY (Cod_Rua))CREATE TABLE Consumidor (Cod_Consumidor int NOT NULL ,Cod_Rua int NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodConsumidorPRIMARY KEY(Cod_Consumidor) ,CONSTRAINT Fk_CodRua_ConsumidorFOREIGN KEY (Cod_Rua)REFERENCES Rua (Cod_Rua))Regards,Branco Medeiros
Hi everibody,it's the first time i post on this newsgroup. I'm Stefano from Milano,italy.I'm a beginners with Sql2000. My problem is this.I run a View using enterprise manager and after less then 20 second it goesin error time out. I run this view using a VB application and the errorcomes again .When i run it with Query Analyzer after 50 seconds it give methe right result.i've tried to change the value of querytimeout using sp_configure with thesame bad result.i've tried to change the ado command timeout in visul basic but stilldoesn't work.any suggest ?Thanks in advance
Last week we've made some modifications to our 20 GB database(with 2 huge tables (around 30 million rows)): 1. we've created 8 filegroups on 8 physical disks with RAID 1+0. In each of them we have one big table and others small tables unrelated with the big table. We've also created a filegroup for the nonclustered indexes from those big tables. 2. The Server is running SQL Server 2000 with SP3a 3. We've reindexed the database, updated statistics on all tables Now, we have a SP that on the old configuration took about 20 min to complete. But now, on our new configuration with the modifications listed above it's taking about 3 HOURS. This is the SP's body: is calling a function: create proc X as begin Select dbo.myfunc(mytable.field) from mytable end
The function used in select statement is querying those 2 huge tables.
If the select statement is runned for only one record it's done instantly, but for 20000 recs it is taking almost 3 hours. I must specify that also the function is running instantly, but in that select statement something is happening and we don't know what!!!
If you have any ideeas on what to do or what to check Thanks a lot Dan
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
Jezemine, No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
We have SQL server 7 installed. And we experiencing the performance problem. When I tried to solve this problem I have found the interesting thing: We have the table S_EVT_ACT with the non clustered index S_EVT_ACT_F4 created on [OWNER_PER_ID], [APPT_REPT_FLG] fields and another clustered one S_EVT_ACT_M4 created on [ROW_STATUS], [OWNER_PER_ID] fields I use the next select statement and before run this statement I declare the variable @P1 - @P5 and set the values to them:
declare @P1 as char(1) declare @P2 as char(1) declare @P3 as char(1) declare @P4 as varchar(10) declare @P5 as char(1)
set @P1 = 'Y' set @P2 = 'Y' set @P3 = 'N' set @P4 = '1-K56' set @P5 = 'Y'
SELECT .. ... ... FROM dbo.S_EVT_ACT T1 --(index = S_EVT_ACT_F4) LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID LEFT OUTER JOIN dbo.S_OPTY T3 ON T1.OPTY_ID = T3.ROW_ID LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.TARGET_OU_ID = T4.ROW_ID LEFT OUTER JOIN dbo.S_EVT_ACT_X T5 ON T1.ROW_ID = T5.PAR_ROW_ID WHERE ((T1.ALARM_FLAG = @P1 OR T1.APPT_REPT_REPL_CD IS NOT NULL) AND (T1.APPT_REPT_FLG = @P2 AND (T1.CAL_DISP_FLG = @P3 OR T1.CAL_DISP_FLG IS NULL)) AND (T1.OWNER_PER_ID = @P4) AND (T1.TEMPLATE_FLG != @P5 OR T1.TEMPLATE_FLG IS NULL)) ORDER BY T1.CREATED
In this case Query Analyzer uses S_EVT_ACT_M4 index and the performance is bad!
But when I try to run the same statement and use hard code 'Y' instead of the variable @P2 the Query Analyzer uses the S_EVT_ACT_F4 index and performance is PERFECT.
Question: What the difference between using variable @P2 and hard code 'Y' in the select statement and how to configure SQL server to use the right index in the situation when I can't change the Select statement and use the hard coding?
Any Idea will be very Appreciated.
Dmitri Denejkine stssystems MS SQL Server DBA, MIS (514) 426-0822 ext. 2676
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
I have a datasource where i assign the control Parameter value depending on whether theres a value in the querystring or a gridview selectedvalue is indeed selected.When i do this using a querystring it works fine, however, when i do it using gridView_selectedIndexChanged it doesn't work.Here's the code <asp:SqlDataSource ID="jobDetailsDS" runat="server" ConnectionString="<%$ ConnectionStrings:promanConn %>" SelectCommand="SELECT tblJobs.intJobId, tblJobs.intJobStaffId, tblJobs.intJobUserId, tblJobs.intJobCategoryId, tblJobs.strJobTitle, tblJobs.strJobDesc, tblJobs.strJobNotes, tblJobs.dateJobLogged, tblJobs.dateJobDeadline, tblJobs.dateJobCompleted, tblJobs.intJobPriorityId, tblJobs.strJobSolution, tblJobs.boolJobCompleted, tblPriority.priorityName, tblPriority.id, tblPriority.priorityOrder, tblStaff.staffName, tblStaff.id AS staffID, tblProblemCategoriesLookup.strProblemCatName, tblProblemCategoriesLookup.intProblemCatId, tblStaff_1.id AS Expr1, tblStaff_1.staffName AS UserName FROM tblJobs INNER JOIN tblPriority ON tblJobs.intJobPriorityId = tblPriority.id INNER JOIN tblStaff ON tblJobs.intJobStaffId = tblStaff.id INNER JOIN tblProblemCategoriesLookup ON tblJobs.intJobCategoryId = tblProblemCategoriesLookup.intProblemCatId INNER JOIN tblStaff AS tblStaff_1 ON tblJobs.intJobUserId = tblStaff_1.id WHERE (tblJobs.intJobId = @intJobId)" InsertCommand="INSERT INTO tblJobs(intJobStaffId, intJobUserId, intJobCategoryId, strJobTitle, strJobDesc, strJobNotes, dateJobLogged, dateJobDeadline, intJobPriorityId, boolJobCompleted) VALUES (@intJobStaffId, 56 , 39, @strJobTitle, @strJobDesc, @strJobNotes,{ fn NOW() }, { fn NOW() }, @intJobPriorityId, 0); "> <SelectParameters> <asp:Parameter Name="intJobId" Type="int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="intJobStaffId" Type="Int32" /> <asp:Parameter Name="strJobTitle" Type="String" /> <asp:Parameter Name="strJobDesc" Type="String" /> <asp:Parameter Name="strJobNotes" Type="String" /> <asp:Parameter Name="intJobPriorityId" Type="Int32" /> <asp:Parameter Name="intJobUserId" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>Code Behind ElseIf dest > 0 Then jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.InsertParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") gvActiveJobs.Visible = "False" End If End Sub Protected Sub gvActiveJobs_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvActiveJobs.SelectedIndexChanged gvActiveJobs.Visible = "False" jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.InsertParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue End Sub I also debugged and checked the value of gvActiveJobs.SelectedValue and it is what i was expecting but its still no good.Can anyone help?ThanksMatt
I moved a table from one file group to another file group. After moving the table the row count show 0 but when I open the table it’s returning all the rows. Is something wrong with the table? Do I have created a cluster index and drop on the table?
Hi, I have table in my db thats used as a temporary table. At the end of the day it contains thousands of records which get summarized then all are deleted. I recently moved the application over to a new server with pretty much the same hardware config and noticed a big performance hit when running queries on this temp table (455 milliseconds as opposed to 1) In short, the fix was dropping the table and re-creating it. Anybody know how this could be? I know that even if you delete records from a table, the table still seems to retain its physical size on the hard drive, could this have something to do with it?
CREATE TABLE #users(users varchar(50),pasw varchar(50),title varchar(50)) insert into #users select 'test','abcdef','ceo' SELECT * FROM #users alter table #users alter column pasw nvarchar(50) update #users set pasw=pwdencrypt(pasw) SELECT * FROM #users
What happens to the TITLE column? I had a table like the above with users and passwords in the Production DB. The password column had VARCHAR type. I changed it to NVARCHAR and encrypted the passwords. When i executed the SELECT *, the title column appeared like this. :eek: If i query the table by column names instead of * i see the correct values. I couldn't understand what behaviour is it.
Hi Experts, Am able to restart my local sql server using a login which dont have a sysadmin privileage.This login only have access to a particular database .Its only having datareader and datawriter as DB roles but still am able to restart the server.Please help
I am using c# and ASP.NET 2 and I am getting a very strange error. I have a field called CompanyID in SQL Server 2005, and it allows null values in it. When this CompanyID is not NULL, ie for example it is 202, then when i press the update button it allows me to update perfectly in the database. However if the CompanyID is NULL, and I try to update the companyID, to lets say 202, the application just crashes with the following message:- System.InvalidCastException: Object cannot be cast from DBNull to other types. I tried to set some breakpoints in the application to debug, however it does not even pass through these breakpoints! Any ideas on what the problem can be or how I can debug? Thanks for your help and time Johann
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
Hi there I have this statement I have written as follows:strCommand = "SELECT * FROM tblstock WHERE Type='"&Statement &"' AND Description like'"&criteria &"%' OR Tag like'"&criteria &"%'OR Location like'"&criteria &"%' OR LAN like'"&criteria &"%' OR RAM like'"&criteria &"%' OR CD like'"&criteria &"%' OR OS like '" &criteria &"%' OR SN like'" &criteria &"%' OR DeviceStatus like'" &criteria &"%' ORDER BY " &sSortStr The variable "Statement" is passed into the sub as "PC" which means only records of type "PC" should be displayed along with any other criteria. The issue I'm having is that when I specify criteria I'm also recieving other types eg "Cameras" if they contain any of my criteria.. I can't understand how because in the statement I tell it only to display records of type "PC".. Anybody know what I'm doing wrong? Thanks
I've done a new tabel that insert the UserId that in a uniqueidentifier get from Membership.GetUser().ProviderUserKeySo if I want to make a select statement threw storedprocedure in codebehind it runs as it shouldCode behindDim GetCustomersCars As CustomerCarByUserId = New CustomerCarByUserId MyCars.DataSource = GetCustomersCars.CarByUserId(Membership.GetUser().ProviderUserKey)MyCars.DataBind() But in when I use ObjectDataSource it fails<asp:ObjectDataSource id="ObjectDataSource1" runat="server" selectmethod="CarByUserId" typename="CustomerCarByUserId"> <SelectParameters> <asp:Parameter defaultvalue="Membership.GetUser().ProviderUserKey" name="UserId" type="Object" /> </SelectParameters> </asp:ObjectDataSource>I've tried with Membership.GetUser().ProviderUserKey.ToString(), but that doesnt work. Error message:InvalidCastExceptionI connect to the same source in both cases.Any one with an Idee ?
So we currently are running a SQL 7 Server, which hopefully we will be updating this year but untill then we're stuck. I've got a web app using ASP.NET 2.0 and on occasion I get this error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
I Some sql that it keeps throwing the following error
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is the sql
UPDATE dbo.temptable SET code = 'MM' FROM dbo.temptable T JOIN dbo.Item I WITH (NOLOCK) ON T.ProductID = P.ProductID JOIN dbo.cars C WITH (NOLOCK) ON I.ModelID = C.ModelID JOIN dbo.carmakes CM WITH (NOLOCK) ON CM.MakeID = C.MakeID JOIN dbo.CarDealerMakes CDM WITH (NOLOCK) ON CM.MakeID = CDM.MakeID WHERE T.ProductID NOT IN (
SELECT distinct A.ProductID FROM dbo.Action A WITH (NOLOCK) JOIN dbo.ActionPurchases AP WITH (NOLOCK) ON A.ActionID = AP.ActionID WHERE A.CustomerID = 2
UNION
SELECT distinct A.ProductID FROM dbo.Action A WITH (NOLOCK) JOIN dbo.ActionServices S WITH (NOLOCK) ON A.ActionID = S.ActionID WHERE 2 = A.ProductID)
That sql will run on windows 2000 but it will not run on windows 2003. I have the latest service pack.
Has anyone ever come across this before or have and suggestions ?
Can any one tell me why sp_helpindex shows several indexes though there is only one index on the table. When i use Enterprise manager it shows only one index..Any idea..is it a bug ..do I need to apply latest service pack ?
i have four separate jobs which run at scheduled time daily..out of which one job has run properly and the other three have not run at all(there is no details of it in "View job history")for only one day in between..again from the next day all the four jobs are running properly.what could be the reason for the three jobs not running on a particular day?(The jobs were scheduled between 3 and 4am in the morning..so nobody would be using the system at that time and the system was definitely up at that time as that one job has run) i could afford to have such strange behaviour in future as the jobs are important and should run daily.any ideas by which i could make sure all four jobs run daily?
We've been finding some strange indexes in our SQL 7 database. They are not indexes we put in, but seem to be system generated. They always have the format "hind_" and then a series of numbers and underscores. Most of the time they replicate indexes we already had in place. The strangest part is that in some cases they seem to be generating multiple clustered indexes on the same table. A previous question recieved an answer saying that they were statistical indexes. Could someone shed a little more light on what these are, where they came from, and how I can prevent them from coming back? Our queries usually take much longer on tables with these "rogue" indexes on them. Thanks.
