Hello. I work on a proprietary software package at work that has a SQL query engine. I'm able to query tables that I know exist but I would like to know what other tables are there in the db. Unfortunately I don't know the name of the database. Is there a SQL statement that will show me the name of the database that I'm running my query on?
Thanks for your help.
FOA, I am using the webservices of the RS2005.
I created a report with same parameters. Two of them a based on a query. The first query contains a simple select * from ... the result will be stored in the parameter @param1. The second parameter contains select * from ... where ID = @param1. So far...
If a pass the reportparameters of the report i will get a valuelist for the first query, but nothing for the second one.
How can I manage that the second parameters also gets a valuelist?
regs
Thank you for your help.
I run a website which uses SQL 2000 and VB ASP. I would like to add a section to the site which posts the most popular data being queried from my SQL server. I'm sure this is possible, but I don't know where to start. Please let me know if any of you need specifics regarding my data and set up.
Thanks again for the help! :)
JAC
Quick question guys, I remember running into this before but can't remember if I was able to complete it or not.
What is the syntax format in the 'Report Parameters' dialog box selecting 'Non-queried' in the "value " field I want to select multiple value parameters ie 001, 002, 003, 004 for one "label" Every combination I tried it's not taking. Thanks in advance
I have a problem about calculating data from the table. I want to sent a row to the local variables and calculate them by C#. Thank you in advance for every suggestion.
View 3 Replies View Relatedhi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
View 4 Replies View RelatedHi, is there such a thing as querying a queried result?
Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B
If there is, could you kindly direct me to a website with the appropiate article on this topic?
Thanks,
-Gabian-
I have a select statement that is being processed through oSql on Sql Server 2000. There are 2 fields in the select statement that are defined in the dateabase as nvarchar(1). When I perform my select statement, they show up in the output as 4 char fields. See dataset below for example.
493575545493575545003753404A 20070805000000002007080520070805 131307269009426800000000000000000000000
493575545493575545003753404A 00000000000000000000000020010410S 131307270009426800000000000000000000000
493575545493575545003753410A 20070805000000002007080520070805 131307271009426800000000000000000000000
How do I get rid of the extra spaces in the output? I have tried using ltrim(rtrim(fielde)) to no avail. Fieldg (the S) is a nullable field and is being processed using an isnull(filedg, ' ').
The general statement is:
Select fielda, fieldb, fieldc, ltrim(rtrim(fieldd)), fielde, fieldf, isnull(fieldg, ' '), filedh from mytable
The functioality can be replicated using:
Select 'a', 'b'
---- ----
a b
(1 row(s) affected)
Any Ideas?
Thanks in advance.
Aaron
I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Hello,
I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.
The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?
Any help would be greatly appreciated!
SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate
In general, what is the most appropriate way to specify a "data driven" default for a report parameter. Seems to me like options are:
1. construct an expression which operates on one of the existing report datasets (possibly the same data set used to populate the defaults dropdown)
2. construct a new dataset which returns a single value which is the desired default
what's the "normal" approach?
For example, I have an MDX dataset which contains the list of dates for which data is available. There is generally some data available for today, but normally the user is interested in the most recent day with data prior to today.
So which would be the better option out of the two above (or a third option if I'm missing something!) to tell my parameter to set itself to:
"The most recent day prior to today for which data exists".
I need to use a non-queried report parameter to filter a dataset for a report.
The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.
The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.
Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."
Which sucks.
Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.
I've tried eveything, researched everywhere online and I can't find any guidance anywhere.
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
[code]....
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
Adding a value to a non-queried default report parameter value does not update on the target server after deployment.
To recreate
1. Create a report in Visual Studio and add a report parameter with the following properties:
Multi-value is checked
Available values = "From Query"
Dataset = [create a dataset that returns a table w/ a Id and Description column]
Value field = [the Id field from the table]
Label field=[the Description column from the table]
Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)
2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.
3. Go back to Visual Studio and add a value to the Non-queried Default values.
4. Build and deploy the report again. View the report. The newly added item is not selected.
Notes
I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.
Work-arounds
1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:
SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId
2. Delete the report in Management Studio, then redeploy.
I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
Dear all,
I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.
The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.
I queried the table with this query
SELECT DISTINCT calling_number
FROM call_records
WHERE called_number = '07786000815'
AND target_operator = 'INDOSAT';
It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?
Thank you,
Bernaridho
I'm beginner in asp.net. Can someone show me how to create coding for this page..
i have to create my own login page and database. I'm using sql sever 2005 for database. can someone show me how to make connection with my login button and my database? please....i'm really need help from you all guys.
I need to flag old records and new records with 1 and 0 respectively... can anyone show me how to do it without having to create a temp table? this is what i've got so far...
create table #tempLRM
([Key] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS null,
[Start Date] datetime null);
insert into #tempLRM
select [key], min([start date])
from ReportDataLRM
group by [key];
update ReportDataLRM
set multi = 0
from #tempLRM t
where ReportDataLRM.[key]= t.[key]
and ReportDataLRM.[start date] = t.[start date];
update ReportDataLRM
set multi = 1
where multi is null;
select * from ReportDataLRM
Hi!!
I have a bar chart and the data are agrupped by Folders (Category groups) and by FileType (Series Groups)... I want to show only the 5 folders with the biggest amount of files... how i could do it?
All suggestions will be wellcome... Thx!!
In Reports Service SQL 2005
my Fields Value is A, but I list table to show Value is B.
How do it.
Thanks
is have this code, and i know that i have a record with the ID=1 but it will not show the data from the record..<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:FormView ID="form1" runat="server" DataSourceID="SqlDataSource1"></asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStrAccess %>"
SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = ?)" ProviderName="<%$ ConnectionStrings:ConnStrAccess.ProviderName %>">
<SelectParameters>
<asp:SessionParameter DefaultValue="1" Name="SiteMainID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Content> Why can't it show the record !??
New to asp and have no idea what I'm doing wrong here. I just want to open a pdf that I have stored in a table and show it on the page. Here is my code. I keep getting the System.NullReferenceException was unhandled by user code.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:smithsdaConnectionStringtest %>"
ProviderName="<%$ ConnectionStrings:smithsdaConnectionStringtest.ProviderName %>"
SelectCommand="SELECT * FROM [correspondence] WHERE ([facilitiesID] = ?)">
<SelectParameters>
<asp:QueryStringParameter Name="facilitiesID" QueryStringField="DACorr" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ScanDoc As New DataViewDim arg As New DataSourceSelectArguments
ScanDoc = SqlDataSource1.Select(arg)
Dim ScanLet(1) As ByteScanLet(0) = CByte(ScanDoc(0)("Letter"))
If ScanDoc IsNot "" Then
Response.Buffer = True
Response.Clear()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(ScanLet)
Response.End()
End IfEnd Sub
</script
Any Help greatly appreciated.
Hello!
I'm trying to show one large image and up to five small images. I can not get this to work. The five small images is in a repeater, the big picture is not in the repeater. I Think I just paste the code so you can look at it, i think that is better than me trying to explain moore:) I thougth I could use one of the events of the repeater and bind the large image when the page loads but I dont know what to use SqlDataSourceStatusEventArgs or what? I hope you all aunderstand whatI mean.<table style="background-color: #E4F9DF; margin: 10px 0px 0px 0px; border: 1px solid green;width: 545px; border-collapse: collapse;"><tr><td class="BoldText" colspan="2" style="border-bottom: 1px solid green; background-color: #b0eda2; text-align: center;">Bilder</td></tr><tr><td style="text-align:center"><asp:Image ID="Image1" Width="530px" runat="server" /></td></tr><tr><td> <asp:Repeater ID="Repeater1" OnItemCommand="imgBtnChangePic_Click" DataSourceID="sqlGetAdPics" runat="server"><ItemTemplate> The images will not appear, the shown like the path is wrong.<asp:ImageButton ID="ImageButton1" CommandName="changePic" ImageUrl='graphics/Advertise/<%#Eval("advertisePic")%>' CommandArgument='<%#Eval("advertisePic") %>' runat="server"/></ItemTemplate></asp:Repeater></td> </tr></table>
Here is the code behind
protected void Page_Load(object sender, EventArgs e){// this works, but I have to get the mage from the database...Image Image1 = (Image)DetailsView1.FindControl("Image1");Image1.ImageUrl = "graphics/Advertise/sadel2.jpg"; }protected void sqlGetAdDetails_Selected(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception != null)lblError.Text = e.Exception.Message.ToString(); }protected void imgBtnChangePic_Click(object sender, RepeaterCommandEventArgs e){// This method is working.if (e.CommandName == "changePic" && Page.IsPostBack){Image Image1 = (Image)DetailsView1.FindControl("Image1");Image1.ImageUrl = "graphics/Advertise/" + e.CommandArgument.ToString();}}
When I set up a DailyBackup using the Backup Wizard it doesn't show in the SQL Agent jobs or in the Database maintainance plan. Is this correct??
Many Thanks
Darren
husband built web site, hosted by 1and1, husband can't help right now and 1and 1 won't. I can't get the connection string correct and need some advise.
This is the info 1and1 gave me for my db.
Database Name db212583089
User Name xxxxxxxxxxxxxxxx
Password xxxxxxxxxxx
Host Name mssql02.1and1.com
Description jamm
Status
My bd is uploaded to their server and should connect with the global.asa page, this is what I have as a connection right now and it's not working
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("Database1_ConnectionString" = "PROVIDER=SQLOLEDB;DATASOURCE=msssql02.1and1.com;DATABASE=db212583089;UID=dbo212583089;PWD=xDBXzNTt"
Application("Database1_ConnectionTimeout") = 15
Application("Database1_CommandTimeout") = 30
this is the error i get on my website when i try to access my db.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/JAMMDatabase.asp, line 131
This is the code on line 131, some before and some after:
strProvider=Application("Database1_ConnectionString")
set objConn = server.createobject("ADODB.Connection")
objConn.Open strProvider(line 131)
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn
right now I am totally lost with all this, if someone can help would really appreciate it, thanks, debi
if you need more info just let me know
Dear All,
What is the sql command to show me the tables
Hello,
When I create new Database User the login name input does not show on the list and in the "login name " when checking the Properties of the name. Why this behavior?
the previous user created a while ago are showing name and login name .
permissions and rights are the same for between different users having the two different behaviors.
When I created the new database user I input e.g.:
Login Name: AD<<User Name>>
Name : "First Name"
Thanks,
Dom
I want to select all those record except those record who have birthdate less then 4/13/1992 and relation= son
I tried this
SELECT * FROM Dependents WHERE code = 2
AND NOT CONTAINS
(SELECT *
FROM Dependents
WHERE BirthDate < '4/13/1992' AND relation= 'son')
But not working....
HI
I have created a stored procedure in SQL Server which outputs all records in a table.
How can I execute that procedure in access please? ( show that table in access?)
I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.
Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots
And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')
You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?
Hello All,
I am using the folliwing sql statement:
execute whs_he_rpt_ds_summary '11/01/05', '11/30/05', '594'
It displays the headers but no information. The date range is obviously a date range and the 594 is a BranchNbr.
How do I see if there is truly any data for this date range.
TIA
Kurt
Hello Everyone,
Thanks for everyone's help on this mess so far especially Peso. Yes this was originally Oracle SQL and I now need to convert it to SQL.
Can someone start by showing me at least part of this the best way to convert this to regular SQL? Also are there any tips, guides out there that anyone knows of?
selectCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB as TECHN,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
Parser.dbo.OJB_JOBS.COMPL_DTE_OJB as COMPLET,
Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION],
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS],
CSG_Hist.dbo.OJB_JOBS.JOB_TYP_OJB as JOB,
CSG_Hist.dbo.OJB_JOBS.JOB_CLASS_OJB as TYP,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR as NUMB,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR,
Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT,
COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW
fromParser_OCR
Inner JoinParser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
Inner JOINParser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOINCSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOINCSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOINCSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB
cross Join(
select 1 as p union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as pstn
WHERECSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB between 950 and 999
and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600
GROUP BYCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3),
Parser.dbo.Parser_OJB.COMPL_DTE_OJB,
Parser.dbo.ELP_Codes.CodeDes,
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE,
Parser.dbo.Parser_OJB.JOB_TYP_OJB,
Parser.dbo.Parser_OJB.JOB_CLASS_OJB,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR,
Parser.dbo.ELP_Codes.CommissionAMT
Again thanks for everyone's help especially Peso!
Have a great day!
Kurt