How Can I Use Variables In This TSQL Statement
Hi all,
I would like to replace the default directory location (c: emp) and the
filename (emails.csv) with variables like @FileDir and @FileName in the
statement below.
SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')
However, my attempts have not been successful.
Any ideas appreciated, and TIA.
Greg
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Assigning TSQL Variable Values Into Package Variables
I have written TSQL script and I am using variables in it, code as follow DECLARE @CURRENTDATE INT DECLARE @STARTDATE INT DECLARE @ENDOFMONTH CHAR (1) SET @ENDOFMONTH = (SELECT ENDOFMONTH FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE CURRENTDAY=0) IF @ENDOFMONTH = 'Y' BEGIN SET @STARTDATE = (SELECT DATE FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE (CURRENTMONTH + 11) = 0 AND DAYOFMONTH=1) SET @CURRENTDATE = (SELECT DATE FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE CURRENTDAY = 0 ) END ELSE BEGIN SET @STARTDATE = (SELECT DATE FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE (CURRENTMONTH + 12) = 0 AND DAYOFMONTH=1) SET @CURRENTDATE = (SELECT DATE FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE CURRENTMONTH+1 = 0 AND ENDOFMONTH='Y' ) END I want to add @startdate and @enddate variable into Package variable... i dont know how can i achieve this task? can ne 1 help me out thanx in advance regards Anas
View Replies !
I Need Help With This Tsql Statement
Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this. SELECT 'Quarter 1' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 2' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 3' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 4' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_cOMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE Order By 'QTR' asc
View Replies !
TSQL - WITH Statement
Hi guys, I need help with this one... Iam Trying to understand how to use the statement WITH I am running the code below, but getting error. note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server) Thanks in advance, Aldo. Code Snippet WITH MyCTE (FILTER, SORTGROUP) AS ( SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS ) SELECT * FROM MyCTE AS CTE_01; Error Messages: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'.
View Replies !
TSQL From An Access SQL Statement
Good morning one and all, I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql. Any and all help appreciated, Thanx Gurmi
View Replies !
What Is Wrong With This TSQL Statement
hello, I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null --code EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1)))) AND c.column_name = ''to_be_transferred'' AND t.table_type = ''BASE TABLE'' ) BEGIN SELECT * FROM ? WHERE to_be_transferred IS NOT NULL END' But I am getting the following error Msg 207, Level 16, State 3, Line 12 Invalid column name 'to_be_transferred' I thought the if statement filters all those tables with the specified column and do select statement fot those tables only what did I do wrong, any suggestionm? Thanks
View Replies !
Tsql Case Statement
Hi, Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table. And below is the syntax. Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(a.net_sale_Dollars) as sale_TY , sum ( b.net_sale_dollars ) as sale_LY , a.division_name, a.department_number fromFact 1 as a ,Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The current table from this query is showing like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded. The structure of table that I want to create must look like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 2 1 15 0 ABC 2 >>>> want to put 0 value where only one side ( current or parrallel period) has sales info. So, I'm thinking the case statement like: Case statement logic like: ------------------------------------------------------------ if a. dept not exist in b.dept then Sale TY -> a.net_sale_dollars Sale LY -> 0 if b.dept not exist in a.dept then sale TY -> 0 sale LY -> b. net_sale_dollars ------------------------------------------------------------- below is the syntax which doesn't work (it's wrong): Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(case when a.department_number = b.department_number then a.net_sale_dollars else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end) as sale_TY , sum ( case when b.department_number =a.department_number then b.net_sale_dollars else case when a.department_number NOT IN (b.department_number) as sale_LY, a.division_name, a.department_number fromFact 1 as a Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key , ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Is it possible to create these kind of structure? Please give me some comments. Thanks.
View Replies !
TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
Help With TSQL Statement To Check For Table Size
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated select Name=left(c.name,20), c.prec, scale = ISNULL(c.scale,0), t.name from syscolumns c inner join systypes t on c.xtype = t.xtype where id in (select id from sysobjects where name = 'mh_demographic2_') order by c.colid COMPUTE sum(c.prec)
View Replies !
Using Variables In SQL Statement
I am trying to use two variables in my SQL statement to query an access database and then pass the results to a datagrid. Nothing shows up in my datagrid. I think that the syntax on my SQL statement is wrong. I am really not sure how to embed the variables, especially since there are two. I really need help with this, it is for work. The code is posted below. Thanks. Sub Search_Click( s as Object, e as eventArgs) Dim conLibrary As OleDbConnectionDim Category As StringDim Search_Field As StringDim dstResults As DataSetDim dadResults As OledbDataAdapterDim dtblBooks As DataTable conLibrary = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:ewpsychiatrylibrarydb.mdb")conLibrary.Open Category = ddlSearch.SelectedItem.textSearch_Field = txtSearch.textdadResults = New OledbDataAdapter("Select * From rec WHERE '"& Category"' "=" '"&Search_Field"'", conLibrary)dstResults = New Dataset()dadResults.Fill(dstResults, "Table1") dgrdResult.DataSource = dstResults.Tables(0).DefaultView dgrdResult.DataBind()conLibrary.closeServer.Transfer("results.aspx") end Sub
View Replies !
Using Variables In SQL Statement (.NET 3.5, VB, Oracle)
Hi Guys, I am trying to manipulate an SQL statement based on the return from a menu with auto postback enabled.Simply I am working with dates, so I will declare variables for today, next week, last week and so on.How do I then use this in the SQL? I assumed that if I declared the following Dim my_today As String = Format(Date.Now(), "dd/MMM/yyyy")Then in the SQL I used SELECT * FROM my_table item_date = @my_todayI would get a result, but I get an error. What is the right way to do this? Many thanksSteve
View Replies !
Update Statement With Variables
I'm trying to execute an update statement in a stored procedure that includes a variable for a column and a variable for a conditional constraint. For example I want to execute the following UPDATE, but using variables: Update table set flagcode = 'A' where Field1 < 100 the field flagcode is a varchar and the field Field1 is an int This basically how I have my code set up: declare @flag as varchar(20) declare @lowrange as varchar(20) set @flag = 'A' set @thefield = 'Field1' exec('update table set Flagcode = ' + @flag + ' where '+@thefield+' < 100') I get the error: "Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'A'." Please help T-SQL Guru's. You're my only hope.
View Replies !
How Do You Pass Variables To A Statement Plz Help
iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong here is the code if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20) set @TABLEDESC = 'ClientComment' set @DBO = '.dbo.' set @BDFR = 'Commander' + @DBO + @TABLEDESC set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC set @EQID = '80_300_113' insert into @BDTO select from @BDFR where Eqid = @EQID
View Replies !
Using Variables In An Update Statement
Declare @vname varchar(4) select @vname = 'MT1' --This works fine UPDATE tblTmpLA SET MT1 = 2222 / 1000 FROM tblTmpLA WHERE ID = 1 This is the way I want to do it but UPDATE tblTmpLA SET @vname = 2222 / 1000 FROM tblTmpLA WHERE ID = 1 I get the error that Disallowed implicit conversion from datatype 'int' to datatype 'varchar' I guess that the variable does not compile as the text that it holds or so it seems. I want to put this code inside a WHILE loop where the @vname will change on each iteration. Any ideas?
View Replies !
Using Variables In Openquery Statement
Does anybody know how to pass variables to openquery statement? I executed the following statement against DB2 mainframe and got an error message. Thanks in advance Hung-Ban declare @deptname varchar(20) select @deptname = 'HEAD OFFICE' select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG where DEPTNAME=@deptname") Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703 ]
View Replies !
Using Declared Variables In SQL INSERT Statement.
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express. I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL. If I hard code the values in the SQL statement it works fine. Can someone explain what I'm doing wrong accomplish this? My code is below. Thanks in advance. file.aspx <asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT * FROM [Table]" InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" > </asp:SqlDataSource> file.aspx.vb Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click Dim variable1 As String = FileUpload.FileName Dim variable2 As String = Date.Now Dim path As String = Server.MapPath("~/directory/) If FileUpload.HasFile = True Then Try SqlDataSource.Insert() FileUpload.PostedFile.SaveAs(path & _ FileUpload.FileName) End Try End If End Sub
View Replies !
Problem With Variables In Select Statement
I am trying to do a proof of concept on a simple sql statement, but it doesn't work. Am I doing it wrong, or is this not possible.... Table: tblItems field: account varchar(20) In Query Analyzer: DECLARE @acct varchar(20) SET @acct = '457760,123456' SELECT account from tblaccount where account in (@acct) Result: 0 Rows Any thoughts? Thanks, Brian Expected 457760
View Replies !
Stored Procedure With 2 Variables, How To Use Them In The WHERE Statement?
Hi! I need a stored procedure with this basic setup: CREATE PROCEDURE test @Type int AS SELECT * FROM Cards WHERE CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END GO I know that the part after WHERE is wrong. But what I would like to achieve is this: if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value. How can this be done? Thanks! /Rickard
View Replies !
Tuning An Application Statement With Bind Variables
Hi gurus,I just started to look at a very slow-running SQL statementgenerated by an application (Siebel). I spooled the SQL from theapplication, replaced the bind variables by their values, and tunedfrom the Query Analyser. But after awhile, I realized that thestatement using bind variables and the same statement using the valuesinstead of the bind variables often have completely differentexecution plans! Is that normal? Can someone tell me how the SQLServer treats bind variables. Don't worry about being too technical,I'm an Oracle DBA/developer.ThanxDaniel
View Replies !
Send Mail Using Variables From Select Statement
I'm getting the error "No recepient is specified". I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly. I couldn't figure out how to included screen shots. Any ideas?
View Replies !
TSQL Statement Extracting Data From One Table Through Another Table
Hi, I have 2 tables, MembersTemp and Organisations I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this. Initiallt I only have the ExecID for the MembersTemp table MembersType table: ExecID 3013 OrganisationID 4550 Organisation table: ID 4550 (PK) Name "Microboff" Any ideas??
View Replies !
Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)
Hi, I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks. João
View Replies !
How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?
Hi, I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] . I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below). It seems it does not get the values passed in for those variables. I deployed the package to a sql server. are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command. exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD **** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl" /Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer" /Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$" /Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls" /Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp" /Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" ' thanks, Guangming
View Replies !
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005 1 -- Full Table Structure select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id 2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL Or SQL CLR?
Hello Friends, I am not sure if this is the right place to post this question but if not please suggest me when it can be posted. I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR. Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs? When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff. And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful. Please advise.Thanks in advance,-L
View Replies !
TSQL Please Help
Hi I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement. SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric." So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING? I did manage to work out a method by using the follwoing SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0 But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0' IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated Many thanks in advance
View Replies !
TSQL Help
A stored procedure was running slowly so I took the code, removed thesubselect and included a join, then took the max and included as partof a correlated subquery.The result is below, however, this is no improvement over the original.An advice would be greatly appreciated.SELECT FSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID,A.CASH AS CASH,A.VOLUME AS VOLUMEFROM ACTUALS AINNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROMACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =A.COLLECTION_PAYMENT_PERIOD_IDINNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =A.FS_ACTUAL_LINE_TYPE_IDINNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =A.PAYMENT_PERIOD_IDWHEREA.ORG_ID=24771AND A.LSC_ORG_ID=5816AND PP.FUNDING_STREAM_ID=5AND PP.FUNDING_PERIOD_ID=6GROUP BYFSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID, A.CASH, A.VOLUME
View Replies !
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex: Table tblMatchedRecords M BLah Blah2 [DATE] 1 this that 20040101 2 this them 20040102 3 this that 20040630 In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date. Make sense? Can anyone offer any suggestions?
View Replies !
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex: Table tblMatchedRecords M BLah Blah2 [DATE] 1 this that 20040101 2 this them 20040102 3 this that 20040630 In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date. Make sense? Can anyone offer any suggestions?
View Replies !
Tsql Help
I have a few variables declared @amount=100000 (any big int) @pamount=100000 @a1,@a2 - thorugh @25 -- each a bigint @p1,@p2,@p3 @id - int if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values) and then @pamount randomly= to either @p1,@p2 or @p3 Can someone help me do the sql for this? it will be within my stored procedure
View Replies !
TSQL Help...please
I am trying to determine concurrency of a program running based on start and stop times. Right now I have 8863 records to compare, but here is a small sample. Start Time Finish Time 1)2003-12-01 00:31:12.0002003-12-01 01:14:23.000 2)2003-12-01 06:55:43.0002003-12-01 15:50:47.000 3)2003-12-01 07:19:12.0002003-12-01 16:30:06.000 4)2003-12-01 07:22:10.0002003-12-01 15:56:44.000 5)2003-12-01 07:27:46.0002003-12-01 18:36:05.000 6)2003-12-01 07:34:53.0002003-12-01 09:57:15.000 I need to compare the times for overlap to determine concurrency. For instance: Comparing record 1 to record 2: If record1.starttime <= record2.finishtime AND record1.finishtime >= record2.starttime, then count = 1, esle 0 If record1.starttime <= record3.finishtime AND record1.finishtime >= record3.starttime, then count = 1, else 0 AND SO ON AND SO ON... when you encounter a 0, SUM the count as Concurrent. Then, I need it to move to record to and do the same thing... Comparing record 2to record 1: If record2.starttime <= record1.finishtime AND record2.finishtime >= record1.starttime, then count = 1, esle 0 Comparing record2 to record 3: If record2.starttime <= record3.finishtime AND record2.finishtime >= record3.starttime, then count = 1, else 0 AND SO ON AND SO ON... when you encounter a 0, SUM the count as Concurrent. Finally, when the loop (or cursor??) is finished, select MAX(count) as maximum concurrency. I tend to stay more on the system side and less on programming so I am really out of my realm. Any help polishing off the logic, and some pointers on how to write this in T-SQL would be much appreciated. Thanks in advance. Ryan Hunt
View Replies !
TSQL Help
Hi, I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5? I successfully extracted the first name using: firstname = rtrim(substring(name, (charindex(',', name)), 25)) But I am having trouble doing the lastname. Please help. Thanks so much! Laura
View Replies !
TSQL ?
I am using the following statement to do an extract for a data security matrix. I can get the select to work, but not the insert . Any help appreciated. set nocount on declare @dbname varchar(30) declare @sqlperm varchar(50) declare @objname varchar (50) declare @secgroup varchar (50) declare @printline varchar(80) declare @sql varchar(255) declare @sql1 varchar(255) if @dbname is NULL begin declare dbcursor cursor for select name from master..sysdatabases where name not in ('master','msdb','pubs','model','northwinds') open dbcursor fetch next from dbcursor into @dbname while (@@FETCH_STATUS <> -1) begin select @dbname, case spt.action when 26 then "REFERENCES " when 193 then "SELECT " when 195 then "INSERT " when 196 then "DELETE " when 197 then "UPDATE " when 224 then "EXECUTE " end , so.name , (select su.name from ois..sysusers su where su.uid = spt.uid) from ois..sysobjects so join ois..sysprotects spt on so.id = spt.id where so.type in ("P","U","V") select @sqlperm = case spt.action Select @sqlobject = so.name select @sqlgroup - su.name insert into bsrdict..tbl_sql_sec values (@dbname @sqlperm, @sqlobject,@sqlgroup) fetch next from dbcursor into @dbname end close dbcursor deallocate dbcursor end
View Replies !
TSQL
I'm new in T-SQL... Is there way I can do loops in SQL? I’m trying to do something like: "for each a.description in account_codes a where clientId in(1,0) select b.balance from account_balance b"..... Right now I'm trying to execute this: "select a.description,b.balance from account_balance b, account_codes a where a.clientid in (1,0)" the result that I get is: description balance -------------------- --------------------- A income 123.1200 A income 235.1200 B income 123.1200 B income 235.1200 but what I want to see is: description balance -------------------- --------------------- A income 123.1200 B income 235.1200 Any help will be greatly appreciated... Thank you, Nastya.
View Replies !
Tsql Help
Hi, I want to write a query which selects ind and clcode where ind(individual) has taken all 3 classes(clcode) 15, 16 and 17. for example: it should only return data for ind 1 from the following data as only ind 1 has taken all three classes. How can I write it? table: ind_history ind clcode ----------- ----------- 1 16 1 17 2 17 1 15 2 16 3 11 4 15
View Replies !
Tsql Help!
simple update, I want to update max_seq with max(0rdr_seq) from another table. how do you? update h set max_seq = d.max(ordr_seq) from h_drug_stage_dup h join drug_ordr_stage d on h.patkey = d.patkey and h.ordr_dtm= d.ordr_dtm and h.h_drug = d.h_Drug
View Replies !
TSQL Help !
How do I say example, adding three business days with TSQL ? Help is greatly appreciated. Thanks, Jeff
View Replies !
|
TRACKER
