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
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
I'm trying to change the <NULL> fields of my table, but I don't know how to tell the query to look for <NULL>
For example:
select * from MyTable
where fieldx = <NULL>
This doesn't work.
How should it be?
Hi all...
I'm connected to a network with 3 Servers ( 3 sql servers on 3 different machines )
How can i know the name of the servers using SQL Code?
Thanks
How can I know how many connections are open at a given point of time while I am testing an ASP.Net application? The application uses SQL Server 2000 as its database.
In SQL Server 2005, I hava a client where I do not have access to their SQL Server. I update the database structure by giving them scripts which they run. As I update the structures I occasionally need to delete a constraint, then typically recreate it later. Usually I use this type of snippet:
IF EXISTS (SELECT * FROM sys.default_constraints
WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintName]')
AND parent_object_id = OBJECT_ID(N'[dbo].[tablename]'))
ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [ConstraintName]
This assumes I know the constraint name. A lot of the older constraints have random type names which I assume are different on their server than mine; they were not explicitly given names when created, so they would heve been given names by SQL Server.
Is there a way to delete a constraint without knowing its name? For example, delete all constraints associated with a field, or all constraints ssociated with a table?
Management Studio is an option, but for my client that involves a higher degree of paperwork and permission seeking than just running a script.
Many thanks,
Mike Thomas
I use SCD to extract and send as output further in the data flow only modified and new records. Before I write to DB and after SCD does its work, I have to execute different controls on both modified and new records. So, I send both SCD outputs (changing attribute updates output and new output ) to a Union All transformation, I execute the needed controls and then I want to insert/update the records in DB.
Is there a way to know which records SCD identified as new/modified after I unified them with a union all transformation? I can create a derivate column for one of the scd outputs and use it in a conditional split before writing to DB but I'd like to know if SCD sends any flags down the dataflow?
Hope I made the problem clear.
Thanx
Sara
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.
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 the need to remove a constraint on a table since I'm trying to alterthe datatype of one of the columns. I know I can drop the constraint giventhe name, but since the name is auto generated (something likeDF__WHRPT_ITV__Expor__45F365D3)I need a way to find this constraint name so that I can programmaticallyremove it.I can get the name using sp_helpconstraint on the table, but can'tseem to locate where the actual constraint_name is stored.This is on SQL 2000.Any help is appreciated.Thanks,-Gary
View 2 Replies View RelatedI 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've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
I'm working on a VB.NET project where we are intending to use MSDE as ourback-end database. The actual number of users is expected to be low and Idon't have any concerns as to whether MSDE will be up to the job (most ofthe time). I'm aware that if we end up with lots of users connecting to oursite at once (which may happen as certain times in the year when people needinformation for deadlines) then the performance governor in MSDE will kickin and slow everything down.What I don't know is how do I find out whether the performance governor haskicked in?I would like to know how to monitor this so we can make an educated decisionas to when we need to migrate to a full version of SQL Server (if at all).Can anyone point me in the right direction for finding this out?thanks,Brian Cryer.
View 2 Replies View Related
Hi Folks,
Always sorry to have to ask what is most likely such a simple question. However I'm in no way a programmer, I'm just patching something together using scripts I've found all over the shop.
I'm reading in an excel source using a dataflow script component. I build up a SQL String in Vb.Net using the output column names and query the spreadsheet via microsoft.Jet.OLEDB.4.0 then processing it in code. I don't want to use the Excel Source task btw, more for the fact I want to learn from this as well as other less important reasons.
This all works fine. Adding new columns to the output means the query string dynamically changes without the script ever having those columns defined in code. Easy stuff so far.
The issue I have is writing back into the outputbuffer the results without explicitly mapping the result to the output column names.
An easy example to see is..
Excel Spreadsheet Looks like..
ServerName
Server1
Server2
Server3
Output Column Names..
ServerName
Code Snippet..
While Reader.Read
With OutputBuffer
.AddRow()
.ServerName = Reader.GetString(0)
End With
End While
Instead of defining what the column name (servername) is, I want to map back by matching the source column header to the output column name. By doing this I won't ever need to change any code when I add or remove output columns.
Could someone lend a hand with this, and I'll buy you a virtual beer or two. I've already spent more time searching for an answer than I have writing the code thus far, and I think my heads getting more and more muddled by it.
Many thanks,
Jode
I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.
Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.
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 have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
View 5 Replies View RelatedI 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] ....
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
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 was querying to find the first non null address value using the COALESCE function.And I got the correct result.But then I jumped into another question and i.e what if I need to find the column name without knowing the column nameand just by using the column value.What I mean is this...My query was.....
SELECT COALESCE(AddressLine1,AddressLine2) AS [Addresss] FROM Person.Address
This is what I got.
Address#500-75 O'Connor Street#9900 2700 Production Way00, rue Saint-Lazare02, place de Fontenoy035, boulevard du Montparnasse081, boulevard du Montparnasse081, boulevard du Montparnasse084, boulevard du Montparnasse1 Corporate Center Drive1 Mt. Dell Drive
But then what if I just know the address of that person i.e #500-75 O'Connor Street..How am I suppose to retrieve that without knowing the column name.