i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks. Thank you, Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms) SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#, dbo.tblCustomersIOA.Active, iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) , dbo.tblCustomersIOA.CreditLimit, FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN dbo.tblCustomersIOA LEFT OUTER JOIN WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.
I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.
I'm trying to append rows horizontally - I'm using the "xml path" approach
SELECT E.[USER_NAME] As 'User Name', ( SELECT ',' + C.[PERMISSION_NAME] FOR XML PATH('') ) As [Associated Groups] FROM TABLEA As A JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF WHERE A.[OBJECT_NAME] = 'MyObjectName'
It's not working. I'm getting:
User nameAssociated Groups A. SmithG1 A. SmithG2 A. SmithG3 etc...
What I'm looking for is:
User NameAssociated Groups A. SmithG1, G2, G3 etc...
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.
declare @i int,@output varchar(max) set @i = 1 while @i < 10 begin set @output = @output + convert(varchar(max),@i) + ',' print @output set @i = @i +1 end
Am abit new to SQL Server Programming and I have an issue which I need Assistance.
I have a table with the following Colunms Inv_Number ItemCode1 ItemCode2 ItemCode3 ItemCode4 ItemCode5 this table as data which i want to put to another table with the following Columns Invoice_Number ItemCode Amount
What i mean here is that Invoice Items were static and were about five and the new programs am designing will have more item codes.
Create a Function call InitCap accepts 1 parameter of type varchar returns a varchar It should only capitalize the first letter of the string passed to it. Items to watch out for. a-z not starting the string The 1st letter is already capitalized
This problems deals with ASCII characters, just wanted to get some insight here.
Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;public static DataSet SQLExecute(string strSQL, string strError) { DataSet dsetResponse = new DataSet(); try {using (SqlConnection conn = new SqlConnection(DHOC.clsDHOC.GetConnString())) {SqlCommand cmd = new SqlCommand();cmd.CommandType = CommandType.Text; } }catch (ThreadAbortException thEx) {throw; }catch (Exception ex) { string strError }return dsetResponse; }
Hi, When I try to create a CLR function in SQL 2005 (June CTP) I get the following error: Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'. The assembly registers successfully, with no errors. I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO The function's code is the following: using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient; namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) { //Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return ""; //Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . } return cFinal; } }} Thanks in advance, Anderson
Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.
Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.
declare @Radius float(10), @BaseDiameter float(10), @BaseCircumference float(10), @BaseArea float(10), @Height float(10), @SurfaceArea float(10), @TotalArea float(10), @Volume float(10) set @Radius = 19.1 set @BaseDiameter = @Radius * 2 set @BaseCircumference = @BaseDiameter * PI() set @BaseArea = power(@Radius,2) * PI() set @Height = 16.27 set @SurfaceArea = 2 * (pi() * @Radius * @Height) set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI()) set @Volume = (power(@Radius,2) * pi()) * @Height select @Radius as [Base Radius], @BaseDiameter as [Base Diameter], @BaseArea as [Base Area], @Height as Height, @SurfaceArea as [Side Area], @TotalArea as [Total Area], @Volume as Volume go
So how do I make a function that does the same thing?
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:
Code Snippet CREATE FUNCTION f_GetGroupMembership(@providerKey nvarchar(200), @connection nvarchar(1000)) RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25)) AS EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser
This returns an error:
Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1
The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.
Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:
Code Snippet using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.DirectoryServices; using System.Collections; public class GroupMembership {
public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type) { //cast the enumerator passed in group grp = (group)obj; //create output parameters guid = new SqlGuid(grp.guid); name = new SqlString(grp.Name); type = new SqlString(grp.applicationData); } }
Can anyone tell me why this is happening? How can I overcome this problem?
Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes
Public Class SICTrans
Public Shared Function TransSIC(ByVal inpSIC As String) As String 'Dim conn As SqlConnection = New SqlConnection("context connection=true") Dim NewSIC, TempSIC, tempFSIC As String
If Len(NZ(inpSIC)) > 0 Then TempSIC = NZ(inpSIC)
If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))
tempFSIC = tempFSIC + "/" + Right(TempSIC, 1) End If End If
NewSIC = tempFSIC
TransSIC = NewSIC
End Function Public Shared Function NZ(ByVal input As String) As String If Not (input Is Nothing) Then Return input Exit Function End If Return String.Empty
End Function End Class
Which compiles fine...
i then use the following code to create the assembly in SQL which is fine:
USE NARD GO CREATE ASSEMBLY SICCodeTrans FROM 'c:SICCodeTrans.dll' WITH PERMISSION_SET = SAFE GO
but when i goto create the function with the following code it wont have it!
CREATE FUNCTION TransSICCode(@inpSIC varchar) RETURNS varchar AS EXTERNAL NAME SICCodeTrans.SICTrans.TransSIC GO
It gives me the following error message
Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1 Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.
I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.
When I hard code the script to use the name of my schema, wcadmin, it works OK.
CREATE FUNCTION wcadmin.dectohex(@a numeric) RETURNS varchar(8) BEGIN DECLARE @x varchar(8) DECLARE @y varchar(1) DECLARE @z numeric DECLARE @w numeric
SET @w=@a SET @x=''
WHILE @w > 0 BEGIN SET @z = @w % 16;
SET @y= CASE @z WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' ELSE CAST(@z AS varchar) END
SET @w = ROUND(@w/16,0,1) SET @x = @y + @x END
-- Pads the number with 0s on the left SET @x = RIGHT(REPLICATE('0',8) + @x ,8) RETURN @x END; GO
select 'WTDOCUMENT' HOLDER, dm.WTDocumentNumber ITEMNUMBER, dm.name ITEMNAME, ad.fileName ContentFilename, fh.hostName VaultHost, wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile, fm.path VaultPath from WTDocument di, WTDocumentMaster dm, HolderToContent hc, ApplicationData ad, FvItem fi, FvFolder ff, FvMount fm, FvHost fh where di.idA3masterReference = dm.idA2A2 and fm.idA3A5 = ff.idA2A2 and fm.idA3B5 = fh.idA2A2 and fi.idA3A4 = ff.idA2A2 and ad.idA3A5 = fi.idA2A2 and hc.idA3B5 = ad.idA2A2 and hc.idA3A5 = di.idA2A2
DROP FUNCTION wcadmin.dectohex; GO
But when I remove my schema name I get the error
'dectohex' is not a recognized built-in function name.
In this case I'm just using :-
CREATE FUNCTION dectohex(@a numeric) . . . . select 'WTDOCUMENT' HOLDER, dm.WTDocumentNumber ITEMNUMBER, dm.name ITEMNAME, ad.fileName ContentFilename, fh.hostName VaultHost, dectohex(fi.uniqueSequenceNumber) VaultFile, . . . . DROP FUNCTION dectohex;
Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.
I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.
i have a function Create Function ReturnAmountB(@CMID int) Returns Decimal as BEGIN declare @Return decimal select @Return =sum(PD_PaymentAmount) from Payment_Details where PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID return @Return END
when i run this on server(remote) this error shows... Incorrect syntex near 'Function' Must declare the variable '@CMID' A RETURN statement with a return value cannot be used in this context
i am not able to understand why this error shows... Any solution!!!
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
hi to all, i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void) i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error. there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error....................... Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
Hi everyone, I'm migrating some information for a client at the moment. They had everything in Excel files and I'm getting them into SQL Server. There are some differences in the way I am storing data and the way they were storing data.
For each client they stored, they had something like Rel1 Rel2 Rel3 100 101 102
Now, what I have is a seperate row for each of Rel1, Rel2 and Rel3 so I would have 3 seperate rows with identical information except for Rel1. So I would have: Rel1 100 101 102
So one way I thought of doing it was inserting a new row specifying that the value for Rel2 should be stored in Rel1 and for the next row that the value for Rel3 should be stored in Rel1.
Now, I am able to do this but SQL Server inserts an extra row will the NULL value in Rel1. Does anyone know why this would be happening? I think what it is doing is finding a NULL value in Rel3 after creating the two extra rows and is inserting that NULL. So I think I need to check for NULLs and not allow it to create a new row if, say, Rel3 is NULL.
Any pointers are gladly welcome. (I know it's complicated )
I am ultra new to this so thanks in advance for any help. I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx : Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();} If there is absolutely any insite into the problems with my code, I really appreciate it.
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...
1. tGradeHist Table Script One (Needs to be run first)
a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'
b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
GradeCode - Same
GradeLocationCode - Same
Step - Same
GradeStartDate - '7/1/2007 12:00:00 A.M.'
GradeEndDate - NULL
GradeCurrencyCode - Same
StepAmount - Result of b (above)
GradeFrequencyCode - Same
RangeMaximumAmount - Same
RangeMidAmount - Same
RangeMinimumAmount - Same
GradeCurrentFlag - 'True'
MarketMaximumAmount - Same
MarketMidAmount - Same
MarketMinimumAmount - Same
GradeGUID - Same
TSCOL - Same
d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'
2. tPersonBasePayHist Table Script Two (Needs to be run second)
a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL
b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
PersonGUID - Same
PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'
PersonBasePayEndDate - NULL
PersonBasePayCurrencyCode - Same
PersonBasePayAmount - Result of b (above)
PersonBasePayFrequency - Same
PersonBasePayPayrollFrequencyCode - Same
BasePayReasonCode - 'SA'
ConductedBasePayReviewDate - Same
ScheduledBasePayReviewDate - Same
PayrollCode - Same
PersonBasePayCurrentFlag - 'True'
ApprovedByPersonGUID - Same
PersonBasePayGUID - Same
TSCol - Same
d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like
Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK'
now can i make a function which returns 'NL','UK','IN','GN','JK'
