I have a long text field in a table. Is there a query that I can execute in Mgt. Studio Express that will display the whole of this field. It is too long to easily see in the table view and SELECT <fieldname> from <tablename> only displays part of the field before displaying ellipses (...).
Many thanks,
Hopefully this makes sense. I am new to SQL Reporting. My code is below. I am trying to get my query to display 0 when a category (prob_ctg.sym, does not have a Count value.
SELECT COUNT(*) AS Expr2, ISNULL(COUNT(*), '0') AS Total, CONVERT(varchar(20), Requestor.c_last_name) + ', ' + CONVERT(varchar(20),
Requestor.c_first_name, 112) AS Customer, Calls.ref_num, Calls.category, prob_ctg.sym, Calls.summary, DATEADD(s, Calls.open_date - 21600,
'1/1/70 12:00:00 am') AS OpenDT, DATEADD(s, Calls.close_date - 21600, '1/1/70 12:00:00 am') AS CloseDT, [Group].c_last_name AS GroupName
FROM ctct Requestor INNER JOIN
ctct [Group] INNER JOIN
call_req Calls ON [Group].id = Calls.group_id ON Requestor.id = Calls.customer INNER JOIN
int_org INNER JOIN
prob_ctg ON int_org.id = prob_ctg.organization ON Calls.category = prob_ctg.persid
WHERE (int_org.iorg_c_id LIKE '%400111%') AND (prob_ctg.del = 0) AND (DATEADD(s, Calls.open_date - 21600, CONVERT(DATETIME, '1970-01-01 00:00:00',
102)) BETWEEN @stdt AND @eddt)
GROUP BY Calls.category, prob_ctg.sym, Calls.summary, Calls.ref_num, Calls.open_date, Calls.close_date, [Group].c_last_name, int_org.iorg_name,
int_org.iorg_c_id, Requestor.c_last_name, Requestor.c_first_name
I am creating a report using report builder and I am using several fields from various entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
Hi
I need to display
1. --->
2. up arrow
3. down arrow
for a field called Levels based on SCORE1 and SCORE2
I am planning on using IIF statements
but I need to Use WINGDINGS font for the arrow symbols
but how do i use it in value property of text box (start typing in arial then cannot swith to WINGDINGS)
THNX
Hello,
I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.
I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.
any ideas really appriciated.
Thanks
Ram
Hi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.
If divisor is "0" I need to print the Field, but for me it displaying #Error. If I pass any value it is working fine. It is not working only for the Field.
RULE: Fields!B.Value=0, PRINT Fields!A.Value.(IIf(B=0,A,A/B))
Working: IIF(Fields!B.Value=0,0,Fields!A.Value/IIF(Fields!B.Value=0,0,Fields!B.Value))
Not working: IIF(Fields!B.Value=0,Fields!A.Value,Fields!A.Value/IIF(Fields!B.Value=0,Fields!A.Value,Fields!B.Value))
A B Cal(A/B)
E 1 0 1
F 4 2 2
G 9 3 3
Does anyone know how to display the properties of a column / data field in ASP? Thanks!
View 6 Replies View Related
Hi Everybody
I am working with SQL Server2000. I have one table named Table1 is described as below
Column1
----------------------------
Val1
Val2
Val3
Val4
Val5
Val6
Val7
Val8
Now I want result like that
Column1 Column2
-------------------------------------
Val1 Val2
Val3 Val4
Val5 Val6
Val7 Val8
How I acheive this result from a Query ?
Hello,I'm having problems saving and then displaying binary data in sql server.I have a form that takes a file specified by the user and inserts this into sql server:protected void btnUploadFile_Click(object sender, EventArgs e) { if (theFile.PostedFile != null) { if (theFile.PostedFile.ContentLength > 0) { byte[] docBuffer = new byte[theFile.PostedFile.ContentLength]; Response.Write(theFile.PostedFile.ContentType.ToString()); if (docBuffer.Length > 0) { // save to db DbAccess dbAccess = new DbAccess(); // my helper function for all db access etc try { dbAccess.BuildCommand("Incentives_SaveDocument"); dbAccess.Parameters.Add("@docImage", SqlDbType.Image).Value = docBuffer; dbAccess.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.ToString()); } } } } }Stored proc:ALTER PROCEDURE Incentives_SaveDocument @docImage imageASSET NOCOUNT ONINSERT INTO Table1 (theData) VALUES (@docImage) RETURNThis appears to work fine. I store the binary data in a image column and if I query the db it shows the row as <Binary>.The problem I have is with retrieving the data and saving it to a file. The file saves OK but when I open it is contains lots of "squares" that I suppose are the binary - it doesn't show the text.The code for retrieving/displaying the doc is:protected void btnView_Click(object sender, EventArgs e) { DbAccess dbAccess = new DbAccess(); byte[] byteArray = null; try { dbAccess.BuildCommand("Incentives_RetrieveDocument"); dbAccess.Parameters.Add("@id", SqlDbType.Int).Value = 6; // id for the doc to return SqlDataReader reader1 = dbAccess.ReturnDataReader(); while (reader1.Read()) { if (reader1.HasRows) { byteArray = (byte[])reader1["theData"]; } } reader1.Close(); FileStream fs = new FileStream("file1", FileMode.CreateNew, FileAccess.Write); fs.Write(byteArray, 0, byteArray.Length); fs.Flush(); fs.Close(); FileInfo fileInfo = new FileInfo("file1"); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name); HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString()); HttpContext.Current.Response.ContentType = "application/msword"; HttpContext.Current.Response.WriteFile(fileInfo.FullName); HttpContext.Current.Response.End(); } catch (Exception ex) { Response.Write(ex.ToString()); } finally { dbAccess.CloseDbConnection(); }The stored proc:ALTER PROCEDURE Incentives_RetrieveDocument @id intASSET NOCOUNT ONSELECT * FROM Table1 WHERE id = @id RETURNI would be grateful for any advice on this - its the first time I've worked with BLOB data.ThanksSi
View 2 Replies View RelatedI have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
Is there a way to display and manage Jobs in SQL Server Management Studio at the same level of detail as existed in Enterprise Manager?
In Enterprise Manager, when I clicked on Jobs, it displayed Name, Category, Enabled, Status, Next Run Date, etc. All very useful when managing many jobs. I particularly used the ability to sort my jobs by category.
In Management Studio, I click on Jobs in Object Explorer, and all I see in Object Explorer Details is Name and Status, with an icon that has a tiny read arrow that is almost invisible that apparently tells me if the job is not enabled.
I can right-click on Jobs and Manage Job Categories, which allows me to View Jobs, but that doesn't help me much unless I do a screen print so that I can go back to Object Explorer and manage the jobs that I want.
Perhaps I am missing something, but this is just one of several areas where it feels like Microsoft has made my job more difficult with this tool rather than easier.
Thanks for any suggestions that may help make this easier. Otherwise I am to the point where I'm going to have to rename all of my jobs beginning with the category so I can use the alphabetical sort.
Cliff
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
View 1 Replies View RelatedI'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View Related
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies View Related<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>" SelectCommand="SELECT * FROM [AI_news]" OnSelecting="SqlDataSource1_Selecting"> </asp:SqlDataSource> This is my code, I have followed the wizard perfectly the way its supposed ot be, well not 100% perfectly. This is how its supposed to be. When using a XXXX.MDF file I could use the wizard and it works fine. I do not know why my above code won't work. I am connected to the database and can view code manually. but not on a aspx page. i am using master page, now trying to add this into default.aspx Any help will be appreciated. I am currently losing some hair.
View 4 Replies View RelatedHi, I am trying to work ouyt how to dislplay text from an SQL database onto the page whilst keeping the correct formatting, mainly the paragraph breaks. When I try using a label the text comes out as one long line instead of being seperated accordingly as it was done when written in a multiline textbox. I am currently achieving this by writing the text to a txt file and then displaying it dynamically by inserting each line into a new table row and then adding these to a table, but I would much rather store them in the databse along with the other information that is collected with it instead. Someone told me to use a textbox to achieve this but the problem is that this is not crawlable for the search engine.
View 10 Replies View RelatedDisplaying a list of parent and child objects in a certain way, here's the code to create and populate an example dataset.
CREATE TABLE OrgStructure
(orgunitID int,
OrgUnitName varchar (100),
ParentID int)INSERT INTO OrgStructure
VALUES
(1,'OrgUnit 1', 0), (2, 'OrgUnit 2', 0), (3, 'OrgUnit 3', 0),
[Code] ....
The 3 OrgUnits with no parentID in one column (parentOU), then any OrgUnits who's parentID matches the orgunitid of the 3 OrgUnits in ParentOU in the next column (Child OU1), followed by any OrgUnits who's parentID matched the orgunitID of the OrgUnits in 'Child OU1' would be in the third column (Child OU2)
The dataset I'm working on contains ~700 rows and the OrgUnitNames can be anything, they aren't labelled like the example dataset with 1.1.1 and so on.
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
I have a report with 6 parameters..in that first 3 param having 23,000 values and last 3 are having 50 values each. If i see preview the report is coming fine and result is displayed properly. But, if i deploy the report in server and click the report ..then all parameters taking values but nothing is seen in report body ...its blank..
in any browser its blank.. atleast table structure also not displayed ..simply page blank.
Hi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
Hi
I have to populate some datas from sql server database and display it directly in a CSV Format or CSV file. When i run the project it should provide me an option to whether open or save or cancel the file. when i click open it should be opened and be viewed in a excel sheet in CSV format and when i click save it should ask the destination folder and should be saved there in CSV format and when i click cancel it should be cancelled and the application should be closed. pls note that all these actions should happen in the same browser and should not be redirected to anyother page. Can anybody give me the detailed description and code in Asp.Net using C# .It's very Urgent.
RegardsVijay.
hi friends, i need a code for storing and receiving an image to/fro SQL SERVER 2000 (in C#). i had searched some sites, all are in VB for windows forms not for website. finally i got a code from some site. it is working for storing purpose. not working for receiving purpose. the code for receiving and displaying purpose is (in a fresh page) private void Page_Load(object sender, System.EventArgs e){ // Put user code to initialize the page here MemoryStream stream = new MemoryStream (); SqlConnection connection = new SqlConnection (@"server=INDIAINDIA;database=iSense;uid=sa;pwd=india"); try { connection.Open (); SqlCommand command = new SqlCommand ("select Picture from Image", connection); byte[] image = (byte[]) command.ExecuteScalar (); stream.Write (image, 0, image.Length); Bitmap bitmap = new Bitmap (stream); Response.ContentType = "image/gif"; bitmap.Save (Response.OutputStream, ImageFormat.Gif); } finally { connection.Close (); stream.Close (); }}what s the problem is.........i'm getting an exception at Bitmap instantiation.(i.e Bitmap bitmap = new Bitmap (stream);)exception is "Parameter is not valid" what is the problem with that coding? was it correct? do u have any code for this in C#? if so, pls provide that......help me........pls.............
View 2 Replies View RelatedFor some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
View 4 Replies View RelatedI changed my laptop name from DELL-5 to KHALEDPC..I have SQL Server 2014 Express Edition with Advanced Services installed.every thing went well for reporting services and connecting to the server but I noticed that the Securables list for my Login still displaying the old server name?
View 5 Replies View RelatedI created a simple procedure in SQL Server Management Studio 2014. When I run it I see my query results but not my return value. Do I have to change settings to see the return value?
CREATE PROCEDURE temp
AS
BEGIN
SELECT GetDate()
FROM mytable
RETURN 1
END
EXEC temp
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.
SQL Server table time value shown in milliseconds:
00:00:00.82
Reporting Services report value shown:
00:00:00
I am using installshield to distribute SQL Server 2005 Express. I have the SQLEXPR.EXE file and I want to run it in /qb mode so the user can see the pretty dialogs pop up but not have to click anything.
View 6 Replies View RelatedI used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View 1 Replies View Related