I am building my SELECT on the fly by use of IF statements something like:
SET @searchParam = @searchParam + ' AND tblXYZ.country = ' + @searchStringCountry
Then I am trying to use @searchParam as my WHERE clause:
WHERE@searchParam
How do I get the string out of @searchParam to use as my WHERE clause?
Thanks in advance!
hi,
i have a global variable that is a file dir param lets say: d:input2006_07_18.bcp.
this param supposed to be built from other 3 params i.e: day, month and year.
how do i build it dinamically i need the exact syntax. i have already put the filedir param as an evaluated expression but when i try to do somthing like:
@[User::Filename] + @[day] + @[month] + @[year] i get an error, although i succeed in putting only one param at its expression i.e: @[user:filename].
the question is how do i build the parameter that will b built from these 3 params in its expression.
Brian, i will b happy to hear from you in regard.
thx,
Tomer
I am building a SQL statement that returns a number.when I execute the Built SQL statment EXEC(@Build). What I need to donow is take that number that comes back and store it in anothervariable so I can do some conditional logic. Any ideas? See SQL below.Something like @Count=Exec(@Build) which I know doesnt work.Thanks,PhilDECLARE @PullDate varchar(12)SET @PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''PRINT(@PullDate)DECLARE @COUNTER BIGINTDECLARE @SELECT VARCHAR(500)DECLARE @SELECT2 VARCHAR(1000)DECLARE @BUILD VARCHAR(5000)SET @SELECT='SELECT COUNTER FROMOPENQUERY(PROD,'SET @SELECT2='''SELECTCOUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTERFROMCOLLECTOR_RESULTS,WHEREWMB.COLLECTOR_RESULTS.ACTIVITY_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCOUNT.END_DATE ) =to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy''''))GROUP BYWMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'SET @BUILD=@SELECT+@SELECT2PRINT(@BUILD)EXEC(@BUILD)--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD STOREDINTO @COUNTER so I can do a conditional statement.)if @COUNTER>=1beginprint('yes')end
View 6 Replies View RelatedHello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
Hi,
I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int,
ClientId int,
StartDate datetime,
EndDate datetime,
ClientActCode char(2)
CodeDescription char(15)
so based on what the user has selected i am getting the names of each codes
and they have the following Rows.
PlanId ClientId Startdate EndDate ClientActCode CodeDescription
111 1 1/1/2008 3/31/2008 01 Begininng balance
111 1 1/1/2008 3/31/2008 02 Contributions
111 1 1/1/2008 3/31/2008 03 something
111 1 1/1/2008 3/31/2008 04 sdkfjdkf
111 1 1/1/2008 3/31/2008 05 dfdfd
111 1 1/1/2008 3/31/2008 06 dfddfs
111 1 1/1/2008 3/31/2008 09 dfdf
111 1 1/1/2008 3/31/2008 15 dfdkfdlfk
and my fund balance i have the following rows..
PlanId int
Participantid int
StartDAte datetime,
end date datetime,
FundId int
Loans
Act1 char(2)
TotAct1 money
.
Act20 char(2)
TotAct20 money
and the data is as follows
PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20
111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561
111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00
so on and so so forth
tht Act1 matches the ClientActCode in the Act..
So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
Any help will be appreciated..
Thanks
Karen
Hi,
Please Help me to build this query.
I have got a "User" Table
---------------------
UserID UserName
---------------------
1 Tuffy
Another Table "Groups" Table
---------------------
GroupID GroupName
---------------------
1 Manager
2 Employee
3 Sales
I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this
---------------------
UserID GroupID
---------------------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
---------------------
Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"
So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"
Please help me how to write this query.
Regards
I have a table with 2 columns:
Col1 Col2
stat1,stat2 AV,AD
stat3 TD
I need to build a query like this:
SELECT *
FROM table3
WHERE stat1=AV and stat2=AD and stat3=TD
How do I do this?
Here is my problem.
I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.
Table 1
Asset Name | InstanceID | Site | Department
Table 2
InstanceID | User Name |
Table 3
User Name | Site | Department
Help will be really appreciated!
Thanks
I need to build following query:
Each item has one or more manufacturers:
Is any way to get all the items with all manufacturers where one of manufacturers fit required expression.
I will try to explain in example:
items | manufacturers
------------------------------
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
ABC2 | skoda
ABC2 | subaru
ABC2 | lada
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Example for Input: *olf*
Output:
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
Example for Input: *aul*
Output:
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Thanks
Hello to all,
I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together.
I used this query:
select * from Ta where Ta.Id = @ID union all select * from Tb where Tb.IdOfa = @ID
but it doesn't work and the following error message comes
"All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "
I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.
I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:
This gets all of the organization, their contactId and their primary Address (as long as they have one).
SELECT ContactID, FullName, PrimaryAddressType
FROM tblContactInformation
WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'
This gets a list of all of the individuals that have an organization assigned.
SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType
FROM tblContactInformation
WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL
Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).
UPDATE tblContactInformation
SET PrimaryAddressType =
WHERE PerOrg = '1'
At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?
I have a table which tracks changes by user, department and date. I want to construct a query which returns two numbers, the first is all the changes by department on a certain date. The second is all changes by user on the same date. I know that the queries seperately will look like,
SELECT COUNT(User) As NumberByUser
FROM Table
WHERE (Date = 'DateGoesHere') AND (User= 'UserGoesHere')
GROUP BY User
SELECT COUNT(Department) As NumberByDepartment
FROM Table
WHERE (Date = 'DateGoesHere') AND (Department = 'DepartmentGoesHere')
GROUP BY Department
But how do I go about linking the two in the same query? Any help would be appreciated, thanks!
an error, incorrect syntax, was generated when the following query was tried:
CREATE TABLE ASPNET2(id smallint IDENTITY(1,1) PRIMARY KEY,word nvarchar(50) NOT NULL,definition nvarchar(MAX) NOT NULL)
I wanted to use nvarchar(MAX) because something I read suggested that I couldn't do editing with the controls if a variable was set to text.
Thanks.
-Larry
Hi there,
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
Can anyone help?
Thanks
Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan
View 1 Replies View RelatedCan anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Hi Everyone,I'm drawing a blank here and I am hoping someone can point me in the right direction. I have a table with the following columns (some omitted)IDGUIDPageNameParentPageID I want to build a hierarchical navigation system (2-tier). The conceptual problem that I am running into was getting this information from the same table. Initially I was going to use a nested repeater but I am thinking a treeview would be better. Anyway, the problem is the query. How would I start with something like this? Let's use the following as an exampleRows(ID '1', GUID '888....', PageName 'Page A', ParentPageID '-1')(ID '2', GUID '111....', PageName 'Page B', ParentPageID '-1')(ID '3', GUID '222....', PageName 'Page C', ParentPageID '-1')(ID '4', GUID '375....', PageName 'Page 1', ParentPageID '1')(ID '5', GUID '562....', PageName 'Page 2', ParentPageID '1')(ID '6', GUID '874....', PageName 'Page 3', ParentPageID '2')
(ID '7', GUID '388....', PageName 'Page 4', ParentPageID '3') So, I want to be able to build a query so that I can do the followingPage A Page 1 Page 2Page B Page 3Page C Page 4 Any help would be greatly appreciated. Thanks!
I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.
View 1 Replies View RelatedNot sure if this is the place to post this, but here goes.
I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.
Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.
Thanks.
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
I have 3 tables, a supplier table, a types table and a relationship table between the two..I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.
CREATE TABLE #Suppliers(
id INT,
nam NVARCHAR(50)
[code]...
It's possible to do this in "one" query?
Hi all,I'm a newbie to SQL and I need help with investigating what ways arepossible to build an interface of some sort that wraps around a SQLquery script.I have a simple SQL query which we normally run inside Query Analyzerto update certain fields in the SQL DB based on FQDN provide by theuser. The user normally opens up the query, edit the script to replaythe xxxxx in the line "set @FQDN="xxxxx" with the node name and thenfires it off.I would like to know what are the ways to wrap a simple user interfacearound it so the user does not have to open and edit the scriptdirectly. A simple GUI or Excel type form interface that has a freeform text field for input and then a button to run the script.Obviously, I would like to see the output in some sort of output paneas well.Is this something that can be done in Query Analyzer ?Thanks in advance,Michael.
View 1 Replies View RelatedCan i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
Hi all,
Sorry if this is the wrong place to ask but I have tried a few places with no joy.
In SQL when querying using a variable I can use @ symbol.
ie:
SELECT COUNT(*) FROM dbo.Persons WHERE SocialSecurityNumber = @SocialSecurityNumber
Then in say visual web developer this would prompt me to enter the variable for @SocialSecurityNumber
This all works perfect.
My issue is when I query a DB2 DB directly say
SELECT COUNT(*)
FROM DB2.table
WHERE (MMITNO = @MMITNO)
I get ther error "Column @MMITNO does not exist"
My question is. What symbol do I use to represent variable in DB2.
In SQL it is @ ..I think these symbols are known as sigils
Please Help
Ray..
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE CASE WHEN @Field = 'Fname' THEN Fname = @Value END, CASE WHEN @Field = 'Lname' THEN Lname = @Value END, CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
View 1 Replies View RelatedHello
I have a sql statement that i want to assign a variable to a field. Can anybody tell me how to do it.
queryString = "select [problemdescr] from [Problemlist] where [problemnumber] = abc "
what i want is to assign the value of the variable abc to problemnumber. I have tried to use @ to solve the problem, but i couldn't.
any help
How do i use a variable in a select query
---------------
Declare @host varchar(20)
set @host = '"Fab"'
Declare @DB_name varchar(25)
set @DB_name = 'config_mpl_18May2005'
Declare @Full_Path varchar(60)
set @Full_Path = @host + '.' + @DB_name + '.dbo.cfg_dn'
Print '@Full_Path = ' + @Full_Path
------ cfg_dn ----------
Print 'Starting on CFG DN'
select * from @Full_Path
where dbid = 5441 or dbid = 5389 or dbid = 562
Error:
Server: Msg 137, Level 15, State 2, Line 16
Must declare the variable '@Full_Path'.
What is the easiest way to pass a value into a variable...
TSQL...
declare @@test int
--select count(*) from authors
select count(*) into @@test from authors
I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.
google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.
Here is the basic gist of my query:
exec SP_GET_FILE_NAME @file_name OUTPUT
I need to take the resulting @file_name and store it in an ssis variable. How?
Hi ,
Is it possible to use a system variable in a 'Execute sql task'
or any other query component in SSIS?
Hello Again, did you miss me?
ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:
DECLARE @A VARCHAR(50)
@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'
There is a form to do it?
Thanks Friends