VC++ Code Example For Extended SP - WANTED
May 8, 2002Please help to find an example of the code written on VC++ for an extended stored procedure (dll) for SQL Server 2000.
Thanks
ADVERTISEMENT
Extended Stored Procedure And C Code
Jul 12, 2007Hi,
I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.
It seems I must use switch clrafe for my executable to deploy to sql.
But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!
But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!
Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?
How Do I Read An Extended Property Of A Database In Code?
May 8, 2006I have recently had the need to create a utility program to upgrade the schema of an existing database for users of my program at install time. I will be using SQL Management Studio to generate the change scripts the program will use, but I will need some kind of way to know what version of the database a user has installed.
After poking around in SQL Management Studio I found a place for "Extended Properties" on a database, and I thought this would be the perfect place to add a property called "SCHEMA_VERSION" to know what version of the database is installed.
I have no idea how to read and update this value in code though. Can someone provide a small example on how to read and update an extended property on a database? Any .NET language example is welcomed; C# is preferred. Thanks in advance!
SSIS Failing To Load Extended Character In Code Page 1252
Aug 8, 2007Background€¦
We€™re receiving the data as flat files, when I open up the file in notepad the characters look like the error€™d examples (see below). Open the file up in a texteditor where you can apply code page 1252 it display the characters correctly, which is expected behaviour!
I use SSIS to load these files into my database, applying the correct code page€¦
€¦however the data gets loaded in the incorrect form. Plus the database is collation €˜SQL_Latin1_General_CP1_CI_AS€™ which is code page 1252 so I can€™t see why I am getting these issues.
This is an example of the Umlaut (German chars) issues were getting, they€™re appearing in the database like this€¦.
tuid travelproductid lastname
1741869 6 P€?hls
1792950 6 Sch€žfer
2087798 6 L€?schhorn
2089010 6 Sagemller
2314086 6 Jnger
€¦& should be€¦
1741869 6 Pöhls
1792950 6 Schäfer
2087798 6 Löschhorn
2089010 6 Sagemüller
2314086 6 Jünger
I think it€™s a bug with SSIS, I know having a Unicode file would certainly solve this but I don't have that option.
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
SQL Guru Wanted
Aug 2, 2006Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
Thanks in advance
Configuration Error...name Wanted
Nov 30, 2006Hi, does somebody recognize the problem with my code? -Thanks!
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.Source Error:
<sessionState mode="SQLServer"stateConnectionString="Jensen"sqlConnectionString=
"datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>
SQL Developers In Seattle Wanted!
Sep 23, 1999Always looking for people who are strong with microsoft technology for new oppurtunities in the Seattle area. If you are good, we can help you find your ideal next position.
don't be shy. shoot me an e-mail.
thanks
Pat Copeland
SQL Developers In Seattle Wanted!
Sep 16, 1999I am looking for strong SQL developers and/or DBA's for some really sweet companies here in Seattle. Anyone interested that is good and wants to hear about new oppurtunities? I would love to help you out.
don't be shy just shoot me an e-mail.
Everything You Wanted To Know About Blocking...but Were Afraid To Ask
Mar 19, 2004OK...this is driving me nuts....
In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...
put in WAITFOR...sometime it worked...started with an empty table..it worked....left the 28k rows, blocked...
Now, put SELECT COUNT(*)...works each and every g-d damn time...
HUH?
Now I get to the bcp out..
added the same code WAITFOR/SELECT *...
blocks each and ever g-d damn time....
I'm very reticent to COMMIT and start another tranny block...
Anyone have any ideas?
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO
CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO
CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO
INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@Path sysname
, @FName sysname
AS
SET NOCOUNT ON
BEGIN TRAN
DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int
, @Old varchar(255), @New varchar(255), @x int
CREATE TABLE #bcpLog(Col1 varchar(8000))
SET @rc = 0
DELETE FROM wrk_DataHold
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew
OPEN OldNew
FETCH NEXT FROM OldNew INTO @Old, @New
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@Old,@New)
WHERE Col1 LIKE '%'+@Old+'%'
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
INSERT INTO #bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'
FETCH NEXT FROM OldNew INTO @Old, @New
END
CLOSE OldNew
DEALLOCATE OldNew
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SELECT @FName = SUBSTRING(@FName,1,CHARINDEX('.',@FName)-1)+'.new'
INSERT INTO #bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @Path + @FName
/*
SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
SET @cmd = 'bcp #bcpLog out D:cpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
*/
COMMIT TRAN
usp_ModifyRows_Exit:
SELECT * FROM #bcpLog
DROP TABLE #bcpLog
SET NOCOUNT OFF
RETURN @rc
usp_ModifyRows_Error:
CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit
GO
SET NOCOUNT OFF
No Recovery/no Log File Wanted
Feb 8, 2006I have a SQL Server (Express) database that I use in a Visual Studio (Visual Basic) 2005 application.
The application is of the Decision Support type, that routine deletes the contents of some of the database tables and then re-populates them with new data. There is no reason for keeping the "old" contents and no reason to restore the old content.
(It does this using "BULK INSERT" statements, as in:
"BULK INSERT SharePrices FROM "C:SharePrice.txt")
Because of all the data replacing, the log file gets huge quickly. I don't need the log file at all.
(I suspect that the logging is also taking up time unncessarily.)
Is there any way I can set the database not to have a log file at all, or to have a small log file?
(I've tried deleting the log file from SQL Server Management SQL Express, but get a message that there has to be a log file.)
When I restrict the log file size, my application returns error messages that the log file is too small, and the application can't do what it needs to do.
I have already made sure that in the database that the recovery mode is set to "Simple" and that "AutoShrink" is set to true.
I've tried manual "Shrinks". These work. However, as soon as the application accesses the database, the log file gets huge again.
I realise I may have to do (unnecessary) backups before/after my bulk insert statements. If so, I'd appreciate some help on how to do this from within a Visual Basic 2005 application. (I'm actually using the Data Access block from the Enterprise Library for my data access.)
I'd really appreciate your help.
Kind regards
Reg Bust
Sample Coding Wanted!
Oct 31, 2007Dear all,
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE";
execute SQL statement "select * from table A where tableA.field B = "ABCDE";
For each record from the result of the above SQL statement,
"select * from tableB where tableB.field C=tableA.fieldX"
if the record can be found in tableB,
"update tableB set tableB.fieldD=something";
if the record cannot be found in tableB,
"insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
thanks in advance!
Row Info Wanted In Column (like Cube)
Aug 26, 2004Hi
I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.
say
select col1, col2 from tblsample where rqtr=1 and ryear = 2000
select col1, col2 from tblsample where rqtr=2 and ryear = 2000
select col1, col2 from tblsample where rqtr=3 and ryear = 2000
select col1, col2 from tblsample where rqtr=4 and ryear = 2000
i want information like
for the Year 2000
1 qtr 2 qtr 3 qtr 4 qtr
How to acheive this in MSSQL 2000
Wanted: Sql Shopping Cart Resources
Sep 30, 2005hi! I'm still fairly wet behind the ears when it comes to databases, especially sql server 2000. But I just got a copy from my University and I really want to spend some time messing with it. The first thing I want to try is making a shopping cart that stores the items in the database rather than in the session. I'm using asp.net (VB) and I had previously made a simple shopping cart for a SMALL site that stored all the information in a dataset stored in the session... but I've been told that isn't a good idea and reccomended I store the data in the sql database. so that's what I want to do!
I'm looking for resources that can help me get this started. These are the first questions I have about this approach, and any advice from you experts would be most appreciated!
1) do I store the data in a temporary table or do I use the regular table and make temporary rows? if the former how do I transfer the rows from the temp table to the order table? if the latter how do I change it from temporary to peromanent? in either case, how do I eliminate rows if the user abandons their cart?
2) how do I first initialize an order and keep track of the orderID for each user and ensure that no one is assigned a duplicate orderID?
those are my biggest confusions about this approach. I've looked for books on this but most are either too much about sql syntax and big-time server management, or they are too general and not specific to sql server. I've tried looking online, but I can't find much information on doing this using asp.net 2.0. thanks again for your help!
-SelArom
Sql Server Certifcation Website Wanted
Jul 20, 2005I am looking for sql server certifcation questions any one hasany.like dumps or some thing
View 1 Replies View RelatedExplanation Wanted About The Service Principal Name ( SPN )
Jun 26, 2007Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
Some Guidance For An SSIS Newbie Wanted
Apr 27, 2006Hi!
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database.
2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it.
3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
Report Hiding Duplicates, Not Wanted
Apr 16, 2007I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.
The query in the dataset DOES return the duplicates as expected.
Anyone know how to fix this, or if it is a bug?
Thanks,
Chris
SELECT Statements Not Providing Results Wanted
Apr 10, 2002I have three tables, Employer, Customer and CustomerEmployer. The employer table contains all employers and the customeremployer table contains customer records for an employer. For example
Employer Table
EmployerID Name Address
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street
4 ZZZ Company 2 Rust Street
Customer Employer Table
ID EmployerID CustomerID
1 3 1
2 3 2
Customer Table
ID Name
1 Joe Smith
2 Jane Thomas
3 Tim James
I would like to run a select statement which lists all the employers and whether the employer has already been assigned to a customer. For example, for Joe Smith I would like to see all the employers listed AND some marker indicating that Joe is already assigned to EmployerID 3, JJJ Company.
Results
EmployerID Name Address CustomerID
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street 3
4 ZZZ Company 2 Rust Street
I have tried unions and many types of joins but none seem to work correctly. I was using the following select statement however only employers that are contained in the customeremployer table appear.
SELECT Employer.EmployerID, Employer.Name, Address, Employer.City,
CustomerEmployer.CustomerID
FROM CustomerEmployer RIGHT OUTER JOIN
Employer ON CustomerEmployer.EmployerID = Employer.EmployerID
WHERE (CustomerEmployer.CustomerID = @customerid) OR (CustomerEmployer.CustomerID IS NULL)
ORDER BY Name
Any ideas?
Thanks.
Help Wanted Msaccess PIVOT-query --&> MS-SQL View/sp
Jan 18, 2006Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
View 3 Replies View RelatedPeople Wanted: Enroll Now To Help To Make SQL Server Even Better!
Apr 14, 2006SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
All I Wanted Was To Submit Customers Info To My Emailbox
Jan 28, 2008I have created an order form for my business. I placed a submit button at the bottom. I wanted all the clients information to go into my emailbox. Someone told me about MS SQL Server. I got excited and downloaded the 2005 Express version with all the trimmings. Now I am more confused than I was before. Someone please explain to me what I should do first. Redo the form with the studio then work with the server? I think I may have deleted something important from the server trying to figure it out. Help!!!!!!!!!!
View 7 Replies View RelatedGuides On Setting Up Schema For A Database Wanted
Jan 16, 2008In my SQL Server 2005, we stored multiple application tables in a single database. All of them is using the dbo schema in the past.
Now, we would like to change this practice and adopt a better one. However, we got some problems in the configuration.
If I want to create such a database, should I first create multiple logins and multiple users for all the application. The mulitple logins are used in the connectionstring of my web. While each login can only have 1 user in a database, I need to create a user for each login as well. (Am I correct in doing so? It seems a bit messy.)
The other problem is what should the database role of all that users? If I don't grant them the db_owner. They can't create the required tables. If I grant it, it voliate the intension of separating different application...
Please help. I am a bit lost on this now. Thanks!
People Wanted: Enroll Now To Help To Make SQL Server Even Better!
Apr 14, 2006
SQL Server Usability Enrollment
Your opinions count.
The SQL Server Design and Usability team invites you to spend a few hours with us and our products (SSMS, SSIS, SSAS, SSRS, BIDS and etc) and tell us what you think. We are interested in how you interact with the products. The information we gather is directly translated into product design improvements, so your feedback makes a difference.
We periodically conduct lab research on campus as well as site visits on various aspects of SQL Server. Your involvement is extremely valuable to us. If you are working with
administering SQL Server databases, building database applications, performing BI
related tasks - we'd like to hear from you.
Please take a couple of minutes to fill out the SQL Server Usability Enrollment form. Your contact information is extremely confidential and will only be used by our team for usability and research purposes. Thanks for your help to make the SQL Server even better!
Microsoft SQL Server Design and Usability
After Merge Replication - Wanted To Remove Rowguid Column
Jul 28, 2001Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
Wishlist: MOST WANTED Tasks, Sources, Transformations, And Destinations?
Dec 5, 2005Until there's an Integration Services 2.0, what custom components would you most like to see examples of? The documentation team is starting work on the 2nd Web refresh of Books Online and SQL Server samples, anticipated for release around April, and may be able to incorporate some requests as samples or BOL topics.
View 26 Replies View RelatedBetter Practices Wanted For Cascading Inserts Of Hierarchical Data From Staging Tables
Aug 28, 2007I apologize if this has been asked, but I can't find a complete answer.
We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.
I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.
I'll give an example that mirrors our actual solution, and ask if anyone has a better solution?
----------
Code Snippet
CREATE TABLE [dbo].[TABLE_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC)
)
GO
CREATE TABLE [dbo].[TABLE_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A_ID] [int] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC)
)
GO
ALTER TABLE [dbo].[TABLE_B]
ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID])
GO
CREATE TABLE [dbo].[STAGE_TABLE_A](
[A_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[STAGE_TABLE_B](
[B_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[A_Key] [bigint] NOT NULL
)
GO
The STAGING_COLUMN columns are the ones that will be added before, and dropped after.
Code Snippet
DECLARE @TABLE_A_MAP TABLE (
A_ID INT,
A_Key BIGINT
)
INSERT INTO TABLE_A (DATA, STAGING_COLUMN)
OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP
SELECT DATA, A_Key FROM STAGE_TABLE_A
INSERT INTO TABLE_B (A_ID, DATA)
SELECT TAM.A_ID, STB.DATA
FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key
This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.
Here are a few of my constraints:
The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.
This has to finish over the course of a weekend.
This has to be delivered to QA this Friday
Thanks for any help or insight.
HELP Wanted: Installing Full Version Over Trial Version
Dec 7, 2007I have the trial version of SQL Server 2005 installed and have setup a couple of databases. This trial version will expire in another few weeks. I recently purchased a copy of the software and want to install it. Will I lose my databases, user info, etc? How do I go about installing without losing anything?
View 3 Replies View RelatedHelp With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005
Jul 27, 2006Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Mar 28, 2007Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Extended SP
Oct 11, 1999i'm calling a create table SQL from my ASP. There are a lot of fields created
by this sql. Wondering if an extended stored procedure would be a better choice? (can't use a stored procedure as i need to pass arrays) Will it enhance performance and also, can i use VB to make that DLL or i need to use VC?
thanks
Useful Extended SPs
Oct 5, 2004Get current free space for all drives in megabytes (Documented)
EXEC master.dbo.xp_fixeddrives
Determine if file exists (UnDocumented Procedure)
DECLARE @i int
EXECUTE master.dbo.xp_fileexist '\myServerc$oot.ini', @i OUTPUT
SELECT @i
SQL / Extended Proc Dll's
Oct 25, 2000I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
View 1 Replies View Related