Showing posts with label create. Show all posts
Showing posts with label create. Show all posts

Friday, March 30, 2012

problem with login

I have a problem with login on slq server 2005 on the other machine. The story is:

1)

I have been trying create classes with help of SqlMetal (Linq tool) and all the time i have a message

Unhandled Exception: System.Data.SqlClient.SqlException: Login failed for user 'me'

2)

So, I tried connect by sqlcmd.

sqlcmd -S OURSERVER -d database -U me -P password

And the error message is:

Msg 18456, Level 14, State 1, Server OURSERVER, Line 1
Login failed for user 'me'.

and...

3)

I CAN connect to this database with help of "SQL Server Management Studio Express"

with the same username and password.

thanks for help

bakuuu

ps. sorry for my english

From your description, I don’t have enough information to determine if my following assumption is correct or not, but it may be worth a try.

I am guessing that the scenarios that fail (1) & (2) are from a remote machine, but the one that succeeds (3) is on the same machine as SQL Server Express. If this is true, it may be possible that TCP communications are disabled and/or the windows firewall is not allowing to connect. Please use SQL Server Configuration Manager to enable TCP (and restart the server) and make sure the firewall allows the connection.

Another possibility is that the DB “database” specified with the parameter –d is not available or you don’t have permission to connect. Try using the default DB (remove the –d database parameter) or master (-d master).

If my assumptions are not correct, we will appreciate further information in order to help.

Thanks a lot.

-Raul Garcia

SDE/T

SQL Server Engine

Monday, March 26, 2012

problem with japanese character 俱

Hi All,

Create a table , and put in some row 子ど and 俱

and fire a query something like SELECT * FROM mytable WHERE (myColumn = '子ど')

it gives me right result.

But if i fire

SELECT * FROM mytable WHERE (myColumn = '俱')

It does not return any result for the same even if myColumn has '俱'.

Surprisnly if i fire query like "SELECT * FROM mytable" it

correctly dispalys 俱.

What's the reason for the same ? Why does it is not able to search me on this japanese character(俱).Collation is Japanese_Unicode_CI_AS

Regards,

Sunil

Hi Sunil,

you wil have to indicate that the string is unicode in your query:

SELECT * FROM mytable WHERE (myColumn = N'俱')

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||Thanks Jens for your quick response.

I have one query why it was working without N in 子ど but not in 俱

Regards,
Sunil

problem with japanese character 俱

Hi All,
Create a table , and put in some row Xど and 俱
and fire a query something like SELECT * FROM mytable WHERE
(myColumn = 'Xど')
it gives me right result.
But if i fire
SELECT * FROM mytable WHERE (myColumn = '俱')
It does not return any result for the same even if myColumn has '俱'.
Surprisnly if i fire query like "SELECT * FROM mytable" it
correctly dispalys 俱.
What's the reason for the same ? Why does it is not able to search me on
this japanese character(俱).Collation is Japanese_Unicode_CI_AS
Regards,
Sunil
Please don't multi-post. If you really need to post in multiple
newsgroups, you should cross-post (post only one time, but with both
newsgroups in the To: field).
I've responded to this question in the
microsoft.public.sqlserver.programming newsgroup.
Razvan

Friday, March 23, 2012

Problem with Incrementing variable

I want create a Incrementing row through a variable in Derived Column Transmition,For that i created a variable called 'Increment'..in the derived column iam creating a new column with this expression

@.[User::INCREMENT] == @.[User::INCREMENT] + 1

Is anything wrong with this statement..bcoz iam getting 0 or the value which i assigned (1000)to all the rows.

Can you pls suggest some solution ?

Niru


Yeah, that won't do what you want. if you want to generate contiguous IDs then look here: http://www.sqlis.com/default.aspx?37

-Jamie

|||Thanks for the quick reply Jamie.

If I have 4 incrementing fileds in a table ,can I create 4 variables with in a single script and map those variables to respective fileds (Or) I have to create seperate script for each and every variable?

Thanks
Kumar
|||

You can do it all in one script.

-Jamie

sql

Problem with IN

I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.

Originally posted by Franky
I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as

Declare @.Query nVarchar(1000)

SET @.Query=N'SELECT * FROM Table ' +
'WHERE Table.ID IN( ' + @.values + ')'

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

====================================

Where @.values must be

@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman|||Originally posted by blindman
Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman

Or this way

SELECT *
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman|||Originally posted by blindman
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman

blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:

create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')

declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where CHARINDEX(cast(id as varchar),@.list)>0

Wednesday, March 21, 2012

Problem with GROUP BY and HAVING in query

I need to create a report showing how many delayed tasks there are at the
end of each month period (last day of month) so I can chart them (monthyear
against TotalTasksOutstanding) - My query is ok until I add the HAVING
clause and I get :
Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.
Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause. (Microsoft
SQL Server, Error: 8121)
SELECT convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
monthyear
Also makes the query long because that I can't GROUP BY monthyear
Also I dont't think the query would report tasks opened many months ago but
still oustanding in any particular monthyear - do I need two queries to do
this?
Thanks
DavidTry replacing your having clause with a where clause (with the exact same
criteria) to see if that works.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"David" <idstechnet@.noemail.noemail> wrote in message
news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>I need to create a report showing how many delayed tasks there are at the
>end of each month period (last day of month) so I can chart them (monthyear
>against TotalTasksOutstanding) - My query is ok until I add the HAVING
>clause and I get :
> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> (Microsoft SQL Server, Error: 8121)
> SELECT convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> monthyear
> Also makes the query long because that I can't GROUP BY monthyear
> Also I dont't think the query would report tasks opened many months ago
> but still oustanding in any particular monthyear - do I need two queries
> to do this?
> Thanks
> David
>|||Alain
Thanks - that gives me a nice chart
Any thoughts on whether my query provides what I need? i.e. would the query
report tasks opened many months ago but still oustanding in any particular
monthyear ?
Cheers
David
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
> Try replacing your having clause with a where clause (with the exact same
> criteria) to see if that works.
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "David" <idstechnet@.noemail.noemail> wrote in message
> news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>>I need to create a report showing how many delayed tasks there are at the
>>end of each month period (last day of month) so I can chart them
>>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
>>HAVING clause and I get :
>> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> (Microsoft SQL Server, Error: 8121)
>> SELECT convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
>> monthyear
>> Also makes the query long because that I can't GROUP BY monthyear
>> Also I dont't think the query would report tasks opened many months ago
>> but still oustanding in any particular monthyear - do I need two queries
>> to do this?
>> Thanks
>> David
>|||On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
> Alain
> Thanks - that gives me a nice chart
> Any thoughts on whether my query provides what I need? i.e. would the query
> report tasks opened many months ago but still oustanding in any particular
> monthyear ?
> Cheers
> David
> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>
> > Try replacing your having clause with a where clause (with the exact same
> > criteria) to see if that works.
> > Alain Quesnel
> > alainsanss...@.logiquel.com
> >www.logiquel.com
> > "David" <idstech...@.noemail.noemail> wrote in message
> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
> >>I need to create a report showing how many delayed tasks there are at the
> >>end of each month period (last day of month) so I can chart them
> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
> >>HAVING clause and I get :
> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> (Microsoft SQL Server, Error: 8121)
> >> SELECT convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> >> convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> >> monthyear
> >> Also makes the query long because that I can't GROUP BY monthyear
> >> Also I dont't think the query would report tasks opened many months ago
> >> but still oustanding in any particular monthyear - do I need two queries
> >> to do this?
> >> Thanks
> >> David- Hide quoted text -
> - Show quoted text -
Generally, the WHERE statement should be used to filter the criteria
before aggregation, and the HAVING is used to filter after
aggregation. That is why your error message had a "not contained in
aggregation" text. You would use a clause like "HAVING
COUNT(tasks.opendate) > 10" to show outstanding months, basically
reduce the rows in the DataSet .
It sounds like what you really want is a count for all tasks where:
1. In the month that the task was opened, if task ended in later
month
2. the month that the task was closed, if the task opened in an
earlier month
3. all months inbetween, where task was opened in earlier month and
ended after
The way the query is currently written, it will only increment in the
month that the task was opened. Tasks that were opened in Dec. 2006
but not closed will have a monthyear of '2006-12' showing up in your
query when you really want it to show up in Dec, Jan, Feb, etc.
To get this to work, you need to join your dataset against a list of
months, then do a count per month. To do that, I found an example
that uses a table-based CTE to generate a list of months. Left join
that against your tasks table, then let RS do the rest.
WITH MYCTE AS
(
SELECT CAST( '2007-01-01' AS DATETIME ) DT
UNION ALL
SELECT DateAdd( mm, 1, DT )
FROM MYCTE
WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
)
SELECT X.EFF_DT AS YEARMONTH,
COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
COUNT( Y.CLSDDATE ) AS TotalTasksClosed
FROM (
SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
FROM MYCTE
) X LEFT JOIN
(
SELECT *
FROM TASKS
WHERE CLSDDATE IS NULL
OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
1 )
) Y
ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
AND X.TERM_DT > Y.OPENDATE
GROUP BY X.EFF_DT
Now, in your chart, use the YEARMONTH column as the Category, and
Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
Values. In the Label of the Category, use an expression like
= Format( Fields!YEARMONTH.Value, "yyyy-MM" )
and you get around having to do all the formatting on the Server-side.
-- Scott|||Scott
Fantastic response - looks exactly what I need - will read up on CTE.
Many thanks!
Cheers
David
"Orne" <polysillycon@.yahoo.com> wrote in message
news:9332099e-41f8-43e6-b082-0da7fa1fa5c2@.s12g2000prg.googlegroups.com...
> On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
>> Alain
>> Thanks - that gives me a nice chart
>> Any thoughts on whether my query provides what I need? i.e. would the
>> query
>> report tasks opened many months ago but still oustanding in any
>> particular
>> monthyear ?
>> Cheers
>> David
>> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
>> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>>
>> > Try replacing your having clause with a where clause (with the exact
>> > same
>> > criteria) to see if that works.
>> > Alain Quesnel
>> > alainsanss...@.logiquel.com
>> >www.logiquel.com
>> > "David" <idstech...@.noemail.noemail> wrote in message
>> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>> >>I need to create a report showing how many delayed tasks there are at
>> >>the
>> >>end of each month period (last day of month) so I can chart them
>> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add
>> >>the
>> >>HAVING clause and I get :
>> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> (Microsoft SQL Server, Error: 8121)
>> >> SELECT convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> >> convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER
>> >> >BY
>> >> monthyear
>> >> Also makes the query long because that I can't GROUP BY monthyear
>> >> Also I dont't think the query would report tasks opened many months
>> >> ago
>> >> but still oustanding in any particular monthyear - do I need two
>> >> queries
>> >> to do this?
>> >> Thanks
>> >> David- Hide quoted text -
>> - Show quoted text -
> Generally, the WHERE statement should be used to filter the criteria
> before aggregation, and the HAVING is used to filter after
> aggregation. That is why your error message had a "not contained in
> aggregation" text. You would use a clause like "HAVING
> COUNT(tasks.opendate) > 10" to show outstanding months, basically
> reduce the rows in the DataSet .
> It sounds like what you really want is a count for all tasks where:
> 1. In the month that the task was opened, if task ended in later
> month
> 2. the month that the task was closed, if the task opened in an
> earlier month
> 3. all months inbetween, where task was opened in earlier month and
> ended after
> The way the query is currently written, it will only increment in the
> month that the task was opened. Tasks that were opened in Dec. 2006
> but not closed will have a monthyear of '2006-12' showing up in your
> query when you really want it to show up in Dec, Jan, Feb, etc.
> To get this to work, you need to join your dataset against a list of
> months, then do a count per month. To do that, I found an example
> that uses a table-based CTE to generate a list of months. Left join
> that against your tasks table, then let RS do the rest.
> WITH MYCTE AS
> (
> SELECT CAST( '2007-01-01' AS DATETIME ) DT
> UNION ALL
> SELECT DateAdd( mm, 1, DT )
> FROM MYCTE
> WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
> )
> SELECT X.EFF_DT AS YEARMONTH,
> COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
> COUNT( Y.CLSDDATE ) AS TotalTasksClosed
> FROM (
> SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
> FROM MYCTE
> ) X LEFT JOIN
> (
> SELECT *
> FROM TASKS
> WHERE CLSDDATE IS NULL
> OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
> 1 )
> ) Y
> ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
> AND X.TERM_DT > Y.OPENDATE
> GROUP BY X.EFF_DT
> Now, in your chart, use the YEARMONTH column as the Category, and
> Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
> Values. In the Label of the Category, use an expression like
> = Format( Fields!YEARMONTH.Value, "yyyy-MM" )
> and you get around having to do all the formatting on the Server-side.
> -- Scott

Tuesday, March 20, 2012

problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 12, 2012

Problem with FILENAME as @variable

Hi.

I am trying to create a database based on variables from xp_regread.
Somehow I cant pass @.dBDir as FILENAME.

I get the following error-message :
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@.dBDir'.
What am I doing wrong ?
I think the problem is somehow related to a datatype-problem or
it is simply not possible to pass a variable to a "create
database"-statement.

I have checked the xp_regread-output...naturally :) ...and it is correct.
Hope someone can help :)

regards
Michael

------------
Source-code is as follows:

USE master
GO

DECLARE @.dBDir nvarchar(128)
DECLARE @.dBlog nvarchar(128)

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\School-Project\IRPF
Database\','dBDir',@.param = @.dBDir OUTPUT

SET @.dBDir =@.dBDir+'\IRPF.mdf'
SET @.dBlog =@.dBDir+'\IRPF.ldf'

CREATE DATABASE IRPF
ON PRIMARY
( NAME = IRPF_db,
FILENAME = @.dBDir)
LOG ON
( NAME = 'IRPF_log',
FILENAME = @.dBlog)
GOMichael (agentmikie@.cFJERNDETTE.dk) writes:
> I get the following error-message :
> Server: Msg 170, Level 15, State 1, Line 24
> Line 24: Incorrect syntax near '@.dBDir'.
> What am I doing wrong ?
> I think the problem is somehow related to a datatype-problem or
> it is simply not possible to pass a variable to a "create
> database"-statement.

The latter. You will have to use dynamic SQL. The short story is:

SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
EXEC (@.sql)

The feature demonstrated here, is one to be used with care. For this
kind of thing, it is the right thing, but there are many cases where
dynamic SQL is not the answer. So there is a full story on
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The latter. You will have to use dynamic SQL. The short story is:
> SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
> ' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
> ''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
> EXEC (@.sql)
> The feature demonstrated here, is one to be used with care. For this
> kind of thing, it is the right thing, but there are many cases where
> dynamic SQL is not the answer. So there is a full story on
> http://www.sommarskog.se/dynamic_sql.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Thank you for your reply.

The link seems to be exactly what I was looking for. :)

Regards
Michael.

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regards
perhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regardsperhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx
.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with EXEC xp_cmdshell in UDF's

Hi All

Some what of a noob to UDF's and the EXEC statement...
I am confused on the following code....

CREATE FUNCTION DBO.DoesFileExist(@.FileLocation NVARCHAR(500))
RETURNS INT AS
BEGIN
DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd
RETURN @.return
END

GO

DECLARE @.FileLocation AS NVARCHAR(500)
SET @.FileLocation = 'C:\to\log.txt'

DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd

SELECT @.return AS Exp1

SELECT DBO.DoesFileExist(@.FileLocation) AS Exp2


RETURNS:
Exp1 = 0
Exp2 = 1

WHY IS THAT? Shouldn't Exp2 also be equal to 0? It's running the same function...

Any help would be much appreciated
Thanks
Mike
IF EXIST c:\MyFile.txt (ECHO 0) ELSE (ECHO 1)

Problem with EXEC command

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>

Problem with enterprise manager ( image attached )

Hi,
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
Did you by chance install YUKON Beta on the same box ?
"Ignacio Machin ( .NET/ C# MVP )" wrote:

> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to know
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
|||Nop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...[vbcol=seagreen]
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
local[vbcol=seagreen]
know[vbcol=seagreen]
|||Probably that is the problem..
"Ignacio Machin ( .NET/ C# MVP )" wrote:

> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to know
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>

Problem with enterprise manager ( image attached )

Hi,
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of TransportationDid you by chance install YUKON Beta on the same box ?
"Ignacio Machin ( .NET/ C# MVP )" wrote:

> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to kno
w
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>|||Nop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...[vbcol=seagreen]
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
>
local[vbcol=seagreen]
know[vbcol=seagreen]|||Probably that is the problem..
"Ignacio Machin ( .NET/ C# MVP )" wrote:

> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to kno
w
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>

Friday, March 9, 2012

Problem with enterprise manager ( image attached )

Hi,
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of TransportationNop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
> > Hi,
> >
> > I have been having a problem for a couple of weeks, for some reason the
> > "Create Date" column of both the tables and SP views appear with weird
> > characters (as shown in the attached image) , This happen both on a
local
> > server as well as on a remote server.
> > When I connect to the remote server using TS and see the same view the
> > "Create Date" column appear normally.
> >
> > What could have happen? and even it's not a big issue I would like to
know
> > how to solve it.
> >
> >
> > Thanks,
> >
> > --
> > Ignacio Machin,
> > ignacio.machin AT dot.state.fl.us
> > Florida Department Of Transportation
> >
> >
> >

Wednesday, March 7, 2012

Problem with domain group and diagrams

I have a problem and I dont have an idea to solve it. I work with SQL Server and my students on Faculty of Information Technology.

    I have create SQL Server login for domain group of users (about 60 od them). That mean I have SQL Server login like this Domain\Users To whole group of users I had grant server role (dbcreator). I dont wish to import 60 login one by one and gice that permision. Ok, student can create database without any problems But, when they try to create database diagram (expanding database diagram node). They get a message that database need to have a valid owner (Hmm..ok its not a big deal to do it) Quick check to some of database (Right click-->properties-->General-->Owner (ther is a Domain\UserName (not name of group)..and that is ok (Right click-->properties-->Files-->TextBox Owner. I try to enter same Domain\UserName from step 5. But I get this message

TITLE: Microsoft SQL Server Management Studio

Set owner failed for Database '1110_EvidencijaKnjiga'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+owner+Database&LinkId=20476


ADDITIONAL INFORMATION:

An exception occurred in SMO. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

The login 'Domein\UserName' does not exist on this server.


BUTTONS:

OK

This is wired. Ok I know that that user is not login but group is. Step 5 show that user, but step 6 have empty owner options and dose not allowe me to enter same user.

How to fix this?

Thank you

Try changing the database owner to a valid login by executing the following SQL statement:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

-Sue

|||

Sue Hoegemeier wrote:

Try changing the database owner to a valid login by executing the following SQL statement:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

-Sue

Thank you Sue for your advice and solution of my problem.

But I am not happy with your solution of problem. Here is why.

Ok, I change owner to sa. But now domain user who is create that database, have not aprorpiate permission. (dont forget I have import doman group on SQL Server..not user one by one). Now I must cretae account on SQL Server for that doman user and give him (or her) db_owner database role. In my case that step I need to repeat about 30 times. In that case doman groups on SQL Server are not cool solution in cases like mine.

Is there any other solution or if somene can expplan why this is hapening? Why user from doman gruop with create database role, can not be a valid owner (in terms of SQL error message)?

Jasmin

|||

Make sure the latest service packs have been applied to all the PCs with the client tools. There were some fixes for related issues on SP1. Other than that, you could have them use Visio instead of the diagram tool in Management Studio.

-Sue

Problem with distributor

In the replication wizard, the first menu "SELECT DISTRIBUTOR", have two
options
a) Make the "Myserver" its own distribuitor, SQl Server will create a
distribuiton database server and log.
b) Use the following Server, ( The selected server must already be
configured as Distribuitor).
When i select the first option pop-ups a window with this text :
"SQL Server Agent on SERVER1\Macuco" currently uses the system account ,
which causes replication between servers to fail, In the following dialog
Box, specy another account for the service startup account "
In the SQL Server Agent Properties which account i need to choose?
Thanks in ADvance
Alejandro Carnero
Brazil-Santos
Ideally use a local admin account.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:OTGvbxcAGHA.740@.TK2MSFTNGP12.phx.gbl...
> In the replication wizard, the first menu "SELECT DISTRIBUTOR", have two
> options
> a) Make the "Myserver" its own distribuitor, SQl Server will create a
> distribuiton database server and log.
> b) Use the following Server, ( The selected server must already be
> configured as Distribuitor).
> When i select the first option pop-ups a window with this text :
> "SQL Server Agent on SERVER1\Macuco" currently uses the system account ,
> which causes replication between servers to fail, In the following dialog
> Box, specy another account for the service startup account "
> In the SQL Server Agent Properties which account i need to choose?
> Thanks in ADvance
> Alejandro Carnero
> Brazil-Santos
>

Saturday, February 25, 2012

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

Hi

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, så they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>> Please turn on TF-1204 and see what is getting deadlocked.
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk

Problem with dbo.sysindexkeys in SQL 2000

Hello,

I am experiencing some performance issues with the dbo.sysindexkeys.

I wonder if anyone can help me create an index on it?

Thanx!

SQL script:

set nocount on
set ansi_warnings off
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create test database'
print
'--------------------------'

use master
go

if not db_id('test') is null
drop database test
go

create database test
go

use test
go

--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create 4000 tables in test database'
print
'--------------------------'

declare @.i_iter int

set @.i_iter = 0
while @.i_iter < 4000
begin

if @.i_iter % 100 = 0
print 'Table -> ' + cast(@.i_iter as char(10))

exec ('
create table TABLE_' + @.i_iter + '
( COLUMN_0 int not null,
COLUMN_1 int not null,
COLUMN_2 datetime not null,
COLUMN_3 datetime not null,
COLUMN_4 varchar(100) not null,
COLUMN_5 smallint not null,
COLUMN_6 bit not null,
constraint TABLE_' + @.i_iter + '_PK primary key (COLUMN_0,
COLUMN_1, COLUMN_2)
)
create index TABLE_' + @.i_iter + '_I1 on TABLE_' + @.i_iter + '
(COLUMN_4, COLUMN_5)
')

set @.i_iter = @.i_iter + 1
end
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Dump dbo.sysindexkeys in temp. table and create a clustered
index on it'
print
'--------------------------'

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
select * into dbo.t_sink from dbo.sysindexkeys
alter table dbo.t_sink add constraint t_sink_pk primary key ([id] ,
[indid], [colid])
go

print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using this'
print ' temp table'
print
'--------------------------'

if not object_id('tempdb..#dump') is null
drop table #dump
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #dump
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.t_sink sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
go

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using the'
print ' system tables'
print
'--------------------------'

if not object_id('tempdb..#direct') is null
drop table #direct
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #direct
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.sysindexkeys sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
goherman (herman@.hotmail.com) writes:
> I am experiencing some performance issues with the dbo.sysindexkeys.
> I wonder if anyone can help me create an index on it?

I guess you could if you change the configuration parameer "allow updates
to system tables", but I have no idea wether SQL Server would actually
look at the index.

Anyway, this query executes almosts as fast as your query with
the temp table:

select sobj.name as table_name,
sind.name as index_name,
index_col(sobj.name, sind.indid, 1) as c01,
index_col(sobj.name, sind.indid, 2) as c02,
index_col(sobj.name, sind.indid, 3) as c03,
index_col(sobj.name, sind.indid, 4) as c04,
index_col(sobj.name, sind.indid, 5) as c05,
index_col(sobj.name, sind.indid, 6) as c06,
index_col(sobj.name, sind.indid, 7) as c07,
index_col(sobj.name, sind.indid, 8) as c08,
index_col(sobj.name, sind.indid, 9) as c09
--into #direct
from dbo.sysobjects sobj
join dbo.sysindexes sind ON sind.id = sobj.id
where sobj.xtype = 'U'
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
order by sobj.name, sind.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp