Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts

Monday, March 12, 2012

ODBC and DB-Lib bcp

Question - I have a little app that uses the ODBC Bulk Operations and DB-Lib
bcp functions to load data into a table from variables (bcp_sendrow). It
works great on SQL 2000, but I'm encountering some problems on SQL 6.5.
Basically on the SQL 6.5 box the bcp_init function is returning FAIL instead
of SUCCEED. It works on SQL 2000, so it doesn't appear to be my code, but
I'm not 100% on that either. Anyone know why this might be happening and
what could be done to fix it?
ThanksFigured out the problem. MDAC versioning.
"Mike C#" <xyz@.xyz.com> wrote in message
news:e7s8QiKvGHA.3552@.TK2MSFTNGP03.phx.gbl...
> Question - I have a little app that uses the ODBC Bulk Operations and
> DB-Lib bcp functions to load data into a table from variables
> (bcp_sendrow). It works great on SQL 2000, but I'm encountering some
> problems on SQL 6.5. Basically on the SQL 6.5 box the bcp_init function is
> returning FAIL instead of SUCCEED. It works on SQL 2000, so it doesn't
> appear to be my code, but I'm not 100% on that either. Anyone know why
> this might be happening and what could be done to fix it?
> Thanks
>

Friday, February 24, 2012

obtain a date from week data

Hi all,

I have a problem with date functions...

In my program I use weeks, and with this variable I need to know which is the first day of the selected week...

For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).

I have proved with this functions...

SET DATEFIRST 1 (to configure Monday as first day of week)

SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)

With this I have the day of the current week... but I can't put my week in this function... aarrgggg.

Please help me...

Thanks a lot.declare @.week tinyint, @.firstdate varchar(4)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Originally posted by joejcheng
declare @.week tinyint, @.firstdate varchar(10)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Simply Outstanding ... why did I not think of it first ?? :)

Monday, February 20, 2012

Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for al
l
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in comp
atibility mode 90.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for
all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:

> This is probably because the database which give you the error isn't in co
mpatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>

Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for all
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in compatibility mode 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:
> This is probably because the database which give you the error isn't in compatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
> >I am using sql functions with some DMVs as follows
> >
> > SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> > NULL)
> >
> > and getting the following error :
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near '('.
> >
> > The statement succed if I use variables as follows :
> >
> > Declare @.test int
> > Declare @.test2 int
> > set @.test = db_id()
> > set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> > SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> > NULL)
> >
> > What is strange is when I executed the first one against AdventureWorks it
> > works correctly without the need to use variables. It sends back info for all
> > indexes ?......
> >
> > So am I missing any config param sonewhere
> >
> > Thanks
>