In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
Help - I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
Hello,I'm wondering if there is a way to concatenate two fields or a field and astring value in a single field in a view?Where in Access I might write;[field1] & " (m3)" as TotalVolumeis there a way to do this in an SQL Server View?Thanks!
Hi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:
Example: SELECT first_name + ' ' + last_name AS full_name FROM names
It works - but it seems rather blah. Is there something that'd be more "proper"?
Hello:I have an Access 2K form I built from a SQL Server 7.0 view. I want tolock certain fields in the database from users so they can see them onthe views and forms, but NOT be able to edit them.I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books andcould not find how to do this.Any advise will be greatly appreciated.Thanks,Richard
Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05? One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.
I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?
My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.
I have consulted the following without finding an answer: http://msdn2.microsoft.com/en-us/library/ms178445.aspx http://msdn2.microsoft.com/en-us/library/ms175642.aspx http://msdn2.microsoft.com/en-us/library/ms175678.aspx http://msdn2.microsoft.com/en-us/library/ms175637.aspx
How do I subtract, say an integer from a datetime value. I tried to use the Convert function but wont work. All I want to do in my SP is subtract an integer from getdate() and do a query based on the result I get from that subtraction.
I've got a list of IDs seperated by commas and I want to get each indivdual ID and insert them into a table. Has anybody got any ideas how this can be done?
I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?
Start Date... This is the first day of the month, one year ago... in VB I worked it out as...
Hi all, I am a little weak at SQL, so bear with me. Can anyone give me an SP which uses "Between" to Display all Dates BETWEEN a fromDate and toDate??? Two conditions 1.Both Dates are stored as varchar. 2. I should be able to get the dates even if years are different, say between 20th December 2006 and 3rd January 2007 3.Is there a way to extract the yearDiff? Regards, Naveen
[I am new to asp.net] Assume I have a databse for "products"; product id is a primary key. Here is what I am trying to: 1. DetailsView for a product (eg id = 1110) (I got this far) 2. how can I list other products starting with 111x id 3. I also to show them on same page as 1110 as "parent" 111x as children Thanks,
I'm in the midst of a long file conversion job. Today I found that one of the tables (converted from csv) to be 6.7 million records. My sql script which I use to reconfigure the weird original date format, into something the rest of the planet uses, times out due to the size.
Does anyone please know of a file utility to automagically split sql server 2005 tables for later re-combining once my scripts have successfully completed their task on the smaller tables?
We've got an employee database that I'm modifying to include two photos of each employee, a small thumbnail image and a full-size image. The HR department maintenance page contains a listbox of employee names, which, when clicked, populates a detailsview control.To get the images to display and be updatable, I've had to structure the following SqlDatasource and DetailsView: 1 <asp:DetailsView ID="dvEmp" runat="server"2 AutoGenerateRows="false"3 DataSourceID="dsEmpView"4 DataKeyNames="empID">5 <Fields>6 <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowInsertButton="true" />7 <asp:BoundField HeaderText="Name (Last, First)" DataField="empname" />8 <asp:TemplateField HeaderText="Thumbnail photo">9 <ItemTemplate>10 <asp:Image ID="imgThumbnail" runat="server" ImageUrl='<%# formatThumbURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />11 </ItemTemplate>12 <EditItemTemplate>13 <asp:Image ID="imgThumbHidden" runat="server" ImageUrl='<%# Bind("thumbURL") %>' Visible="false" />14 <asp:FileUpload ID="upldThumbnail" runat="server" />15 </EditItemTemplate>16 </asp:TemplateField>17 <asp:TemplateField HeaderText="Full Photo">18 <ItemTemplate>19 <asp:Image ID="imgPhoto" runat="server" ImageUrl='<%# formatImageURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />20 </ItemTemplate>21 <EditItemTemplate>22 <asp:Image ID="imgPhotoHidden" runat="server" ImageUrl='<%# Bind("photoURL") %>' Visible="false" />23 <asp:FileUpload ID="upldPhoto" runat="server" />24 </EditItemTemplate>25 </asp:TemplateField>26 </Fields>27 </asp:DetailsView>28 29 <asp:SqlDataSource ID="dsEmpView"30 runat="server"31 ConnectionString="<%$ ConnectionStrings:eSignInConnectionString %>"32 OnInserting="dsEmpView_Inserting"33 OnUpdating="dsEmpView_Updating"34 SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @empID)"35 InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@empname, @photoURL, @thumbURL)"36 UpdateCommand="UPDATE employees SET empname=@empname, photoURL=@photoURL, thumbURL=@thumbURL WHERE (empID = @empID)">37 <SelectParameters>38 <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />39 </SelectParameters>40 </asp:SqlDataSource>41 42 -----43 44 Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)45 Dim bAbort As Boolean = False46 Dim bThumb(), bPhoto() As Byte47 If e.Command.Parameters("@ename").Value.trim = "" Then bAbort = True48 Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)49 If imgT.HasFile Then50 Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)51 bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)52 e.Command.Parameters("@thumbURL").Value = bThumb53 End Using54 End If55 Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)56 If imgP.HasFile Then57 Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)58 bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)59 e.Command.Parameters("@photoURL").Value = bPhoto60 End Using61 End If62 e.Cancel = bAbort63 End SubIf the user updates both images at the same time by populating their respective FileUpload boxes, everything works as advertized. But if the user only updates one image (or neither image), things break. If they upload, say, just the full-size photo during an update, then it gives the error "System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with image".I think this error occurs because the update command is trying to set the parameter "thumbURL" without having any actual data to set. But since I really don't want this image updated with nothing, thereby erasingthe photo already in the database, I'd rather remove this parameter from the update string.So, let's remove the parameter that updates that image by adding the following code just after the "End Using" lines: Else Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image) e.Command.Parameters.Remove(p) (Similar code goes into the code block that handles the photo upload)Running the same update without an image in the thumb fileupload box, I now get this error: "System.ArgumentException: Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection."Huh? It's not there? Okay, so lets work it from the other end: let's remove all references to the thumbURL and photoURL from the dsEmpView datasource. We'll make its UpdateCommand = "UPDATE employees SET empname=@empname WHERE (empID = @empID)", and put code in the dsEmpView_Updating sub that adds the correct parameter to the update command, but only if the fileupload box has something in it. Therefore: If imgT.HasFile Then Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream) bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength) e.Command.Parameters.Add(New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image, imgT.PostedFile.ContentLength)) e.Command.Parameters("@thumbURL").Value = bThumb End Using End If (Similar code goes into the code block that handles the photo upload)But reversing the angle of attack only reverses the error. Uploading only the photo and not the thumb image results in: "System.Data.SqlClient.SqlException: The variable name '@photoURL' has already been declared. Variable names must be unique within a query batch or stored procedure."So now it's telling me the parameter IS there, even though I just removed it.ARRRGH!What am I doing wrong, and more importantly, how can I fix it?Thanks in advance.
I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.
Hi, I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries: 1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6) 2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10) 3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was: SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working: SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
Hi I am new to SQL programming. This is what I am trying to do in store procedure. Alter a table - adding two columns. Then update those columns Both the above action in same procedure . When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists. So the SP looks like this create store procedure <name> as alter table <name> ADD column_1 nvarchar (256), column_2 nvarchar (256);
update table set column_1 = <condition> column_2 = <condition>
The SQL complains - invalid column name column_1 invalid column name column_2
Hi,I have one stored procedure that calls another ( EXEC proc_abcd ). I wouldlike to return a result set (a temporary table I have created in theprocedure proc_abcd) to the calling procedure for further manipulation. Howcan I do this given that TABLE variables cannot be passed into, or returnedfrom, a stored procedure?Thanks,RobinExample: (if such a thing were possible):DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )...../*Insert a test value into the first temporary table*/INSERT INTO @myTempTable1 VALUES ( 1234 )...../*Execute a stored procedure returning another temporary table ofvalues.*/EXEC proc_abcd @myTempTable2 OUTPUT......../*Insert the values from the second temporary table into the first.*/SELECT * INTO @myTempTable1 FROM @myTempTable2
I am trying to find the best way to use the getDate() function in SQL Server CE to return a date and time which starts at midnight rather than the value of now which getDate() returns.
When running getDate I get the value of 29/04/2008 10:48:33 returned, but I want to return 29/04/2008 00:00:00 instead. The only way I can see to do this is like so:
Code Snippet
--SQL to get shifts for next 7 days. select * from SHIFTS
where STARTDATE between --Today at midnight 2008-04-29 00:00:00.000
A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:
Protected Overrides Sub OnInit() ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello() End Sub
The TextBox is called textbox2 and the custom assembly simply returns a string.
I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".
I am having a problem writing a large amount of ntext data to a field within an ADO recordset. I am using the append chunk method but it does not seem to work. The SQL 7 field will hold the data its only about 60K.
I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this.. EXEC sp_executesql @Statement,N'@param1 varchar(3),@param2 varchar(1)',@param1,@param2,GO As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement". DECLARE @Statement varbinary(16)SELECT @Statement = TEXTPTR(Statement)FROM table1 READTEXT table1.statement @Statement 0 16566 So far so good, the issue is how to convert @Statment varbinary to nText to get it passed in sp_executesql. Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called. I would appreciate if any body respond to this.
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
I am recieving complete row in one single column and I have pipe delimeter in this row . I want to retrieve the data in individual columns...Currently row is in one column
tblClients -------------------------------------------------- ClientID (varchar(3)) | ClientName (varchar(100)) -------------------------------------------------- 001 | First 002 | Second
tblProjects --------------------------------------------------- ProjectID (varchar(6)) | ProjectName (varchar(100)) --------------------------------------------------- 001001 | First Project1 001002 | First Project2 002001 | Second Project1 002002 | Second Project2
As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.
The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.
I need something like: tblClients INNER JOIN tblProjects ON tblClients.ClientID = tblProjects.First3DigitsOfTheProjectID
In previous versions of SQL Server you were able to execute single steps within a DTS package. It doesn't seemt to be possible in visual studio. Does anybody know otherwise?
