T-SQL (SS2K8) :: How To Add Multiple Spaces To Dynamic String
Jun 15, 2015
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
View 2 Replies
ADVERTISEMENT
Oct 1, 2007
I am facing a reporting service issue.
System information:
1. Our Reporting Server installed on a DB server.
2. We have one windows application which executes on the same server, which generates the reports snapshots.
3. One set of reports with single Shared data source
Scenario:
We have many countries for which report history snapshots needs to be generated. The report information is stored in different databases named as database_CountryCode on the same DB server.
Questions:
1. We need to dynamically change the shared data source connection string to point to the respective country database, when generating the report for that particular country. --> We found out this can be accomplised by using parameterized connection string in report specific data sources in SQL 2005. Can we achive something similar in SQL 2003 Reports as well?
2. We also need to instantiate the reporting service web service in multiple threads, one for each country, where in each thread picks up the corresponding country code, changes the connection string and generates reports snapshots. Can this be accomplished? I know this goes against the entire idea of licensing, but my question is just about feasibility of this idea.
Addnl Info
The CreateReportHistorySnapshot method of reportingservice.asmx returns a snapshotID, which is the timestamp of report snapshot creation. We tried creating the same report snapshot for the same country in 2 threads. Whenever the timestamp for both snapshots was same, only one report snapshot actually got created. Can this be overcome?
View 1 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Jul 14, 2014
I am loading a dimension using a distinct query.There are duplicates coming through and the only differnce is a trailing space on one of the columns.
RTRIM is not removing the space.
how i can fix it?
View 4 Replies
View Related
Nov 1, 2007
declare @DatabaseName varchar(128)
set @DatabaseName = 'my new db test'
DECLARE @SQLStr varchar(500)
PRINT @DatabaseName
set @SQLStr = 'use '
+ @DatabaseName
+ ' PRINT '
+ @DatabaseName
EXEC (@SQLStr)
Error Output:
--------------------------------------------------------------------------
my new db test
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'my'. No entry found with that name. Make sure that the name is entered correctly.
-----------------------------------------------------------------------
Any idea how to get the entire database name to be used with spaces in the database name. It prints the @DatabaseName just fine, but in the dynamic sql, it truncates after the first space.
I already tried N'my new db test' in the set statement. That didn't work. I tried using NVARCHAR when declaring the variable.
Let me know as soon as you can. Thanks!!
View 7 Replies
View Related
May 17, 2015
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
View 5 Replies
View Related
Aug 29, 2014
I am trying to debug a procedure which is timing out, not in every case, but in some cases.
During my perusals, I note that there seems to be a stray space in the 3rd line of following fragment of an UPDATE statement in the procedure:
...
FROM dbo.LoadMedicationsGP LMGP
INNER JOIN dbo.Patient P ON LMGP.PatientId = P.PatientId
INNER JOIN dbo. PatientMonths PM ON P.PatientKey = PM.PatientKey
AND LMGP.DatePeriodKey = PM.DatePeriodKey
...
SSMS seems to parse the statement perfectly OK, but to me it looks as if it ought to be wrong.
I suspect that the space has no material effect, but I just wanted some confirmation one way or the other.
View 4 Replies
View Related
Mar 19, 2015
I have a procedure that generates some XML from a bunch of tables.Then i use BCP to export it to a file. This works just fine.Here's the sample code:
CREATE TABLE dbo.t_test (i INT, z VARCHAR(30) COLLATE DATABASE_DEFAULT)
INSERT INTO t_test (i, z)
SELECT1, 'Test'
GO
[code]....
But recently, i wanted to add a test that calls this procedure. So, Test procedure uses INSERT / EXECUTE thingy to put XML data into a temp table to compare things. But then you get following error: "The FOR XML clause is not allowed in a INSERT statement."
DECLARE@T TABLE (x XML)
INSERT INTO @t (x)
EXECSPRC_EXPORT -- crashes here
So, i thought, fine, i'll wrap the FOR XML inside a sub-SQL:
CREATE PROCEDURE SPRC_EXPORT
AS
SELECT(
SELECT*
FROMt_test
FOR XML PATH('root')
)
And BCP call still works, BUT, now the file generated becomes 64kb instead of 1kb :) When i look into the file, it displays same XML, but the string is right-padded with a LOT of spaces.how BCP uses SET FMTONLY, OR that the "type" of result somehow gets changed when i do the wrapping.
View 1 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Dec 8, 2014
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
[Code] .....
View 5 Replies
View Related
Jan 22, 2007
Hi,
I am trying to add spaces to a string value in a cell but when I run the report the value is trimmed.
Any ideas?
Here is the expression I am using:
=Fields!strVal.Value & " "
Thanks,
Igor
View 9 Replies
View Related
Feb 15, 2007
I need to map several columns of data from one database to anotherwhere the data contains multiple spaces (once occurance of a variablenumber or spaces) that I need to replace with a single space. Whatwould be the most efficient way to do this? I am using SQL2K. I wasthinking a function since I know of no single Transact-SQL commandthat can accomplish this task.
View 8 Replies
View Related
Sep 10, 2015
I have several fields that have multiple spaces between the City State and Zip Code. I want to be able to make only one space between each. A combination of Substring and Replace is what i have been trying but not able to make it work. a do while might be what i need but not sure how to do it.
View 10 Replies
View Related
May 21, 2008
I have records where there are multiple spaces between items of text. I would like to strip out all the excess spaces characters. To that end I have written to separate functions (both are listed below) neither of which I would imagine will be particularly efficient. The first uses an int table and the second a while loop. I am presently ignoring the leading and trailing space issue as this can easily be recitfied with LTRIM and RTRIM. However, I would be keen to know if there is a better way of achieving a similar result.
CREATE FUNCTION dbo.StripAdditionalSpaces (@Input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Changed int, @Output varchar(1000)
SELECT @Output = ''
DECLARE @String TABLE ([Output] varchar(1000))
INSERT INTO @String ([Output]) VALUES (@Input)
SELECT @Output = @Output + SUBSTRING([Output], i1.Position, 1)
FROM @String
LEFT JOIN dbo.Ints i1
ON SUBSTRING([Output], i1.Position, 1) <> ' '
or ISNULL(SUBSTRING([Output], i1.Position - 1, 1), '') <> ' '
RETURN @Output
END
GO
CREATE FUNCTION dbo.StripAdditionalSpaces2 (@Input varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Output varchar(1000)
SELECT @Output = @Input
WHILE @Output LIKE '% %'
BEGIN
SELECT @Output = REPLACE(@Output, ' ' , ' ')
END
RETURN @Output
END
GO
View 12 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Aug 14, 2014
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
View 6 Replies
View Related
Sep 17, 2014
I am creating dynamic pivot and my column order is as below
[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]
but i want to display as below:
[Prior],[2014-08],[2014-11],[2015-02],[2015-04]
View 1 Replies
View Related
Dec 27, 2014
I need to retrieve columns names(instead of select * from) as single row from table in dynamic way.
i m using following query.
its working fine while using from selected database. but its not working when i m running from different database.
DECLARE @columnnames varchar(max)
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
I need to pass database name dynamically like using by variable.
Is this possible to use variable after from clause. or any other methods?
eg:
DECLARE @columnnames varchar(max)
DECLARE @variable='db_month11_2014'
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from @VARIABLE.INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
View 9 Replies
View Related
Apr 9, 2015
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Insert
('+@columns+',KickoutID) Values('+@columns+',1);
Dynamic code:
Declare @columns as nvarchar(max);
Declare @InvSQL as nvarchar(max);
SELECT @columns =
STUFF ((
SELECT ', [' + name + ']'
FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
[Code] ....
View 8 Replies
View Related
Jun 23, 2015
Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
inner join (execute a.thesql) as b
on a.userId=b.userid
View 9 Replies
View Related
Mar 18, 2014
How do you take a string and search for the first "-" and then drop everything from the start to, and including, the "-"?
View 5 Replies
View Related
Jul 29, 2014
I need to create a TSQL to return MAX(Value) removing the first part and last part.
Example
I have these Varchar Reference code:
1.00001-Q1
2.00100-Q2
3.00005-Q4
I need to cut the string to find the max number excluding (Part1):
1.
2.
3.
And also excluding (Part3):
-Q1
-Q2
-Q4
In this case converting varchar to INT, the correct value that i want is: 00101
The middle part excluding (Part1 and Part3)
Then my final reference could be:
1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4
View 3 Replies
View Related
May 18, 2015
I have a table Alert_Types with these fields :
alert_id,
alert_source,
body,
Now whenever a new alert is registered record goes in table like
alert_id = 1,
alert_source= document_detail_view,
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.the converted string was then sent forward using a webservice.now my requirement is to add conditional values in body field e.g:
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
IF isnull(@rate, 0) > 0 Rate: @rate
IF isnull(@rate, 0) > 0 Amount: @amount
how can i parse strings like this. I'm open to change format of values for body field.
View 9 Replies
View Related
May 12, 2004
I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()
This is what I would write if the name of the table was known:
Select @RecordNo = MAx([VehicleID]) from Alto
This is what my dynamic SQl string looks like:
Select @SqlStatement = 'Select Max([VehicleID]) from ' + @TableName
So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?
View 17 Replies
View Related
Jun 6, 2000
Hello,
I'm trying to build a dynamic SQL string in the following
SP, but the ' and "" character gives me trouble regarding
the @strPosition. Position could be A,B,C etc. All kinds of
suggestions are very welcome!
Regards,
Abrazame
CREATE PROCEDURE spEstateList
@PositionID nvarchar(1)=NULL
AS
DECLARE @strPosition nvarchar(20),
@strSQL nvarchar(500)
/* Build WHERE-string */
--Position
IF @PositionID IS NULL
SELECT @strPosition = '1=1'
ELSE
SELECT @strPosition = 'tblEstate.Position=' +@PositionID
/* Build complete SQL-string */
SELECT @strSQL = 'SELECT *
FROM tblEstate
WHERE ' +@strPosition + '
ORDER BY EstateID'
/* Execute SQL string.*/
EXEC sp_executesql @strSQL
View 1 Replies
View Related
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Aug 20, 2014
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result.
2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS (
SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL
SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL
SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL
SELECT 'more_stuff.yoursminehershis_falafel', 'yours first'
)
SELECT string, pattern, ca.item, ca.itemnumber
FROM testctes
CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
View 9 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Oct 27, 2014
I am having a
Source Table: #test1(studID, FIrstNAme,LasteName)
Target Table: #Test2(StudID, UserName)
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar
2, Arun, Pawar
3, Ashis, Panday
Then i want to create username like:
apawar
apawar01
apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.
View 5 Replies
View Related
Nov 19, 2014
I need to add a filter clause like
WHERE username = '%%%'
I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.
View 6 Replies
View Related
Jan 22, 2015
i have 2 table:
t1:
t1_idint
namenvarchar(10)
t2:
t2_idint
t1_idint
Sematnvarchar(50)
then i write following SQL Command
SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product
FROM dbo.T1 INNER JOIN
dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id
GROUP BY dbo.T1.t1_id
but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.
View 3 Replies
View Related
Mar 27, 2015
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
View 8 Replies
View Related
