URGENT:How To Show A Appended String To The Query Results?
Oct 31, 2007
Hello Frds,
This is my query
Select Assessment_Id,Question_Id,Question_short_description,
(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer
from dbo.viewX where assessment_id ='xxxx'
Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.
Thanks in advance..
Phani.
View 17 Replies
ADVERTISEMENT
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Feb 5, 2015
My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?
Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]
[Code] ....
View 1 Replies
View Related
Jan 15, 2014
I am trying to insert an NTEXT value from one table T1 to another table T2 within a database. Table structures are as below;
Create table T1 (T1ID INT NOT NUll, SourceColumn Ntext null)
Create table T2 (T2ID INT NOT NUll, TargetColumn NVARCHAR(MAX) null)
Every time when long text value is getting inserted into T2.TargetColumn , it is appending with an unwanted character '?' either at the beginning or at the ending of the text string. Same problem happens even when I am trying to update T2.TargetColumn = T1.TextColumn. Because of this, the same column never matches to the source column and gets updated every time even there is no change...
I am also converting NTEXT column to NVARCHAR(MAX) and replacing CHAR(10) to '' .
I am using SQL Server 2012. How can I avoid inserting '?' in T2.TargetColumn . Is there any setting which I need to set in the target table?
View 1 Replies
View Related
Sep 10, 2007
I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
View 2 Replies
View Related
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
Jul 20, 2005
I need to append text to the end of my sql query results.For instance, my query returns a list of user names infirstname.lastname. I need to add @yahoo.com to each record. So theend result should be Join Bytes!. What should byselect statement look like?Any help?
View 2 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Oct 29, 2007
I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.
SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'
View 5 Replies
View Related
Sep 10, 2007
I have 2 Table
Authors
ID Name
1 Clint
2 Voke
Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.
thanks,
View 15 Replies
View Related
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
View 1 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Oct 19, 2015
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"
[Code] ....
View 2 Replies
View Related
Nov 19, 2007
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
View 9 Replies
View Related
Oct 15, 2007
I have a column in a table that has numbers. How can i convert my number from this 2.36 to 2.4
or
2.53 to 3.0
Thanks
View 6 Replies
View Related
Jun 3, 2014
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
View 5 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
Aug 7, 2014
The data we are getting from our shipping company has the customer name and customer number attached.
so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'
So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)
I need to match this up with our customer table... on Customer Name.
View 9 Replies
View Related
Oct 31, 2014
i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.
like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much
View 3 Replies
View Related
May 30, 2001
One of our databases seems to be looking dodgy as some scheduled jobs are failing, but DBCC CHECKDB is no use since it has been running for over 1/2 hour without giving me any results, just the spinning globe.
How do I find out what is wrong without resorting to backups?
View 3 Replies
View Related
Mar 12, 2007
hi
im using sql server 2005
i have a table called download_transactions which contains wallpaper and video downloads. videos have a download_type of 110 and wallpapers 104. i need to pull out a count of these transactions where download_type is 110 and 104.
so one results column is count of 110 downloads
and the other column is count of 104.
thing is, one canned do; ..
where download_type 110 And 104
where download_type in (110, 104)
because this just confuses SQLserver. two conditionals.
how to do this, any ideas.
must i use a cursor to go through results once for 110, then again for 104?
at the moment, i select to pull out the count, and it gives two rows for each download type e.g.
clientID date count
50015 2007-03-06 00:00:00.000922
50015 2007-03-06 00:00:00.0001430
50020 2007-03-06 00:00:00.000486
50020 2007-03-06 00:00:00.000872
two rows for each clientID, one is count of 110, and one is count of 104.
i just need the 2nd row count to be in a 4th column next, like this:
clientID date 104/count 110/count
50015 2007-03-06 00:00:00.000922 1430
this is the sql i am using:
declare @start_date datetime
declare @end_date datetime
declare @start_timeshift int
declare @end_timeshift int
declare @client_id int
select@start_timeshift =0,
@end_timeshift =23,
@start_date = (dateadd(hh,@start_timeshift,'2007-03-06')),
@end_date = (dateadd(hh,@end_timeshift,'2007-03-06'))
select c.client_id,
@start_date[date],
count(txn.download_transaction_id) + '' + count(txn.download_transaction_id)
from[main_tbl].dbo.download_transactions txn with ( nolock )
inner join [main_tbl].dbo.billing_partners as b with ( nolock ) on ( ( b.billing_partner_id = txn.billing_partner_id )
and ( b.is_active = 1 ) and ( b.is_development <> 1 ) )
inner join [main_tbl].dbo.suppliers s with ( nolock ) on ( ( s.supplier_id = txn.supplier_id )
and ( s.is_active = 1 ) and ( s.is_development <> 1 ) )
inner join [main_tbl].dbo.clients c with ( nolock ) on ( ( c.client_id = txn.client_id )
and ( c.is_active = 1 ) and ( c.is_development <> 1 ) )
inner join [main_tbl].dbo.downloads d with ( nolock ) on ( ( d.download_id = txn.download_id ) )
inner join [main_tbl].dbo.download_types dt with ( nolock ) on ( ( dt.download_type_id = d.download_type_id ) )
--inner join [LOGGING].dbo.wap_sessions wp with (nolock)on ( ( wp.client_id = txn.client_id ) )
where (txn.client_id in (50025,50015,50030,50020))
and (txn.date_created between @start_date AND @end_date) --between '2007-01-01' And '2007-01-10')
and(txn.billing_successful = 1)
and (txn.PORTAL_CAMPAIGN_ID is null)
--and (dt.download_type_id = '104' + '110') --choose downloadtype
--and (dt.download_type_id = 110) --ch+oose downloadtype
--and (dt.download_type_id = 104) --ch+oose downloadtype
group by
c.client_ID, datepart(day,txn.date_created), dt.download_type_id, dt.download_type_id
order by
c.client_ID, dt.download_type_id
any help will be VERY VERY VERY appreciated.
Thanks
James
View 1 Replies
View Related
Jul 7, 2015
How do i get the mentioned format. if today is 7th July then,2015-07-07 15:30 i.e. date with HH and MM appended in today's date?
want just one simple statement. don't want to write big query as i want to use this condition in one of the joining criteria.
View 6 Replies
View Related
Oct 10, 2005
I'm encountering a strange problem in all the applications I'm working on and am totally dumbfounded as to why it's occuring:From a standard web form I'm inserting a record using a stored procedure. (I'm writing this to a SQL 2000 db - where the column types and variables are all consistant) No matter what I do, the columns are padded with extra characters maxing out the field length (if it's nchar or nvarchar or char) after insert or update. I've tried Trim - ing the field.text values that I'm feeding to the @variables used in my stored procedure. I've even RTRIM() - ed the @variables within the stored procedure. No matter what I do I get extra spaces padding the end of the intended column input. Ideas anyone???Thanks in advance. - Abe
View 4 Replies
View Related
Oct 7, 2004
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
View 1 Replies
View Related
Apr 14, 2004
When I check the totals for various dimensions in the database which is used for my cube, I get the correct results (I have tallied them with the results from my OLTP)...
But when I browse my cube (using the same parameters, or should i say dimension combination) i get different results as compared to what I mentioned earlier :confused:
Please guide/help.
View 2 Replies
View Related
Jul 31, 2002
How do I get the file name appended with the time stamp info like this:
Order_file__2002626_8_34_4.csv in an automated process which generates the file? Any help??
Thanks.
Reddy.
View 1 Replies
View Related
Jul 23, 2005
I'm trying to create a trace file with the file as part of it's name.For example, I'd like to create a file called FailedLogins-20050428. Sofar I haven't been able to figure out how to get the name of the fileand the date together (I'm sill very new to SQL Server and tracing).What I've done is:declare @rc intdeclare @traceid intdeclare @maxfilesize bigintset @maxfilesize = 50exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins', @maxfilesize=@maxfilesize,@stoptime=NULLif @rc > 0 print 'sp_trace_code failed with error code ' +rtrim(cast(@rc as char))else print 'traceid for the trace is ' + rtrim(cast(@traceid as char))I can create a trace file on C drive without difficulty. I've triedcreating a file like this:exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins + convert (varchar,getdate(),112',@maxfilesize=@maxfilesize, @stoptime=NULLBut what I end up created is a file on C calledfailedlogins + convert(varchar,getdate(),112).trcI have no doubt what I want to do can be done. I just done know how todo it.If anyone could tell me where I'm going wrong, I'd really appreciateit.Thanks in advance.
View 2 Replies
View Related
May 2, 2007
I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.
Any idea why ?
View 9 Replies
View Related
Oct 29, 2007
I have a fairly elaborate query that returns a string value something like this:
,,,6,7,6,7,,,,,,,,6,,,,,6,7,6,,,,,,,,,6,,,,,6,7,6,7,,,,,,,,6,,
basically its a series of numeric values seperated by commas.
What I want to do now is count the '6's ini the string and return a numeric value, i.e. the above example would = 9 and if i do the same for 7's it would return a 5
any idea on how to do this?
View 14 Replies
View Related
Dec 30, 2005
Sorry if this is a super-basic question...I'm used to join selects but not sure how to approach an append select (or something like it)
I have two tables with identical field structures: a Master table with 10,000 rows and a Custom table table with 1,000 rows
To keep it simple, let's say the two tables each have a FirstName field and a LastName Field.
Is it possible to use a View or a Select statement (or any other method) to 'append' the rows of both tables so that the result set still has only the two columns (FirstName and LastName) and has 11,000 rows?
Thanks for your help!
Randy
View 4 Replies
View Related
May 4, 2006
hi,
Can anybody help to guide how to extract/check the sql connection string in SQL Server 2k?
Any hit will be sincerely appreciated.
Regards
View 5 Replies
View Related
Jul 21, 2015
I want to extract a number within a column results, that is the number between the first two commas
Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....
How to extract the second number(between the first two commas) from the above results?
The output should be
1158286
1158289
View 2 Replies
View Related
