Hi,
I have to create an expression that makes bold the text for some possibilities, otherwise the font will keep same, is there any staff for creating an expression like "IF ID_Name IN (x,y,z...) THEN bla-bla" like querying in SQL? Cuz I have to use multiple possibilites for same condition
I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof.
The portion that selects all works fine. I am have trouble with the later. My code looks like this:
CREATE PROCEDURE vch_GetSurgeryPatientsWithoutOrdersFromEyeSite_clb
(
@StartDate datetime,
@EndDate datetime,
@center varchar (1000)
)
AS
IF @StartDate IS NULL
BEGIN
Set @StartDate = GetDate()
Set @EndDate = (GetDate ()+90)
END
Declare @StoreList TABLE ( CenterID int )
-- Get our Centers
IF ( @Center = 'ALL' ) or ( @Center IS NULL )
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock)
END
ELSE
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID
FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center
END
the first if statement returns all of the store number like this
CenterID
001
002
010
024
057
the second if returns nothing
I tried using coalesce but it returns the store number like this:
CenterID
001, 002, 010, 024, 057
This does not work with the rest of my program.
Hello,
I have a question about using the query builder, or just straight sql, to create expressions. My first example caused the query builder to generate a query configuration error:
Expression: EIM_REQUISITION_DETAILS.EST_UNIT_COST * EIM_REQUISITION_DETAILS.REQRD_QTY AS COST
Syntax error: Expecting "," or "FROM" after column alias or "*"
Initially I thought I was dead in the water but then I decided to just run it anyway and it worked! I got my cost. OK so fine the query tool doesn't recognize some sql expressions. Because I am trying to make a large flat file I thought I'd concatenate 2 fields to generate a recognizable requisition, which everyone knows is the year followed by a hyphen then the req number:
My old Access query had this: Req: Right([REQ_YR],2) & "-" & [REQ_NO]
and I tried to change it to this: substring (EIM_REQUISITIONS.REQ_YR from 2 for 2) || '-' || EIM_REQUISITIONS.REQ_NO AS REQ
So apparently my older sql book doesn't have the syntax right. Since I plan to do more of this could someone point me to a reliable book or site that could help me with sql syntax?
Thanks,
Dana
Good Morning all.
Is it possible to put multiple expressions in one cell.
Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))
Desired output will look sim to this in one cell
Y = 5
N = 3
NA = 0
Thanks,
Rick
Good Morning all.
Is it possible to put multiple expressions in one cell.
Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))
Desired output will look sim to this in one cell
Y = 5
N = 3
NA = 0
Thanks,
Rick
I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.
Here are my two expressions within the filter:
Code Snippet
=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)
Code Snippet
=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")
The operator for both these is set to "=", and the value is set to "=true".
Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.
Is there anyway to accomplish this programatically?
Thanks in advance for you help!
Is it possible in a precedence constraint to have multiple expressions evaluated? IE
Operation set to Expression and expression to evaluate is: @File = x or @File = y
Thanks
Little complex but I'll try to explain the best I canI have the following table…
UserID___Year___Month_____A1____A2____B1____B2
__1_______4______5______45_____5____43____0
__1_______4______6______10_____12___1_____3
__1_______5______5______12_____22___8_____7
__1_______5______6______11_____10___5_____0
__2_______4______5______9______65___33____66
__2_______4______6______3______3____10____11
__2_______5______5______44_____2___77_____1
I want to pull out the data to show the sum of the totals for each year the user is in. The idea is to then be able to see a comparison for year on year on a month by month basis
Eg.
UserID____TotalAYear4____TotalBYear4___TotalAYear5____TotalBYear5___Month
__1___________50_____________43___________34____________15__________5
__1___________22_____________4____________21____________5___________6
__2___________74_____________99___________21____________5___________5
There would be no data returned for UserID2 for month 6 as there is no data for them for year 5 month 6 therefore no comparison can be made.
I’ve having real difficulty getting the correct SQL syntax to do this. Any ideas or pointers would be great thanks
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
I'm trying to build a sproc with a where cluse that looks at a boolean field. I need to say if [Primary] is 1 then show all primary phones. If [Primary] is 0 or Null then return all records. I have it started like this;
WHERE dbo.tblPhones.[Primary]=Isnull(@PrimOrNot,dbo.tblPhones.[Primary]=1) Or dbo.tblPhones.[Primary]=0
Thanks for any help,
"TempDB in RAM. This option allows SQL Server to process the results of intermediate database operation in memory rather than on the hard disk. If you have enough memory available, using TempDB in RAM can dramatically improve performance of sorting, GROUP BY calculations, joins and queries that require temporary work tables"
Hi, that was a quote from MS white paper over the internet... Does that mean that I can get faster query output once I used the temp DB in Ram... what other things which I do not know that I may worry about if I use tempDB in Ram...
when allocating some ram to the tempdb, does that mean that I am not going to use it ... Please advice
Ali
I am developing an application that uses SQL Server 2000 for the back end. I am at the stage where some modules in the app can be tested while I finish development on some others. I run my own tests against SQL Server running on my own PC but for other people to test I have set up another server with SQL Server 2000 and have restored my database there.
My question is as follows:
I would like any changes to my database (structure and data) to be replicated on the test server's database (not necessarily immediately, but without much delay). I've heard the buzz words (log shipping, replication, etc) but would like some advice on the best way to proceed.
At the moment I don't need any data back from the test server and I don't particularly care if test data on that server is lost although these may become issues later on.
Wondering if anyone has pro's and cons for choosing a vehicle to build front end applications for a SQL Server 2000 back end.
Historically we used Access 97 with VBA code to create client side ODBC connections. In Code we would issue stored procedures through a connection string. This worked fine.
With Microsoft moving onward our company is transitioning to supported products. So DAO is dead.
My colleagues like Access 2000 and have imported some 97 databases doing small re-writes to make them work.
Everything I read says we should be interacting with SQL using ADO.
The other problem is not all users have admin rights on their machines, so adding application extensions for VB6 is a problem. We might be able to change this.
Should we just build with Access 2000 from scratch using ADO to avoid JET?
Should we use VB6?
Is there another application we should be looking at?
Hello,
I am searching for a resolution. I did a few searches on this site and could not gain the exact answer. I apologize is this thread is a duplicate.
I have written a report containing Multi-Values for Reporting Services. When a user selects the values for the report parameters, the checked values continue to be selected after the report is completed.
Is there a way to clear these parameters?
Some of our users run the same report more than once during a single web session.
Thank you for your attention!
I need to make a brief presentation to upper management about High Availability options in SQL Server 2005.
Current choices being considered are:
- Failover clustering
- Log shipping
- Mirroring
Q1: Are there other choices?
Q2: How do these choices compare in terms of cost, complexity of setup, ease of deployment, recovery procedures in case of a disaster?
Ben Aminnia
Dear Anyone,
View 2 Replies View RelatedI'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.
The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.
My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?
TIA
Currently, I am running SQL Server 2000 with the default instance. I know SQL2K can run up to 16 instances simultaneously. I would like to add another instance but am unable to find documentation on how to add it. Books online tells me "about" multiple instances, but not how to set it up. I've obviously missed something somewhere. Can someone please direct me to a document that will help explain "how" to add another instance?
Thanks for your time and your help.
is there a way to create one backup from multiple databases ?
View 3 Replies View Related
Hi,
With Report Builder, I created a basic chart using the chart Wizard..
I am wondering If I can create some 5 charts on a Single Page(Webpage), like a Mini Dashboard.
Can someone please guide me in this regard ?
Thanks in Advance for your help.
Regards,
Sundar
Hello,I need to create around 1500 similar tables.Does anyone know how to create them all at once instead of one-by-one?thanks
View 8 Replies View RelatedHas anyone encounted a problem with Reporting Services (2005) with the following scenario:
A report with a dropdown parameter allowing a Select All choice -
If the selection list is fairly long and Select All is selected - if the user clicks the View Report button the selection list is completely reset (i.e. all items are unselected). This seems to happen if the user quickly clicks the View Report button immediately after clicking the Select All box on the parameter dropdown.
This happens inconsistently - some users experience it frequently while others do not see it at all (using the same parameter values, etc.)
I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.
View 1 Replies View RelatedHi everyone:
I have a question about T-SQL scripting:
I want to create several databases with identical properties: size, name (database1, database2, database5, databaseN), path, etc, and I would want to automate the process
So, I downloaded a good T-SQL script from the Internet, which automates the process of creation of ONE database
The problem is I would like to "put" this script within a bucle sentence; I mean, within a FOR or WHILE sentence, so I could create, for example, thirty identicales DBs without prompting me for the database name (without desktop interaction, I mean)
Here you have the generical T-SQL script:
USE master
GO
CREATE DATABASE emc
ON
( NAME = 'emc',
FILENAME = D:MSSQL7DATAemc.mdf',
SIZE = 5 )
LOG ON
( NAME = 'emc',
FILENAME = 'DMSSQL7DATAemc.ldf',
SIZE = 1 )
GO
If any oy yoy have some similar script for doing this, I would highly appreciate.
Many thx.
I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:
CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS
INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS
INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
Hey
I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyzer I get multiple errors but the one that is coursing me the most bother is
‘Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134
Incorrect syntax near the keyword 'procedure'.’
What does this mean and why can’t I run more than once Stored Procedure at once?
Many thanks
Quish
CREATE TABLE IssueLog (
ItemNo int NOT NULL IDENTITY PRIMARY KEY
REFERENCES IssueProgress (ItemNo)
ON UPDATE CASCADE
ON DELETE CASCADE,
REFERENCES Approvals(ItemNo)
ON UPDATE CASCADE
ON DELETE SET NULL,
Msg 142, Level 15, State 2, Line 0
Incorrect syntax for definition of the 'TABLE' constraint.
I'd like to update or delete the IssueProgress plus update and setting null to the Approvals tables at the same time whenever the ItemNo of the parent table namely IssueLog is updated or deleted.
How can I achieve this?
Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?
View 3 Replies View Related
I have 50-60 stored procedures in my application and these are stored in different files as per file one stored procedudre.
I want to deploy my applicaiton. I want to create a batch or Single SQL file to create all stored precodures in on go.
My objective is to ensure that No stored procedure has left while deploying the applicaiton.
thanks.
Hey
I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me the most bother is
€˜Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134
Incorrect syntax near the keyword 'procedure'.€™
What does this mean and why can€™t I run more than once Stored Procedure at once?
Many thanks
Quish
Hello,
I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?
Any help is appreciated!!
Thanks,
Lisa
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id