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

Is There A Place Where I Can Find The Events That Takes Place In Sql Server?

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

Literal Value With IN Clause

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

'Go' Keyword Being Interpreted In Literal

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

Get Literal Month From Date

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

Select * From Something Where Field1 = N'literal'

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

Too Many Characters In Character Literal?

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

String Literal Problem

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

String Character Literal In C#

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

How To Convert Literal String To Unicode ?...

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

Find Literal Underscore - Not Wildcard

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

Excel Export Footer Is A Literal?

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

Transact SQL :: Using Literal In Openquery Where Clause

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

How To Code DateTime-Literal In SQL Server

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

Different Plans With Literal String Vs. Param

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

Quoted Literal Strings Won't Force A Phrase Match

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

Web Service Task And Document/Literal Calling Convention

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

Frror: 4000 Max String Literal In Expression For Datareader Component

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

Reporting Services :: SSRS Report - Need To Remove A Default Literal

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

SQL Server 2012 :: Case Statement On Nvarchar With Literal String Comparison To Varchar?

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

Best Place To Put TempDB

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

Sql Box Moved From One Place To Other

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

No Place On Disk

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

Best Place To Start

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

Where To Place An Order By

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

Where To Place Articles

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

SQL Servers Place In The 'Application'

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

SQL Query Needs Decimal Place

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

Place Index On Filegroup

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

Null Error In One Place, But Not The Next

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

Upgrading From SQL 6.5 To 7.0 With Replication In Place.

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

Database Backup To Different Place

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

Can You Use A VARIABLE In Place Of A Database Name?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved