Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO
Hey,First, sorry if this post appear twice, because, I can not find my postrecently send, trying to post it once again.I'm out of ideas, so, I thought, will search help here again :(I'm trying to prepare a view for ext. app. This is in normal cases veryeasy, but what if the view structure should be dynamic?!Here is my point (I will siplify the examples).I have a table:create table t_data (id bigint identity (1,1) not null,valvarchar(10) not null,data varchar(100) not nullconstraint [PK_t_data] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_data (val, data) values('1111111111','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('2222222222','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz12345abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('3333333333','12345abcdefghijklmnoprstuvwxyz12345 67890abcdefghijklmnoprstuvwxyz1234567890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('4444444444','67890abcdefghijklmnoprstuvwxyz12345 67890abcdefg12345hijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('5555555555','1230abcdefghijklmnoprst12345uvwxyz1 234567890abcdefghijklmnoprstuvwxyz67890abcdefghijk lmnoprstuvwxyz')gocreate table t_dataVal (id bigint identity (1,1) not null,valvarchar(10) not null,fill varchar(4) not nullconstraint [PK_t_dataVal] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_dataVal (val, fill) values ('1111111111','AAAA')insert into t_dataVal (val, fill) values ('2222222222','KKKK')insert into t_dataVal (val, fill) values ('3333333333','DDDD')insert into t_dataVal (val, fill) values ('4444444444','ZZZZ')insert into t_dataVal (val, fill) values ('5555555555','CCCC')gocreate table t_conf (id bigint identity (1,1) not null,start int not null,length int not null,description varchar(20) not null,constraint [PK_t_conf] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_conf (start, length, description) values (1,10,'value_1')insert into t_conf (start, length, description) values (11,3,'value_2')insert into t_conf (start, length, description) values(55,15,'value_3')insert into t_conf (start, length, description) values (33,2,'value_4')insert into t_conf (start, length, description) values (88,1,'value_5')insert into t_conf (start, length, description) values (56,7,'value_6')goNow here is the issue:table t_conf contain data, which can be modified by user. The user isseting the appropriate values.Now, there should be a view, which returns:- as headers (collumn names) this what is defined in description columnof t_conf (for example: value_1, value_2 ... value_6)- as values, substrings of all data from t_data, cutted with start andlength values for appropriate decription from t_conf.- first two columns of view, should be column val and fill of t_dataValtableSo the effect should be like this:valfillvalue_1value_2value_3value_4value_5value61111111111AAAA1234567890abc....2222222222KKKK1234567890abc....3333333333DDDD12345abcdefgh....4444444444ZZZZ67890abcdefgh....5555555555CCCC1230abcdefghi....of course, for all other value_x should be the appropriate substringsshown.Sounds simple, hm?Well, I'm trying to do this, since yesterday evening, and can not :(In real life, the call of view/function might happend a lot.The table t_data might have around 4000 records, but the data string islonger (around 3000 characters).Application, might acess a udf, which returns table, and I was focusingin that.Was trying, to create local temp table in function, to insert values,using cursor over t_conf.Unfortunately, everything what I get, is just a vertical representationof the data, and I need it horizontal :(The other problem in function is, that I can not use exec() (wll known)so I can not even create a table,dynamicly, using as column names description value from table t_conf,and as size of field length from this table.Sorry, that the description is maybe not exactly for my problem, butthis is because I'm not even sure, which way to use :(any help will be appreciated!Thank You - Matik
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
We are seeing memory issues with a simple C# based TVF that splits an input string into a table based a delimiter. Entries such as the following show up in the SQL log:
AppDomain 8 (DBName.dbo[runtime].7) is marked for unload due to memory pressure. AppDomain 8 (DBName.dbo[runtime].7) unloaded.
These entries only show up after the TVF has been called many times (~ half million times or more).
We encountered the same memory issues with June CTP, but they appeared to be fixed in Sepetmber CTP. Somehow the issues come back for us in the SQL Server 2005 RTM version. With June CTP after these errors show up many many times, the SQL server had to be re-started.
I'd appreciate any comments/ideas regarding these error messages in the SQL log?
We are using the RTM version of SQL2005: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.
Low Qty High Qty Fee Actual Qty Price 0 7500 0.025 7500 187.50 7501 15000 0.020 2221 44.42
Below is the table definition that we have to work with (ugghh).
What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628
The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.
ALTER FUNCTION [dbo].[fn_GetPrice] ( @plincdvarchar(3), @pgrpcodevarchar(4), @pitmcodevarchar(4), @qtydecimal(10,1) = 1, @corpnbrvarchar(9) )
Hi All, I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder. The relevant code (so far) is:Dim sqlServer As New Server() With sqlServer.ConnectionContext .ServerInstance = "(local)" .Connect() .Disconnect() End With
For Each db As Database In sqlServer.Databases ListView1.Items.Add(db.Name) Next
Dim newDatabase As New Database(sqlServer, DbName.Text.ToString) newDatabase.Create() This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder? Thanks & regards, Paul
I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.
I'm looking for the best solution for creating database tables based on a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables. One table would consist of the fields of the horizontal upper most data e.g. conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt, count(*) + 1 as cntplusone, cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent from metric_instance mi INNER JOIN incident i on i.number = mi.id WHERE mi.definition = 'Solution Rejected' AND i.state = 'Closed' group by mi.id
The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:
create assembly totalmem from '!WORKINGDIR! otalmem.dll'
WITH PERMISSION_SET=UNSAFE
go
create function fnGetTotalMem()
returns int
as external name totalmem.SqlClrVB.GetTotalPhysicalMemory
go
When I call this function, it returned following error:
select dbo.fnGetTotalMem()
Msg 6522, Level 16, State 2, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':
System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()
at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()
at SqlClrVB.GetTotalPhysicalMemory()
.
Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.
I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...
I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?
Example--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008' @EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 4150.66 Jaime KRiSH 5/3/2015 4150.66 Peter Jason 5/1/2015 321.02 Peter Jason 5/1/2015 321.02 Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66 2 Jaime KRiSH 5/1/2015 1043.66 2 Jaime KRiSH 5/1/2015 1043.66 3 Jaime KRiSH 5/1/2015 4150.66 4 Jaime KRiSH 5/3/2015 4150.66 5 Peter Jason 5/1/2015 321.02 6 Peter Jason 5/1/2015 321.02 7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
I have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.
USE database1 GO CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Output VARCHAR(8000) SET @Output = '' SELECT @Output =CASE @Output WHEN '' THEN dwg_Seq ELSE @Output + ', ' + dwg_Seq END FROM dbo.Un_Combined WHERE prt_Mark = @prt_Mark ORDER BY dwg_Seq RETURN @Output END GO
Msg 325, Level 15, State 1, Line 2 Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. Msg 137, Level 15, State 2, Line 14 Must declare the scalar variable "@prt_Mark". Msg 178, Level 15, State 1, Line 17 A RETURN statement with a return value cannot be used in this context. Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'To'.
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')