Database

17-Nov-03 3:14

_SQLObjectListPtr
what sql namespace should be useed to access this ptr ??
i want to use the ListUserPermissions method ..for htis i need the above mentioned ptr..

ranjani

sqldmo.tlh

ranjjj16-Nov-03 22:33

16-Nov-03 22:33

in my c++ program to find the user permissions for each user in a sql database...i use ListUserPermissions method after importing sqldmo.rll
on execution i get an error saying...
undeclared identifier..ListUserPermissions !!
shud i use any headre file other thann sqldmo.rll ?

ranjani

listdatabasepermissions

ranjjj15-Nov-03 23:53

Load a file into a binary column in SQL server

15-Nov-03 23:53

can i use the function
ListDatabasePermissions
for getting the acccess permissiosn for a particular database?

ranjani

CillyMe15-Nov-03 17:32

CillyMe

15-Nov-03 17:32

There're plenty of articles that shows you how to load a disk file/binary from ASP frontend and insert it into a binary column in SQL server. But, is there a stored procedure that can load file/binary from disk WITHOUT ASP or relying on external component?

QUESTION 1:
If that's possible, I can create a simple stored procedure to insert binary file into a binary column. Pseudo code:

CREATE PROCEDURE sproc_UpdateMessageAttachment
@msgID uniqueidentifier
@inputfile varchar(100),
@status binary[50000] OUTPUT
AS
BEGIN
DECLARE @binaryattachment binary[50000]

--Of course, I made this up. The question is whether such stored procedure exists.
EXEC sp_loadfile @inputfile, @binary OUTPUT

UPDATE messages
SET Attachment = @binaryattachment
WHERE msgID=@msgID
END

QUESTION 2:
Is there alternative to this? Can I instantiate/call method on a COM component from within body of stored procedure?

And if I can what component would I need in this case?

Thanks.

Reference from www.stardeveloper.com:
1. Classical ASP http://stardeveloper.com/articles/display.html?article=2001033101&page=1
2. ASP.NET
a. http://stardeveloper.com/articles/display.html?article=2003022601&page=1
b. http://stardeveloper.com/articles/display.html?article=2003031201&page=1

Re: Load a file into a binary column in SQL server

mhmoud rawas15-Nov-03 18:39

mhmoud rawas

15-Nov-03 18:39

I think that otherwise you have to create an extended stored procedure for that

Mhmoud Rawas
------------
Software Eng.

Re: Load a file into a binary column in SQL server

CillyMe16-Nov-03 22:32

CillyMe

16-Nov-03 22:32

Thanks. Creating extended stored procedure... means I need to read up on how to pass arguments and returns to/from SQL server and the dll. I think I'll just keep things simple for now:
a. Create a SQL Server Job that launches an application (say, LoadBinary.exe) using xp_xmdshell
b. LoadBinary.exe will load the binary file (disk file) into SQL server.

Load a file into a binary column in SQL server

CillyMe15-Nov-03 17:31

CillyMe

15-Nov-03 17:31

There're plenty of articles that shows you how to load a disk file/binary from ASP frontend and insert it into a binary column in SQL server. But, is there a stored procedure that can load file/binary from disk WITHOUT ASP or relying on external component?

QUESTION 1:
If that's possible, I can create a simple stored procedure to insert binary file into a binary column. Pseudo code:

CREATE PROCEDURE sproc_UpdateMessageAttachment
@msgID uniqueidentifier
@inputfile varchar(100),
@status binary[50000] OUTPUT
AS
BEGIN
DECLARE @binaryattachment binary[50000]
EXEC sp_loadfile @inputfile, @binary OUTPUT --Of course, I made this up. The question is whether such stored

procedure exists.

UPDATE messages
SET Attachment = @binaryattachment
WHERE msgID=@msgID
END

QUESTION 2:
Is there alternative to this? Can I instantiate/call method on a COM component from within body of stored procedure?

And if I can what component would I need in this case?

Thanks.

Reference from www.stardeveloper.com:
1. Classical ASP http://stardeveloper.com/articles/display.html?article=2001033101&page=1
2. ASP.NET
a. http://stardeveloper.com/articles/display.html?article=2003022601&page=1
b. http://stardeveloper.com/articles/display.html?article=2003031201&page=1

Re: Load a file into a binary column in SQL server

Jeff Varszegi18-Nov-03 5:38

Jeff Varszegi

18-Nov-03 5:38

I don't know about your specific situation, but have you looked at using BULK INSERT or bcp (via xp_cmdshell)?

Regards,

Jeff Varszegi

SQL Server Date Problem

sim_san15-Nov-03 1:16

sim_san

15-Nov-03 1:16

Hi Geeks,
I am quite new to SQL Server.Well I have a problem..
Could someone help me ??

This is my Query:

sql="SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate='11/15/2003'
ORDER BY IP_Id"

Now in the IP table there are records containing date values
i.e. AdmDate='15/11/2003' ;these date values r in small date format with precision to seconds.

Now I want All the records of a paticular date irrespective of their time Value.

-SimSan

Mike Dimmick15-Nov-03 1:55

Mike Dimmick

15-Nov-03 1:55

I typically use

SELECT Id,Name, Age,City, AdmDate 
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId 
WHERE AdmDate >= '2003-11-15' AND
AdmDate < DATEADD( Day, 1, '2003-11-15' )
ORDER BY IP_Id

Of course, typically I use this is in a stored procedure where the literal date is replaced by a parameter, where using DATEADD makes a bit more sense.

sim_san17-Nov-03 6:40

sim_san

17-Nov-03 6:40

This works fine...Thanks for the tip.

Well...I wanted to clarify if SQL Server treats
Dates in American Date format.If it does,why this date format ('yyyy-mm-dd') is in use.

-SimSan

Mike Dimmick17-Nov-03 6:54

Mike Dimmick

17-Nov-03 6:54

Because I'm in the UK Wink | ;)

SQL Server interprets literal dates in queries using the user's default locale - even in stored procedures, IIRC. You could write '10/11/2003' to be interpreted as October 11th, but if your user is using the English (UK) locale, it will be interpreted as 10 November.

"SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting." Using Date and Time Data[^]

The ISO format yyyy-mm-dd is supposed to be supported in all locales with the same meaning.

On re-reading Writing International Transact-SQL Statements[^], it actually looks like you should use unseparated dates (format yyyymmdd) to ensure correct interpretation. So I've been getting it wrong. Blush | :O

sim_san22-Nov-03 6:23

sim_san

22-Nov-03 6:23

Thanks again for your info..U've got it right!!!

-SimSan

sqlDataAdapter Wizard does not Update

Anonymous21-Nov-03 5:45

Anonymous

21-Nov-03 5:45

You could use

SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate BETWEEN '11/15/2003 00:00' AND '11/15/2003 23:59'
ORDER BY IP_Id"

Bruce L. Scheffler14-Nov-03 11:39

Bruce L. Scheffler

14-Nov-03 11:39

Real simple problem but it is driving me nuts.
Create a table in SQL with two columns.
NameId and FullName
Make the NameId a primary Key.
Add a nameid and a fullname to the table.
Save the table as NameId.

Now using only the .net wizards for a windows forms project I do the following:

1. Create new project (windows forms)
2. Drag two textboxes onto the form.
3. Drag a button onto the form.
4. Drag a sqlDataAdapter onto the form which starts the wizard.
5. Connect to the sqldatabase for the table.
6. Allow it to create the sql statements.
7. Create the query using both fields.
8. Do not change the advanced settings.
9. Save the wizard.
10. Create a dataset using the NameId table.
11. Bind the two textboxes to the table fields via their respective properties.
12. On the window form events create an event for load.
13. Enter the fill method of the sqldataadapter:
this.sqlDataAdapter1.Fill(this.dataset1, "NameId");
14. Return to the design screen and create an click event for the button under its respective events.
15. In the code enter the following:
this.sqlDataAdapter1.Update(this.dataset1, "NameId");

now compile
my results are the form will display the one record, however when I change the fullname field to something else it does not update the sql NamId table...

What am I doing wrong...
Smile | :)

Bruce...

Join

Anonymous14-Nov-03 8:25

Anonymous

14-Nov-03 8:25

I have a table1 where I have a dateentered and trantype.
I need to find the max(dateentered) and then based on trantype join table2 or table3. All data should be in one result. How do I do this?

Re: Join

jeff_martin14-Nov-03 9:52

jeff_martin

14-Nov-03 9:52

You can't selectively join tables. You should probably use a union. Without knowing your table structure, here is a stab at it...

select max(dateentered), otherfields
from table1
join table2 on (table1.trantype = table2.trantype)

union

select max(dateentered), otherfields
from table1
join table3 on (table1.trantype = table3.trantype)

Re: Join

Anonymous17-Nov-03 2:39

Anonymous

17-Nov-03 2:39

My more detail problem
CREATE TABLE [dbo].[table1] (
[TranID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SID] [varchar] (10) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[TranType] [varchar] (1)NOT NULL ,
[DateEntered] [datetime] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
[TranID] [numeric](18, 0) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[SID] [varchar] (10)
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3] (
[TranID] [numeric](18, 0) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[SID] [varchar] (10)
) ON [PRIMARY]

Example1: I need select info from table1 and 2
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'Y'
DateEntered '9/1/2003'
---------------
Example2: Infor from table1 and 3 (Maxdate)
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'Y'
DateEntered '9/1/2003'
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'N'
DateEntered '9/2/2003'

sql

ranjjj13-Nov-03 17:18

13-Nov-03 17:18

how to check for the access permissions of each user in a sql database?

can some one help me with a c++ code or this

ranjani

Access Query

bgeroux13-Nov-03 16:25

bgeroux

13-Nov-03 16:25

I am working on a new asp.net project using a Access 2000 database. I want to create some update queries in Access and then be able to call them using parameters to update certain fields. I have read some articles on how to do this using a SQL Server database, but I can't find any information on how to do this for Access. What I don't want to do is hard code any queries in code.

bgeroux

Hesham Amin13-Nov-03 21:28

Hesham Amin

13-Nov-03 21:28

in Access .. on the left bar of the database window..
Select Queries then ->create Query in design view ->Close the show table screen -> right click ->choose SQL View

write your update query :

Update table1 set userName="bla bla" where ID=@MyID

@MyID is the value you pass to the query..(parameter)
you can declare it like this :at the beginning of the query (not required)

parameters @MyID long;

Roger Wright14-Nov-03 3:14

Roger Wright

14-Nov-03 3:14

Here's an example[^] that demonstrates using stored queries in Access. It also briefly mentions some of the advantages of SQL Server over Access. Downloadable code and a sample database are available, as well.

"Your village called -
They're missing their idiot."

bgeroux14-Nov-03 3:43

bgeroux

14-Nov-03 3:43

Thank for responding so quickly. The example code is great, but this code is for using a ADO recordset and I am looking for a ADO.net solution. Is this possible?;)