Quoted Identifier
May 14, 2003During Index Rebuild, I get the following error:
"DBCC failed because the following set options have incorrect settings: 'Quoted_Identifier'"
Any ideas what might be causing this?
Thanks,
Ken Nicholson
ADVERTISEMENT
Problem With Quoted Identifier On 2000 Sql Server
Jan 16, 2003Hi,
If you run the following code on 7.0 server, it will send a test file in csv format.
--*********************************
create table ##temp1
(tmp varchar(15))
insert into ##temp1
select 'Erin, Brockowich'
declare @cmd varchar(255)
select @cmd = 'select * from ##temp1'
EXEC master..xp_sendmail
@recipients = '<email>', --change to your email
@query = @cmd,
@subject = 'test-test',
@attach_results = 'true',
@attachments = 'test.csv',
@width = 800
drop table ##temp1
--**********************************
When you receive an email and double click on te attachment, it will launch excel automatically and put 'Erin' and 'Brockowich' in seperate columns. which is good and that is the way I want it.
But if I run this code on sql 2000 server, it will generate a file with 'csv' extension unrecognizable by excel. If you open this 2000 attachment in notepad, you can see that the data looks like
"Erin, Brockowich" (vs Erin, Brockowich without quotes on 7.0 server ), no wonder it is unrecognizable by excel.
I have set quoted_identifier off while compiling all the user sps on 2000 server. But while sending emails from within the procedure, all the attachments still generated with quotes.
How can I get rid of the quotes? We have at least 45 routines running generating coma seperated files as a result of the query and sending emails to the clients for years. Now all of a sudden all the routines got messed up with 2000 upgrade.
Can you suggest any work around?
Thanks,
Shaili
:confused:
Replication Snapshot Failing - QUOTED IDENTIFIER
Sep 9, 2007Hi,
My replication is failing to apply the initial snapshot because of the issue with QUOTED identifier. The snapshot files for the stored procedure are being generated using the 'SET QUOTED IDENTIFIER ON' where as my store procedure code is using the double quotes for string comparision e.g if @val = "test".
It is not possible for me to change the sps code as there are 1000+ sps exists. Is there any way to generate the snapshot files with 'SET QUOTED IDENTIFIER OFF'
Transact SQL :: Alter Table Set Quoted Identifier On And Set Ansi Null
May 27, 2015I have existing table which is having Set Quoted Identifier Off and Set Ansi Null
Now I want to change those setting so Is there any alter statement for the same?
Also Let's say At my database level If those settings are off and If I convert it to ON then It is not taking effect on existing tables SP which are already build.
Quoted Identifiers In A CSV
Sep 26, 2006There have been some posts on the difficulties of handling csv with quoted identifers.
I heard there were some custom components that aid in dealing with these as input files.
IYO, what do you consider to be the best way to deal with csv with quoted identifiers as an input to an ETL operation?
Also, if you know of any bloggers making quality (hopefully free) custom components?
Thanks!!
Quoted Identified And Odbc Connection
Oct 2, 2006We have several sp's created to support a 3rd pary app that uses crystal inside the app with an odbc connection. We got sql row errors on any Citrix server where Use ANSI quoted identifiers was not checked. If we had them checked it works. Does this mean the sp's were created with the identifiers on and this is causing the issue?
View 1 Replies View RelatedBCP Template Using Quoted Text Qualifiers
Nov 2, 2005I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?
View 4 Replies View RelatedDTS 2005 And Quoted Table Names
Jul 23, 2005We're trying to use DTS from SQL Server 2005 beta 2 to query datafrom an Informix IDS server via OLEDB.Unfortunately DTS is building queries of the form:select * from "database":"owner"."tabname"and the quoted table name is being rejected by the Informix server as asyntax error.Is there a way to keep DTS from quoting the table name in queries? Or isthis happening within the OLEDB provider?Thanks.--John HardinDevelopment and Technology group (Seattle)CRS Retail Systems, Inc.
View 1 Replies View RelatedAnsi Nulls And Quoted Identified
Aug 8, 2005Hello everyone,SQL Server 2000. I have a database in which there are several objectswhich have ansi nuls and quoted identifier turned ON. Is there a way Ican generate a script which:(1) Can identify all objects within the database that have those twoproperties turned ON and(2) Change the properties for these objects and turn the ansi nulls andquoted identifier OFF for those objects.I am trying to avoid going throuh gazillions of objects and manuallydoing this.Thanks for any help.Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 17 Replies View RelatedSQL Server 2000 + Inserting Quoted Strings
Jul 27, 2006Hello,
I am trying to insert quoted strings into a database table, however, I cannot remember how to do so. For instance, I am trying to insert the following values into a SQL table:
My Friend's
"Happy Birthday"
exactly as they are listed. How can I do that in a SQL insert statement?
Thanks,
Crystal
Stored Procedure Problem Using Un Quoted Varchar
Aug 16, 2007I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO
ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
@Category VARCHAR(255),
@Range INT,
@PageIndex INT,
@NumRows INT,
@CategoryName nvarchar(255) OUTPUT,
@CategoryProductCount INT OUTPUT
AS
BEGIN
/*
Get product count
*/
SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/*set row variables*/
Declare @startRowIndex INT;
set @startRowIndex =(@PageIndex * @NumRows) + 1;
/* get full list of products */
With ProductEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2
FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar
WHERE Range=@Range
AND Category = @Category
)
/*get only needed rows */
SELECT field1, field2
FROM ProductEntries
WHERE Row Between
@startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category
in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes.
If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
Quoted Field And Escape Character Issue
Nov 8, 2006I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.
So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
ANSII NULL AND QUOTED IDENTIFERS - Upgrade Problems
Oct 26, 2007I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005. Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"
The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:someConnectionString %>"
SelectCommand="spTESTSearch" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="M" Name="TypeCode" Type="String" />
<asp:Parameter DefaultValue="EQ" Name="FirstPubOp" Type="String" />
<asp:ControlParameter ControlID="FirstPubDateTextBox" DefaultValue="" Name="FirstPubDate"
PropertyName="Text" Type="DateTime" />
<asp:Parameter DefaultValue="C" Name="UserName" Type="String" />
<asp:Parameter DefaultValue="NO" Name="SearchTextOp" Type="String" />
<asp:Parameter Name="SearchText" Type="String" />
</SelectParameters></asp:SqlDataSource>
The dates are selected properly in the testing tool, with code such as :
DateDiff(day, FirstPubDate, @FirstPubDate) = 0
I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.
Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?
I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.
Thanks for the assist!
Quoted Literal Strings Won't Force A Phrase Match
Jan 18, 2008Hello 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.
T-SQL (SS2K8) :: Extract Quoted Text Elements From Varchar Column
Nov 18, 2014I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.
declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values
[Code] ....
Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III
I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.
Unfortunately, CLR is not an option.
Row Identifier
Aug 19, 1998Does anyone know the column name for the system row identifier. For example Informix uses rowid, for Ingres its tid, in
MS SQL server its _______ (fill in the blank).
Thanks again in advance.
Levi Akers
Unique Identifier
Jan 19, 2005I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.
So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.
I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.
So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?
Here is my stored procedure:
CREATE PROCEDURE dbo.REQ_HDR_INSERT
@ddo varchar(50) = null,
@requestor varchar(100) = null,
@dt datetime = null,
@abrtype varchar(20) = null,
@subject varchar(250) = null,
@description varchar(500) = null,
@review char(10) = null,
@ay char(4) = null,
@origallo varchar(50) = null,
@reqallo varchar(50) = null,
@logl_del_dt datetime = null,
@phys_del_dt datetime = null
AS
Insert into dbo.DIM_ABR_REQ_HDR (ABR_ddo, ABR_requestor, ABR_dt, ABR_type, ABR_subject, ABR_description, ABR_review, ABR_AY, ABR_orig_fund_allo, ABR_req_fund_allo, ABR_LOGL_DEL_DT, ABR_PHYS_DEL_DT)
values (UPPER(@ddo), UPPER(@requestor), @dt, UPPER(@abrtype), UPPER(@subject), UPPER(@description), UPPER(@review), @ay, convert(money, @origallo), convert(money, @reqallo), @logl_del_dt, @phys_del_dt)
return @@identity
GO
I would be using @ay and @ddo as the first two parts of the unique identifier number. Any help is appreciated.
Thank you,
-D-
Unique Identifier
Jan 31, 2004How can i get a numer for using it as unique identifier in two related tables?
View 3 Replies View RelatedIdentifier Too Long
Oct 14, 2006hi there, i have following code
SET QUOTED_IDENTIFIER ON
DECLARE @sTableDiff varchar(1000)
SET @sTableDiff= " ""C:Program FilesMicrosoft SQL Server90COM ablediff"" -sourceserver ""VSNET1"" -sourceuser ""sa"" -sourcepassword ""sa2345"" -sourcedatabase ""PMSTEMP"" -sourcetable ""DEMAT"" -destinationserver ""VSNET1"" -destinationuser ""sa"" -destinationpassword ""sa2345"" -destinationdatabase ""PMSRESTORE"" -destinationtable ""DEMAT"" -f ""c:Diff"""
PRINT @sTableDiff
EXEC XP_CMDSHELL @sTableDiff
When i execute it, throws error as
Msg 103, Level 15, State 4, Line 3
The identifier that starts with ' "C:Program FilesMicrosoft SQL Server90COM ablediff" -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sour' is too long. Maximum length is 128.
When i split the Identifier as
SET QUOTED_IDENTIFIER ON
DECLARE @sFull varchar(1000)
DECLARE @s1 varchar(1000)
DECLARE @s2 varchar(1000)
DECLARE @s3 varchar(1000)
DECLARE @s4 varchar(1000)
SET @s1= '""C:Program FilesMicrosoft SQL Server90COM ablediff""'
SET @s2= ' -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sourcedatabase "PMSTEMP" '
SET @s3=' -sourcetable "DEMAT" -destinationserver "VSNET1" -destinationuser "sa" -destinationpassword '
SET @s4=' "sysadm" -destinationdatabase "PMSRESTORE" -destinationtable "DEMAT" -f "c:Diff"'
SET @sFull = @s1 +@s2 + @s3 + @s4
EXEC XP_CMDSHELL @sFull
this executes fine but the optput is as
The input line is too long.
NULL
What is this?
how could i execute such long commad using CMDSHELL?
Regards,
Thansks.
Gurpreet S. Gill
Uniqie Identifier
Sep 23, 2006Hi
I have an salesorders table and products table.
products table (PK) is productid , it is uniqueIdentifier
I added a field to salesorders table to keep the productid and I made INT
so, now I go to vb.net, data connections, diagrams
when I try and relate this two field (salesorders.productid with products.productid) I get an error and it says, data type, size doesn't match.
Could you please help me with this issue.
thanks
Sql Error Identifier
Mar 15, 2008
Is there a field in the SQLException class that identifies what specific exception was thrown. I tried Number and ErrorCode but those seem to change randomly, they are not consistent. Thanks
Unique Identifier Or Int For Table ID
Feb 29, 2008I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int Or uniqueIdentifier? Or is it all the same??
View 11 Replies View RelatedIdentity Or Unique Identifier
Aug 1, 2005Hi there,I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.Any help would be greatCheersStu
View 2 Replies View RelatedUserID -&> Unique Identifier
Nov 25, 2005Hello!I work with oracle, mySQL but im completely new to SQLserver.How can i set a auto-increment unique variable for userID ?After deleted user, the userID should be never used again on a new user.Thank you,
View 1 Replies View RelatedMulti-part Identifier
Jun 18, 2008use projectserver2003
selectr.RES_NAME, p.PROJ_NAME, a.TASK_NAME, w.WWORK_START, w.WWORK_FINISH, w.WWORK_VALUE
from MSP_WEB_RESOURCES r,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_PROJECTS p,
MSP_WEB_WORK w
join MSP_VIEW_PROJ_TASKS_ENT TE on r.WPROJ_ID=TE.WPROJ_ID
join MSP_VIEW_PROJ_RES_ENT RE on r.WPROJ_ID=RE.WPROJ_ID
join MSP_VIEW_PROJ_PROJECTS_ENT PE on r.WPROJ_ID=PE.WPROJ_ID
wherew.WWORK_TYPE = 1 -- actual work
andw.WASSN_ID = a.WASSN_ID
anda.WPROJ_ID = p.WPROJ_ID
anda.WRES_ID = r.WRES_ID
This statement is returning the following errors:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "r.WPROJ_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "r.WPROJ_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "r.WPROJ_ID" could not be bound.
I have all tables identified; however unclear as to why it cannot be bound.
Please help.
How To Return Unique Identifier In SP
Oct 18, 2013I have trouble to get the uniqueidentifier I just inserted out.
---sp
CREATE PROCEDURE dbo.FAC_Ins_USR
@LAST_NAME AS nvarchar(60)
,@FIRST_NAME AS nvarchar(60)
,@NewID uniqueidentifier output
AS
BEGIN
[Code] ....
The new data went into the table, and the print @myErr shows 0.
But print @myID shows nothing.
---here is the part of the table
CREATE TABLE [dbo].[USERS](
[USER_ID] [uniqueidentifier] DEFAULT NEWID() NOT NULL,...
The Multi-part Identifier
Apr 4, 2006if exists (select 'x' from obj where new_obj.key1 = obj.key1 and new_obj.class = obj.class)
BEGIN
set @temp_old_ref = (select obj.rowid from obj where new_obj.key1 = obj.key1 and new_obj.class = obj.class)
SET IDENTITY_INSERT new_obj ON
insert into new_obj(rid, key1, class, is_searchable, is_deleted, is_loaded, old_rid, old_ref) select rid, key1, class, is_searchable, is_deleted, is_loaded, old_rid, @temp_old_reffrom new_obj
delete from new_obj where old_ref != @temp_old_ref
END
while running it i m getting this error
Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 35
The multi-part identifier "new_obj.key1" could not be bound.
Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 35
The multi-part identifier "new_obj.class" could not be bound.
Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 37
The multi-part identifier "new_obj.key1" could not be bound.
Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 37
The multi-part identifier "new_obj.class" could not be bound.
any thoughts
Another Multipart-identifier Problem
Mar 20, 2008Hey everyone,
I've found lot of other people experiencing the same problem I'm having, but I can't get any of their solutions to work for me. I have two tables with the exact same structure Today and Yesterday. I'm trying to compare a price column from Yesterday's column against Today's and I'm getting the 'The multi-part identifier '' could not be bound' error. Here's the code I'm using:
DECLARE @ProgramName varchar(100)
SET @ProgramName = 'AffiliateName'
SELECT CJ_RawImport_Today.* FROM CJ_RawImport_Today
WHERE (CJ_RawImport_Today.ProgramName = @ProgramName) AND (CJ_RawImport_Today.RetailPrice < CJ_RawImport_Yesterday.Price)
Anyone have a suggestion?
Thanks in advance!
--Joel
Unique Identifier Of A Database?
Jul 25, 2007Hello,
Does anyone know if there is a read-only unique identifier of a database in a given server instance? E.g. a value stored somewhere in the database meta data that is generated during CREATE DATABASE...
Multi Part Identifier
Nov 7, 2007Can anyone explain why i get the folling error.
Msg 4104, Level 16, State 1, Procedure rpt_getChildren, Line 33
The multi-part identifier "c.childID" could not be bound.
Msg 4104, Level 16, State 1, Procedure rpt_getChildren, Line 33
The multi-part identifier "c.siteID" could not be bound.
Msg 4104, Level 16, State 1, Procedure rpt_getChildren, Line 33
The multi-part identifier "c.siteID" could not be bound.
Msg 1011, Level 16, State 1, Procedure rpt_getChildren, Line 33
The correlation name 'sg' is specified multiple times in a FROM clause.
create procedure rpt_getChildren
@cmb1 as varchar(100)
AS
BEGIN
set dateformat YMD
set datefirst 7
CREATE TABLE #ChildSessions (
siteid integer null
,childid integer null
,sessionid integer null
,sun integer default 0
,mon integer default 0
,tue integer default 0
,wed integer default 0
,thr integer default 0
,fri integer default 0
,sat integer default 0)
declare @firstofweek as datetime
declare @lastofweek as datetime
select @firstofweek=cast(floor(cast(dateadd(day,(-1*datepart(dw,getdate())+1),getdate()) as float)) as datetime)
select @lastofweek=dateadd(minute,-1,dateadd(day,7,@firstofweek))
declare @myday integer
set @myday=0
while @myday<7
BEGIN
INSERT INTO #ChildSessions
SELECT
c.siteid
,c.childid
,sg.sessionid
,case @myday WHEN 1 THEN 1 ELSE 0 end
,case @myday WHEN 2 THEN 1 ELSE 0 end
,case @myday WHEN 3 THEN 1 ELSE 0 end
,case @myday WHEN 4 THEN 1 ELSE 0 end
,case @myday WHEN 5 THEN 1 ELSE 0 end
,case @myday WHEN 6 THEN 1 ELSE 0 end
,case @myday WHEN 7 THEN 1 ELSE 0 end
FROM
Child c
,sessionAttendance sa
,session s
,sessiongroup sg
INNER JOIN
SessionAttendance sa
ON c.childID = sa.childID
INNER JOIN
Session s
ON c.siteID = s.siteID
INNER JOIN
Site
ON c.siteID = s.siteID
INNER JOIN
SessionGroup sg
ON c.siteID = sg.siteID
WHERE
c.childID = sa.childid
AND c.siteid = sa.siteid
AND c.active = 1
AND c.potential = 0
AND s.identityid = sa.identityid
AND s.siteid = sa.siteid
AND sg.sessionid = s.sessionID
AND sg.siteid = s.siteid
--AND site.sitename = @cmb1
AND s.dayofweek = @myday
AND @firstofweek <= sa.dateTo
AND @lastofweek >= sa.dateFrom
SET @myday=@myday+1
END
SELECT
c.forename,
c.surname,
sg.sessionname,
--sitename,
CASE (sum(sun)) WHEN 0 THEN ' ' ELSE 'X' END as sun,
CASE (sum(mon)) WHEN 0 THEN ' ' ELSE 'X' END as mon,
CASE (sum(tue)) WHEN 0 THEN ' ' ELSE 'X' END as tue,
CASE (sum(wed)) WHEN 0 THEN ' ' ELSE 'X' END as wed,
CASE (sum(thr)) WHEN 0 THEN ' ' ELSE 'X' END as thr,
CASE (sum(fri)) WHEN 0 THEN ' ' ELSE 'X' END as fri,
CASE (sum(sat)) WHEN 0 THEN ' ' ELSE 'X' END as sat,
case when datepart(day,c.dob)<=datepart(day,getdate())
then cast((datediff(month,c.dob,getdate())/12) as varchar(3)) + ' Yr '
+ cast((datediff(month,c.dob,getdate())% 12) as varchar(2)) + ' Mnth'
when datepart(day,c.dob)>datepart(day,getdate())
then cast(((datediff(month,c.dob,getdate())-1)/12) as varchar(3)) + ' Yr '
+ cast(((datediff(month,c.dob,getdate())-1)% 12) as varchar(2)) + ' Mnth' end as Child_Age
FROM child c,sessiongroup sg, site, #childsessions cs
WHERE c.childid=cs.childid
AND c.siteid=cs.siteid
AND sg.sessionid=cs.sessionid
AND sg.siteid=cs.siteid
--AND s.sitename = @cmb1
GROUP BY c.forename,c.surname,sg.sessionname,c.dob,c.childid
ORDER BY sg.sessionname,c.forename,c.surname
DROP TABLE #ChildSessions
END
GO
Database Catalog Unique Identifier
Jan 4, 2007I need to develop a strong licensing solution based on the database accessed...
Currently our solution is easily hackable because the the license information is kept in the database of your choice and is not dependant on some static information, the current encryption key is static and kept in the software so it can be hacked easily. What i want to do to change this is simple in nature but i don't know how to get that one particular info i need.
I want to get some kind of unique identifier for a database (catalog) that sql server could generate. This info must be static and must not be movable. If for example, someone would backup and restore, this information would not be transfered with the backup. Thus, a user that backups his database and restores it on another database server or another database catalog even on the same server would corrupt his license since the Unique ID returned by the SQL Server would be different; the hashing code would change and thus the decryption would fail.
Is there any such info i can query using SQL that will not require admin rights?
The Multi Type Identifier Xxx Cannot Be Bound
Jul 2, 2007HI Gurus,
Trying to alter an existing Trigger on an insert event on one of our tables. The following sql generates: "Msg 4104, Level 16, State 1, Procedure insxECSIBHEADER, Line 4 The multi-part identifier "INSERTED.PROJECT" could not be bound."
Here's the SQL itself:
set QUOTED_IDENTIFIER ONgoALTER TRIGGER [insxECSIBHEADER] ON [dbo].[xECSIBHeader] AFTER INSERT AS BEGIN IF SUBSTRING(INSERTED.[PROJECT],4,2) = 'DR' BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + 'D' + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END ELSE BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END
END
The Multi-part Identifier ....... Could Not Be Bound.
May 29, 2008I am trying to execute a query in SQL SERVER 2005 for calculating days difference for each. I created a function because there are a lot of calculations. In SPeriods table I have 4 fields that I want to pass as parameters in the table-value function TFC_date_diff, but I receive the error "The multi-part identifier "CC.start_period_no" could not be bound.
Is there a solution for this ?
SELECT CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year, TFC_date_diff.dates_differnceFROM dbo.[SPeriods] AS CC CROSS JOIN dbo.TFC_date_diff(CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year) AS TFC_date_diff_1
Thanks a lot in advance!