|
Pen and Paper are your friend.
Back in Uni I have had pen and paper assignments that were larger and more complicated than real-life assignments.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
Hello,
I need to create a view to a database on a remote server, and I found linked servers in SQL. I mostly got it to work, except for one subtle requirement.
The database name on the remote server can change, and I don't want to hard code it in my views (also for testing purposes).
When I call sp_addlinkedserver[^] I can specify a default database name as the last parameter.
The thing is, what's is it for? The way to connect to another server is using the four part notation ([server].[database].[owner].[table]) such as SERVER2.ShippingDB.dbo.Shipments. But if I specify a default database when creating the linked server, I suppose I should be able to obviate the database name, like this:
SELECT * FROM SERVER2..dbo.Shipments
I've tried it and I get this error messege:
Msg 7313, Level 16, State 1, Line 1<br />
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SERVER2".
I've searched all around the net and I only found a post in MSDN forums asking the same thing, but no answer.
Any ideas?
Thanks!
|
|
|
|
|
Just a guess. Maybe catalog (or default database) has actual meaning only on OLEDB-queries?
I'm not able to test this at this time, but if you can try what happens if you modify your query to use OPENQUERY-call:
SELECT * FROM OPENQUERY(SERVER2, 'SELECT * FROM dbo.Shipments')
Any difference?
Mika
|
|
|
|
|
Mika Wendelius wrote: Maybe catalog (or default database) has actual meaning only on OLEDB-queries?
After I saw your post, I tried this:
EXEC sp_addlinkedserver @server ='TRW', @srvproduct = '', @provider = 'SQLOLEDB',
@datasrc = '127.0.0.1', @location = NULL, @provstr = '', @catalog = 'trw_data'and it didn't work either.
OPENQUERY seems to work fine. One question though: Performance-wise, is it a good choice if the remote table pontentially will have a lot of records? I suppose that if add a filter in the inner query, it would be faster. But what if that filter were dynamic? The only thing I can think of is to build the query with string concatenation before passing it to OPENQUERY.
Thanks, you got me into the right track. 
|
|
|
|
|
Good question!
Dynamic SQL seems to be the only option and since you cannot use variables inside OPENQUERY, all the conditions must use literals for values. This however isn't so big problem any more in SQL Server 2005 than it was in earlier versions since the database is now much more intelligent in reusing SQL statements.
Also one caveat is that max size of the query string is 8K. Because of that and performance reasons, maybe you could create a view or views at remote site and try to use as many static filters in the view as possible.
Glad that it helped
Mika
|
|
|
|
|
I was thinking about building a query string, and passing that to OPENQUERY. But it can't be done. You can't even concatenate anything in the OPENQUERY call itself.
I'll have to see how big my remote table is. I would rather not modify the remote database, but if that is the only way, I'll talk to my customer.
Thanks for your help!
|
|
|
|
|
You're welcome
A little trick that came into my mind which could be useful to you.
BEGIN
DECLARE @remoteQuery varchar(8000);
SET @remoteQuery = 'SELECT * FROM OPENQUERY(SERVER2, ''SELECT * FROM dbo.Shipments'')'
PRINT @remoteQuery
EXEC (@remoteQuery)
END
Mika
|
|
|
|
|
hi everyone,
I've a table in which the primary key needs to be a six digit number and must start from 1, so for that I made it an identity key (100001,1) but the problem is that I can't figure out how to restrict this to not exceed 199999
I'm using Sql Express 2005 as the Database. As far as I've seen while googling it out. There's no obvious way to do that. Can anyone comment on that plz?
Thanks in advance
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
this can be done using either with Check Constraint or Trigger
Regards
KP
|
|
|
|
|
oh yea, I just got this in mind a few minutes ago,
Thanks![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Seems like a design nightmare. What is going to happen when the data grows past that number? Also you are breaking the cardinal rule of record identifiers, you are assigning intelligence to the ID field. Next I suppose you are going to sort by the id field!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
well I've asked almost the same question from the designers of the db but I'm still waiting for their response on this. But if they do insist I guess I'll have to say: As u say Mr. XXX
Ur damn right:
Mycroft Holmes wrote: Never underestimate the power of human stupidity
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Rocky# wrote: designers of the db
Oh - you don't own the design  , poor bugger, my sincere sympathies.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ahhh, well, its the designers themselves who r gona suffer at the end.haha  I pass ur sympaties to them
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
i don´t know what you have in mind but if you trigger a SP to add data to one table, edit in that SP a if/else statement that counts the table records and; 1) add a record or 2) the display a error info message to the user. 
|
|
|
|
|
Dear All,
I have a date values stored as Decimal in the SQL server 2005.
I need to convert these decimal values into Date in the format (yyyy-mm-dd).
Example: I need to convert 20080721 To 2008-07-21
I wrote the following statement for this purpose, but the result contains 00:00:00:00 in the end, and I think this is because I am using datetime function in the query.
select convert(datetime, cast(DayDate AS char(8))) AS Date
from TableName where DayDate = 20080721
Result from the previous query is: 2008-07-21 00:00:00:00, but I need it to be 2008-07-21 without 00:00:00:00
Kind Regards
OBarahmeh
|
|
|
|
|
hi,
well as far as my experience goes, whenever u user datetime in SQL Server it always appends a default time, -- even if u're not specifying it.
So I suggest u should stick to this result and try and improve the logic of ur front end to compensate to that.
Hope it helps,
Regards,
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Rocky# wrote: whenever u user datetime in SQL Server it always appends a default time
No it doesn't. You need to use GETDATE() or a fixed date if you don't supply a date into a non-nullable database field.
[EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it.
My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]
modified on Wednesday, July 23, 2008 7:08 AM
|
|
|
|
|
my point is that datetime field always has a time part in it. try
Select Getdate()
or may be we're not on the same page at this point 
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
 No, no, no. You're just plain wrong.
GETDATE() is a system function, not field data. It's no different in effect to you typing SELECT 3. This doesn't mean that every integer field defaults to 3. Tell you what, as a test, run the following script:
USE [<<database>>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].Test(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Text] NVARCHAR(20) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
GO
INSERT INTO Test([Text]) VALUES ('Hello')
GOGuess what. Created doesn't get a default value. What you do get is an error:
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Created', table 'database.dbo.Test'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So no default value there then. Please, before you pass information like this along, will you please check to see if you are right?
[EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it.
My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]
modified on Wednesday, July 23, 2008 7:12 AM
|
|
|
|
|
Pete O'Hanlon wrote: Guess what. Created doesn't get a default value. What you do get is an error:
what else would it give, trying to insert a null into a not null column,
try this
Insert into Test(text, created)
values ('my test', CAST('7/23/2008' as datetime))
The result looks like this on my end: 7/23/2008 12:00:00 AM
My point was simple, if u r getting a dateTime object, it will ALWAYS show the time, if u dont wanna see the time then cast it to varchar or something but datetime will always show u the time. here in the above query I'm not giving the time but its still comming. Thats the simple point I was talking abt. Pardon me if this doesnt satisfy the guys original question but my point was this alright!
I deeply respect ur place Pete, I dont mean any offense alright
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Crap. My apologies - I just reread your original post, and it seems you and I were talking about different things. So sorry - and I will amend my answers to reflect this.
I really shouldn't post answers late at night.
|
|
|
|
|
No problem Pete!
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
select substring(convert(varchar, convert(datetime, cast(DayDate AS char(8))),20),1,10) AS Date<br />
from TableName where DayDate = 20080721
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I have a solution for your question hope this helps you out 
What you can do is that create a custom function in your Database, Code given below
Function:
*********
CREATE FUNCTION dbo.FormatDateTime
(
@dt DATETIME,
@format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format
WHEN 'LONGDATE' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
WHEN 'LONGDATEANDTIME' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20),
@dt - CONVERT(DATETIME, CONVERT(CHAR(8),
@dt, 112)), 22), 11)
WHEN 'SHORTDATE' THEN
LEFT(CONVERT(CHAR(19), @dt, 0), 11)
WHEN 'SHORTDATEANDTIME' THEN
REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
'AM', ' AM'), 'PM', ' PM')
WHEN 'UNIXTIMESTAMP' THEN
CAST(DATEDIFF(SECOND, '19700101', @dt)
AS VARCHAR(64))
WHEN 'YYYYMMDD' THEN
CONVERT(CHAR(8), @dt, 112)
WHEN 'YYYY-MM-DD' THEN
CONVERT(CHAR(10), @dt, 23)
WHEN 'YYMMDD' THEN
CONVERT(VARCHAR(8), @dt, 12)
WHEN 'YY-MM-DD' THEN
STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
5, 0, '-'), 3, 0, '-')
WHEN 'MMDDYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))
WHEN 'MM-DD-YY' THEN
CONVERT(CHAR(8), @dt, 10)
WHEN 'MM/DD/YY' THEN
CONVERT(CHAR(8), @dt, 1)
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @dt, 101)
WHEN 'DDMMYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))
WHEN 'DD-MM-YY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')
WHEN 'DD/MM/YY' THEN
CONVERT(CHAR(8), @dt, 3)
WHEN 'DD/MM/YYYY' THEN
CONVERT(CHAR(10), @dt, 103)
WHEN 'HH:MM:SS 24' THEN
CONVERT(CHAR(8), @dt, 8)
WHEN 'HH:MM 24' THEN
LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)
WHEN 'HH:MM:SS 12' THEN
LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))
WHEN 'HH:MM 12' THEN
LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
ELSE
'Invalid format specified'
END
RETURN @dtVC
END
GO
Demo on Usage(Taking your own question as a sample):
*****************************************************
select dbo.FormatDateTime(convert(datetime, cast(DayDate AS char(8))),'YYYY-MM-DD') AS Date
from TableName where DayDate = 20080721
OutPut:
*******
2008-07-21
Usage of Function:
******************
DECLARE @now DATETIME
SET @now = GETDATE()
PRINT dbo.FormatDateTime(@now, 'LONGDATE')
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'SHORTDATE')
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'YYMMDD')
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'MMDDYY')
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')
PRINT dbo.FormatDateTime(@now, 'DDMMYY')
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')
PRINT dbo.FormatDateTime(@now, 'HH:MM 12')
PRINT dbo.FormatDateTime(@now, 'goofy')
Hope this will help you out , if it is reply back yo!!!
My email ID; iloveuzak@yahoo.com
Bye take care
Regards,
Zahed
ZAK
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
