How To Use A Parameter In Place Of A Literal?
Oct 5, 2007
I have a stored procedure that starts like this:
.
.
.
UPDATE Employees
set depth=0, hierarchy=NULL
UPDATE Employees
set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength)
where Child = Parent
WHILE EXISTS
(
SELECT *
FROM Employees
WHERE Depth=0
)
.
.
.
I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?
Thanks
View 4 Replies
ADVERTISEMENT
Jul 20, 2007
Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....
Regards
Karen
View 4 Replies
View Related
Apr 12, 2006
Howdy,Is it okay to use a literal value with the IN clause. E.g.SELECT somefield, anotherfield.....WHERE ...etc.AND 1234 IN (SELECT userid FROM tblUsers)I was told it wasn't valid, but I'm pretty sure it worked for me. Justseeking clarification.cheers,
View 2 Replies
View Related
Apr 1, 2008
Hi,
A really basic problem in psuedo code...
select * from mytable where email = 'test@go.com'
This throws back:
Unclosed quotation mark before the character string 'test@'.
Line 1: Incorrect syntax near 'test@'.
Unclosed quotation mark before the character string ''.
Because of the 'go'. Other addresses featuring 'go' suffer the same problem. How do I escape it?
Thanks,
Joe
View 4 Replies
View Related
Oct 25, 2007
I am trying to extract the month from a date field, and I am able to get the integer for the month, however, when I try to convert the integer to the literal, e.g. 3 to March, I am having issues.
I have a field called PROCES, which represents a date processed, which is numeric(8).... i.e. 20061231.
I am using:
SELECT datepart(mm,cast(convert(char(8),PROCES)as datetime))
which gives me the correct integer.
When I try:
SELECT datename(mm,datepart(mm,cast(convert(char(8),PROCES)as datetime)))
I get January for all records...?
Does anyone know why, and how to fix.
Thanks
View 1 Replies
View Related
Jul 23, 2005
What exactly is happening when a query is sent using the N in front ofthe string to be found?Under what conditions would someone use the N' in a query?I have been testing out some chinese text. I set up some fields ofnVarchar, nText and it works with an N. Without the N, it wont work.N also works with fields of varchar and text for english.Would this ever cause a problem to a query depending on how themachines regional settings are set? Why not just put N in all of thequeryies?If anyone has some ideas, I would be grateful for any and allinformation about the N.
View 1 Replies
View Related
Feb 24, 2008
Hello,
I am trying to insert some values retrieved from textboxes into an Access DB using ASP.NET. When I try to run this code I get an error reading "Too many characters in character literal". What does this mean? Also, how do I break new lines, does way this look ok?
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
string sQuery2 = INSERT INTO Member(FirstName, LastName, StreetAdress, PostalAdress, Telephone, Email),
VALUES ('strFirstName', 'strLastName', 'strStreetAddress', 'strPostalAddress', 'strTelephone', 'strEmail');
OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);
oOleDbConnection.Open();
OleDbCommand command2 = new OleDbCommand(sQuery2, oOleDbConnection);
OleDbDataReader reader2 = command2.ExecuteNonQuery();
View 8 Replies
View Related
Aug 8, 2006
hi!
din't work. I need some thing in C# that can execute like this:
This works in SQl Query perfectly.
xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx"/Set package.Variables[User::connectst].Properties[Value];""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""'
How do we interpret in c# currently i have some thing like this which is not correct i need to fix this to work in C#
string conn = @"""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""""";
path = @"""D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx";
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f " + path + "" /Set \package.Variables[User::connectst].Properties[Value]; " + conn + ""'", cconn);
Thanks,
Ja
View 5 Replies
View Related
Aug 7, 2006
when i execute in sql this works fine:
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn TahoeDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
but when i execute in C# i get this value
string connect = @"TahoeDB;""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""";
xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "TahoeDB;"Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"" ' -- this thorughs up error in sql
Option "Source=SE413695\AASQL2005;Initial" is not valid.This is basically after Data there is a space till Initial and then space catalog.
what should i do ? any help
thanks,
Jas
View 3 Replies
View Related
Aug 6, 2007
Hi,
I want to convert literal string to unicode before insert into the database. and after insertion i want to retrive this value from data base and convert back to literal string.
pls tell me how to incode and decode literal string to unicode and from unicode to literal string.
View 2 Replies
View Related
Mar 21, 2005
In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:
SELECT * FROM foo WHERE bar like 'a\_%'
where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?
I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )
Any other suggestions?
View 1 Replies
View Related
May 3, 2007
When a report is exported to Excel, the footer is set to a literal "page 1 of 1" even though it's specified as
="Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)
in the rldc report definition.
Hence, if you print from Excel every page has the 'page 1 of 1' footer. Not very useful.
Is there a way to either suppress the output of the footer (only when going to Excel cause you need it when exporting to PDF) -or- get it to generate the correct excel footer of 'page 1 of ?'????
Thanks,
View 1 Replies
View Related
Aug 14, 2015
I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error incorrect syntax near '32810'.
declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122'
declare @sql varchar(500) =
'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')'
exec(@sql)
selecting @sql it looks like
select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')
View 6 Replies
View Related
Apr 20, 2006
Hi,
I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype? For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!
Any help very appreciated!
Thanks in advance and best regards
Reiner
PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).
View 9 Replies
View Related
May 9, 2008
The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.
But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)
How can I make the second query seek instead of scanning?
CREATE PROCEDURE test_like
@searchText nvarchar(64)
AS
-- Straight literal string search
SELECT companyId
FROM companies
WHERE searchbrand LIKE 'foo%'
-- With param
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
OPTION(OPTIMIZE FOR(@searchText = 'foo%'))
GO
EXEC test_like 'foo%'
-- Query 1:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Query 2:
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 40 ms.
View 4 Replies
View Related
Jan 18, 2008
Hello all,
From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literals. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?
Thanks.
View 1 Replies
View Related
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
View 5 Replies
View Related
Apr 15, 2006
Hi, I have a datareader component of which i am dynamically setting its sqlcommand statement with expression (click the background of dataflow > properties > expressions). Now my sql select statement has about 600 fields so that makes my expression statment "select field1, field2, .....from table1 where field2 >=" + @[User::dateforfield2] but when i evalute the expresssion (which is right), i get the error: A string literal in the expression exceeds the maximum allowed length of 4000 character and i think its because of the fields in my select statment causing my string literal to grow more than 4000 characters. Is there any way to increase the max string literal for expressions. Please help.
View 1 Replies
View Related
Nov 9, 2015
I am working on SSRS report deployed on the sharepoint.
By default, it is displaying " Microsoft SQL Server Reporting Services " on the report
How can that be removed?
Secondly as I don't have the requirement to default the report parameters so I am getting
"Specify parameter values ............... button "
Any way to replace it with some other text etc...
View 2 Replies
View Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
Nov 16, 2001
Hi,
Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?
Thank You for any help!
Eric
View 1 Replies
View Related
Jan 5, 2006
what happens if the physical location of a box(which had sql server 2000 on it) is chaned.
what happens to the replication and distributed queries.
Thanks.
View 1 Replies
View Related
May 20, 2004
I have one big db and i heve no place on disk
the log file is big too. how can i delete the log
View 7 Replies
View Related
Apr 17, 2007
I am fairly versed with SQL 2005 as I have been using it since it came out. Now I have need to learn about Cubes.
Does anybody have any suggestions of where I need to begin to learn about what cubes are, how to create them and use them?
Ultimately we will be incorporating them into Reporting Services.
Thanks for the information.
View 3 Replies
View Related
Dec 26, 2007
Where would i place an orderby my DateCreated field...everywhere i try to place it i get this error...
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Any help with this issue would be greatly appreciated....
CREATE PROCEDURE GetTimeCard
( @LoginID nvarchar(50),
@DateRangeFrom datetime,
@DateRangeTo datetime
)
AS
BEGIN
IF ( @DateRangeFrom = '1/1/1753' ) AND ( @DateRangeTo = '1/1/1753' )
select x.*, x1.TotalExpenses, x2.WorkedCount
from (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
union
select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
ELSE
select x.*, x1.TotalExpenses, x2.WorkedCount
from (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
union
select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
End
GO
View 3 Replies
View Related
May 30, 2006
I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.
Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?
dennist685
View 5 Replies
View Related
Jan 28, 2004
I'm rapidly understanding that much more of my application as a whole is in SQL Server that I would have originally thought.
Stored Procedures
Triggers
Constrains
And so on
It generally means that some of the stuff I'd have naturally done in the Business Layer might be best done in SQL - certain issues in the Business Layer might be best being triggers or constraints for example...
One thing that still puzzles me, and I'd like some references or advice now as it's a blank area in my mind is how this interfaces to your asp.net code.
Obviously I call stored procedures and the like from code, and use parameters, etc, not problem, it's more what I do when these stored procedures or associated triggers fail (or a constrain fails - though this should be less likely)?
SQL sends back an error? But what? Then what do you get your page to do, especially if SQL failed midway through a 'big' transaction? Do you have save 'where the user was somehow' so they don't start inputting again?
It's all a bit vague at the moment, some detail would be nice? :)
View 4 Replies
View Related
Nov 12, 2005
Hello all,I have an SQL query which retrieves a COUNT number from 2 different tables, and i want to do a division with botht he COUNT data retrieved. Trouble is I can't get it in the format that I want, my SQL query is as below :-SELECT ROUND( ((T1.Present/T2.Total ) * 100), 2) FROM(Select Count(Date) as Present from Attendance WHERE Month(Date)=12 AND Status=1) T1,(Select Count(Date) as Total from Attendance WHERE Month(Date)=12) T2The trouble here is that the result should be as below: T1.Present = 3T2.Total = 5 T1.Present/T2.Total = 3/5 = 0.6The final should be 60 after divided by 100But i am getting a zero as my result, even when I don't multiply the number by 100, the division result is still zero. I am guessing it is a conversion problem. Could anyone please offer me any advise on how to get the final result in the format I want?
View 2 Replies
View Related
Dec 14, 2001
Hi, I want to replace the indexes in my database to a different
filegroup. How can I do that using T-SQL? I only found a way that
uses the EM, but I have a lot of indexes and I hate to do it manually.
Thanks a lot.
View 2 Replies
View Related
Aug 9, 2001
One of our customers has a production and a test database. We are loading tables via a C++ program that works on production, but not on test, which is supposed to be an exact duplicate.
The error we are getting when trying to add columns to one of the table is 80004e21 null That is it.
When we load the exact same records in the production environment we do not get the error. We have spent many hours ensuring that the tables are exactly the same on test as in production.
Does anyone know, is there an environment variable at a database level that says how to handle null values?
Thanks!!!
C
View 1 Replies
View Related
Feb 4, 2000
If I have a publisher, distributor, and subscribers all running SQL 6.5, and I wish to upgrade to SQL 7.0, in what order to I upgrade?
View 1 Replies
View Related
Mar 11, 2004
Hi All!
I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:isl_fs1DBName.bak'
BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\isl_fs1DBName.bak'
it doesn't work please help
View 2 Replies
View Related
Apr 8, 1999
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement
For Example: Instead of using the following with each database hardcoded in the SP:
select @dataused = sum(b.reserved)
from DBSglep..sysindexes b
where b.indid in (0, 1, 255) and segment != 2
I would like to loop for every database listed in sysdatabases and do this:
select @dbname = @dataname+'..sysindexes'
select @dataused = sum(b.reserved
from @dbname b
where b.indid in (0, 1, 255) and segment != 2
I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).
Any assistance in resolving this problem would be greatly appreciated! :-)
Many thanks in advance...
Regards,
Wayne
View 2 Replies
View Related