Transact SQL :: Output Data To Confluence Page
Jul 8, 2015We are using Confluence 5.6.5; essentially I want to output some data from a SQL Server 2012 Database onto a Confluence page; how to go about achieving this?
View 3 Replies
ADVERTISEMENT
I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir
Nov 28, 2006Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output
Sep 21, 2015Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
How To Query For Master-detail Output On Same Page
Jul 27, 2007I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>
1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
Reporting Services :: Tables Are Showing Up In Same Page When There Is No Data Even After Giving Page Break Option
May 7, 2015I have a report with multiple tables. I need to show each tables in different pages. When there is no data for tables/tables , it is coming with the next table which has data. I have given "Add a page break after" option in the tablix but still the tables are coming together when no data available. How can I show it in different page?
View 2 Replies View RelatedOne Page Report - One Result Row In SQL = One Full Page Of Data
Feb 27, 2008Hi - I am pretty new to Reporting Services. I need to create a report where a single result row from the Select Statement populates an entire page of data. The regular grid or Matrix reports don't fit this need. Is there a simple way to do this?
Thanks - Dave
SSRS Printing Gives Different Page Output In Visual Studio And Deployed Environments.
May 6, 2008
Hi,
I'm having a struggle with the printing of landscape reports in SSRS.
The reports appear fine when printed from the Visual Studio environment, but when I deploy to a web environment I get 1 or 2 blank pages before reporting continues (i'm using the SSRS printer icon, not the browser printer icon).
I've spent ages looking at margin and printer settings and can't find anything wrong - especially as it works under Visual Studio.
Has anyone experienced problems of this nature and how did you resolve them?
Cheers.
Elracorey
Transact SQL :: FOR XML Output To A File
Feb 19, 2013I am using SELECT FOR XML which is working great.My problem is writing the results out to the filesystem.I am using spWriteStringToFile procedure that uses Scripting.FileSystemObject to write the file.The file gets written and all of the "xml" is there, but there are no CR/LFs and parsers, browsers and validators don't like it.What can I do to get a more usable output file?
View 11 Replies View RelatedTransact SQL :: How To Get Output Through Email
Jul 2, 2015I have one PS script; Basically it reads all application logs and give a report. Here is the PS script:-Â
$logs='C:AppLog*.log'
$ufo=Get-Date -ufo '%Y-%m-%d' # Unidentified Flying Object
$match="^$ufo.+error.+$"
sls $match $logs|
  group filename -noe|%{
    [pscustomobject]@{Name=$_.name;Date=$ufo;'#Errors'=$_.count}
[code]..
Now I would like to setup a SQL job and want to get the result in email.
Transact SQL :: UPDATE With OUTPUT Clause
May 28, 2015I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
Transact SQL :: Limiting Recordset Output
Jun 9, 2015I’m thinking about the best way to run these queries, which need to be run regularly.
The first query is two tables linked, one is a data table containing a unique person identifier and three activity fields, the second a lookup table with an activity_type linked to the activity in the table data.
Data Table
PersonID
Lots of other fields
Activity1
Activity2
Activity3
The ACTIVITY fields can contain data anywhere between all being NULL and all being complete.
Lookup Table
ActivityID
ActivityDesc ( which appears in Activity 1 – 3)
ActivityType
I’d like to create a function which will create a recordset containing the Person ID and the Activity Type. I am unsure as to whether to do this in a way which will create one record for each person, or potentially 3 records for each person. This is how I have done the 3 records:
SELECT PersonID, Activity1 As Sport, ActivityType
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity1 = dbo.tblLUActivityType.ActivityDesc
UNION
SELECT PersonID, Activity2 As Sport, ActivityType
[Code] ...
And this is how I have done the 1 record:
SELECT ClientID,
Activity1,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity1) As ActivityType1,
Activity2,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity2) As ActivityType2,
Activity3,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity3) As ActivityType3
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity3 = dbo.tblLUActivityType.ActivityDesc
Order by PersonID
The reason I would like to do this is because I need to create a stored procedure which returns one record per person with two fields (Person Id, ActivityType) which states their ActivityType depending on certain rules:
Rule 1: If any of Activity 1 – 3 are ‘O’ report the Person ID and ActivityType = ‘O’
Rule 2:  Of the rest of the recordset, if any of Activity 1 – 3 are ‘N’ but none of Activity 1-3 are ‘O’ or ‘A’ then report the Person ID and ‘N’.
Rule 3: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ but none of Activity 1-3 are ‘O’ or ‘N’ then report the Person ID and ‘A’.
Rule 4: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ and one of the is ‘N’ then report the Person ID and ‘AN’.
At the end this I’m looking for one recordset with two fields containing a personID and one of ‘O’, ‘A’, ‘N’ or ‘AN’. I can do the first part of this in any way necessary, as long as the second part returns the recordset I need.Â
Transact SQL :: Query Output To JS File
Oct 5, 2015I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
View 7 Replies View RelatedTransact SQL :: Possible To Use Results From OUTPUT And Use Them As Parameters?
May 28, 2015I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
Transact SQL :: Return Value 1 For Output Parameter
Oct 7, 2015I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
 Â
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
Transact SQL :: Query To Get Web Page Hits On Website
Sep 24, 2015I'm trying to piece together some code on how to find members of our organization who have visited our website.
View 2 Replies View RelatedTransact SQL :: Add A Grand Total To Pivot Output?
Oct 9, 2015I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.
the SP is as below :
/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders = COALESCE ( (@columnHeaders) + ',[' + [Date] + ']', '[' + [Date] + ']')
[code]....
I am getting the below error:
Invalid column name 'Grand Total'.
Msg 205, Level 16, State 1, Line 16
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Transact SQL :: MERGE Function And OUTPUT To A File
Sep 14, 2015I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
Transact SQL :: Output Views In Dependency Order
Jun 25, 2015Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.Â
View 2 Replies View RelatedTransact SQL :: How To Set Error Message To Output Parameter
Aug 31, 2015In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?
create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY
[code].....
Transact SQL :: Merge 2 Rows Into One Line Output
May 21, 2015How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,
[Code] ....    Â
Is it possible to return the data in the following format merging 2 lines of data into one output:
 SiteID  ReadStartDate ReadEndDate    ReadStartIncome    ReadEndIncome
L0020Â Â Â Â 19/05/2015 05:00Â 20/05/2015 05:00Â Â Â 85.98Â Â Â Â 145.98
L0101Â Â Â Â 19/05/2015 22:07Â Â Â 20/05/2015 22:14Â Â Â Â Â Â Â Â Â 1,936.08Â Â Â Â Â Â 1,438.89
L0102Â Â Â Â 20/05/2015 21:16Â Â Â 19/05/2015 21:48Â Â 143.65Â 243.5
I am using SQL 2008 R2.
Transact SQL :: Unable To Get Required Output Using Case Statement
May 17, 2015Table Structure
EIDÂ Â Â COLAÂ Â Â COLB
1   name   A
1   age   23
1   city   hyd
1   email   abc@live.in
1   mobile   45126
2   name   B
2   age   43
[code]....
how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null
Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table
Jul 14, 2015Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
Transact SQL :: Create Job Which Automatically Convert Output Into Excel And Send Mail
Oct 31, 2015I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to automate this script means wants to create a job which runs on a Friday basis and save the output results of the query as a excel file and then automatically sends the mail to everyone.
View 9 Replies View RelatedTransact SQL :: Server Is Cutting Off Result-output Running A Powershell Script
May 7, 2015I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:
CREATE TABLE #DrvLetter (
iid int identity(1,1) primary key
,Laufwerk char(500),
)
INSERT INTO #DrvLetter
EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"'
select * from #DrvLetter
SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):
Â
[code]....
Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page
Nov 11, 2015I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedTransact SQL :: Use Print Function To Output Numeric Variable With Fixed Amount Of Leading Zeroes
Apr 23, 2015I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. Â For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.
Transact SQL :: Convert Output Into Excel And Schedule Automate Job That Runs Every Friday And Send Email With Attachment
Nov 3, 2015Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter
Apr 11, 2007I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
Reporting Services :: Print Data With Blank Space And Move To Continue Data To Next Page
May 15, 2015I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups.
While printing the report, Â data print with blank space and move to continue data to next page.Â
Departure flight : 70 rows
First Page           : 42 rows printed
Second Page     : 23  rows printed  [ Supposed to be print 28 ,  if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Third Page          : 5 rows printed
Departure flight : 42 Â rows
First Page           : 42  rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]
Departure flight : 26 rows
First Page           : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Second Page     : 3 rows printed
Reporting Services :: Keep Multiple Data Tables Having Fixed Size Data In Same Page On Runtime?
Jul 31, 2015I have a report where in I have a combination of matrix ,table data regions.
The problem what I am facing is that the data tables don't remain fixed in their position and they tend to move down.
E.g. table 1 and table 2Â are on the same page in design time side by side (right and left)however during the runtime the table1 is pushed down and table2 is at its position .
Now how can I keep them all fixed in their same position. Most of the tables have fixed size rows and some who have high size of rows have been put at the end . What settings we can set?
Output Data Is 2 TIMES The Input Data.SSIS.Newbie
Apr 10, 2008
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
Transact SQL :: Output A File With A File Header
Aug 27, 2015In my SSIS Package, I have to write my [FileHeaderRecord] row, then my [BatchHeaderRecord] row, then my details. How can I do this in a SQL Server Query? When I try my SSIS, my file looks like so..
FHTEST 00000208262015Â Â Â Â Â Â Â Â Â Â Â Â BH000208262015Â Â
I want my BH, Batch Header data, to appear on a new row in the file.Do I have to build a dynamic query to do this?Is there any trick in SSIS to do something like this?I did try creating separate Data Flow Tasks to Query the [FileHeaderRecord] and then use a Flat File Destination and then another Data Flow Task to Query the [BatchHeaderRecord] and use a Flat File Destination again NOT overwriting the file.
Repeating Data On Each Page
Sep 4, 2007
Hi
Pulled all my hair out now, can someone please offer some help before I go totally mad...
I have no page header, this is all in the body. I have a List with account number and job number in it at the top, called list1. In list1 I have list2, with the job details, basically a list of people who attended the job. The fields in list2 are textboxes, they're not in a table.
EG:
Acc No: 12345
Job No: 54321
Name
Fred Bloggs
Joe Bloggs
Etc
When my list2 of names goes over 1 page, I want to repeat the account number and job number on each subsequent page. In my real report I actually have 10 fields I want to repeat.
I then want to be able to export this report to a PDF.
I can't put the header in the page header and RepeatWith doesn't work when exporting to PDF, or at all depending on what mood it's in.
Does anyone have a workaround for this please?
Cheers